注:代码来源于网络
查看表空间使用情况
select a.a1 as 表空间名称,
c.c2 as 类型,
c.c3 as 区管理,
b.b2 / 1024 / 1024 as 表空间大小M,
(b.b2 - a.a2) / 1024 / 1024 as 已使用M,
(b.b2 / 1024 / 1024) - ((b.b2 - a.a2) / 1024 / 1024) 未使用M,
substr((b.b2 -
a.a2) / b.b2 * 100,
1,
5) as 利用率
from (select tablespace_name a1, sum(nvl(bytes, 0)) a2
from dba_free_space
group by tablespace_name) a,
(select tablespace_name b1, sum(bytes) b2
from dba_data_files
group by tablespace_name) b,
(select tablespace_name c1, contents c2, extent_management c3
from dba_tablespaces) c
where a.a1 = b.b1
and c.c1 = b.b1;
表空间自动扩展
关闭自动扩展:
alter database datafile 'xxx.dbf' autoextend off;
打开自动扩展
alter database datafile 'xxx.dbf' autoextend on next 10m [maxsize 500m];
表空间扩展
(1)增加数据文件
ALTER TABLESPACE TESTTBS
ADD DATAFILE 'D:/ora/datafile/users.ora' SIZE 500M
AUTOEXTEND ON
NEXT 50M
MAXSIZE 2000M;
增加了一个500M的数据文件,并且可以自动扩展到2G,每次扩展50M。
(2)增加当前数据文件的大小
ALTER DATABASE
DATAFILE 'D:/ora/datafile/users.ora'
RESIZE 50000M;