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