检查数据库空间

col tablespace format a25  /*将字段长度压缩到25个字符型数据显示*/
col owner format a20
col type format a19
col sort1 noprint  /*屏蔽字段的显示*/
col mb format 999,990.00  /*将数值型字段以指定的格式显示*/
clear breaks
clear compute
break on report on tablespace on owner on type/*当同一字段的健值相同时,跳过不显示*/

set echo off
set feedback off
set timing off
set pagesize 66
set verify off
set trimspool on
col instance new_value V_INSTANCE noprint/*将列值存入某个变量*/
select instance from v$thread;
spool spc_&&V_INSTANCE

SELECT tablespace_name tablespace ,
owner,
'a' sort1,
segment_type type,
SUM(bytes)/1048576 mb
FROM dba_segments
GROUP BY tablespace_name , owner,segment_type
UNION ALL
select tablespace,
username owner,
'b' sort1,
segtype type,
SUM(blocks)/128 mb
FROM v$sort_usage
GROUP BY tablespace, username , segtype
UNION ALL
SELECT tablespace_name tablespace,
'' owner,
'c' sort1,
'-------total-------' type,
SUM(bytes)/1048576 mb
FROM dba_segments
GROUP BY tablespace_name
UNION ALL
SELECT tablespace,
'' owner,
'd' sort1,
'-------total-------' type ,
SUM(blocks)/128 mb
FROM v$sort_usage
GROUP BY tablespace
UNION ALL
SELECT tablespace_name tablespace,
'' owner,
'e' sort1,
'-----allocated-----' type,
SUM(bytes)/1048576 mb
FROM dba_data_files
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name tablespace,
'' owner,
'f' sort1,
'-----allocated-----' type,
SUM(bytes)/1048576 mb
FROM dba_temp_files
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name tablespace,
'' owner ,
'g' sort1,
'-----allocatable-----' type,
SUM(DECODE
(autoextensible,'YES',maxbytes,bytes))/1048576 mb
FROM dba_data_files
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name tablespace,
'' owner
,'h' sort1,
'---allocatable----' type,
SUM(DECODE
(autoextensible,'YES',maxbytes,bytes))/1048576 mb
FROM dba_temp_files
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name tablespace,
'' owner,
'i' sort1,
'' type ,
TO_NUMBER('') mb
FROM dba_tablespaces
UNION ALL
SELECT tablespace ,
owner,
sort1,
type,
SUM(mb)
FROM (SELECT '' tablespace ,'Total' owner ,'a' sort1,'Used' type,
              SUM(bytes)/1048576 mb
        FROM dba_segments
        UNION ALL
      SELECT '' tablespace , 'Total' owner, 'a' sort1, 'Used' type , SUM(blocks)/128 mb
        FROM v$sort_usage
      )
GROUP BY tablespace ,owner ,sort1 ,type
UNION ALL
SELECT tablespace ,owner ,sort1,type,SUM(mb)
FROM (SELECT '' tablespace ,
            'Total' owner,'b' sort1, 'Allocated' type,
            SUM(bytes)/1048576 mb
        FROM dba_data_files
      UNION ALL
      SELECT '' tablespace ,
             'Total' owner,
              'b' sort1,
              'Allocated' type ,
              SUM(bytes)/104856 mb
         FROM dba_temp_files
      )
GROUP BY tablespace ,owner,sort1,type
UNION ALL
SELECT tablespace,
owner,
sort1,
type,
sum(mb)
FROM (SELECT '' tablespace ,'Total' owner ,'c' sort1,'Allocatable' type,
              SUM(DECODE
                (autoextensible,'YES',maxbytes , bytes))/1048576 mb
         FROM dba_data_files
      UNION ALL
       SELECT '' tablespace,
         'Total' owner,
        'c' sort1,
      'Allocatable' type ,
       SUM(DECODE
        (autoextensible,'YES',maxbytes,bytes))/1048576 mb
       FROM dba_temp_files
       )
GROUP BY tablespace ,owner,sort1,type
ORDER BY 1,2,3,4
/
spool off
/

注:此段代码中的clear breaks   clear compute

不知道是何意思,请路过的同仁们赐教.

谢谢!

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10595277/viewspace-670715/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10595277/viewspace-670715/