linux数据库空间检查,检查数据库空间

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

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

谢谢!