|
来自metalink，可能有用
/*
purpose:
========
the purpose of this article is to provide you with a script file that
shows you space usage for a database.
database report - space usage:
==============================
overview:
---------
this script shows the space usage for a database. this script performs the
following functions:
o show the tablespaces, size, and percent used.
o the total free space still available in each tablespace.
o the free space blocks in each of the tablespaces.
o the total free space still available in each tablespace.
o the usage by each user segment, the inital extent size,
and the size of the next extents - ordered by segment name.
o the usage by each user segment, the inital extent size,
and the size of the next extents - ordered by tablespace name.
o the usage by each user segment, the inital extent size,
and the size of the next extents - ordered by segment size.
o show the size of the entire database.
program notes:
--------------
run it by sys from sql*plus or sql*worksheet.
*** ***
the sample program in this article is provided for educational purposes
only and is not supported by oracle support services. however, it has been
tested internally, and works as documented. oracle does not guarantee that
it will work for you, so be sure to test it in your environment before you
rely on it.
program listing:
----------------
*/
-- - - - - - - - code begins here - - - - - - - - -
set linesize 132;
set pagesize 1000;
set long 50;
set echo on;
break on "tbl space" on "total bytes";
/* *************************************************** */
/* block-size */
/* *************************************************** */
column db_block_size new_value block_size
select to_char(value, '9999') db_block_size
from v$parameter
where name = 'db_block_size';
/* *************************************************** */
/* date & user_id */
/* *************************************************** */
column today new_value dba_date
select to_char(sysdate, 'mm/dd/yy hh:miam') today
from dual;
break on instance
column instance new_value instance_name
select substr(name,1,4) instance
from v$database;
clear breaks
set termout on
set pagesize 60 linesize 132 verify off
set space 2
ttitle left 'date: ' format a18 dba_date -
center 'space report - ' format a4 instance_name -
right 'page: ' format 999 sql.pno skip 2
set echo off;
set echo on;
/* *************************************************** */
/* tablespace usage via the dba_data_files */
/* *************************************************** */
set echo off;
select substr(d.tablespace_name, 1, 12) "tbl space",
substr(to_char(sum(d.bytes), '999,999,999,999'), 1, 16) "total bytes",
substr(to_char(sum(d.bytes)/1024/1024, '999,999.9'), 1, 10) "tot mbytes",
substr(to_char(sum(d.bytes)/(z.value), '99,999,999'), 1, 11) "blocks"
from sys.dba_data_files d,
v$parameter z
where z.name = 'db_block_size'
group by d.tablespace_name, z.value;
set echo on;
/* *************************************************** */
/* show tablespace usage */
/* *************************************************** */
set echo off;
select distinct substr(tablespace_name, 1, 12) "tbl space",
substr(to_char(sum(blocks), '999,999,999'), 1, 12) "tot blks",
substr(to_char(sum(bytes), '999,999,999,999'), 1, 16) "tot bytes"
from sys.dba_free_space
group by
tablespace_name;
set echo on;
/* *************************************************** */
/* show tablespace usage */
/* *************************************************** */
set echo off;
break on "tbl space";
select substr(tablespace_name,1,12) "tbl space",
substr(to_char(file_id, '99'), 1, 4) "id",
substr(to_char(count(*), '9,999'), 1, 6) "pcs",
substr(to_char(max(blocks), '9,999,999'), 1, 10) "max blks",
substr(to_char(min(blocks), '9,999,999'), 1, 10) "min blks",
substr(to_char(avg(blocks), '9,999,999.9'), 1, 12) "avg blks",
substr(to_char(sum(blocks), '9,999,999'), 1,10) "sum blks",
substr(to_char(sum(bytes), '99,999,999,999'), 1, 15) "sum bytes"
from
sys.dba_free_space
group by
tablespace_name,file_id
order by 1, 4;
set echo on;
/* *************************************************** */
/* show tablespace usage */
/* *************************************************** */
set echo off;
select
distinct substr(tablespace_name,1,12) "tbl space",
substr(to_char(sum(blocks), '999,999,999'), 1, 12) "sum blks",
substr(to_char(sum(bytes)/(1024*1024), '999,999.999'), 1, 16) "sum mbytes"
from
sys.dba_free_space
group by
tablespace_name;
set echo on;
/* *************************************************** */
/* show tablespace segments - order by segment */
/* *************************************************** */
set echo off;
break on "segment" on "type";
select substr(segment_name, 1, 20) "segment",
substr(segment_type,1,7) "type",
substr(tablespace_name,1,8) "tbl space",
substr(to_char(sum(bytes)/(1024*1024), '999,999.999'), 1, 11)
"sum mb",
/* substr(to_char(sum(blocks), '999,999'), 1, 8) "sum blks", */
substr(to_char(extents, '999'), 1, 4) "extents",
substr(to_char((initial_extent/(1024*1024)), '99,999.999'), 1, 10)
"ini (mb)",
substr(to_char((next_extent/(1024*1024)), '999.999'), 1, 8) "nxt (mb)"
from user_segments
group by segment_name,
segment_type,
tablespace_name,
bytes,
blocks,
extents,
initial_extent,
next_extent
order by 1, 2;
set echo on;
/* *************************************************** */
/* show tablespace segments - order by tablespace */
/* *************************************************** */
set echo off;
break on "tbl space" on "segment" on "type";
select substr(tablespace_name,1,12) "tbl space",
substr(segment_name, 1, 20) "segment",
/* substr(segment_type,1,7) "type", */
substr(to_char(sum(bytes)/(1024*1024), '999,999.999'), 1, 12)
"sum mb",
/* substr(to_char(sum(blocks), '9,999,999'), 1, 10) "sum blks", */
substr(to_char(extents, '999'), 1, 4) "exts",
substr(to_char(initial_extent/(1024*1024), '999.999'), 1, 8)
"ini mb",
substr(to_char(next_extent/(1024*1024), '999.999'), 1, 8)
"nxt mb"
from user_segments
group by segment_name,
segment_type,
tablespace_name,
bytes,
blocks,
extents,
initial_extent,
next_extent
order by 1, 2;
set echo on;
/* *************************************************** */
/* show tablespace segments - order by bytes */
/* *************************************************** */
set echo off;
break on "sum bytes" on "segment" on "type";
select substr(to_char(sum(bytes)/(1024*1024), '999,999.999'), 1, 11)
"sum mb",
substr(segment_name, 1, 20) "segment",
substr(tablespace_name,1,12) "tbl space",
/*substr(to_char(sum(blocks), '9,999,999'), 1, 10) "sum blks",*/
substr(to_char(extents, '999'), 1, 4) "exts",
substr(to_char(initial_extent/(1024*1024), '9,999.999'), 1, 10)
"ini mb",
substr(to_char(next_extent/(1024*1024), '999.999'), 1, 8)
"nxt mb"
from user_segments
group by segment_name,
segment_type,
tablespace_name,
bytes,
blocks,
extents,
initial_extent,
next_extent
order by 1, 2;
set echo on;
/* *************************************************** */
/* show size of database */
/* *************************************************** */
set echo off;
select substr(to_char(sum(bytes)/1024/1024, '999,999,999.99'), 1, 15) "tot mbytes",
substr(to_char(sum(bytes)/1024/1024/1024, '999,999.99'), 1, 11) "tot gbytes"
from sys.dba_data_files;
undefine block_size;
set echo off;
set long 80;
--- - - - - - code ends here - - - - - - - - -
|
