Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2667235
  • 博文数量: 570
  • 博客积分: 1860
  • 博客等级: 上尉
  • 技术积分: 6259
  • 用 户 组: 普通用户
  • 注册时间: 2008-04-07 08:51
个人简介

偶尔有空上来看看

文章分类

全部博文(570)

文章存档

2022年(64)

2021年(238)

2020年(115)

2019年(11)

2018年(9)

2017年(9)

2016年(17)

2015年(7)

2014年(4)

2013年(1)

2012年(11)

2011年(27)

2010年(35)

2009年(11)

2008年(11)

分类: Oracle

2009-06-15 09:19:54

来自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 - - - - - - - - -

阅读(1852) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~