テーブルスペース

●空き容量
SELECT * FROM USER_TS_QUOTAS;

SELECT TABLESPACE_NAME, USERNAME,
BYTES/1024/1024 'USED(MB)',
MAX_BYTES/1024/1024 'MAX(MB)',
BYTES/MAX_BYTES * 100 'USED(%)'
FROM DBA_TS_QUOTAS
WHERE USERNAME LIKE 'USERNAME'
ORDER BY TABLESPACE_NAME
/

●テーブルスペースことの空き容量確認
select tablespace_name, sum(bytes)/1024/1024 as freeMB
from user_free_space
group by tablespace_name
/

●使用状況確認
SQL> select * from dba_tablespace_usage_metrics;

●使用済みテーブル容量：

SELECT substr(segment_name, 1, 30), bytes/1024/1024 as "Size in MB"
FROM user_segments
WHERE tablespace_name = 'TBS4001';

●セグメント容量確認
SELECT SUM(bytes)/1024/1024 as "SUMMARY SIZE"
FROM user_segments
WHERE tablespace_name = 'TBS4001'
/

SUMMARY SIZE
------------
542.75

SQL>

●空き容量確認
SELECT
A.TABLESPACE_NAME,
USED_KB,
AVAILABLE_KB,
ROUND(USED_KB/AVAILABLE_KB*100)||'%' PERCENTAGE
FROM (SELECT
TABLESPACE_NAME,
SUM(BYTES)/1024 AVAILABLE_KB
FROM DBA_DATA_FILES
WHERE STATUS='AVAILABLE'
GROUP BY TABLESPACE_NAME
) A,
(SELECT
TABLESPACE_NAME,
SUM(BYTES)/1024 USED_KB
FROM DBA_SEGMENTS
GROUP BY TABLESPACE_NAME
) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
;

TABLESPACE_NAME USED_KB AVAILABLE_KB PERCENTAGE
------------------------- ---------- ------------ -----------------------------------------
SYSAUX 412352 1024000 40%
SYSTEM 459136 2048000 22%
TBS1002 15087872 20480000 74%
TBS1003 13953792 20480000 68%
TBS1004 19832768 20480000 97%
TBS1005 13305664 20480000 65%
UNDO01 4928 3072000 0%
WORK 137024 3072000 4%

8行が選択されました。

SQL>
●テーブルスペース内の各ブロック確認
SELECT
TABLESPACE_NAME,
FILE_ID,
BLOCK_ID AS START_BLOCK,
BLOCK_ID + BLOCKS - 1 AS END_BLOCK,
BYTES AS SIZE_BYTES,
BLOCKS AS BLOCK_CNT
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = 'TBS4001'
ORDER BY BLOCK_ID