tablespace 查詢

 tablespace 查詢

@查tablespace使用空間

SELECT
d.tablespace_name "Name",
d.status "Status",
d.CONTENTS,
d.EXTENT_MANAGEMENT,
a.AUTOEXTENSIBLE,
((NVL(a.BYTES,0) - NVL(f.BYTES,0))/1024/1024) AS "USE(MB)",
NVL(a.BYTES/1024/1024, 0) AS "Size (M)",
NVL(a.MAXBYTES/1024/1024,0) AS "MAX_SIZE(MB)",
ROUND(NVL((NVL(a.BYTES,0)-NVL(f.BYTES,0))/a.MAXBYTES * 100, 0),3) as "Used%"
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES, SUM(MAXBYTES) MAXBYTES,AUTOEXTENSIBLE
FROM dba_data_files
GROUP BY tablespace_name,AUTOEXTENSIBLE) a,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name
AND d.tablespace_name = f.tablespace_name
AND a.AUTOEXTENSIBLE = 'YES'
UNION ALL
SELECT
d.tablespace_name "Name",
d.status "Status",
d.CONTENTS,
d.EXTENT_MANAGEMENT,
a.AUTOEXTENSIBLE,
((NVL(a.BYTES,0) - NVL(f.BYTES,0))/1024/1024) AS "USE(MB)",
NVL(a.BYTES/1024/1024, 0) AS "Size (M)",
NVL(a.MAXBYTES/1024/1024,0) AS "MAX_SIZE(MB)",
ROUND(NVL((NVL(a.BYTES,0)-NVL(f.BYTES,0))/a.BYTES * 100, 0),3) as "Used%"
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES, SUM(MAXBYTES) MAXBYTES,AUTOEXTENSIBLE
FROM dba_data_files
GROUP BY tablespace_name,AUTOEXTENSIBLE) a,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name
AND d.tablespace_name = f.tablespace_name
AND a.AUTOEXTENSIBLE = 'NO' order by 9;

@查詢 tablespace裡有哪些table，刪除tablespace時，要先查詢
select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables;
select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where tablespace_name='SYSTEM';
select FILE_NAME,TABLESPACE_NAME, BYTES,sTATUS,USER_BYTES from dba_data_files;

@查tablespace 的 table大小

SELECT segment_name, segment_type, bytes/1024/1024 AS size_mb
FROM dba_segments
WHERE owner = 'YOUR_SCHEMA_NAME'
AND segment_name = 'YOUR_TABLE_NAME'
AND segment_type = 'TABLE';




查 tablespace name 表空間名
select tablespace_name from sys.dba_tablespaces;

查tablespace 已使用空間
select TABLESPACE_NAME,
round(sum(bytes)/(1024*1024),2) "已使用空間(M)"
from sys.dba_segments GROUP BY TABLESPACE_NAME;

查表空間大小
SELECT DD.TABLESPACE_NAME,
　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) "表空間大小(M)"
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME;

查表空間空閒空間
SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) "空閒空間(M)",
ROUND(MAX(BYTES) / (1024 * 1024), 2) "最大塊(M)"
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME ;


select TABLESPACE_NAME,
round(sum(bytes)/(1024*1024),2) "已使用空間(M)",
ROUND(SUM(BYTES) / (1024 * 1024), 2) "表空間大小(M)",
round(sum(bytes)/(1024*1024),2)-ROUND(SUM(BYTES) / (1024 * 1024), 2) "空閒空間(M)"
from SYS.DBA_DATA_FILES GROUP BY TABLESPACE_NAME;
(select TABLESPACE_NAME,round(sum(bytes)/(1024*1024),2) "已使用空間(M)" from sys.dba_segments GROUP BY TABLESPACE_NAME;)




查 剩餘空間
以下主要用到 DBA_DATA_FILES 和 DBA_FREE_SPACE這兩個View, 會呈現整個資料庫所有 tablespace 的使用狀況;
如果只想查某個 tablespace, 請將以下 -- WHERE TABLESPACE_NAME='My_Tablespace_Name' , 解除註解, 同時置換為實際的 tablespace name 即可.

SELECT A.TABLESPACE_NAME, A.FILE_NAME,
ROUND(B.FREE_GB,2) AS FREE_GB ,
ROUND(A.TOTAL_GB - b.FREE_GB) AS USED_GB ,
ROUND(A.TOTAL_GB,2) AS TOTAL_GB ,
ROUND(((A.TOTAL_GB - B.FREE_GB)/ A.TOTAL_GB )*100,2) AS USED_PERCENT,
ROUND((B.FREE_GB/ A.TOTAL_GB )*100,2) AS FREE_PERCENT
FROM
(
SELECT TABLESPACE_NAME, FILE_NAME,
SUM(BYTES)/ (1024*1024*1024) AS TOTAL_GB
FROM DBA_DATA_FILES
-- WHERE TABLESPACE_NAME='My_TableSpace_Name'
GROUP BY TABLESPACE_NAME, FILE_NAME
)A,
(
SELECT TABLESPACE_NAME,
SUM(BYTES) / (1024*1024*1024) AS FREE_GB
FROM DBA_FREE_SPACE
-- WHERE TABLESPACE_NAME='My_TableSpace_Name'
GROUP BY TABLESPACE_NAME
) B
WHERE A.TABLESPACE_NAME= B.TABLESPACE_NAME
;