Oracle表空間擴容

Oracle表空間擴容

 

1 建立表空間

SQL> create tablespace my_01 logging datafile '/oracle/app/oradata/mytablespace/my_01.dbf' size 128M;

表空間已建立。

 

在表空間上建立表

SQL> create table t1 (id int) tablespace my_01;

表已建立。

 

插入資料

SQL> insert into t1 values(10);

已建立 1 行。

 

2 表空間擴容

方法一：改變資料檔案的大小

SQL> alter database datafile '/oracle/app/oradata/mytablespace/my_01.dbf' resize 256M;

資料庫已更改。

 

驗證：

SQL> select bytes/1024/1024, tablespace_name from dba_data_files where tablespace_name='MY_01';

BYTES/1024/1024 TABLESPACE_NAME

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

            256 MY_01

 

SQL> select table_name from dba_tables where tablespace_name='MY_01';

TABLE_NAME

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

T1

 

SQL> select * from t1;

        ID

----------

        10

 

方法二：新增資料檔案

SQL> alter tablespace my_01 add datafile '/oracle/app/oradata/mytablespace/my_02.dbf' size 128M;

表空間已更改。

 

驗證：

SQL> select sum(bytes)/1024/1024, tablespace_name from dba_data_files where tablespace_name='MY_01' group by tablespace_name;

SUM(BYTES)/1024/1024 TABLESPACE_NAME

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

                 384 MY_01

 

SQL> select table_name from dba_tables where tablespace_name='MY_01';

TABLE_NAME

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

T1

 

SQL> select * from t1;

        ID

----------

        10

 

3 檢視錶空間大小和使用率

select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%" from 
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a, 
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;

 

TABLESPACE_NAME      TOTAL       FREE       USED FREE%      USED%

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

MY_01                  384   381.9375     2.0625 99.46      .5371

QUR_DT01               128   126.9375     1.0625 99.16      .8300

QUR_DT02               128   126.9375     1.0625 99.16      .8300

QUR_DT03               128   126.9375     1.0625 99.16      .8300

QUR_DT04               128   126.9375     1.0625 99.16      .8300

QUR_IDX01              128   126.9375     1.0625 99.16      .8300

QUR_IDX02              128   126.6875     1.3125 98.97      1.025

QUR_IDX03              128   126.9375     1.0625 99.16      .8300

QUR_IDX04              128   126.9375     1.0625 99.16      .8300

SYSAUX                 500    31.5625   468.4375 6.312      93.68

SYSTEM                 680      8.625    671.375 1.268      98.73

UNDOTBS1                75     10.375     64.625 13.83      86.16

USERS                    5     3.6875     1.3125 73.75      26.25