1、查看表空间情况:
select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used", round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total
from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) used
from dba_extents group by tablespace_name) u,
(select tablespace_name, round(sum(bytes/(1024*1024))) free
from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name and a.tablespace_name = u.tablespace_name;
或者
SELECT upper(f.tablespace_name) 表空间名,
d.Tot_grootte_Mb "表空间大小(M)",
d.Tot_grootte_Mb - f.total_bytes "已使用空间(M)",
round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2) "使用比",
f.total_bytes "空闲空间(M)",
f.max_bytes "最大块(M)"
FROM
(SELECT tablespace_name,
round(SUM(bytes)/(1024*1024),2) total_bytes,
round(MAX(bytes)/(1024*1024),2) max_bytes
FROM sys.dba_free_space
GROUP BY tablespace_name) f,
(SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
FROM sys.dba_data_files dd
GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name
ORDER BY 4 DESC;
2、查看Oracle数据文件:
select * from v$datafile ;
select name,bytes/1024/1024 兆 from v$datafile;
3、给表空间增加数据文件:
ALTER TABLESPACE "WORKFLOW01"
ADD
DATAFILE 'D:\ORACLE9\WORKFLOW02.ORA' SIZE 128M;
或(大小无限制)
ALTER TABLESPACE "ECODEPLATFORM"
ADD
DATAFILE '/opt/oracle10g/oradata/OANET/ECODEPLATFORM2.dbf'
SIZE 64M
AUTOEXTEND ON NEXT 64M
MAXSIZE UNLIMITED;
4、调整表空间现有数据文件大小
ALTER DATABASE DATAFILE 'D:\ORACLE9\WORKFLOW01.ORA' RESIZE 305M;
提示:
在生产环境上具体调整时,可以先用EM生成示例语句,修改后再在生产环境中使用。