Oracle表空间

1、查询表空间容量

select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name;

2、查询表空间利用率

select total.tablespace_name,
       round(total.MB,2)                       as total_MB,
       round(total.MB-free.MB,2)               as used_MB,
       round((1-free.MB/total.MB)*100,2)||'%'  as used_PCT
from
       (select tablespace_name,
               sum(bytes)/1024/1024 as MB
        from dba_free_space
        group by tablespace_name) free,
       (select tablespace_name,
               sum(bytes)/1024/1024 as MB
        from dba_data_files
        group by tablespace_name) total
where free.tablespace_name=total.tablespace_name;

3、清空表空间（并非删除）

drop user user_name cascade;

4、修改表空间大小

alter database datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF' resize 5G;

5、自动增加表空间大小

alter database datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF’ autoextend on next 100M maxsize 1000M;

6、增加表空间数据文件

alter tablespace system add datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM02.DBF' size 200M;