Oracle Tablespace Management

In Oracle Database, tablespaces are used to store data. There are several types:

System Tablespace: Stores Oracle’s essential system data, such as the data dictionary and internal tables.
Undo Tablespace: Manages data for rolling back transactions, ensuring consistency and recovery.
Temporary Tablespace: Stores temporary data generated during operations like sorting or creating indexes, but doesn’t hold user data permanently.
Default Tablespace: Where user data (tables, indexes) are stored by default, unless another tablespace is specified.
Bigfile Tablespace: A large, single file for storing data, simplifying file management by reducing the number of files.
Smallfile Tablespace: Composed of multiple smaller files, and is the typical option for most databases.
SYSAUX Tablespace: A supplementary tablespace to the System tablespace. It holds various database components and reduces the load on the System tablespace. It stores data for features like Oracle Streams, Oracle Enterprise Manager, and others.
User Tablespace: Specifically for user data. Users can create their own tablespaces to store their application data, providing better organization and separation from system data.

Find all the essential Oracle Tablespace Management Scripts to monitor and manage your tablespaces effortlessly. Perfect for simplifying database tasks!

Check All Tablespaces

all_tablespaces.sql

set line 100
set pagesize 100
col TABLESPACE format a30;
col TOTAL_MB format 999,999,999
col USED_MB format 999,999,999
col FREE_MB format 999,999,999
col %_USED format 999.99
break on report 
compute sum of TOTAL_MB on report
compute sum of USED_MB on report
compute sum of FREE_MB on report
select          a.tablespace_name "TABLESPACE",
		(b.bytes/1048576) "TOTAL_MB",
		nvl((c.bytes/1048576),0) "USED_MB",
		nvl((d.bytes/1048576),0) "FREE_MB",
		nvl((c.bytes/b.bytes)*100,0) "%_USED"
from            (select tablespace_name from dba_tablespaces) a,
		(select tablespace_name,bytes from sys.sm$ts_avail) b,
		(select tablespace_name,bytes from sys.sm$ts_USED) c,
		(select tablespace_name,bytes from sys.sm$ts_FREE) d
where           a.tablespace_name= b.tablespace_name(+)
and             a.tablespace_name= c.tablespace_name(+)
and             a.tablespace_name= d.tablespace_name(+)
order by 5 desc;

Check a Specific Tablespace

tablespace.sql

set lines 100
set pages 1000
col tspace format a30
colu tot_ts_size format 99999.999
colu free_ts_size format 99999.999
colu used_ts_size format 99999.999
select df.tablespace_name tspace,
df.bytes/(1024*1024*1024) tot_ts_size_GB,
(df.bytes/(1024*1024*1024) -sum(fs.bytes)/(1024*1024*1024)) Used_ts_size_GB,
sum(fs.bytes)/(1024*1024*1024) free_ts_size_GB,
round(sum(fs.bytes)*100/df.bytes) free_pct,
round((df.bytes-sum(fs.bytes))*100/df.bytes) used_pct1
from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files  group by tablespace_name ) df
where fs.tablespace_name = df.tablespace_name and fs.tablespace_name='&name'
group by df.tablespace_name, df.bytes
order by 6 desc
/

Check Tablespaces with Autoextension Enabled

tablespace_auto.sql

set linesize 100
set pagesize 100
select
a.tablespace_name,
round(SUM(a.bytes)/(1024*1024*1024)) CURRENT_GB,
round(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),b.maxextend*(select block_size from dba_tablespaces where tablespace_name = a.tablespace_name)/(1024*1024*1024)))) MAX_GB,
round(SUM(a.bytes)/(1024*1024*1024) - round(c.Free/1024/1024/1024),2) USED_GB,
round((SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),b.maxextend*(select block_size from dba_tablespaces where tablespace_name = a.tablespace_name)/(1024*1024*1024))) - (SUM(a.bytes)/(1024*1024*1024) -round(c.Free/1024/1024/1024))),2) FREE_GB,
round(100*(SUM(a.bytes)/(1024*1024*1024) -round(c.Free/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),b.maxextend*(select block_size from dba_tablespaces where tablespace_name = a.tablespace_name)/(1024*1024*1024)))),2) USED_PCT
from
dba_data_files a,
sys.filext$ b,
(SELECT
d.tablespace_name ,sum(nvl(c.bytes,0)) Free
FROM
dba_tablespaces d,
DBA_FREE_SPACE c
WHERE
d.tablespace_name = c.tablespace_name(+)
group by d.tablespace_name) c
WHERE
a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name and a.tablespace_name='&name'
GROUP BY a.tablespace_name, c.Free/1024
ORDER BY tablespace_name;

Top 20 Segments for a Given Tablespace

top20_segments.sql

set lines 200 pages 45
col OWNER for a30
col PARTITION_NAME for a20
col SEGMENT_NAME for a30
col SEGMENT_TYPE for a30
select * from (
select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 size_in_gb ,TABLESPACE_NAME,RELATIVE_FNO 
from dba_segments
where TABLESPACE_NAME like '%&TS_name%'
Order by size_in_gb desc 
) where rownum < 21;

Check High Water Mark

high_watermark.sql

set lines 200 pages 100
col file_name for a80
 
SELECT file_name,
            ceil((nvl(hwm, 1) * (select block_size from v$datafile where file# = a.file_id))/1024/1024) smallest_MB,
            ceil(blocks * (select block_size from v$datafile where file# = a.file_id)/1024/1024) currsize_MB,
            ceil(blocks * (select block_size from v$datafile where file# = a.file_id)/1024/1024) - ceil((nvl(hwm, 1) * (select block_size from v$datafile where file# = a.file_id))/1024/1024) savings_MB
FROM dba_data_files a,
            (
                        SELECT file_id,
                                    max(block_id + blocks - 1) hwm
                        FROM dba_extents
                        GROUP BY file_id
                        ) b
WHERE a.file_id = b.file_id(+)
            AND a.file_name LIKE '%/path/to/your/tablespace/data% order by 4 desc;

Note: You need to change the mount point path in the above query as needed.

Check UNDO Tablespace Usage

undo_tablespace.sql

select df.tablespace_name "Tablespace", 
totalusedspace "Used MB", 
(df.totalspace - tu.totalusedspace) "Free MB", 
df.totalspace "Total MB", 
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free" 
from 
(select tablespace_name, 
SUM (DECODE (Autoextensible,'YES', maxbytes/ 1048576,'NO', BYTES/ 1048576)) TotalSpace 
from dba_data_files 
group by tablespace_name) df, 
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name 
from dba_segments 
group by tablespace_name) tu 
where df.tablespace_name = tu.tablespace_name and df.tablespace_name='&Tab' order by 5;

Check Datafile Details for a Tablespace

datafiles.sql

Set lines 400
col file_name for a65
select FILE_NAME, TABLESPACE_NAME,BYTES/1024/1024, MAXBYTES/1024/1024 ,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='&TABLESPACE_NAME';

Verify Datafile Creation Time

datafile_creation_time.sql

SET LINESIZE 200
SET PAGESIZE 50
SET COLSEP '    '
COLUMN creation_time FORMAT A20 HEADING 'Creation Time'
COLUMN name FORMAT A50 HEADING 'Datafile Name'

SELECT TO_CHAR(CREATION_TIME, 'DD-MM-YYYY HH24:MI:SS') AS creation_time, 
       name 
FROM v$datafile 
WHERE name = 'C:\ORACLE19C\ORADATA\ORCL\SYSTEM01.DBF';

Add Datafiles

-- Add Datafile to W3BUDDY_TEST Tablespace (Windows)
ALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE 'C:\ORCL\ORADATA\W3BUDDY\W3BUDDY_TEST01.DBF' SIZE 1G;

-- Add Datafile with Autoextend to W3BUDDY_TEST Tablespace (Windows)
ALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE SIZE 1024M AUTOEXTEND ON MAXSIZE 32765M;

-- Add Datafile with Autoextend to W3BUDDY_TEST Tablespace (Windows)
ALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE 'D:\ORCL\ORADATA\W3BUDDY\DBF\W3BUDDY_TEST_45.DBF' SIZE 100M AUTOEXTEND ON NEXT 64M MAXSIZE 31G;

-- Add Datafile to W3BUDDY_TEST Tablespace (Linux)
ALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE '/var/opt/oracle/oradata/W3BUDDY/W3BUDDY_TEST_2.DBF' SIZE 31G;

-- Add Datafile with Autoextend to W3BUDDY_TEST Tablespace (Linux)
ALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/W3BUDDY_TEST_03.DBF' SIZE 1G AUTOEXTEND ON NEXT 64M MAXSIZE 20G;

-- Add Tempfile to W3BUDDY_TEST Tablespace (Linux)
ALTER TABLESPACE W3BUDDY_TEST ADD TEMPFILE '/w3buddy/oracle/oradata/W3BUDDY/W3BUDDY_TEST_TEMP_5.DBF' SIZE 20G;

Resize Datafiles

-- Resize the datafile for W3BUDDY_TEST_DATAFILE_1.dbf in W3BUDDY_TEST tablespace
ALTER DATABASE DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/W3BUDDY_TEST_DATAFILE_1.dbf' RESIZE 31G;

-- Resize the datafile for W3BUDDY_TEST_DATAFILE_2.dbf in W3BUDDY_TEST tablespace
ALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/W3BUDDY_TEST_DATAFILE_2.dbf' SIZE 30G;

-- Resize the datafile for W3BUDDY_TEST_UNDOTBS.dbf in W3BUDDY_TEST tablespace
ALTER DATABASE DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/W3BUDDY_TEST_UNDOTBS.dbf' AUTOEXTEND ON MAXSIZE 25G;

-- Resize the datafile for W3BUDDY_TEST_DATAFILE_3.dbf in W3BUDDY_TEST tablespace
ALTER DATABASE DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/W3BUDDY_TEST_DATAFILE_3.dbf' AUTOEXTEND ON MAXSIZE 15G;

Enable/Disable Autoextend for a Datafile

-- Alter database datafile to enable autoextend
ALTER DATABASE DATAFILE '/u01/data/datafile_1.dbf' AUTOEXTEND ON;

-- Alter database datafile to disable autoextend
ALTER DATABASE DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/datafile_2.dbf' AUTOEXTEND OFF;

Take a Datafile Offline/Online

Alter database datafile '/u01/data/users02.dbf' offline; 
Alter database datafile '/u01/data/users02.dbf' online;

Drop a Datafile

Alter tablespace USERS drop datafile '/u01/data/users02.dbf';

Rename or Move a Datafile

-- Alter database to move datafile to a new location
ALTER DATABASE MOVE DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/datafile_1.dbf' TO '/w3buddy/oracle/oradata/W3BUDDY/datafile_2.dbf';

For 11g, Follow Below Steps (Requires Downtime for the Datafile)

-- Make the tablespace offline
ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf' OFFLINE;

-- Move the file physically to a new location
mv /home/oracle/app/oracle/oradata/cdb1/testin1.dbf /home/oracle/producing1.dbf

-- Rename the file at the database level
ALTER DATABASE RENAME FILE '/home/oracle/app/oracle/oradata/cdb1/testin1.dbf' TO '/home/oracle/producing1.dbf';

-- Recover the datafile
RECOVER DATAFILE 37;

Create a New Tablespace

-- Create tablespace W3BUDDY_TEST with a datafile (5GB size with autoextend)
CREATE TABLESPACE W3BUDDY_TEST DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/datafile_1.dbf' SIZE 5G AUTOEXTEND ON NEXT 500M;

-- Create tablespace W3BUDDY_TEST2 with a datafile (1GB size with autoextend, max size 5GB)
CREATE TABLESPACE W3BUDDY_TEST2 DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/datafile_2.dbf' SIZE 1G AUTOEXTEND ON NEXT 64M MAXSIZE 5G;

Create a Bigfile Tablespace

-- Create BIGFILE tablespace W3BUDDY_BIGTS with a datafile (100GB size with autoextend)
CREATE BIGFILE TABLESPACE W3BUDDY_BIGTS DATAFILE '/w3buddy/oracle/oradata/W3BUDDY/datafile_bigts.dbf' SIZE 100G AUTOEXTEND ON NEXT 1G;

Create a Tablespace on ASM Diskgroup

Create tablespace DATA datafile '+DATAG' size 5G autoextend on next 500M;

Rename a Tablespace in Oracle Database

-- Query to check the file details for FILE_ID 37
SELECT file_id, file_name, tablespace_name 
FROM dba_data_files 
WHERE file_id = 37;

-- Output:
-- FILE_ID    FILE_NAME                                          TABLESPACE_NAME
-- -------    -------------------------------------------------  ------------------------------
-- 37         /w3buddy/oracle/oradata/W3BUDDY/datafile_1.dbf      W3BUDDY_TEST

-- Rename the tablespace_name from W3BUDDY_TEST to W3BUDDY_PRODUCING
ALTER TABLESPACE W3BUDDY_TEST RENAME TO W3BUDDY_PRODUCING;

-- Query to verify the updated details for FILE_ID 37
SELECT file_id, file_name, tablespace_name 
FROM dba_data_files 
WHERE file_id = 37;

-- Output:
-- FILE_ID    FILE_NAME                                           TABLESPACE_NAME
-- ---------- --------------------------------------------------- ------------------------------
-- 37         /w3buddy/oracle/oradata/W3BUDDY/datafile_1.dbf       W3BUDDY_PRODUCING

Drop Tablespace

-- Drop the tablespace W3BUDDY_TEST
DROP TABLESPACE W3BUDDY_TEST;

-- Query to check file names associated with the tablespace W3BUDDY_TEST
SELECT file_name 
FROM dba_data_files 
WHERE tablespace_name = 'W3BUDDY_TEST';

-- Drop the tablespace W3BUDDY_TEST including the physical datafiles
DROP TABLESPACE W3BUDDY_TEST INCLUDING CONTENTS AND DATAFILES;

-- Take the tablespace W3BUDDY_TEST_DATA offline
ALTER TABLESPACE W3BUDDY_TEST_DATA OFFLINE;

-- Drop the tablespace W3BUDDY_TEST_DATA including contents and datafiles
DROP TABLESPACE W3BUDDY_TEST_DATA INCLUDING CONTENTS AND DATAFILES;

Tablespace Growth

tablespace_growth.sql

set lines 400 pages 200
alter session set nls_date_format='yyyy-mm-dd';
with t as (
select ss.run_time,ts.name,round(su.tablespace_size*dt.block_size/1024/1024/1024,2) alloc_size_gb,
round(su.tablespace_usedsize*dt.block_size/1024/1024/1024,2) used_size_gb
from
dba_hist_tbspc_space_usage su,
(select trunc(BEGIN_INTERVAL_TIME) run_time,max(snap_id) snap_id from dba_hist_snapshot 
group by trunc(BEGIN_INTERVAL_TIME) ) ss,
v$tablespace ts,
dba_tablespaces dt
where su.snap_id = ss.snap_id
and   su.tablespace_id = ts.ts#
and   ts.name          = dt.tablespace_name 
and   ts.name          =upper('USERS'))
select e.run_time,e.name,e.alloc_size_gb,e.used_size_gb curr_used_size_gb,b.used_size_gb prev_used_size_gb,
case when e.used_size_gb > b.used_size_gb then to_char(e.used_size_gb - b.used_size_gb)
     when e.used_size_gb = b.used_size_gb then '***NO DATA GROWTH'
     when e.used_size_gb < b.used_size_gb then '******DATA PURGED' end variance
from t e, t b
where e.run_time = b.run_time + 1
order by 1;

Datafile Shrink Analysis and Resizing Script

datafile_shrink.sql

set lines 1000 
SELECT ceil( blocks*(a.BlockSize)/1024/1024) "Current Size", 
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Smallest Poss.", 
   ceil( blocks*(a.BlockSize)/1024/1024) - 
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Savings", 
   'alter database datafile '''|| file_name || ''' resize ' || 
      ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024/100)*100 || 'm;' "Command" 
FROM (SELECT a.*, p.value BlockSize FROM dba_data_files a 
JOIN v$parameter p ON p.Name='db_block_size') a 
LEFT JOIN (SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b 
ON a.file_id = b.file_id 
WHERE ceil( blocks*(a.BlockSize)/1024/1024) - ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) 
   > 100 /* Minimum MB it must shrink by to be considered. */ 
ORDER BY "Savings" Desc;

This script analyze the current size, smallest possible size, and potential savings for each datafile in your Oracle database. It generates an ALTER DATABASE DATAFILE command to shrink the datafile to its smallest possible size while retaining all data.

Breakdown:

Purpose:
- Identify datafiles with unused space.
- Suggest resizing to reclaim disk space without impacting data.

Key Components:
- Current Size: The current size of the datafile in MB, calculated from the total number of blocks multiplied by the block size.
- Smallest Poss.: The smallest possible size the datafile can be, based on the high watermark (hwm) of used blocks.
- Savings: The difference between the current size and the smallest possible size, representing the amount of space that can be reclaimed.
- Command: The SQL command to resize the datafile to its smallest possible size, rounded to the nearest 100 MB for safety.

Logic:
- Uses DBA_DATA_FILES to get the metadata of each datafile.
- Joins with DB_BLOCK_SIZE from V$PARAMETER to calculate sizes in MB.
- Joins with DBA_EXTENTS to determine the high watermark (hwm), which represents the highest allocated block.
- Filters datafiles where the potential savings exceed 100 MB.
- Orders the output by potential savings in descending order to prioritize larger space savings.

Generated Command:
- The script generates a command for each eligible datafile:sqlCopy codeALTER DATABASE DATAFILE ” RESIZE m;
- This command can be executed to shrink the datafile.

Preconditions:
- Ensure the database allows shrinking datafiles (not all file systems or setups support it).
- The datafile cannot shrink below the high watermark (hwm).

Safety Note:
- Test the suggested commands in a non-production environment first.
- Confirm the high watermark is accurate to avoid truncating active data.

For more details, refer to the official Oracle documentation on Oracle Tablespace Management.

Oracle Tablespace Management

Check All Tablespaces

Check a Specific Tablespace

Check Tablespaces with Autoextension Enabled

Top 20 Segments for a Given Tablespace

Check High Water Mark

Check UNDO Tablespace Usage

Check Datafile Details for a Tablespace

Verify Datafile Creation Time

Add Datafiles

Resize Datafiles

Enable/Disable Autoextend for a Datafile

Take a Datafile Offline/Online

Drop a Datafile

Rename or Move a Datafile

For 11g, Follow Below Steps (Requires Downtime for the Datafile)

Create a New Tablespace

Create a Bigfile Tablespace

Create a Tablespace on ASM Diskgroup

Rename a Tablespace in Oracle Database

Drop Tablespace

Tablespace Growth

Datafile Shrink Analysis and Resizing Script

Breakdown:

SQL Query to Check User-wise INACTIVE Session Count in Oracle

How to Create an Oracle Directory for Export/Import

Data Guard Broker Commands for Managing Oracle Database

Fixing Index Ownership Issues in Oracle

Backing Up Oracle Directories and Grants

How to Fix ORA-01017: invalid username/password; logon denied

Leave a Reply Cancel reply

Check All Tablespaces

Check a Specific Tablespace

Check Tablespaces with Autoextension Enabled

Top 20 Segments for a Given Tablespace

Check High Water Mark

Check UNDO Tablespace Usage

Check Datafile Details for a Tablespace

Verify Datafile Creation Time

Add Datafiles

Resize Datafiles

Enable/Disable Autoextend for a Datafile

Take a Datafile Offline/Online

Drop a Datafile

Rename or Move a Datafile

For 11g, Follow Below Steps (Requires Downtime for the Datafile)

Create a New Tablespace

Create a Bigfile Tablespace

Create a Tablespace on ASM Diskgroup

Rename a Tablespace in Oracle Database

Drop Tablespace

Tablespace Growth

Datafile Shrink Analysis and Resizing Script

Breakdown:

You might like

Leave a Reply Cancel reply