Oracle maxsize

1 创建表空间chen, 自动增长，最大增长到10M ；

SQL> create tablespace chen datafile '/u01/app/oracle/oradata/orcl/chen01.dbf' size 5M autoextend on next 1M maxsize 10M;

Tablespace created.

2 创建用户，指定默认表空间为chen

SQL> create user chen identified by chen default tablespace chen;

User created.

SQL> alter user chen account unlock;

User altered.

SQL> grant connect,resource to chen;

Grant succeeded.

3 为用户chen 导入数据

[oracle@chen ~]$ exp scott/tiger file='/home/oracle/scott.dmp'

[oracle@chen ~]$ imp system/oracle file='/home/oracle/scott.dmp' fromuser=scott

touser=chen

SQL> conn chen/chen

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

DEPT TABLE

EMP TABLE

SALGRADE TABLE

4 增加数据文件

SQL> alter tablespace chen add datafile '/u01/app/oracle/oradata/orcl/chen02.dbf' size 10M;

Tablespace altered.

SQL> alter tablespace chen add datafile '/u01/app/oracle/oradata/orcl/chen03.dbf' size 10M;

Tablespace altered.

SQL> alter tablespace chen add datafile '/u01/app/oracle/oradata/orcl/chen04.dbf' size 10M autoextend on next 1M maxsize 15M;

SQL> select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files;

………………………………………

/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 5M 10M

/u01/app/oracle/oradata/orcl/chen02.dbf CHEN 10M 0M

/u01/app/oracle/oradata/orcl/chen03.dbf CHEN 10M 0M

/u01/app/oracle/oradata/orcl/chen04.dbf CHEN 10M 15M

数据文件添加成功，说明chen01.dbf maxsize=10M 并不会限制其他数据文件的大小

SQL> conn chen/chen

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

DEPT TABLE

EMP TABLE

SALGRADE TABLE

5 删除数据文件

SQL> alter tablespace chen drop datafile '/u01/app/oracle/oradata/orcl/chen04.dbf';

Tablespace altered.

SQL> alter tablespace chen drop datafile '/u01/app/oracle/oradata/orcl/chen03.dbf';

Tablespace altered.

SQL> alter tablespace chen drop datafile '/u01/app/oracle/oradata/orcl/chen02.dbf';

Tablespace altered.

SQL> select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files;

/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 5M 10M

SQL> alter tablespace chen drop datafile '/u01/app/oracle/oradata/orcl/chen01.dbf';

alter tablespace chen drop datafile '/u01/app/oracle/oradata/orcl/chen01.dbf'

*

ERROR at line 1:

ORA-03261: the tablespace CHEN has only one file

删除失败，说明一个表空间至少一个数据文件

SQL> conn chen/chen

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

DEPT TABLE

EMP TABLE

SALGRADE TABLE

SQL> conn /as sysdba

Connected.

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/chen01.dbf' resize 1M;

alter database datafile '/u01/app/oracle/oradata/orcl/chen01.dbf' resize 1M

*

ERROR at line 1:

ORA-03214: File Size specified is smaller than minimum required

重新创建数据文件失败，原因是数据文件的大小过小，容纳不下所有的数据

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/chen01.dbf' resize 2M;

Database altered.

SQL> select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files;

/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 2M 10M

创建大表

SQL> conn chen/chen

Connected.

SQL> create table t1(id number,qq number);

Table created.

SQL> insert into t1 values(1,1);

1 row created.

SQL> insert into t1 select * from t1;

1 row created.

SQL> /

……………………….

SQL> /

262144 rows created.

SQL> /

insert into t1 select * from t1

*

ERROR at line 1:

ORA-01653: unable to extend table CHEN.T1 by 128 in tablespace CHEN

插入数据失败，原因是数据已经超过maxsize 值, 说明maxsize 对自动增长上限有限制；

SQL> select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files;

……………

/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 10M 10M

手动增大maxsize 的值

SQL> alter tablespace chen add datafile '/u01/app/oracle/oradata/orcl/chen02.dbf' size 5M autoextend on next 1M maxsize 10M;

Tablespace altered.

SQL> insert into t1 select * from t1;

524288 rows created.

SQL> /

insert into t1 select * from t1

*

ERROR at line 1:

ORA-01653: unable to extend table CHEN.T1 by 128 in tablespace CHEN

SQL> select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files;

……………………………………….

/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 10M 10M

/u01/app/oracle/oradata/orcl/chen02.dbf CHEN 10M 10M

SQL> alter tablespace chen drop datafile '/u01/app/oracle/oradata/orcl/chen02.dbf';

alter tablespace chen drop datafile '/u01/app/oracle/oradata/orcl/chen02.dbf'

*

ERROR at line 1:

ORA-03262: the file is non-empty

删除数据文件chen02.dbf 失败，原因是数据文件chen02.dbf 里有数据，非空，不能删除

SQL> conn chen/chen

Connected.

清空大表

SQL> truncate table t1;

Table truncated.

SQL> conn /as sysdba

Connected.

SQL> select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files;

………………..

/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 10M 10M

/u01/app/oracle/oradata/orcl/chen02.dbf CHEN 10M 10M

清空大表后，数据文件的大小并没有回缩，仍然是10M ；

SQL> alter tablespace chen drop datafile '/u01/app/oracle/oradata/orcl/chen02.dbf';

Tablespace altered.

删除数据文件chen02dbf 成功，原因是数据文件chen02.dbf 为空

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/chen01.dbf' resize 200M;

Database altered.

……………………….

/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 200M 10M

重建数据文件chen01 的大小为200M ，200M>10M ，说明maxsize 只对自动扩展的空间有限制，对与手动增加的空间没有限制

SQL> drop tablespace chen including contents and datafiles;

Tablespace dropped.

SQL> create tablespace chen datafile '/u01/app/oracle/oradata/orcl/chen01.dbf' size 5M autoextend on next 1M maxsize 6M;

Tablespace created.

SQL> select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files where tablespace_name='CHEN';

FILE_NAME TABLESPACE F M

--------------------------------------------- ---------- ---------- ----------

/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 5M 6M

手动修改maxsize的值

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/chen01.dbf' autoextend on maxsize 15M;

Database altered.

SQL> select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files where tablespace_name='CHEN';

FILE_NAME TABLESPACE F M

--------------------------------------------- ---------- ---------- ----------

/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 5M 15M

将maxsize 设置为unlimited

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/chen01.dbf' autoextend on maxsize unlimited;

Database altered.

当maxsize 设置为unlimited 时，其大小为32G

SQL> select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files where tablespace_name='CHEN';

FILE_NAME TABLESPACE F M

--------------------------------------------- ---------- ---------- ----------

/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 5M 32767.9843

75M

SQL> select 32767.984375/1024 from dual;

32767.984375/1024

-----------------

31.9999847

数据文件最大的大小是2^22*db_block_size ，对于8k 的db block size ，最大大小是32G

SQL> show parameter db_block_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_block_size integer 8192

SQL> select power(2,22)*8192/1024/1024/1024||'G' m from dual;

M

----------

32G

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！

oracle maxsize