What is an Oracle Tablespace?
A tablespace is a logical storage unit – multiple application objects (e.g. tables) can be stored in one tablespace. A tablespace can be online or offline (not accessible), and can contain one or more datafiles, each of which can be online or offline.
There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.
Locally Managed Tablespaces (LMT’s) : When creating an LMT, the storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not required (invalid syntax). An LMT can have either uniform or variable extent sizes. Variable extents are determined automatically by the system. When you create the tablespace, the UNIFORM or AUTOALLOCATE (system-managed) clause specifies the type of allocation.
For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Temporary tablespaces that manage their extents locally can only use this type of allocation.
For system-managed extents, you can specify the size of the initial extent and Oracle determines the optimal size of additional extents, with a minimum extent size of 64 KB. This is the default for permanent tablespaces.
The following statement creates a locally managed tablespace named test_ts and specifies AUTOALLOCATE:
SQL Code :
CREATE TABLESPACE test1_ts DATAFILE '/u02/oracle/data/test1_ts.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Automatic segment-space management
SQL Code :
CREATE TABLESPACE test2_ts DATAFILE '/u02/oracle/data/test2_ts.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
Dictionary Managed Tablespaces: A tablespace that uses the data dictionary to manage its extents has incremental extent sizes, which are determined by the storage parameters INITIAL, NEXT, and PCTINCREASE. These can be adjusted to control the extent sizes. When additional space is needed, the NEXT and PCTINCREASE parameters determine the sizes of new extents.
As an example, the following statement creates the tablespace tbsa, with the following characteristics:
The data of the new tablespace is contained in a single datafile, 50M in size.
The tablespace is explicitly created as a dictionary-managed tablespace by specifying EXTENT MANAGEMENT DICTIONARY.
The default storage parameters for any segments created in this tablespace are specified.
The following statement creates the tablespace tbsb:
SQL Code :
CREATE TABLESPACE tbsb
DATAFILE '/u02/oracle/data/tb01.dbf' SIZE 50M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE (
INITIAL 50K
NEXT 50K
MINEXTENTS 2
MAXEXTENTS 50
PCTINCREASE 0);
Temporary tablespace: Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles except that:
- You cannot create a tempfile with the ALTER DATABASE statement.
- You cannot rename a tempfile or set it to read-only.
- Tempfiles are always set to NOLOGGING mode.
- When you create or resize tempfiles, they are not always guaranteed allocation of disk space for the file size specified. On certain file systems (for example, UNIX) disk blocks are allocated not at file creation or resizing, but before the blocks are accessed.
Create Temporary TablespaceSQL Code :
CREATE TEMPORARY TABLESPACE test_temp TEMPFILE '/u02/oracle/data/test_temp01.dbf'
SIZE 20M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
System Tablespace: Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database. All data stored on behalf of stored PL/SQL program units (procedures, functions, packages, and triggers) resides in the SYSTEM tablespace.
The SYSTEM tablespace is always online when the database is open.
Data Dictionary Objects to get information about tablespace
Tablespaces ts$, dba_tablespaces ,
user_tablespaces, Tablespace
Quotas tsq$, dba_ts_quotas, user_ts_quotas
Data Files dba_data_files, v_$backup_datafile,
v_$datafile,
v_$datafile_copy, v_$datafile_header
Free Space dba_free_space
Segments dba_segments, v_$segment_statistics
Extents dba_extents
Blocks v_$database_block_corruption
Groups dba_tablespace_groups
SYSAUX Tablespace v_$sysaux_occupants
Temp Tablespace dba_temp_files
Undo Tablespace dba_rollback_segs, dba_undo_extents,
v_$rollstat, v_$undostat
Transportable Tablespaces transport_set_violations
Dictionary Management fet$, uet$
Monitoring TablespaceTo list the names and default storage parameters of all tablespaces in a database
SQL Code:
SELECT TABLESPACE_NAME "TABLESPACE",
INITIAL_EXTENT "INITIAL_EXT",
NEXT_EXTENT "NEXT_EXT",
MIN_EXTENTS "MIN_EXT",
MAX_EXTENTS "MAX_EXT",
PCT_INCREASE
FROM DBA_TABLESPACES;
----------------------------------------------------------------------
SQL Code:
SELECT tablespace_name,
block_size,
status,
contents,
retention,
extent_management,
allocation_type,
plugged_in,
segment_space_management
FROM dba_tablespaces;
----------------------------------------------------------------------
SQL Code:
SELECT tablespace_name,
ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB,
ROUND(SUM(total_mb)) CUR_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL,
ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name
----------------------------------------------------------------------
SQL Code:
SELECT dd.tablespace_name tablespace_name,
dd.file_name file_name,
dd.bytes/1024 TABLESPACE_KB,
SUM(fs.bytes)/1024 KBYTES_FREE,
MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;
----------------------------------------------------------------------
SQL Code:
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;
Displaying Statistics for Free Space (Extents) of Each TablespaceSQL Code:
SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,
COUNT(*) "PIECES",
MAX(blocks) "MAXIMUM",
MIN(blocks) "MINIMUM",
AVG(blocks) "AVERAGE",
SUM(blocks) "TOTAL"
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;
To list the names, sizes, and associated tablespaces of a databaseSQL Code:
SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME
FROM DBA_DATA_FILES;
Monitoring Free Space of a databaseSQL Code:
SELECT BLOCK_ID, BYTES, BLOCKS
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = 'USERS'
ORDER BY BLOCK_ID;
BLOCK_ID BYTES BLOCKS
---------------------- ---------------------- ----------------------
417 1835008 224
Coalescing Free SpaceSyntax:
ALTER TABLESPACE <tablespace name> COALESCE;
Availability of Datafiles or TempfilesSyntax:
ALTER TABLESPACE ... DATAFILE {ONLINE|OFFLINE}
ALTER TABLESPACE ... TEMPFILE {ONLINE|OFFLINE}
SQL Code:
ALTER TABLESPACE users OFFLINE NORMAL;
Dropping TablespaceThe following statement drops the users tablespace, including the segments in the tablespace
SQL Code:
DROP TABLESPACE users INCLUDING CONTENTS;
The following statement drops the USER tablespace and its associated datafiles:
SQL Code:
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
Get SQL Code of a tablespaceSQL Code:
SELECT dbms_metadata.get_ddl('TABLESPACE', 'USERS')
FROM dual;
To see the default tablestace informationSQL Code:
SELECT *
FROM props$
WHERE name LIKE '%DEF%'
TABLESPACE INFORMATIONTablespace 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 Code:
Select TABLESPACE_NAME,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
CONTENTS
from dba_tablespaces
order by TABLESPACE_NAME
Coalesced ExtsTablespace 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 Code:
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
UsageTablespace 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 Code:
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)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.
SQL Code:
select USERNAME,
CREATED,
PROFILE,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE
from dba_users
order by USERNAME
Objects in SYSTEM TablespacOwner – 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 Code:
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 ExtTablespace – 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
SQL Code:
select TABLESPACE_NAME,
sum(BYTES) Total_free_space,
max(BYTES) largest_free_extent
from dba_free_space
group by TABLESPACE_NAME
