DBA Hub

📋Steps in this guide1/15

Mastering Tablespace Management in Oracle Database

Learn Oracle tablespace management with real DBA scenarios, tips for TEMP, UNDO, monitoring, and best practices to avoid space issues.

oracle configurationintermediate
by OracleDba
17 views
1

1. Understanding What You’re Managing

A tablespace in Oracle is like a logical container that stores your data in physical datafiles on disk. You can think of it as a well-organized warehouse where each section stores different kinds of goods. You’ll deal with three main types: - Permanent tablespaces — where actual table/index data lives. - Temporary tablespaces — used for sorts, joins, and other temporary work. - Undo tablespaces — store undo data for rollbacks and read consistency. 💡 Imagine this: You’re running a large sales report at month-end. The query has several GROUP BY and ORDER BY clauses. Where does all the sorting happen? TEMP tablespace. If it’s undersized or poorly managed, your report will fail, and you’ll have a queue of angry users at your desk.
2

2. Creating Tablespaces — Doing It Right from the Start

When you create a new tablespace, you’re not just allocating space, you’re also making a performance decision that affects long-term stability. Example: Why this matters for you: - AUTOEXTEND helps avoid sudden “unable to extend segment” errors, but without a MAXSIZE limit, you could fill up the filesystem overnight. - For OLTP systems, AUTOALLOCATE is fine, but in data warehouses, UNIFORM extents give you more predictable space usage. Hypothetical: Imagine giving AUTOEXTEND ON with no max size for a dev database. Overnight, a poorly written ETL process runs amok and fills 200GB of space, crashing the test environment. A small setting could have prevented hours of cleanup.

Code/Command (click line numbers to comment):

1
2
3
4
CREATE TABLESPACE sales_data 
DATAFILE '/u01/app/oracle/oradata/ORCL/sales01.dbf' SIZE 500M 
AUTOEXTEND ON NEXT 50M MAXSIZE 5G 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
3

3. Monitoring — Your Early Warning System

Here’s the truth: if you only check tablespace usage when an error appears, you’re already too late. Run this query daily (or automate it): Hypothetical: You log in Monday morning and see your USERS tablespace at 92% usage. Do you wait for it to hit 100%? No. You investigate growth patterns and plan to clean up or extend space before it becomes an emergency.

Code/Command (click line numbers to comment):

1
2
3
SELECT tablespace_name, 
       ROUND((used_space / tablespace_size) * 100, 2) pct_used
FROM dba_tablespace_usage_metrics;
4

4. TEMP Tablespace — The Silent Killer

TEMP is like a spare desk you use only when the main one overflows. You don’t notice it until it’s cluttered, and then you can’t work. Oracle uses TEMP for: - Sorting (ORDER BY, GROUP BY) - Hash joins - Index rebuilds - Global temporary table operations Check usage: 💡 Scenario: You’re rebuilding a huge index during off-peak hours. Halfway through, the rebuild fails because TEMP was too small. The downtime window closes, the app team is frustrated, and you’re left explaining why a “simple rebuild” took all night. Pro Tip: Always size TEMP for the heaviest operation in your workload, not just average usage.

Code/Command (click line numbers to comment):

1
2
3
SELECT tablespace_name, SUM(blocks)*8/1024 MB_USED
FROM v$sort_usage
GROUP BY tablespace_name;
5

5. Undo Tablespace — Your Transaction Safety Net

Undo ensures: - Transactions can roll back - Queries see consistent data - Flashback features work Best practice: - Enable AUTOEXTEND with a reasonable MAXSIZE. - Monitor longest-running transactions with: Scenario: You run a bulk update of 50M rows in one transaction. Halfway through, you hit ORA-30036. The transaction fails and rolls back, wasting hours. If you’d split it into smaller batches or pre-sized undo correctly, it would’ve succeeded.

Code/Command (click line numbers to comment):

1
2
3
SELECT s.sid, s.serial#, t.used_ublk, t.used_urec
FROM v$transaction t, v$session s
WHERE t.ses_addr = s.saddr;
6

6. Resizing & Adding Datafiles — Quick Fixes That Work

When space runs out, you have two quick tools: Add a new datafile: Resize existing datafile: Pro Tip: Before adding more space, run a segment shrink or rebuild to reclaim unused space. Otherwise, you’re just feeding an inefficient system.

Code/Command (click line numbers to comment):

1
2
3
ALTER DATABASE ADD DATAFILE '/u01/oradata/sales02.dbf' SIZE 200M AUTOEXTEND ON;

ALTER DATABASE DATAFILE '/u01/oradata/sales01.dbf' RESIZE 800M;
7

7. Bigfile vs Smallfile Tablespaces

- Smallfile tablespaces — multiple datafiles, each up to 32GB. - Bigfile tablespaces — single datafile up to TBs in size. When to use Bigfile: - Large data warehouses with ASM or OMF. - When reducing file management overhead matters more than recovery granularity.
8

8. Partitioning Storage Across Tablespaces

Sometimes it’s smart to separate data logically: - Keep indexes in a separate tablespace for I/O optimization. - Store large objects (LOBs) in dedicated tablespaces. - Use different storage tiers (fast SSD for hot data, slower HDD for archive). Scenario: An e-commerce client saw performance drops in checkout queries. Splitting product indexes into a dedicated SSD-backed tablespace improved speed by 30%.
9

9. Automation — Your Best Friend

Manual checks fail in large environments. Automate alerts with: - OEM Thresholds - Shell/Python scripts - Integration with monitoring tools like Zabbix, Nagios Example script for alerting when >85% full:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
sqlplus -s / as sysdba <<EOF
SET PAGESIZE 0 FEEDBACK OFF
SELECT tablespace_name || ' ' || used_percent 
FROM dba_tablespace_usage_metrics 
WHERE used_percent > 85;
EOF
10

10. Troubleshooting Tablespace Issues

- TEMP Full: Identify query with v$sort_usage. Kill or optimize it. - Undo Full: Commit in smaller batches, check runaway transactions. - Autoextend Failures: Ensure filesystem has free space. - ORA-01653 Unable to Extend: Check free space, resize datafile, or move objects.
11

11. My Golden Rules for Tablespace Management

- Separate data, index, undo, and temp tablespaces. - Always use MAXSIZE in production. - Size undo based on transaction volume. - Monitor TEMP daily in sort-heavy environments. - Investigate sudden growth, don’t just add space. - Automate alerts for usage thresholds. - Document growth trends for capacity planning.
12

Daily Tablespace Checklist (Quick Health Checks)

These are your “morning coffee” checks — fast but effective. - Check Overall Usage Ensure no tablespace is above 85% usage. 2. TEMP Tablespace Usage Make sure TEMP isn’t stuck at high usage, could indicate a runaway session. 3. Undo Tablespace Usage Watch for long-running transactions. 4. Autoextend Status Ensure MAXSIZE is set for all datafiles.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT tablespace_name, ROUND((used_space/tablespace_size)*100, 2) pct_used
FROM dba_tablespace_usage_metrics
ORDER BY pct_used DESC;

SELECT tablespace_name, SUM(blocks)*8/1024 MB_USED
FROM v$sort_usage
GROUP BY tablespace_name;

SELECT tablespace_name, file_id, ROUND(bytes/1024/1024, 2) MB
FROM dba_data_files
WHERE tablespace_name LIKE 'UNDO%';

SELECT file_name, autoextensible, maxbytes/1024/1024 AS max_mb
FROM dba_data_files;
13

Weekly Tablespace Checklist (Trend Analysis & Cleanup)

- Growth Trend Report Compare this week’s usage to last week’s — look for unusual jumps. - Identify Top Space Consumers Helps you spot big tables/indexes growing unexpectedly. 3. TEMP Usage by Session See if any session is hogging TEMP unnecessarily. 4. Reclaim Unused Space - Use ALTER TABLE … SHRINK SPACE for reclaiming from big tables. - Rebuild fragmented indexes.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
SELECT owner, segment_name, segment_type, bytes/1024/1024 MB
FROM dba_segments
ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY;

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;
14

Monthly Tablespace Checklist (Capacity Planning & Optimization)

1. Forecast Growth Export past 6 months’ tablespace usage and project future needs 2. Validate Tablespace Layout - Are indexes, LOBs, and data still in optimal tablespaces? - Can any inactive tables be archived? 3. TEMP & Undo Stress Testing Simulate heavy batch jobs in staging to ensure sizing is correct. 4. Documentation Update - Record current tablespace sizes, autoextend settings, and growth rates. 💡 Pro Tip from my experience: I once had a client who never tracked TEMP growth trends. For 6 months, usage was creeping up silently until a quarter-end batch failed spectacularly. After implementing this checklist, they haven’t had a single space-related outage in 3 years.
15

Final Thoughts

Tablespace management isn’t just another DBA task, it’s about protecting uptime and user trust . If you and I are both honest, most space issues aren’t sudden; they’re slow-build problems we could’ve seen coming. If you monitor regularly, size wisely, and act before the alerts hit, you’ll avoid 90% of the late-night emergency calls. And trust me, nothing beats the peace of mind of knowing your database won’t run out of space in the middle of a business-critical process. Happy Reading! ANKUSH😎

Comments (0)

Please to add comments

No comments yet. Be the first to comment!