An Oracle database consists of one or more logical storage units called tablespaces which collectivley store all of the database's data.
Each tablespace in an ORACLE database consists of one or more files called datafiles which are physical structures that conform with the operating system in which Oracle is running.
For Example::: A simple Oracle Database have one tablespace and one datafile. For more Information @ prakash.dba@gmail.com
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:
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
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
Oracle Stores logically data in tablespaces every database atleast one tablespace accumulated with it tablespace is two types System Tablespaces:- created automatically when database is created by oracle server to hold system objects data dictionary and not user data. Non-System Tablespace:- created by DBA to store UNDO TEMP and APPLICATION objects.
tablespace is consist of :- One or more segments each segment composed of one or more extents and extent is further consist of data blocks and this is last stage.
Tablespace is the logical component of a database. It is the combination of one or more segments as each segment is formed by one Or more extents. On the other hand tablespace consists of one or more data files in which the data resides. So a tablespace is the combination of logical and physical structure.
0 
1