jump to content

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