| |
 |
|
Oracle Tips by Burleson |
Monitoring Tablespace Extent
Mapping
A final set of freespace information that is handy to have around
(OEM and many third-party tools will provide a GUI-based map) is the
location of the freespace in a tablespace and the size of the
fragments themselves. The script in Source 11.18 provides this data.
The output from the script in Source 11.18 is shown in Listing
11.15.
SOURCE 11.18 Script to document freespace extents inside a
tablespace.
rem
rem Name: mapper.sql
rem Function: create an extent map for a specific tablespace
rem Based on a technique from DBA Handbook
rem Mike Ault 7/19/96 TUSC
rem
SET PAGES 47 LINES 132 VERIFY OFF FEEDBACK OFF
COLUMN file_id HEADING 'File|id'
COLUMN value NEW_VALUE dbblksiz NOPRINT
COLUMN meg FORMAT 9,999.99 HEADING 'Meg'
COLUMN partition_name FORMAT a30 HEADING 'Partition|Name'
SELECT value FROM v$parameter WHERE name='db_block_size';
START title132 '&&ts Mapping Report'
SPOOL rep_out/&db/ts_map
SELECT
'free space' owner, ' ' object,'Not Part.' partition
file_id, block_id, blocks,
(blocks*&dbblksiz)/(1024*1024) meg
FROM
dba_free_space
See code depot
This is an excerpt by Mike Ault’s book “Oracle9i
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download. |
