1、查看表空间
select sysdate,
df.tablespace_name,
df.totalblocks totalblocks,
df.totalblocks - NVL(fs.freeblocks, 0) usedblocks,
NVL(fs.freeblocks, 0) freeblocks,
round(df.totalspace / 1024 / 1024 / 1024, 2) "totalspace(G)",
round((df.totalspace - NVL(fs.freespace, 0)) / 1024 / 1024 / 1024,
2) "usedspace(G)",
round(NVL(fs.freespace, 0) / 1024 / 1024 / 1024, 2) "freespace(G)",
round((df.totalspace - NVL(fs.freespace, 0)) / df.totalspace * 100,
2) "usedpercent",
round(NVL(fs.freespace, 0) / df.totalspace * 100, 2) "freepercent"
from (select s.tablespace_name,
sum(s.bytes) totalspace,
sum(s.blocks) totalblocks
from dba_data_files s
group by s.tablespace_name) df
LEFT OUTER JOIN (select s.tablespace_name,
sum(s.bytes) freespace,
sum(s.blocks) freeblocks
from dba_free_space s
group by s.tablespace_name) fs ON df.tablespace_name =
fs.tablespace_name
order by "freepercent";
SELECT UPPER (F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR (ROUND ((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2),
'990.99') "使用比",
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、查看表空间文件所在的目录
select * from dba_data_files
3、扩展表空间
alter database datafile 'D:\FARS_DATA\FARS_DATA4.dbf' resize 30000m;
alter tablespace FARS_DATA add datafile 'D:\FARS_DATA\FARS_DATA4.dbf' size 1000m autoextend on next 500m maxsize 20000m;