oracle maxsize

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-1265747/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-1265747/