Script to Detect Tablespace Fragmentation [ID 1020182.6]

rongshiyuan發表於2012-09-02

Script. to Detect Tablespace Fragmentation [ID 1020182.6]

Abstract
This bulletin gives Script. to Detect Tablespace Fragmentation.

Product Name, Product Version Oracle Server 7.3 to 11.2
Platform Platform. Independent
Date Created 30-JUL-2002

Instructions
Execution Environment:
SQL, SQL*Plus

Access Privileges:
SELECT privilege on DBA_FREE_SPACE

Usage:
sqlplus / @tfstsfgm

Instructions:
1. Connect to the database having above accesss privileges.2. Copy the below script. titled “tfstsfgm” and execute it
          from SQL*Plus.Usage:
Sql> @tfstsfgmPROOFREAD THIS SCRIPT. BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script. may not be in an executable state
when you first receive it. Check over the script. to ensure that errors of
this type are corrected.The script. will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.

Description
The following is a script. that will determine how many extents of contiguous
free space you have in Oracle as well as the total amount of free space you
have in each tablespace. From these results you can detect how fragmented your
tablespace is.

The ideal situation is to have one large free extent in your tablespace. The
more extents of free space there are in the tablespace, the more likely you
will run into fragmentation problems. The size of the free extents is also
very important. If you have a lot of small extents (too small for any next
extent size) but the total bytes of free space is large, then you may want to
consider defragmentation options.

References

Script
========
Script. tfstsfgm
========SET ECHO off
REM NAME:TFSTSFRM.SQL
REM USAGE:"@path/tfstsfgm"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT ON DBA_FREE_SPACE
REM ------------------------------------------------------------------------
REM PURPOSE:
REM    The following is a script. that will determine how many extents
REM    of contiguous free space you have in Oracle as well as the
REM total amount of free space you have in each tablespace. From
REM    these results you can detect how fragmented your tablespace is.
REM
REM    The ideal situation is to have one large free extent in your
REM    tablespace. The more extents of free space there are in the
REM    tablespace, the more likely you will run into fragmentation
REM    problems. The size of the free extents is also very important.
REM    If you have a lot of small extents (too small for any next
REM    extent size) but the total bytes of free space is large, then
REM    you may want to consider defragmentation options.
REM ------------------------------------------------------------------------
REM DISCLAIMER:
REM    This script. is provided for educational purposes only. It is NOT
REM    supported by Oracle World Wide Technical Support.
REM    The script. has been tested and appears to work as intended.
REM    You should always run new scripts on a test instance initially.
REM ------------------------------------------------------------------------
REM Main text of script. follows:

create table SPACE_TEMP (
TABLESPACE_NAME        CHAR(30),
CONTIGUOUS_BYTES       NUMBER)
/

declare
cursor query is select *
          from dba_free_space
                  order by tablespace_name, block_id;
this_row        query%rowtype;
previous_row    query%rowtype;
total           number;

begin
open query;
fetch query into this_row;
previous_row := this_row;
total := previous_row.bytes;
loop
fetch query into this_row;
     exit when query%notfound;
     if this_row.block_id = previous_row.block_id + previous_row.blocks then
        total := total + this_row.bytes;
        insert into SPACE_TEMP (tablespace_name)
                  values (previous_row.tablespace_name);
     else
        insert into SPACE_TEMP values (previous_row.tablespace_name,
               total);
        total := this_row.bytes;
     end if;
previous_row := this_row;
end loop;
insert into SPACE_TEMP values (previous_row.tablespace_name,
                           total);
end;
.
/

set pagesize 60
set newpage 0
set echo off
ttitle center 'Contiguous Extents Report' skip 3
break on "TABLESPACE NAME" skip page duplicate
spool contig_free_space.lis
rem
column "CONTIGUOUS BYTES"       format 999,999,999
column "COUNT"                  format 999
column "TOTAL BYTES"            format 999,999,999
column "TODAY"   noprint new_value new_today format a1
rem
select TABLESPACE_NAME "TABLESPACE NAME",
       CONTIGUOUS_BYTES "CONTIGUOUS BYTES"
from SPACE_TEMP
where CONTIGUOUS_BYTES is not null
order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc;

select tablespace_name, count(*) "# OF EXTENTS",
         sum(contiguous_bytes) "TOTAL BYTES"
from space_temp
group by tablespace_name;

spool off

drop table SPACE_TEMP
/   ===================================
Sample output from the above script.:
===================================Contiguous Extents Report

TABLESPACE NAME                CONTIGUOUS BYTES
------------------------------ ----------------
RBS                                  52,426,752
RBS                                   2,662,400
RBS                                     798,720
RBS                                     266,240

...

TABLESPACE NAME                CONTIGUOUS BYTES
------------------------------ ----------------
USERS                                    20,480
USERS                                    16,384
USERS                                    10,240
USERS                                    10,240
USERS                                    10,240
USERS                                     4,096

TABLESPACE_NAME                # OF EXTENTS TOTAL BYTES
------------------------------ ------------ ------------
RBS                                      15   56,154,112
SYSTEM                                   10      927,744
TEMP                                      5      665,600
TOOLS                                    10   89,397,248
USERS                                     6       71,680


Disclaimer
EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.

Limitation of Liability
IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.

Script to Detect Tablespace Fragmentation [ID 1020182.6]

相關文章