script for Tablespace&…

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；