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
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
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.
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.
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.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.
相关内容 …
产品 …
?Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition > RDBMS
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17252115/viewspace-742494/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17252115/viewspace-742494/