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

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！