A tablespace is a logical storage container in Oracle Database used to store database objects such as tables and indexes. Each tablespace consists of one or more physical datafiles where the actual data is stored
We have two types tablespaces in oracle
1) Normal Tablespace
2) Big Tablespace
Note: Data file MAX size is depends on the data block size.
Normal Tablespace
Block Size Max Datafile Size
2 KB 8 GB
4 KB 16 GB
8 KB 32 GB
16 KB 64 GB
32 KB 128 GB
Big Tablespace:
Block Size Max Datafile Size
8 KB 32 TB
16 KB 64 TB
32 KB 128 TB
Default Tablespaces:
SYSTEM:
Stores data dictionary objects
Contains core metadata of the database
Must always be ONLINE
SYSAUX:
Auxiliary tablespace for SYSTEM
Stores components like:
AWR (Automatic Workload Repository)
OEM data
Stores undo data:
Rollback operations ,Read consistency and Flashback
Users:
Default tablespace for normal users
Temp:
Temporary operations:
Sorting, Joins and Hash operations
You’ll deal with three main types:
Tablespace States:
State Description
ONLINE Accessible
OFFLINE Not accessible
READ ONLY No changes allowed
READ WRITE Default
To View Tablespace Information:
v$tablespace
v$datafile
v$tempfile
dba_tablespaces
dba_data_files
dba_temp_files
Create tablespace:
Create tablespace PROD_DATA datafile '/2a/data/oracle12c/SDADS/datafile/PROD_DATA.dbf' size 1G;
Create tablespace with autoextend on:
CREATE TABLESPACE PROD DATAFILE '/2a/data/oracle12c/SDADS/datafile/PROD.dbf' SIZE 500M AUTOEXTEND ON;
CREATE TABLESPACE PROD_IDATA DATAFILE '/2a/data/oracle12c/SDADS/datafile/PROD_IDATA .dbf' SIZE 500M AUTOEXTEND ON NEXT 50M
MAXSIZE UNLIMITED;
Resize Datafile:
alter database datafile '/2a/data/oracle12c/SDADS/datafile/prod1.dbf' resize 300m;
Add a new datafile to existing tablespace:
alter tablespace PROD add datafile '/2a/data/oracle12c/SDADS/datafile/prod1.dbf' size 20m;
Drop Tablespace:
Drop a tablespace without removing the physical database files:
drop tablespace PROD_DATA;
Drop tablespace including the physical datafiles:
Drop tablespace PROD_DATA including contents and datafiles;
Make tablespace Read only:
alter tablespace PROD_DATA READ ONLY;
alter tablespace PROD_DATA read write;
Take tablespace offline:
ALTER TABLESPACE PROD_DATA OFFLINE;
ALTER TABLESPACE PROD_DATA ONLINE;
Create a temporary tablespace:
CREATE TEMPORARY TABLESPACE TEMP1 '/1a/app/oracle/oradata/ORCL/datafile/temp01.dbf' size 1G autoextend on next 200M;
Adding a tempfile:
alter tablespace TEMP1 add tempfile '/1a/app/oracle/oradata/ORCL/datafile/temp02.dbf' size 1G autoextend on next 200M;
Resize tempfile:
alter database tempfile '/1a/app/oracle/oradata/ORCL/datafile/temp02.dbf' resize 2G;
Drop Temp Tablespace:
select FILE_NAME,FILE_ID,TABLESPACE_NAME,
AUTOEXTENSIBLE,STATUS,maxbytes/1024/1024/1024,BYTES/1024/1024/1024
from dba_temp_files ;
Verify if any active sessions are using the temp files:
SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;
If any active sessions using the temp tablespace kill it:
alter system kill session 'SID_NUMBER, SERIAL#' immediate;
Drop Temp Tablespace:
ALTER TABLESAPCETEMP drop tempfile '/1a/app/oracle/oradata/ORCL/datafile/temp01.dbf';
ALTER DATABASE TEMPFILE '/1a/app/oracle/oradata/ORCL/datafile/temp02.dbf' DROP INCLUDING DATAFILES;
Tablespaces and Datafiles Info:
SELECT file_name, tablespace_name, bytes/1024/1024 MB
FROM dba_data_files;
select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024,STATUS,AUTOEXTENSIBLE from dba_data_files;
SELECT tablespace_name, ROUND((used_space / tablespace_size) * 100, 2) pct_used
FROM dba_tablespace_usage_metrics;
Tablespace Usage Query:
SELECT
df.tablespace_name,
ROUND(df.total_mb,2) total_mb,
ROUND(fs.free_mb,2) free_mb,
ROUND(df.total_mb - fs.free_mb,2) used_mb,
ROUND((df.total_mb - fs.free_mb)/df.total_mb * 100,2) used_pct
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_mb
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes)/1024/1024 free_mb
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name
ORDER BY used_pct DESC;
Tablespace Usage Report:
SET LINES 200
SET PAGES 100
COLUMN tablespace_name FORMAT A20
COLUMN total_mb FORMAT 999999
COLUMN used_mb FORMAT 999999
COLUMN free_mb FORMAT 999999
COLUMN pct_used FORMAT 999.99
SELECT
df.tablespace_name,
ROUND(df.total_mb) total_mb,
ROUND(df.total_mb - fs.free_mb) used_mb,
ROUND(fs.free_mb) free_mb,
ROUND((df.total_mb - fs.free_mb)/df.total_mb * 100, 2) pct_used
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_mb
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes)/1024/1024 free_mb
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name
ORDER BY pct_used DESC;
TEMP Tablespace Monitoring:
SELECT
tablespace_name,
SUM(bytes_used)/1024/1024 used_mb,
SUM(bytes_free)/1024/1024 free_mb
FROM v$temp_space_header
GROUP BY tablespace_name;
Verify Tablesapce are comsuming above 85%:
SELECT tablespace_name || ' ' || used_percent
FROM dba_tablespace_usage_metrics
WHERE used_percent > 85;
Monitor longest-running transactions with:
SELECT s.sid, s.serial#, t.used_ublk, t.used_urec
FROM v$transaction t, v$session s
WHERE t.ses_addr = s.saddr;
Check usage:
SELECT tablespace_name, SUM(blocks)*8/1024 MB_USED
FROM v$sort_usage
GROUP BY tablespace_name;
Check Overall Usage:
SELECT tablespace_name, ROUND((used_space/tablespace_size)*100, 2) pct_used
FROM dba_tablespace_usage_metrics
ORDER BY pct_used DESC;
Identify Top Space Consumers:
SELECT owner, segment_name, segment_type, bytes/1024/1024 MB
FROM dba_segments
ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY;
TEMP Usage by Session:
SELECT s.username, s.sid, u.tablespace, u.blocks*8/1024 AS mb_used
FROM v$sort_usage u, v$session s
WHERE u.session_addr = s.saddr
ORDER BY mb_used DESC;
