oracle中表空间

来源：互联网发布：淘宝方舟生存进化steam 编辑：程序博客网时间：2024/06/10 03:56

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;

0 0