Tablespace Information

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.
SQL>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
SQL>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:
Tablespace Name - Name of the tablespace
Bytes Used - Size of the file in bytes
Bytes Free - Size of free space in bytes
Largest - Largest free space in bytes
Percent Used - Percentage of tablespace that is being used - Careful if it is more than 85%
SQL>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
SQL>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