Oracle查看并增加表空间大小

1、查看表空间使用率:

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;

2、查看表空间文件存储位置;

select tablespace_name,file_id,file_name,round(bytes/(1024*1024),0) total_space   
from dba_data_files
order by tablespace_name;

3、增加表空间;

ALTER TABLESPACE 表空间名称 ADD DATAFILE
'位置' SIZE 100M
AUTOEXTEND ON NEXT 100M maxsize unlimited;

举例:

ALTER TABLESPACE USERS ADD DATAFILE
'E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS05.DBF' SIZE 100M
AUTOEXTEND ON NEXT 100M maxsize unlimited;