表空间查询

----查看表空间
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;