Donald K. Burleson Oracle Tips

Viewing tablespace object contents

For Oracle professional who accesses their databases remotely, you do not have the luxury of using the Oracle Enterprise Manager GUI tools for displaying the contents of objects within a tablespace.

The following script will accept a tablespace name (in uppercase) and display the contents of all objects in the tablespace.

This script is especially useful for monitoring sorting operations within Oracle.  You can run this script against the TEMP tablespace.

#!/bin/ksh

sqlplus -s /@$ORACLE_SID <<EOT

--

-- MAPPER.SQL

--

-- Parameters: tablespace to be mapped

--

-- This script generates a mapping of the space usage

-- (free space vs used) in a tablespace. It graphically

-- shows segment and free space fragmentation.

--

set pagesize 60 linesize 132 verify off

break on file_id skip 1

column file_id heading "File|Id"

column tablespace_name for a15

column object          for a15

column owner           for a15

column MBytes          for 999,999

select tablespace_name,

       'free space' owner,         /*"owner" of free space */

       '   '        object,        /*blank object name     */

       file_id,                    /*file id for the extent header*/

       block_id,                   /*block id for the extent header*/

       CEIL(blocks*4/1024) MBytes  /*length of the extent, in Mega Bytes*/

  from dba_free_space

 where tablespace_name like '%$1%'

union

select tablespace_name,

       substr(owner, 1, 20),       /*owner name (first 20 chars)*/

       substr(segment_name, 1, 32), /*segment name               */

       file_id,                    /*file id for extent header  */

       block_id,                   /*block id for extent header */

       CEIL(blocks*4/1024) MBytes  /*length of the extent, in Mega Bytes*/

  from dba_extents

 where tablespace_name like '%$1%'

 order by 1, 4, 5

/

exit

EOT  

Here is a sample of the output.  It provides a great visualization tool for showing the locations of objects within a tablespace.

Root> space_map.ksh RBS

TABLESPACE_NAME OWNER        OBJECT       Id   BLOCK_ID   MBYTES

--------------- ------------ --------- ----- ---------- --------

RBS             SYS          RBS01         2          2        1

RBS             SYS          RBS01                   37        1

RBS             free space                           72        1

RBS             SYS          RBS01                  107        1

RBS             free space                          142        1

RBS             SYS          RBS02                  177        1

RBS             SYS          RBS02                  212        1

RBS             SYS          RBS02                  247        1

RBS             SYS          RBS02                  282        1

RBS             SYS          RBS02                  317        1

RBS             SYS          RBS03                  352        1

RBS             free space                          387        1

RBS             free space                          422        1

RBS             free space                          457        1

RBS             free space                          492        1

RBS             free space                          527        1

RBS             free space                          562        1

RBS             SYS          RBS02                  597        1

RBS             SYS          RBS02                  632        1

RBS             SYS          RBS02                  667        1

RBS             SYS          RBS02                  702        1

RBS             SYS          RBS02                  737        1

RBS             SYS          RBS02                  772        1

RBS             SYS          RBS02                  807        1

RBS             SYS          RBS02                  842        1

RBS             SYS          RBS02                  877        1

RBS             SYS          RBS02                  912        1

RBS             SYS          RBS02                  947        1

RBS             SYS          RBS02                  982        1

RBS             SYS          RBS02                 1017        1

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: