表空间查询

----查看表空间
select fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from dba_free_space
group by tablespace_name) fs
where df.tablespace_name = fs.tablespace_name;

--扩表空间
CREATE TEMPORARY TABLESPACE tbs_data_sig_etl_rpt
TEMPFILE '/opt/oracle/oradata/tbs_data_sig_etl_rpt/vg01_tbs_sig_etl_rpt_2000m_01'
SIZE 2G AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;


Alter database datafile '/opt/oracle/oradata/tbs_data_sig_user_rpt5/tbs_data_sig_user_rpt5_01.dbf' resize 7000m;


/** 获取表空间利用率 **/
WITH TOTAL_SPACE AS
(SELECT TABLESPACE_NAME, SUM(BYTES) AS TOTAL, SUM(
(CASE
WHEN MAXBYTES > 0
THEN MAXBYTES
ELSE BYTES
END)
) AS MAXTOTAL
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME),
USED_SPACE AS
(SELECT TABLESPACE_NAME, SUM(BYTES) AS USED
FROM DBA_SEGMENTS
GROUP BY TABLESPACE_NAME)
SELECT TABLESPACE_NAME, ROUND(US.USED*100/TS.TOTAL, 2) AS PERCENTAGE,
ROUND(US.USED*100/TS.MAXTOTAL, 2) AS MAX_PERCENTAGE
FROM TOTAL_SPACE TS NATURAL JOIN USED_SPACE US
ORDER BY PERCENTAGE DESC;