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_NAMETOTALFREEUSED FREE%USED%
---------------
---------- ---------- ---------- ---------- ----------
MY_01384381.93752.0625 99.46.5371
QUR_DT01128126.93751.0625 99.16.8300
QUR_DT02128126.93751.0625 99.16.8300
QUR_DT03128126.93751.0625 99.16.8300
QUR_DT04128126.93751.0625 99.16.8300
QUR_IDX01128126.93751.0625 99.16.8300
QUR_IDX02128126.68751.3125 98.971.025
QUR_IDX03128126.93751.0625 99.16.8300
QUR_IDX04128126.93751.0625 99.16.8300
SYSAUX50031.5625468.4375 6.31293.68
SYSTEM6808.625671.375 1.26898.73
UNDOTBS17510.37564.625 13.8386.16
USERS53.68751.3125 73.7526.25