How to Display Storage Map for Database | Tablespace | Datafile Storage (Doc ID 1377458.1)

In this Document

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.

Purpose

This note will present a set of steps by which a map of used and free space can be displayed for one or more tablespaces / datafiles

Requirements

Any command line interface (SQLPLUS .. ISQLPLUS etc)

Configuring

Executing user must have been granted access to DBA_FREE_SPACE and DBA_EXTENTS

Instructions

1) Create a 'holding table' table called SPACE_MAP

2) Load SPACE_MAP with the data from DBA_EXTENTS

3) Load SPACE_MAP with the data from DBA_FREE_SPACE

4) Format output columns to fit the users screen

5) Query SPACE_MAP for the needed data

OUTPUT EXPLANATION:
==================

     BEGIN and END indicate the beginning and ending blocks for the 'chunk' of storage

        For example .. if FILE is 4 and ...
                                 TABLESPACE is USERS and ...
                                 BEGIN block is 137 and ...
                                 END block is 145 and ...
                                 NAME is SCOTT.EMP and ...
                                 TYPE is TABLE

         This means that BLOCKS 137 to 145 of TABLESPACE users dataFILE 4
             are being used by a TABLE extent for a table with the name EMP that is owned by SCOTT

Caution

This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Script

-- Create a 'holding table' table called SPACE_MAP

CREATE TABLE SPACE_MAP (
   FILE_NO NUMBER,
   TBLSP_NAME VARCHAR2(30),
   BEGIN_BLOCK NUMBER,
   END_BLOCK NUMBER,
   SEGMENT_NAME VARCHAR2(60),
   CHUNK_TYPE VARCHAR2(30));

-- Load SPACE_MAP with the data from DBA_EXTENTS

INSERT INTO SPACE_MAP SELECT FILE_ID, TABLESPACE_NAME, BLOCK_ID, BLOCK_ID+BLOCKS-1, OWNER||'.'||SEGMENT_NAME, SEGMENT_TYPE FROM DBA_EXTENTS;
COMMIT;

-- Load SPACE_MAP with the data from DBA_FREE_SPACE

INSERT INTO SPACE_MAP SELECT FILE_ID, TABLESPACE_NAME, BLOCK_ID, BLOCK_ID+BLOCKS-1, NULL, '' FROM DBA_FREE_SPACE;
COMMIT;

-- Format output columns to fit the users screen

COLUMN FILE# FORMAT 9999
COLUMN TABLESPACE FORMAT A10
COLUMN NAME FORMAT A30
COLUMN TYPE FORMAT A12
COLUMN BEGIN FORMAT 9999999
COLUMN END FORMAT 9999999

-- Query SPACE_MAP for the needed data

-- TABLESPACE QUERY

select FILE_NO FILE#, TBLSP_NAME TABLESPACE, BEGIN_BLOCK BEGIN, END_BLOCK END, SEGMENT_NAME NAME, CHUNK_TYPE TYPE
from SPACE_MAP where TBLSP_NAME = ''
order by FILE_NO, BEGIN_BLOCK;

-- DATAFILE QUERY

select FILE_NO FILE#, TBLSP_NAME TABLESPACE, BEGIN_BLOCK BEGIN, END_BLOCK END, SEGMENT_NAME NAME, CHUNK_TYPE TYPE
from SPACE_MAP where FILE_NO =
order by BEGIN_BLOCK;

-- DATABASE EXAMPLE

select FILE_NO FILE#, TBLSP_NAME TABLESPACE, BEGIN_BLOCK BEGIN, END_BLOCK END, SEGMENT_NAME NAME, CHUNK_TYPE TYPE
from SPACE_MAP
order by TBLSP_NAME, FILE_NO, BEGIN_BLOCK;

Sample Output

TABLESPACE EXAMPLE

select FILE_NO FILE#, TBLSP_NAME TABLESPACE, BEGIN_BLOCK BEGIN, END_BLOCK END, SEGMENT_NAME NAME, CHUNK_TYPE TYPE
from SPACE_MAP where TBLSP_NAME = 'USERS'
order by FILE_NO, BEGIN_BLOCK;

FILE# TABLESPACE    BEGIN      END NAME                 TYPE
----- ---------- -------- -------- -------------------- ------------
    4 USERS           128      135 DEAN.ID_IND          INDEX
    4 USERS           136      143 DEAN.ID_IND          INDEX
    4 USERS           144      151
....
    4 USERS           256      263 TEST.SPACE_MAP       TABLE
    4 USERS           264    271 TEST.SPACE_MAP       TABLE
    4 USERS           272      279 TEST.SPACE_MAP       TABLE
    4 USERS           312    12799

DATAFILE EXAMPLE

select FILE_NO FILE#, TBLSP_NAME TABLESPACE, BEGIN_BLOCK BEGIN, END_BLOCK END, SEGMENT_NAME NAME, CHUNK_TYPE TYPE
from SPACE_MAP where FILE_NO = 3
order by BEGIN_BLOCK;

FILE# TABLESPACE    BEGIN      END NAME                   TYPE
----- ---------- -------- -------- ------------------------------ ------------
    3 UNDOTBS1       9984    10111 SYS._SYSSMU10_16154620$        TYPE2 UNDO
    3 UNDOTBS1      10112    10239 SYS._SYSSMU2_3913496631$       TYPE2 UNDO
    3 UNDOTBS1      10240    10367 SYS._SYSSMU2_3913496631$       TYPE2 UNDO
    3 UNDOTBS1      10368    10495 SYS._SYSSMU9_2823258255$ TYPE2 UNDO
...
    3 UNDOTBS1      24192    24703
    3 UNDOTBS1    24704    24831 SYS._SYSSMU10_16154620$        TYPE2 UNDO
    3 UNDOTBS1      24832    25471
    3 UNDOTBS1      25472    25599 SYS._SYSSMU10_16154620$        TYPE2 UNDO

DATABASE EXAMPLE

select FILE_NO FILE#, TBLSP_NAME TABLESPACE, BEGIN_BLOCK BEGIN, END_BLOCK END, SEGMENT_NAME NAME, CHUNK_TYPE TYPE
from SPACE_MAP
order by TBLSP_NAME, FILE_NO, BEGIN_BLOCK;

FILE# TABLESPACE    BEGIN      END NAME                           TYPE
----- ---------- -------- -------- ------------------------------ ------------
    2 SYSAUX          128      135 SYS.SQLLOG$_PKEY               INDEX
    2 SYSAUX          136      143 SYS.SMB$CONFIG                 TABLE
    2 SYSAUX          144      151 SYS.I_SMB$CONFIG_PKEY          INDEX
...
    1 SYSTEM          128      135 SYS.SYSTEM                     ROLLBACK
    1 SYSTEM          136      143 SYS.SYSTEM                     ROLLBACK
    1 SYSTEM          144    151 SYS.C_OBJ#                     CLUSTER
...
    3 UNDOTBS1        128    135 SYS._SYSSMU1_1002995750$       TYPE2 UNDO
    3 UNDOTBS1        136      143 SYS._SYSSMU8_4175155633$       TYPE2 UNDO
    3 UNDOTBS1        144      151 SYS._SYSSMU2_3913496631$       TYPE2 UNDO
...
    4 USERS           128      135 DEAN.ID_IND                    INDEX
    4 USERS           136      143 DEAN.ID_IND    INDEX
    4 USERS           144    151
    4 USERS           152      159 TEST.TRACKING                  TABLE
...