Analyzing Object Storage and Segments

1. Assessing Table Size and Row Counts

To evaluate storage consumption across all schemas, join the data dictionary views regarding tables and segments. The following query calculates size in gigabytes and retrieves row estimates.

-- Segment size analysis across all schemas
SELECT /*+ PARALLEL(4) */ 
    obj.owner AS "Schema Owner",
    obj.table_name AS "Object Name",
    seg.tablespace_name AS "Storage Location",
    ROUND(seg.total_bytes / 1024 / 1024 / 1024, 2) AS "Size_GB",
    obj.num_rows AS "Estimated Rows",
    obj.partitioned AS "Is Partitioned"
FROM all_tables obj
LEFT JOIN (
    SELECT 
        SUM(bytes) AS total_bytes,
        owner,
        segment_name,
        MAX(tablespace_name) AS tablespace_name
    FROM dba_segments 
    GROUP BY segment_name, owner
) seg ON obj.table_name = seg.segment_name AND obj.owner = seg.owner
WHERE seg.total_bytes IS NOT NULL
ORDER BY "Size_GB" DESC;

For the current schema context, utilize user-specific views:

-- Segment size analysis for current user
SELECT /*+ PARALLEL(4) */ 
    obj.table_name AS "Object Name",
    seg.tablespace_name AS "Storage Location",
    ROUND(seg.total_bytes / 1024 / 1024 / 1024, 2) AS "Size_GB",
    obj.num_rows AS "Estimated Rows",
    obj.partitioned AS "Is Partitioned"
FROM user_tables obj
LEFT JOIN (
    SELECT 
        SUM(bytes) AS total_bytes,
        segment_name,
        MAX(tablespace_name) AS tablespace_name
    FROM user_segments 
    GROUP BY segment_name
) seg ON seg.segment_name = obj.table_name
WHERE seg.total_bytes IS NOT NULL
ORDER BY "Size_GB" DESC;

2. Index Storage Consumption

Identify large indexes that may impact maintenance windows or storage limits.

-- Index size details (DBA view)
SELECT 
    seg.segment_name AS "Index Name",
    seg.tablespace_name AS "Tablespace",
    tbl.table_name AS "Parent Table",
    ROUND(seg.bytes / 1024 / 1024 / 1024, 2) AS "Size_GB",
    seg.blocks AS "Block Count"
FROM dba_segments seg
JOIN all_indexes tbl ON seg.segment_name = tbl.index_name
WHERE seg.segment_type = 'INDEX'
ORDER BY seg.bytes DESC;

3. Partition Management

Convert a standard table to a partitioned structure using interval partitioning.

-- Convert table to interval partitioned
ALTER TABLE AUDIT_TRAIL MODIFY
  PARTITION BY RANGE (TRANSACTION_DATE) 
  INTERVAL (NUMTODSINTERVAL(1, 'DAY')) 
  STORE IN (TS_ARCHIVE)
  ( 
    PARTITION P_INIT VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
    TABLESPACE TS_ARCHIVE
  ) ONLINE;

Create a utility function to inspect partition boundaries:

CREATE OR REPLACE TYPE ty_partition_info AS OBJECT (
  part_name VARCHAR2(50),
  part_boundary VARCHAR2(500)
);

CREATE OR REPLACE TYPE ty_partition_list AS TABLE OF ty_partition_info;

CREATE OR REPLACE FUNCTION fn_list_partitions(p_table_name VARCHAR2) 
RETURN ty_partition_list AS
  l_parts ty_partition_list := ty_partition_list();
  l_boundary VARCHAR2(500);
BEGIN
   FOR rec IN (
     SELECT partition_name, high_value
     FROM user_tab_partitions
     WHERE table_name = UPPER(p_table_name)
   ) LOOP
     l_boundary := SUBSTR(TO_CHAR(rec.high_value), 1, 50);
     l_parts.EXTEND();
     l_parts(l_parts.COUNT) := ty_partition_info(rec.partition_name, l_boundary);
   END LOOP;
   RETURN l_parts;
END fn_list_partitions;

Execute the function to view results:

SELECT * FROM TABLE(fn_list_partitions('AUDIT_TRAIL'));

4. Index Maintenance Operations

Remove constraints or retrieve DDL for reconstruction.

-- Drop primary key constraint
ALTER TABLE AUDIT_TRAIL DROP CONSTRAINT PK_AUDIT_TRAIL;

-- Extract table DDL
SELECT DBMS_METADATA.GET_DDL('TABLE', 'AUDIT_TRAIL', 'SCHEMA_OWNER') FROM DUAL;

Create local and global indexes on partitioned tables:

CREATE TABLE temp_hash_data (
  col_id VARCHAR2(100),
  col_data VARCHAR2(100),
  col_meta VARCHAR2(100)
) PARTITION BY HASH (col_id) PARTITIONS 4;

-- Local partitioned index
CREATE INDEX idx_temp_local ON temp_hash_data(col_id) LOCAL;

-- Global index
CREATE INDEX idx_temp_global ON temp_hash_data(col_data);

-- Global partitioned index
CREATE INDEX idx_temp_global_part ON temp_hash_data(col_meta) GLOBAL
PARTITION BY HASH (col_meta) PARTITIONS 4;

If an index build is interrupted, clean the state:

DECLARE
  l_success BOOLEAN;
  l_obj_id NUMBER;
BEGIN
  l_obj_id := 98177;
  l_success := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN();
  COMMIT;
END;

Tablespace Capacity Planning

1. Utilization Metrics

Monitor free versus allocated space per tablespace.

SELECT 
    ts.tablespace_name,
    ROUND(ts.total_mb / 1024, 2) AS "Total_GB",
    ROUND((ts.total_mb - ts.free_mb) / 1024, 2) AS "Used_GB",
    ROUND(ts.free_mb / 1024, 2) AS "Free_GB",
    ROUND((ts.total_mb - ts.free_mb) / ts.total_mb * 100, 2) AS "Usage_Pct"
FROM (
    SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS free_mb
    FROM dba_free_space
    GROUP BY tablespace_name
) free,
(
    SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_mb
    FROM dba_data_files
    GROUP BY tablespace_name
) ts
WHERE free.tablespace_name = ts.tablespace_name
ORDER BY "Usage_Pct" DESC;

Expand storage capacity when threshold are breached:

-- Add datafile with auto-extension
ALTER TABLESPACE TS_ARCHIVE ADD DATAFILE 
  '/oradata/DBINST/ts_archive_02.dbf' 
  SIZE 50G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;

-- Add tempfile to temporary tablespace
ALTER TABLESPACE TEMP ADD TEMPFILE 
  SIZE 20G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;

2. High Water Mark Analysis

Identify tables where allocated blocks significantly exceed actual data volume.

SELECT 
    owner,
    table_name,
    ROUND((blocks * 8192 / 1024 / 1024), 2) AS "Allocated_MB",
    ROUND((num_rows * avg_row_len / 1024 / 1024), 2) AS "Data_MB",
    ROUND((blocks * 8192 / 1024 / 1024) - (num_rows * avg_row_len / 1024 / 1024), 2) AS "Wasted_MB",
    ROUND((num_rows * avg_row_len) / NULLIF(blocks * 8192, 0) * 100, 2) AS "Efficiency_Pct"
FROM dba_tables
WHERE (num_rows * avg_row_len) / NULLIF(blocks * 8192, 0) < 0.5
AND owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN')
AND blocks > 0
ORDER BY "Wasted_MB" DESC;

3. Defragmentation Procedures

Reclaim space by moving tables on line. Note that indexes must be rebuilt afterward.

-- Move heap table
ALTER TABLE AUDIT_TRAIL MOVE ONLINE;

-- Move specific partition
ALTER TABLE AUDIT_TRAIL_PART MOVE PARTITION P_202301 ONLINE;

Automate the removal of empty partitions:

SET SERVEROUTPUT ON;
DECLARE 
  l_cmd VARCHAR2(4000);
  l_count NUMBER;
BEGIN
  FOR rec IN (
    SELECT partition_name 
    FROM user_tab_partitions 
    WHERE table_name = 'AUDIT_TRAIL_PART' 
    AND partition_name <> 'P_INIT'
  ) LOOP
    l_cmd := 'SELECT COUNT(1) FROM AUDIT_TRAIL_PART PARTITION(' || rec.partition_name || ')';
    EXECUTE IMMEDIATE l_cmd INTO l_count;
    
    IF l_count = 0 THEN
       l_cmd := 'ALTER TABLE AUDIT_TRAIL_PART DROP PARTITION ' || rec.partition_name || ' UPDATE GLOBAL INDEXES';
       DBMS_OUTPUT.PUT_LINE(l_cmd);
    END IF;
  END LOOP;
END;

Rebuild indexes after movement operations:

SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD ONLINE PARALLEL 4 NOLOGGING;' 
FROM all_indexes 
WHERE table_name = 'AUDIT_TRAIL';

-- Gather statistics post-maintenance
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCHEMA_OWNER',
    tabname => 'AUDIT_TRAIL',
    degree => 4,
    cascade => TRUE,
    force => TRUE
  );
END;

4. Provisioning Users and Storage

-- Create data tablespace
CREATE TABLESPACE TS_DATA 
  DATAFILE '/opt/oracle/ts_data_01.dbf' SIZE 1G 
  EXTENT MANAGEMENT LOCAL 
  SEGMENT SPACE MANAGEMENT AUTO;

-- Create user
CREATE USER APP_SERVICE_ACCT 
  IDENTIFIED BY "SecurePass123!" 
  DEFAULT TABLESPACE TS_DATA 
  TEMPORARY TABLESPACE TEMP 
  PROFILE DEFAULT;

-- Grant privileges
GRANT CONNECT, RESOURCE TO APP_SERVICE_ACCT;
GRANT CREATE ANY TABLE, CREATE ANY SEQUENCE TO APP_SERVICE_ACCT;
GRANT UNLIMITED TABLESPACE TO APP_SERVICE_ACCT;

Compression and Concurrency Control

1. Identify Candidates for Compression

Find large tables that are not currently compressed.

WITH segment_data AS (
  SELECT 
    segment_name, 
    tablespace_name, 
    SUM(bytes) / 1024 / 1024 / 1024 AS size_gb
  FROM dba_segments
  WHERE owner = 'SCHEMA_OWNER'
  AND segment_type = 'TABLE'
  GROUP BY segment_name, tablespace_name
)
SELECT 
    sd.segment_name,
    sd.size_gb,
    tbl.num_rows,
    tbl.compression,
    CASE WHEN pt.table_name IS NULL THEN 'NO' ELSE 'YES' END AS is_partitioned
FROM segment_data sd
JOIN all_tables tbl ON sd.segment_name = tbl.table_name
LEFT JOIN all_part_tables pt ON tbl.table_name = pt.table_name
WHERE tbl.compression = 'DISABLED'
AND sd.segment_name LIKE '%HISTORY%'
ORDER BY sd.size_gb DESC;

2. Apply Compression

Enable advanced row compression for heap tables.

DECLARE 
  l_cmd VARCHAR2(4000);
  l_target_table VARCHAR2(100) := 'TRANSACTION_LOG';
BEGIN
  FOR rec IN (
    SELECT table_name 
    FROM user_tables 
    WHERE table_name = l_target_table 
    AND compression = 'DISABLED'
  ) LOOP
    l_cmd := 'ALTER TABLE ' || rec.table_name || ' MOVE ONLINE ROW STORE COMPRESS ADVANCED';
    EXECUTE IMMEDIATE l_cmd;
    
    FOR idx IN (
      SELECT index_name 
      FROM user_indexes 
      WHERE table_name = rec.table_name 
      AND index_type <> 'LOB'
    ) LOOP
      l_cmd := 'ALTER INDEX ' || idx.index_name || ' REBUILD ONLINE PARALLEL 4 NOLOGGING COMPRESS ADVANCED HIGH';
      EXECUTE IMMEDIATE l_cmd;
      l_cmd := 'ALTER INDEX ' || idx.index_name || ' NOPARALLEL LOGGING';
      EXECUTE IMMEDIATE l_cmd;
    END LOOP;
  END LOOP;
END;

3. Lock Detection and Resolution

Identify sessions holding locks on specific objects.

SELECT 
    obj.object_name,
    TO_CHAR(NVL(sess.sql_exec_start, sess.prev_exec_start), 'YYYY-MM-DD HH24:MI:SS') AS start_time,
    sess.status,
    sess.osuser,
    sess.machine,
    sess.program,
    NVL(sess.sql_id, sess.prev_sql_id) AS sql_id,
    'ALTER SYSTEM DISCONNECT SESSION ''' || sess.sid || ',' || sess.serial# || ''' IMMEDIATE;' AS kill_cmd
FROM gv$locked_object lock
JOIN dba_objects obj ON lock.object_id = obj.object_id
JOIN gv$session sess ON lock.inst_id = sess.inst_id AND lock.session_id = sess.sid
WHERE obj.object_name = 'AUDIT_TRAIL'
ORDER BY start_time;

If session termination fails via SQL, identify the OS process:

ps -ef | grep ora_
kill -9 <pid>

Administrative Utilities

1. Flashback and Recovery

Enable row movement to allow flashback operations.

ALTER TABLE TEMP_DATA ENABLE ROW MOVEMENT;

-- Restore dropped table
FLASHBACK TABLE TEMP_DATA TO BEFORE DROP;

-- Restore data to specific timestamp
FLASHBACK TABLE TEMP_DATA 
  TO TIMESTAMP TO_TIMESTAMP('2023-09-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS');

2. Job Scheduler

Automate maintenance tasks using DBMS_SCHEDULER.

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'JOB_MAINT_HWM_CLEAN',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'PROC_RECLAIM_SPACE',
    start_date      => SYSDATE,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
    enabled         => TRUE,
    auto_drop       => TRUE,
    comments        => 'Daily high water mark maintenance'
  );
END;

3. Connectivity and Diagnostics

Manage database links and enspect performance metrics.

-- Create public database link
CREATE PUBLIC DATABASE LINK DBL_REMOTE_PROD 
  CONNECT TO REMOTE_USER IDENTIFIED BY "RemotePass!"
  USING '192.168.1.50:1521/PRODDB';

-- Close link session
BEGIN
    DBMS_SESSION.CLOSE_DATABASE_LINK('DBL_REMOTE_PROD');
END;

-- Check invalid procedures
SELECT object_name, status 
FROM dba_objects 
WHERE object_type LIKE '%PROC%' 
AND owner = 'SCHEMA_OWNER' 
AND status = 'INVALID';

-- Top SQL by elapsed time
SELECT 
    v.sql_id,
    v.elapsed_time / 1000000 AS elapsed_sec,
    v.cpu_time / 1000000 AS cpu_sec,
    v.disk_reads,
    v.sql_text
FROM gv$sql v
WHERE v.elapsed_time > 1000000
ORDER BY v.elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;