Oracle管理命令之表空间

常用管理语句

--相关参数
dba_create_file_dest
dba_create_online_log_dest_n
dba_recovery_file_dest

----创建表空间
--创建语法：
CREATE [UNDO]  TABLESPACE tablespace_name          
[DATAFILE datefile_spec1 [,datefile_spec2] ......   
[{MININUM EXTENT integer [k|m]   
|BLOCKSIZE integer [k]   
|logging clause | FORCE LOGGING   
|DEFAULT {data_segment_compression} storage_clause   
|[online|offline]   
|[PERMANENT|TEMPORARY]   
|extent_manager_clause   
|segment_manager_clause}]
create bigfile tablespace test2 datafile '/oradata/anixfs/test2.dbf' size 50M   extent    management local  segment space management auto;
create temporary tablespace test4 datafile '/oradata/anixfs/test2.dbf'size50M reuse extent    management local uniform size 8M;
create  tablespace  test1  datafile '+YONG_DG' size 5G AUTOEXTEND off  extent    management local;
create undo tablespace test2 datafile  '/oradata/anixfs/test2.dbf' size 50M reuse  extent    management local;

--表空间压缩
create  tablespace  dbmon datafile '/ldata/datafile/lymon/dbmon01.dbf' size 5G   compress for OLTP;  ----11g压缩表空间
create  tablespace  dbmon datafile '/ldata/datafile/lymon/dbmon01.dbf' size 5G  default row store compress advanced;  ----12c压缩表空间
alter tablespace tbs_name default row store compress advanced;
alter tablespace tbs_name default compress basic; ----压缩程度为basic
alter tablespace tbs_name default nocompress ;  -----禁用压缩

-------重命名表空间
alter tablespace tbs_old_name rename to tbs_new_name;
--增加数据文件
alter tablespace test1 add datafile '/opt/oracle/oradata/rcountdb/rcountdb03.dbf' size 8000M;
alter tablespace TBS_ODS_201007 add datafile '+CDRDG' size 32767M autoextend off; 
alter tablespace TBS_ODS_201007 add datafile '+CDRDG' size 10239M autoextend on next 64M;
alter tablespace TBS_ODS_201008 add datafile '+ODSNDMCDG' size 32767M,'+ODSNDMCDG' size 32767M autoextend off; 

--重命名表空间
alter tablespace test rename to test1;

--重命名数据文件
alter database rename file '/oradata/datafile/anix/test02.dbf ' To '/oradata/datafile/anix/test03.dbf ';

--修改数据文件大小
alter database datafile '/opt/oracle/oradata/rcountdb/rcountdb02.dbf' resize  15G;
------数据文件状态
alter tablespace test1 offline; 
alter tablespace test1 online; 
alter database datafile '/oradata/datafile/anix/test01.dbf' offline|online;
注：非归档模式只能先offline表空间之后才能offline数据文件
------删除数据文件
alter database datafile '/oradata/datafile/anix/test02.dbf' offline  drop;
------删除表空间
drop  tablespace YONG_UNFORM including contents and datafiles cascade constraints;
--删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
--删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
--删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
--删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace YONG_UNFORM including contents and datafiles CASCADE CONSTRAINTS;

--移动数据文件
RMAN> sql "alter tablespace dlm  offline";
RMAN> copy datafile '/archive/dlm/dlm_data.dbf' to '+dlm_data/dlm/datafile/DLM.268.873998453';
SQL> alter database rename file '/archive/dlm/dlm_data.dbf' to '+dlm_data/dlm/datafile/DLM.268.873998453';
SQL> sql " alter tablespace dlm online ";
--12c在线移动数据文件
alter database move  datafile 'old_datafile_name' to 'new_datafile_name'  [keep|reuse] ;  

表空间与数据文件查询

--查询表空间数据文件
set linesize 1000 pagesize 500
col file_name for a150
col TABLESPACE_NAME for a30
select tablespace_name,
FILE_ID,
      -- STATUS,
       bytes / 1024 / 1024  size_mb,
      -- ONLINE_STATUS,
       file_name
  from dba_data_files
 where  tablespace_name in ('TBS_SY','TBS_CRM','TBS_CHANGCHUN')
 --and file_name like '/opt/oracle/oradata3%'
 order by tablespace_name,FILE_ID, file_name;

--表空间使用率

set linesize 1000 pagesize 500
col tablespace_name for a30
with free_space as
 (SELECT /*+ materialize */
   tablespace_name, file_id, SUM(BYTES) BYTES
    FROM dba_free_space
  --where bytes > 1024 * 1024
   GROUP BY tablespace_name, file_id)
SELECT df.tablespace_name,
       COUNT(*) dnt,
       ROUND(SUM(df.BYTES) / 1048576 / 1024, 2) size_gb,
       ROUND(SUM(free.BYTES) / 1048576 / 1024, 2) free_gb,
       ROUND(SUM(df.BYTES) / 1048576 / 1024 -
             SUM(free.BYTES) / 1048576 / 1024,
             2) used_gb,
       100 - ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_used,
       ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_free
  FROM dba_data_files df, free_space free
 WHERE df.tablespace_name = free.tablespace_name(+)
   AND df.file_id = free.file_id(+)
--and df.tablespace_name like 'IRM_DATA%'
 GROUP BY df.tablespace_name
 ORDER BY pct_free;

--妥协做法
select a.TABLESPACE_NAME, a.tbs_mb, b.used_mb, a.tbs_mb - b.used_mb free_mb, a.cnt_df, round((b.used_mb /
              a.tbs_mb)*100,
              2) user_pct
  from (select TABLESPACE_NAME, round(sum(BYTES) / 1024 / 1024, 2) tbs_mb, count(*) cnt_df
           from dba_data_files
          group by TABLESPACE_NAME) a
  join (select tablespace_name, round(sum(a.BYTES) / 1024 / 1024, 2) used_mb
          from dba_segments a
         group by tablespace_name) b
    on a.TABLESPACE_NAME = b.TABLESPACE_NAME
    order by user_pct  desc;

--表空间查询慢，收集统计信息
select * from table(dbms_xplan.display_cursor(null,null,'allstats +alias +outline'));
execute dbms_stats.gather_fixed_objects_stats;
execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'RECYCLEBIN$');
----或者直接清理回收站

--查看pdb表空间或数据文件信息
set linesize 1000 pagesize 500
col FILE_NAME for a70
select con.con_id,
       con.DBID,
       con.name,
       con.OPEN_MODE,
       tbs.TABLESPACE_NAME,
       tbs.STATUS          tbs_status,
       dbf.STATUS          dbf_STATUS,
       dbf.FILE_NAME
from   cdb_data_files dbf, cdb_tablespaces tbs, v$containers con
where  con.CON_ID = tbs.CON_ID
and    tbs.CON_ID = dbf.CON_ID
and    tbs.TABLESPACE_NAME = dbf.TABLESPACE_NAME
and    con.name = ''
order  by con_id, TABLESPACE_NAME, FILE_NAME;


--表空间中段大小排序
set linesize 1000 pagesize 500
col SEGMENT_NAME for a30
col OWNER for a20
select *
  from (select *
          from (select OWNER, SEGMENT_NAME, sum(BYTES) / 1024 / 1024/1024 size_gb
                  from dba_segments a
                 where a.tablespace_name like  'BOCO%'
                       --and SEGMENT_NAME like ''
                      -- and owner='XJMON'
                 group by OWNER, SEGMENT_NAME )
        --where size_gb > 2
         order by size_gb desc)
 where rownum < 21;

--表空间历史增长情况
select b.name,
       a.rtime,
       round(a.tablespace_usedsize*8/1024/1024,2) used_gb,
       round(a.tablespace_size*8/1024/1024,2) size_gb,
       round(100 * a.tablespace_usedsize / a.tablespace_size,2) used_percent
  from dba_hist_tbspc_space_usage a,
       (select t2.name, max(rtime) rtime, min(tablespace_id) tablespace_id
          from dba_hist_tbspc_space_usage t1
         inner join v$tablespace t2
            on t1.tablespace_id = t2.TS#
         where t2.NAME = upper('UNDOTBS2')
         group by name, substr(rtime, 1, 10)) b
 where a.tablespace_id = b.tablespace_id
   and a.rtime = b.rtime
 order by to_date(a.rtime,'mm/dd/yyyy hh24:mi:ss');


--查询表空间  from toad
SELECT ts.tablespace_name,
       ts.status,
       ts.contents,
       ts.extent_management,
       ts.bigfile,
       size_info.megs_alloc_gb,
       size_info.megs_free_gb,
       size_info.megs_used_gb,
       size_info.pct_free,
       size_info.pct_used,
       size_info.max
  FROM (SELECT a.tablespace_name,
               round(a.bytes_alloc / 1024 / 1024/1024) megs_alloc_gb,
               round(nvl(b.bytes_free, 0) / 1024 / 1024/1024) megs_free_gb,
               round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024/1024) megs_used_gb,
               round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
               100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
               round(maxbytes / 1048576) MAX
          FROM (SELECT f.tablespace_name,
                       SUM(f.bytes) bytes_alloc,
                       SUM(decode(f.autoextensible, 'YES', f.maxbytes, 'NO',
                                  f.bytes)) maxbytes
                  FROM dba_data_files f
                 GROUP BY tablespace_name) a,
               (SELECT f.tablespace_name, SUM(f.bytes) bytes_free
                  FROM dba_free_space f
                 GROUP BY tablespace_name) b
         WHERE a.tablespace_name = b.tablespace_name(+)
        UNION ALL
        SELECT h.tablespace_name,
               round(SUM(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
               round(SUM((h.bytes_free + h.bytes_used) -
                         nvl(p.bytes_used, 0)) / 1048576) megs_free,
               round(SUM(nvl(p.bytes_used, 0)) / 1048576) megs_used,
               round((SUM((h.bytes_free + h.bytes_used) -
                          nvl(p.bytes_used, 0)) /
                     SUM(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
               100 - round((SUM((h.bytes_free + h.bytes_used) -
                                nvl(p.bytes_used, 0)) /
                           SUM(h.bytes_used + h.bytes_free)) * 100) pct_used,
               round(SUM(f.maxbytes) / 1048576) MAX
          FROM sys.v_$TEMP_SPACE_HEADER h,
               sys.v_$Temp_extent_pool  p,
               dba_temp_files           f
         WHERE p.file_id(+) = h.file_id
           AND p.tablespace_name(+) = h.tablespace_name
           AND f.file_id = h.file_id
           AND f.tablespace_name = h.tablespace_name
         GROUP BY h.tablespace_name) size_info,
       sys.dba_tablespaces ts
 WHERE ts.tablespace_name = size_info.tablespace_name
 ORDER BY tablespace_name;

--12c表空间
set linesize 1000 pagesize 500
col tablespace_name for a25
col name for a20
with free_size as
 (SELECT CON_ID, tablespace_name, SUM(BYTES) BYTES, MAX(BYTES) maxbytes
    FROM cdb_free_space free
   GROUP BY CON_ID, tablespace_name),
total_size as
 (select CON_ID, TABLESPACE_NAME, sum(BYTES) BYTES
    from cdb_data_files a
   group by CON_ID, TABLESPACE_NAME)
select t.con_id,
       c.name,
       t.tablespace_name,
       round(t.BYTES / 1024 / 1024 / 1024, 2) total_gb,
       ROUND(f.BYTES / 1024 / 1024 / 1024, 4) free_gb,
       round((t.BYTES - f.BYTES) / 1024 / 1024 / 1024, 2) used_gb,
       ROUND(f.maxbytes / 1024 / 1024, 2) maxfree_mb,
       100 - ROUND(100.0 * f.BYTES / t.BYTES, 2) pct_used
  from total_size t
  join free_size f
    on t.CON_ID = f.CON_ID
       and t.TABLESPACE_NAME = f.TABLESPACE_NAME
  left join v$containers c
    on t.CON_ID = c.CON_ID
 order by con_id, pct_used desc;

其他表空间相关查询

--查看段在快照期间内的空间变化情况
column owner format a16
column object_name format a36
column start_day format a11
column block_increase format 9999999999

SELECT obj.owner,
       obj.object_name,
       to_char(sn.BEGIN_INTERVAL_TIME, 'RRRR-MON-DD') start_day,
       SUM(a.db_block_changes_delta) block_increase
  FROM dba_hist_seg_stat a, dba_hist_snapshot sn, dba_objects obj
 WHERE sn.snap_id = a.snap_id
   AND obj.object_id = a.obj#
   AND obj.owner NOT IN ('SYS', 'SYSTEM')
   AND end_interval_time BETWEEN to_timestamp('01-JAN-2000', 'DD-MON-RRRR') AND
       to_timestamp('02-FEB-2013', 'DD-MON-RRRR')
 GROUP BY obj.owner,
          obj.object_name,
          to_char(sn.BEGIN_INTERVAL_TIME, 'RRRR-MON-DD')
 ORDER BY obj.owner, obj.object_name ;
 
 
--表空间数据文件使用明细
SET PAGESIZE 1000 LINES 1320 ECHO OFF VERIFY OFF FEEDB OFF SPACE 1 TRIMSP ON
COMPUTE SUM OF a_byt t_byt f_byt ON REPORT
BREAK ON REPORT ON tablespace_name ON pf
COL tablespace_name FOR A17   TRU HEAD 'Tablespace|Name'
COL file_name       FOR A70   TRU HEAD 'Filename'
COL a_byt           FOR 9,990.999 HEAD 'Allocated|GB'
COL t_byt           FOR 9,990.999 HEAD 'Current|Used GB'
COL f_byt           FOR 9,990.999 HEAD 'Current|Free GB'
COL pct_free        FOR 990.0     HEAD 'File %|Free'
COL pf              FOR 990.0     HEAD 'Tbsp %|Free'
COL seq NOPRINT
DEFINE b_div=1073741824
--
SELECT 1 seq,
       b.tablespace_name,
       nvl(x.fs, 0) / y.ap * 100 pf,
       b.file_name file_name,
       b.bytes / &&b_div a_byt,
       NVL((b.bytes - SUM(f.bytes)) / &&b_div, b.bytes / &&b_div) t_byt,
       NVL(SUM(f.bytes) / &&b_div, 0) f_byt,
       NVL(SUM(f.bytes) / b.bytes * 100, 0) pct_free
  FROM dba_free_space f,
       dba_data_files b,
       (SELECT y.tablespace_name, SUM(y.bytes) fs
          FROM dba_free_space y
         GROUP BY y.tablespace_name) x,
       (SELECT x.tablespace_name, SUM(x.bytes) ap
          FROM dba_data_files x
         GROUP BY x.tablespace_name) y
 WHERE f.file_id(+) = b.file_id
   AND x.tablespace_name(+) = y.tablespace_name
   AND y.tablespace_name = b.tablespace_name
   AND f.tablespace_name(+) = b.tablespace_name
 GROUP BY b.tablespace_name,
          nvl(x.fs, 0) / y.ap * 100,
          b.file_name,
          b.bytes
UNION
SELECT 2 seq,
       tablespace_name,
       j.bf / k.bb * 100 pf,
       b.name file_name,
       b.bytes / &&b_div a_byt,
       a.bytes_used / &&b_div t_byt,
       a.bytes_free / &&b_div f_byt,
       a.bytes_free / b.bytes * 100 pct_free
  FROM v$temp_space_header a,
       v$tempfile b,
       (SELECT SUM(bytes_free) bf FROM v$temp_space_header) j,
       (SELECT SUM(bytes) bb FROM v$tempfile) k
 WHERE a.file_id = b.file#
 ORDER BY 1, 2, 4, 3;

--检查当前各表空间的数据文件，确认对应表空间使用的ASM磁盘组
SET LINES 200 PAGES 1000
COL tablespace_name FORMAT a21;
COL file#           FORMAT 999;
COL file_name       FORMAT a68;
COL status          FORMAT a9;
COL auex            FORMAT a4;
COL size(mb)        FORMAT 9999999;
SELECT TABLESPACE_NAME,
       FILE_ID "FILE#",
       FILE_NAME,
       ROUND(BYTES / 1024 / 1024) "SIZE(MB)",
       ROUND(MAXBYTES / 1024 / 1024) "MAXSIZE(MB)",
       --BLOCKS, 
       STATUS,
       AUTOEXTENSIBLE "AUEX"
  FROM DBA_DATA_FILES
 ORDER BY TABLESPACE_NAME, FILE_ID;


-----检查ASM磁盘组剩余空间
set linesize 1000 pagesize 500
column gnum  format 999;
column gname format a12;
column au_mb format 9999;
column state format a10;
column type  format a10;
col state for a20
col type for a20
col  total_gb for 999999.99
col  free_gb for 999999.99
SELECT group_number gnum,
       NAME gname,
       sector_size,
       block_size,
       allocation_unit_size / 1024 / 1024/1024 alloc_Gb,
       state,
       TYPE,
       total_mb/1024 total_GB,
       free_mb/1024 free_gb,
       round(a.FREE_MB/a.TOTAL_MB *100,2) pct_free,
       required_mirror_free_mb/1024 rm_gb,
       usable_file_mb/1024 uf_gb,
       offline_disks
  FROM v$asm_diskgroup  order by  pct_free;

--磁盘组磁盘信息
set linesize 1000 pagesize 500
col name for a20
col state for a20
col path for a40
col  total_gb for 999999.99
col  free_gb for 999999.99  
SELECT a.NAME,
       b.PATH,
       b.STATE,
       b.MOUNT_STATUS,
       b.TOTAL_MB/1024 total_gb,
       b.FREE_MB/1024 free_gb
  FROM v$asm_disk b, v$asm_diskgroup a
 WHERE a.GROUP_NUMBER = b.GROUP_NUMBER order by 2,1;

--查询表空间一周的增长
SELECT C.tablespace_name,
       D."Total(MB)",
       D."Used(MB)" - C."Used(MB)" AS "Increment(MB)",
       to_char(next_day(trunc(SYSDATE), 2) - 7, 'yyyy/mm/dd') || '--' ||
       to_char(next_day(trunc(SYSDATE), 2) - 7, 'yyyy/mm/dd') "TIME"
  FROM (SELECT B.name tablespace_name,
               CASE
                 WHEN B.name NOT LIKE 'UNDO%' THEN
                  round(A.tablespace_size * 8 / 1024)
                 WHEN B.name LIKE 'UNDO%' THEN
                  round(A.tablespace_size * 8 / 1024 / 2)
               END AS "Total(MB)",
               round(A.tablespace_usedsize * 8 / 1024) "Used(MB)",
               A.rtime
          FROM DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B
         WHERE A.tablespace_id = B.TS#
           AND to_char(to_date(REPLACE(rtime, '/', NULL),
                               'mmddyyyy hh24:mi:ss'), 'yyyymmdd hh24:mi') =
               to_char(next_day(trunc(SYSDATE), 2) - 14, 'yyyymmdd hh24:mi')) C,
       (SELECT B.name tablespace_name,
               CASE
                 WHEN B.name NOT LIKE 'UNDO%' THEN
                  round(A.tablespace_size * 8 / 1024)
                 WHEN B.name LIKE 'UNDO%' THEN
                  round(A.tablespace_size * 8 / 1024 / 2)
               END AS "Total(MB)",
               round(A.tablespace_usedsize * 8 / 1024) "Used(MB)",
               A.rtime
          FROM DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B
         WHERE A.tablespace_id = B.TS#
           AND to_char(to_date(REPLACE(rtime, '/', NULL),
                               'mmddyyyy hh24:mi:ss'), 'yyyymmdd hh24:mi') =
               to_char(next_day(trunc(SYSDATE), 2) - 7, 'yyyymmdd hh24:mi')) D
 WHERE C.tablespace_name = D.tablespace_name;

 

表空间监控

 
-----


----空间增长监控
-- Create table
create table TAB_SNAP_TABLESPACE_DAY
(
  tablespace_name VARCHAR2(30),
  datafile_count  NUMBER,
  size_gb         NUMBER,
  free_gb         NUMBER,
  used_gb         NUMBER,
  maxfree         NUMBER,
  pct_used        NUMBER,
  pct_free        NUMBER,
  snap_time       DATE
)
tablespace INDEXTS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );



CREATE OR REPLACE PROCEDURE p_snap_kongjian_day AS
BEGIN
  INSERT INTO tab_snap_segment_day
    SELECT owner,
           segment_name,
           partition_name,
           segment_type,
           tablespace_name,
           bytes,
           SYSDATE
      FROM dba_segments;
  COMMIT;

  INSERT INTO tab_snap_lob_day
    SELECT owner,
           table_name,
           column_name,
           segment_name,
           tablespace_name,
           index_name,
           SYSDATE
      FROM dba_lobs;
  COMMIT;

  INSERT INTO tab_snap_tablespace_day
    SELECT df.tablespace_name,
           COUNT(*) datafile_count,
           ROUND(SUM(df.BYTES) / 1048576 / 1024, 2) size_gb,
           ROUND(SUM(free.BYTES) / 1048576 / 1024, 2) free_gb,
           ROUND(SUM(df.BYTES) / 1048576 / 1024 -
                 SUM(free.BYTES) / 1048576 / 1024,
                 2) used_gb,
           ROUND(MAX(free.maxbytes) / 1048576 / 1024, 2) maxfree,
           100 - ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_used,
           ROUND(100.0 * SUM(free.BYTES) / SUM(df.BYTES), 2) pct_free,
           SYSDATE
      FROM dba_data_files df,
           (SELECT tablespace_name,
                   file_id,
                   SUM(BYTES) BYTES,
                   MAX(BYTES) maxbytes
              FROM dba_free_space
             WHERE bytes > 1024 * 1024
             GROUP BY tablespace_name, file_id) free
     WHERE df.tablespace_name = free.tablespace_name(+)
       AND df.file_id = free.file_id(+)
     GROUP BY df.tablespace_name
     ORDER BY 8;
  COMMIT;
END;

 
 ---每个表空间每天空间增长
SELECT to_char(snap_time, 'yyyymmdd'),
       tablespace_name,
       free_gb,
       used_gb,
       used_gb - lag(used_gb, 1, used_gb) over(PARTITION BY tablespace_name ORDER BY snap_time) grow_byte
  FROM dbmon.tab_snap_tablespace_day
 ORDER BY 2, 1;

SELECT to_char(snap_time, 'yyyymmdd'),
       tablespace_name,
       free_gb,
       used_gb,
       used_gb - lag(used_gb, 1, used_gb) over(PARTITION BY tablespace_name ORDER BY snap_time) grow_byte
  FROM dbmon.tab_snap_tablespace_day a
 WHERE a.TABLESPACE_NAME = 'CDCP_DATA'
 ORDER BY 2, 1;


SELECT to_char(snap_time, 'yyyymmdd'),
       tablespace_name,
       free_gb,
       used_gb,
       used_gb - lag(used_gb, 1, used_gb) over(PARTITION BY tablespace_name ORDER BY snap_time) grow_byte
  FROM dbmon.tab_snap_tablespace_day a
 WHERE a.TABLESPACE_NAME = 'USERS'
 ORDER BY 2, 1;




---全库每天空间增长
SELECT date1,
       daybyte,
       a.daybyte - lag(a.daybyte, 1, a.daybyte) over(ORDER BY a.date1) grow_byte
  FROM (SELECT to_char(snap_time, 'yyyymmdd') date1, SUM(used_gb) daybyte
          FROM dbmon.tab_snap_tablespace_day
         WHERE tablespace_name NOT LIKE '%UNDO%'
         GROUP BY to_char(snap_time, 'yyyymmdd')) a;
         

         
         
col host_name for a30
col host_name for a30
col COLUMN_LABEL for a30

select mt.host_name,
       mt.host_ip,
       M.COLUMN_LABEL,
       M1.KEY_VALUE,
       round(M1.VALUE_AVERAGE, 2) as usered_rate,
       round(M1.VALUE_AVERAGE, 2) -
       (select round(VALUE_AVERAGE, 2)
          from MGMT_METRICS_1DAY m2
         where m1.target_guid = m2.target_guid
           and m1.metric_guid = m2.metric_guid
           and m1.key_value = m2.key_value
           and m2.rollup_timestamp = trunc(sysdate - 7)) pct
  from sysman.MGMT_METRICS_1DAY M1,
       sysman.MGMT_METRICS      M,
       sysman.MGMT_TARGETS      T,
       sysman.MGMT_TARGET_TYPES TT,
       sysman.my_target         mt
 where M1.METRIC_GUID = M.METRIC_GUID
   and m1.target_guid = t.target_guid
   and TT.TARGET_TYPE = T.TARGET_TYPE
   and T.TARGET_TYPE = M.TARGET_TYPE
   and T.TYPE_META_VER = M.TYPE_META_VER
   AND (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ')
   AND (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ')
   AND (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ')
   AND (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ')
   and (T.CATEGORY_PROP_5 = M.CATEGORY_PROP_5 or M.CATEGORY_PROP_5 = ' ')
   and t.host_name = mt.host_name
   and M.COLUMN_LABEL in ('Tablespace Space Used (%)')
   and m1.key_value not like '%UNDO%'
   and m1.key_value not like '%TEMP%'
   and m1.rollup_timestamp = trunc(sysdate - 1)
   and mt.host_ip='10.212.170.48'
 order by 1, 2, 3;