💾 Module 18: Tablespaces & Storage Management

-- Create permanent tablespace
CREATE TABLESPACE users
  DATAFILE '/oracle/data/users01.dbf' SIZE 500M
  AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;

-- Create temporary tablespace
CREATE TEMPORARY TABLESPACE temp
  TEMPFILE '/oracle/data/temp01.dbf' SIZE 1G
  AUTOEXTEND ON NEXT 100M
  EXTENT MANAGEMENT LOCAL
  UNIFORM SIZE 1M;

-- Create undo tablespace
CREATE UNDO TABLESPACE undotbs1
  DATAFILE '/oracle/data/undotbs01.dbf' SIZE 500M
  AUTOEXTEND ON NEXT 50M
  RETENTION GUARANTEE;

-- Create bigfile tablespace
CREATE BIGFILE TABLESPACE big_tbs
  DATAFILE '/oracle/data/big01.dbf' SIZE 32T
  EXTENT MANAGEMENT LOCAL
  UNIFORM SIZE 4M;

-- Alter tablespace to add datafile
ALTER TABLESPACE users
ADD DATAFILE '/oracle/data/users02.dbf' SIZE 500M;

-- Resize existing datafile
ALTER DATABASE DATAFILE '/oracle/data/users01.dbf'
RESIZE 1000M;

-- Enable autoextend on datafile
ALTER DATABASE DATAFILE '/oracle/data/users01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

-- Make tablespace read-only
ALTER TABLESPACE users READ ONLY;

-- Make tablespace read-write
ALTER TABLESPACE users READ WRITE;

-- Drop tablespace and contents
DROP TABLESPACE users INCLUDING CONTENTS
AND DATAFILES;

-- Create ASM disk group (as SYSASM user)
CREATE DISKGROUP data_dg
  EXTERNAL REDUNDANCY
  DISK '/dev/sdd'
  DISK '/dev/sde'
  DISK '/dev/sdf';

-- Create with normal redundancy
CREATE DISKGROUP data_dg
  NORMAL REDUNDANCY
  FAILGROUP controller1 DISK '/dev/sdd'
  FAILGROUP controller2 DISK '/dev/sde';

-- Create with high redundancy
CREATE DISKGROUP data_dg
  HIGH REDUNDANCY
  DISK '/dev/sdd', '/dev/sde', '/dev/sdf',
       '/dev/sdg', '/dev/sdh', '/dev/sdi';

-- Add disk to diskgroup
ALTER DISKGROUP data_dg ADD DISK
'/dev/sdg';

-- Drop diskgroup
DROP DISKGROUP data_dg INCLUDING CONTENTS;

-- Tablespace usage percentage
SELECT tablespace_name,
       ROUND(used_space*100/total_space, 2) percent_used
FROM (
  SELECT tablespace_name,
         SUM(bytes) total_space,
         SUM(DECODE(status, 'AVAILABLE', bytes, 0)) used_space
  FROM dba_free_space
  GROUP BY tablespace_name
)
ORDER BY percent_used DESC;

-- Datafile sizes
SELECT tablespace_name, file_name, bytes/1024/1024 size_mb
FROM dba_data_files
ORDER BY tablespace_name;

-- Top 10 objects by size
SELECT segment_name, segment_type, bytes/1024/1024 size_mb
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM')
ORDER BY bytes DESC
FETCH FIRST 10 ROWS ONLY;

-- ASM diskgroup status
SELECT name, total_mb, usable_file_mb,
       ROUND(100 * (total_mb - usable_file_mb)/total_mb, 2) percent_used
FROM v$asm_diskgroup;

-- Enable automatic segment space management
CREATE TABLESPACE users
  DATAFILE '/oracle/data/users01.dbf' SIZE 500M
  SEGMENT SPACE MANAGEMENT AUTO;

-- Monitor segment growth
SELECT owner, segment_name, segment_type, bytes/1024 size_kb
FROM dba_segments
WHERE bytes > 100*1024*1024
ORDER BY bytes DESC;

-- Shrink tablespace (remove empty space)
ALTER TABLESPACE users SHRINK SPACE;

-- Move table to reclaim space
ALTER TABLE emp MOVE TABLESPACE users;