tablespace drop

SELECT 

    t.tablespace_name,

    t.owner AS schema_name,

    ROUND(SUM(t.bytes) / 1024 / 1024 / 1024, 2) AS size_gb

FROM 

    dba_segments t

WHERE 

    t.owner = 'YOUR_SCHEMA_NAME'  -- Replace with your schema name

GROUP BY 

    t.tablespace_name, t.owner

ORDER BY 

    size_gb DESC;

================

DECLARE

    v_schema_name VARCHAR2(30) := 'YOUR_SCHEMA_NAME';  -- Replace with your schema name

    v_sql VARCHAR2(4000);

    v_count NUMBER;

BEGIN

    DBMS_OUTPUT.PUT_LINE('TABLE_NAME                          | ROW_COUNT');

    DBMS_OUTPUT.PUT_LINE('------------------------------------|----------');

    FOR rec IN (SELECT table_name 

                FROM dba_tables 

                WHERE owner = v_schema_name

                ORDER BY table_name)

    LOOP

        v_sql := 'SELECT COUNT(*) FROM ' || v_schema_name || '.' || rec.table_name;

        EXECUTE IMMEDIATE v_sql INTO v_count;

        DBMS_OUTPUT.PUT_LINE(RPAD(rec.table_name, 35) || '| ' || v_count);

    END LOOP;

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END;

/

SELECT p.table_owner AS schema_name, p.table_name, p.partition_name, p.high_value AS partition_range, -- For range partitions; shows the upper bound ROUND(SUM(s.bytes) / 1024 / 1024 / 1024, 2) AS approx_size_gb, -- Approximate size as a proxy if counts are expensive 'SELECT COUNT(*) FROM ' || p.table_owner || '.' || p.table_name || ' PARTITION (' || p.partition_name || ');' AS count_query -- Generate query for exact row count FROM dba_tab_partitions p JOIN dba_segments s ON p.table_owner = s.owner AND p.table_name = s.segment_name AND p.partition_name = s.partition_name WHERE p.table_owner = 'YOUR_SCHEMA_NAME' -- Replace with schema AND p.table_name = 'YOUR_TABLE_NAME' -- Replace with table GROUP BY p.table_owner, p.table_name, p.partition_name, p.high_value ORDER BY p.partition_position;

==============

CREATE TABLE datapump_job_status (

    owner_name VARCHAR2(128),

    job_name VARCHAR2(128),

    state VARCHAR2(30),

    degree NUMBER,

    last_updated TIMESTAMP DEFAULT SYSTIMESTAMP,

    CONSTRAINT pk_datapump_job_status PRIMARY KEY (owner_name, job_name)

);

COMMENT ON TABLE datapump_job_status IS 'Stores status of Data Pump jobs';

COMMENT ON COLUMN datapump_job_status.owner_name IS 'Owner of the Data Pump job';

COMMENT ON COLUMN datapump_job_status.job_name IS 'Name of the Data Pump job';

COMMENT ON COLUMN datapump_job_status.state IS 'Current state of the job (e.g., EXECUTING, IDLING, COMPLETED)';

COMMENT ON COLUMN datapump_job_status.degree IS 'Parallelism degree of the job';

COMMENT ON COLUMN datapump_job_status.last_updated IS 'Timestamp of last status update';

INSERT INTO datapump_job_status (owner_name, job_name, state, degree)

VALUES ('RDSADM', 'SYS_IMPORT_05', 'EXECUTING', 16);

COMMIT;

===

DECLARE

  v_handle NUMBER;

  v_job_state VARCHAR2(30);

BEGIN

  -- Attach to the job

  v_handle := DBMS_DATAPUMP.ATTACH(job_name => 'FULL_EXPORT_HEMANT');

  DBMS_OUTPUT.PUT_LINE('Attached to job: FULL_EXPORT_HEMANT');

  -- Get current job state

  DBMS_DATAPUMP.GET_STATUS(

    handle => v_handle,

    mask => DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS,

    timeout => 0,

    job_state => v_job_state

  );

  DBMS_OUTPUT.PUT_LINE('Current job state: ' || v_job_state);

  -- Stop the job (graceful stop)

  DBMS_DATAPUMP.STOP_JOB(

    handle => v_handle,

    immediate => 0, -- 0 for graceful, 1 for immediate

    keep_master => 0 -- 0 to drop master table, 1 to keep it

  );

  DBMS_OUTPUT.PUT_LINE('Job stop requested');

  -- Detach from the job

  DBMS_DATAPUMP.DETACH(v_handle);

EXCEPTION

  WHEN OTHERS THEN

    IF v_handle != 0 THEN

      DBMS_DATAPUMP.DETACH(v_handle);

    END IF;

    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

    RAISE;

END;

/

=====

SELECT 'DROP TABLE "' || owner || '"."' || segment_name || '" CASCADE CONSTRAINTS /*+ PARALLEL(32) */;' AS drop_statement

FROM dba_segments

WHERE segment_type = 'TABLE'

AND owner IN ('SCOTT', 'HR', 'TEST') -- Replace with your schema list

ORDER BY owner, segment_name;

```

=======

BEGIN

  FOR ts IN (

    SELECT tablespace_name

    FROM dba_tablespaces

    WHERE tablespace_name NOT IN ('RDSADMIN', 'RDS_SQL_TS', 'RDS-TEMP_SQLT_TS', 

                                  'SYSAUX', 'SYSTEM', 'TEMP', 'UNDO_T1', 'USERS')

  ) LOOP

    EXECUTE IMMEDIATE 'DROP TABLESPACE ' || ts.tablespace_name || 

                      ' INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';

  END LOOP;

END;

/

=======

DECLARE
  v_ts_count NUMBER;
  v_job_name VARCHAR2(100) := 'DROP_TABLESPACES_JOB_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS');
BEGIN
  -- Step 1: Verify the count of tablespaces to drop
  SELECT COUNT(*)
  INTO v_ts_count
  FROM dba_tablespaces
  WHERE tablespace_name NOT IN ('RDSADMIN', 'RDS_SQL_TS', 'RDS-TEMP_SQLT_TS', 
                                'SYSAUX', 'SYSTEM', 'TEMP', 'UNDO_T1', 'USERS');
  IF v_ts_count != 12 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Expected 12 tablespaces to drop, but found ' || v_ts_count);
  END IF;
  -- Step 2: Create a logging table if it doesn't exist
  BEGIN
    EXECUTE IMMEDIATE '
      CREATE TABLE drop_ts_log (
        log_id NUMBER GENERATED ALWAYS AS IDENTITY,
        tablespace_name VARCHAR2(128),
        status VARCHAR2(20),
        message VARCHAR2(4000),
        log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT pk_drop_ts_log PRIMARY KEY (log_id)
      )';
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -955 THEN NULL; -- Table already exists
      ELSE RAISE;
      END IF;
  END;
  -- Step 3: Create a scheduler job to drop tablespaces
  DBMS_SCHEDULER.CREATE_JOB(
    job_name   => v_job_name,
    job_type   => 'PLSQL_BLOCK',
    job_action => q'[
      DECLARE
        v_sql VARCHAR2(4000);
      BEGIN
        -- Log start of job
        INSERT INTO drop_ts_log (tablespace_name, status, message)
        VALUES ('NONE', 'START', 'Starting tablespace drop job');
        -- Loop through tablespaces to drop
        FOR ts IN (
          SELECT tablespace_name
          FROM dba_tablespaces
          WHERE tablespace_name NOT IN ('RDSADMIN', 'RDS_SQL_TS', 'RDS-TEMP_SQLT_TS', 
                                        'SYSAUX', 'SYSTEM', 'TEMP', 'UNDO_T1', 'USERS')
        ) LOOP
          BEGIN
            v_sql := 'DROP TABLESPACE ' || DBMS_ASSERT.SQL_OBJECT_NAME(ts.tablespace_name) || 
                     ' INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';
            EXECUTE IMMEDIATE v_sql;
            INSERT INTO drop_ts_log (tablespace_name, status, message)
            VALUES (ts.tablespace_name, 'SUCCESS', 'Tablespace dropped successfully');
          EXCEPTION
            WHEN OTHERS THEN
              INSERT INTO drop_ts_log (tablespace_name, status, message)
              VALUES (ts.tablespace_name, 'ERROR', 'Failed to drop: ' || SQLERRM);
          END;
        END LOOP;
        -- Log completion
        INSERT INTO drop_ts_log (tablespace_name, status, message)
        VALUES ('NONE', 'COMPLETED', 'Tablespace drop job completed');
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN
          INSERT INTO drop_ts_log (tablespace_name, status, message)
          VALUES ('NONE', 'ERROR', 'Job failed: ' || SQLERRM);
          COMMIT;
          RAISE;
      END;
    ]',
    start_date => SYSTIMESTAMP,
    enabled    => TRUE,
    auto_drop  => TRUE,
    comments   => 'Job to drop all non-system tablespaces except specified ones'
  );
  -- Log job submission
  INSERT INTO drop_ts_log (tablespace_name, status, message)
  VALUES ('NONE', 'SUBMITTED', 'Job ' || v_job_name || ' submitted successfully');
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Job ' || v_job_name || ' submitted. Check drop_ts_log table for progress.');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    RAISE;
END;
/