DBA Scripts - Very Useful - 3. Tablespace Information

Here are some scripts related to Tablespace Information .

Information

TABLESPACE INFORMATION NOTES:

  Tablespace Name - Name of the tablespace

  Initial Extent - Default initial extent size

  Next Extent - Default incremental extent size

  Min Extents - Default minimum number of extents

  Max Extents - Default maximum number of extents

  PCT Increase - Default percent increase for extent size

  Status - Tablespace status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped)

  Contents - Type of tablespace. This column will have 'TEMPORARY' (v7.3+) for dedicated temporary tablespaces, and 'PERMANENT' for tablespaces that can store both temporary sort segments and permanent objects.

select       TABLESPACE_NAME,

               INITIAL_EXTENT,

               NEXT_EXTENT,

               MIN_EXTENTS,

               MAX_EXTENTS,

               PCT_INCREASE,

               STATUS,

               CONTENTS

from        dba_tablespaces

order        by TABLESPACE_NAME 

Coalesced Exts

WAIT STATISTIC NOTES:

  Tablespace Name - Name of tablespace

  Total Extents - Total number of free extents in tablespace

  Extents Coalesced - Total number of coalesced free extents in tablespace

  % Extents Coalesced - Percentage of coalesced free extents in tablespace

  Total Bytes - Total number of free bytes in tablespace

  Bytes Coalesced - Total number of coalesced free bytes in tablespace

  Total Blocks - Total number of free oracle blocks in tablespace

  Blocks Coalesced - Total number of coalesced free Oracle blocks in tablespace

  % Blocks Coalesced - Percentage of coalesced free Oracle blocks in tablespace

select       TABLESPACE_NAME,

               TOTAL_EXTENTS,

               EXTENTS_COALESCED,

               PERCENT_EXTENTS_COALESCED,

               TOTAL_BYTES,

               BYTES_COALESCED,

               TOTAL_BLOCKS,

               BLOCKS_COALESCED,

               PERCENT_BLOCKS_COALESCED

from        dba_free_space_coalesced

order        by TABLESPACE_NAME

Usage

TABLESPACE USAGE NOTES:

1. Tablespace Name - Name of the tablespace
2. Bytes Used - Size of the file in bytes
3. Bytes Free - Size of free space in bytes
4. Largest - Largest free space in bytes
5. Percent Used - Percentage of tablespace that is being used - Careful if it is more than 85%

select       a.TABLESPACE_NAME,

               a.BYTES bytes_used,

               b.BYTES bytes_free,

               b.largest,

               round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used

from        

               (

                               select       TABLESPACE_NAME,

                                              sum(BYTES) BYTES 

                               from        dba_data_files 

                               group       by TABLESPACE_NAME

               )

               a,

               (

                               select       TABLESPACE_NAME,

                                              sum(BYTES) BYTES ,

                                              max(BYTES) largest 

                               from        dba_free_space 

                               group       by TABLESPACE_NAME

               )

               b

where       a.TABLESPACE_NAME=b.TABLESPACE_NAME

order        by ((a.BYTES-b.BYTES)/a.BYTES) desc

Users Default (SYSTEM)

SYSTEM TABLESPACE USAGE NOTES:

  Username - Name of the user

  Created - User creation date

  Profile - Name of resource profile assigned to the user

  Default Tablespace - Default tablespace for data objects

  Temporary Tablespace - Default tablespace for temporary objects

  Only SYS, SYSTEM and possibly DBSNMP should have their default tablespace set to SYSTEM.

select       USERNAME,

               CREATED,

               PROFILE,

               DEFAULT_TABLESPACE,

               TEMPORARY_TABLESPACE

from        dba_users

order        by USERNAME

Objects in SYSTEM TS

OBJECTS IN SYSTEM TABLESPACE NOTES:

  Owner - Owner of the object

  Object Name - Name of object

  Object Type - Type of object

  Tablespace - Tablespace name

  Size - Size (bytes) of object

  Any user (other than SYS, SYSTEM) should have their objects moved out of the SYSTEM tablespace

select       OWNER,

               SEGMENT_NAME,

               SEGMENT_TYPE,

               TABLESPACE_NAME,

               BYTES

from        dba_segments

where       TABLESPACE_NAME = 'SYSTEM'

and          OWNER not in ('SYS','SYSTEM')

order        by OWNER, SEGMENT_NAME

Freespace/Largest Ext

FREE, LARGEST, & INITIAL NOTES:

  Tablespace - Name of the tablespace

  Total Free Space - Total amount (bytes) of freespace in the tablespace

  Largest Free Extent - Largest free extent (bytes) in the tablespace

select       TABLESPACE_NAME,

               sum(BYTES) Total_free_space,

               max(BYTES) largest_free_extent

from        dba_free_space

group       by TABLESPACE_NAME