Tablespace Information

Tablespace Information

Here are some scripts related to Tablespace Information .
Источник: http://vsbabu.org/oracle/sect03.html

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