Sunday, September 7, 2025

DMS - Part 3


tep 1: Mapping Old Tablespaces to New Tablespaces

-- cross_schema_ts_check.sql
-- Comprehensive Cross-Schema Tablespace Usage Analysis (Fixed for ORA-06550)
-- Run as DBA user. Edit l_schemas below with your 7 schemas.

SET SERVEROUTPUT ON SIZE UNLIMITED
SET PAGESIZE 0
SET LINESIZE 200
SET FEEDBACK OFF
SET HEADING OFF

SPOOL cross_schema_report.txt

DECLARE
    -- Edit these: Your 7 schemas (add/remove as needed)
    TYPE t_schema_array IS TABLE OF VARCHAR2(128);
    l_schemas t_schema_array := t_schema_array('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4', 'SCHEMA5', 'SCHEMA6', 'SCHEMA7');
   
    -- For cross-usage tracking
    TYPE t_ts_schema_map IS TABLE OF t_schema_array INDEX BY VARCHAR2(128); -- Key: tablespace, Value: array of schemas using it
    l_ts_to_schemas t_ts_schema_map;
   
    l_schema VARCHAR2(128);
    l_tablespace VARCHAR2(128);
    l_count NUMBER;
    l_shared_count NUMBER := 0;
    
    -- Helper for schema list
    l_schema_list VARCHAR2(4000) := l_schemas(1);
BEGIN
    -- Build schema list manually
    FOR j IN 2..l_schemas.COUNT LOOP
        l_schema_list := l_schema_list || ', ' || l_schemas(j);
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('=== CROSS-SCHEMA TABLESPACE USAGE REPORT ===');
    DBMS_OUTPUT.PUT_LINE('Generated on: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    DBMS_OUTPUT.PUT_LINE('Schemas Analyzed: ' || l_schema_list);
    DBMS_OUTPUT.PUT_LINE(CHR(10) || '=== PER-SCHEMA TABLESPACE USAGE ===');
    DBMS_OUTPUT.PUT_LINE('SCHEMA_NAME                  | UNIQUE_TABLESPACES (Count)');
    DBMS_OUTPUT.PUT_LINE('-----------------------------|----------------------------');
   
    -- Loop per schema: Collect tablespaces used by its objects
    FOR i IN 1..l_schemas.COUNT LOOP
        l_schema := UPPER(l_schemas(i));
       
        -- Query to get unique tablespaces for this schema (all object types)
        SELECT LISTAGG(tablespace_name, ', ') WITHIN GROUP (ORDER BY tablespace_name),
               COUNT(DISTINCT tablespace_name)
        INTO l_tablespace, l_count
        FROM (
            -- Tables
            SELECT tablespace_name FROM dba_tables WHERE owner = l_schema
            UNION
            -- Indexes
            SELECT tablespace_name FROM dba_indexes WHERE owner = l_schema
            UNION
            -- LOBs
            SELECT tablespace_name FROM dba_lobs WHERE owner = l_schema
            UNION
            -- Table Partitions/Subpartitions
            SELECT tablespace_name FROM dba_tab_partitions WHERE owner = l_schema
            UNION
            SELECT tablespace_name FROM dba_tab_subpartitions WHERE owner = l_schema
            UNION
            -- Index Partitions/Subpartitions
            SELECT tablespace_name FROM dba_ind_partitions WHERE owner = l_schema
            UNION
            SELECT tablespace_name FROM dba_ind_subpartitions WHERE owner = l_schema
        );
       
        IF l_count = 0 THEN
            l_tablespace := 'NO OBJECTS FOUND';
        END IF;
       
        DBMS_OUTPUT.PUT_LINE(RPAD(l_schema, 30) || ' | ' || RPAD(l_tablespace, 30) || ' (' || l_count || ')');
       
        -- Track cross-usage: For each TS in this schema, add schema to map
        IF l_count > 0 THEN
            FOR rec IN (
                SELECT DISTINCT tablespace_name FROM (
                    -- Same UNION query as above, but for this schema
                    SELECT tablespace_name FROM dba_tables WHERE owner = l_schema
                    UNION SELECT tablespace_name FROM dba_indexes WHERE owner = l_schema
                    UNION SELECT tablespace_name FROM dba_lobs WHERE owner = l_schema
                    UNION SELECT tablespace_name FROM dba_tab_partitions WHERE owner = l_schema
                    UNION SELECT tablespace_name FROM dba_tab_subpartitions WHERE owner = l_schema
                    UNION SELECT tablespace_name FROM dba_ind_partitions WHERE owner = l_schema
                    UNION SELECT tablespace_name FROM dba_ind_subpartitions WHERE owner = l_schema
                )
            ) LOOP
                l_tablespace := rec.tablespace_name;
                IF NOT l_ts_to_schemas.EXISTS(l_tablespace) THEN
                    l_ts_to_schemas(l_tablespace) := t_schema_array();
                END IF;
                l_ts_to_schemas(l_tablespace).EXTEND;
                l_ts_to_schemas(l_tablespace)(l_ts_to_schemas(l_tablespace).COUNT) := l_schema;
            END LOOP;
        END IF;
    END LOOP;
   
    DBMS_OUTPUT.PUT_LINE(CHR(10) || '=== CROSS-SCHEMA SHARED TABLESPACES (Vice Versa Overlaps) ===');
    DBMS_OUTPUT.PUT_LINE('TABLESPACE_NAME              | SCHEMAS_USING_IT (Count)                  | NOTES');
    DBMS_OUTPUT.PUT_LINE('-----------------------------|-------------------------------------------|------');
   
    -- Report shared tablespaces (used by 2+ schemas)
    l_shared_count := 0;
    l_ts_to_schemas.FIRST;
    LOOP
        EXIT WHEN l_ts_to_schemas.NEXT(l_tablespace) IS NULL;
        IF l_ts_to_schemas(l_tablespace).COUNT > 1 THEN
            l_shared_count := l_shared_count + 1;
            
            -- Fixed: Manual LISTAGG for schemas per TS (similar issue here)
            DECLARE
                l_schemas_str VARCHAR2(4000) := l_ts_to_schemas(l_tablespace)(1);
            BEGIN
                FOR k IN 2..l_ts_to_schemas(l_tablespace).COUNT LOOP
                    l_schemas_str := l_schemas_str || ', ' || l_ts_to_schemas(l_tablespace)(k);
                END LOOP;
                DBMS_OUTPUT.PUT_LINE(RPAD(l_tablespace, 30) || ' | ' || l_schemas_str || ' (' || l_ts_to_schemas(l_tablespace).COUNT || ') | *** SHARED - CHECK DEPENDENCIES ***');
            END;
        END IF;
        l_tablespace := l_ts_to_schemas.NEXT(l_tablespace);
    END LOOP;
   
    IF l_shared_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No shared tablespaces found across the 7 schemas.');
    ELSE
        DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Total shared tablespaces: ' || l_shared_count || '. Review for vice-versa usage (e.g., Schema A objects in Schema B''s default TS).');
    END IF;
   
    -- Optional: Detailed objects per schema/tablespace (uncomment to enable)
    /*
    DBMS_OUTPUT.PUT_LINE(CHR(10) || '=== DETAILED OBJECTS PER SCHEMA/TS (SAMPLE) ===');
    FOR i IN 1..l_schemas.COUNT LOOP
        l_schema := UPPER(l_schemas(i));
        FOR rec IN (
            SELECT object_type, object_name, tablespace_name
            FROM (
                SELECT 'TABLE' object_type, table_name object_name, tablespace_name FROM dba_tables WHERE owner = l_schema
                UNION ALL SELECT 'INDEX', index_name, tablespace_name FROM dba_indexes WHERE owner = l_schema
                UNION ALL SELECT 'LOB', table_name || '.' || column_name, tablespace_name FROM dba_lobs WHERE owner = l_schema
                -- Add partitions here if needed
            )
            ORDER BY object_type, object_name
        ) LOOP
            DBMS_OUTPUT.PUT_LINE(l_schema || ' | ' || rec.object_type || ' | ' || rec.object_name || ' | ' || rec.tablespace_name);
        END LOOP;
    END LOOP;
    */
   
    DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Report complete. Check for overlaps and adjust tablespace assignments if needed.');
END;
/

SPOOL OFF
SET PAGESIZE 14
SET LINESIZE 80
SET FEEDBACK ON
SET HEADING ON

DECLARE
    CURSOR c_mappings IS
        SELECT new_tablespace,
               LISTAGG(old_tablespace, ',') AS old_tablespaces  -- No space after comma for clean pairs
        FROM ts_mappings
        GROUP BY new_tablespace
        ORDER BY new_tablespace;
    v_missing_tablespaces NUMBER;
    v_table_exists NUMBER;
    v_remap_clause VARCHAR2(32767) := 'REMAP_TABLESPACE=';  -- Build the clause here
    v_first_pair BOOLEAN := TRUE;
BEGIN
    -- Validate ts_mappings table existence
    SELECT COUNT(*) INTO v_table_exists
    FROM user_tables
    WHERE table_name = 'TS_MAPPINGS';
    IF v_table_exists = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Table ts_mappings does not exist. Run populate_ts_mappings.sql first.');
    END IF;
    
    -- Check for unmapped tablespaces in schemas
    SELECT COUNT(*) INTO v_missing_tablespaces
    FROM (
        SELECT DISTINCT tablespace_name
        FROM dba_segments
        WHERE owner IN ('SCHEMA_01', 'SCHEMA_02', 'SCHEMA_03', 'SCHEMA_04', 'SCHEMA_05', 'SCHEMA_06', 'SCHEMA_07')
        MINUS
        SELECT old_tablespace
        FROM ts_mappings
    );
    IF v_missing_tablespaces > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Warning: ' || v_missing_tablespaces || ' tablespaces used by schemas are not mapped in ts_mappings');
    END IF;
    
    -- Output header with fixed-width columns
    DBMS_OUTPUT.PUT_LINE(RPAD('NEW_TABLESPACE NAME', 25) || ' | ' || RPAD('OLD_TABLESPACE NAME(S)', 60));
    DBMS_OUTPUT.PUT_LINE(RPAD('-', 25, '-') || '-+-' || RPAD('-', 60, '-'));
    
    -- Output mappings and build remap clause
    FOR rec IN c_mappings LOOP
        DECLARE
            v_old_list VARCHAR2(4000);
            v_pairs VARCHAR2(32767);
        BEGIN
            v_old_list := rec.old_tablespaces;
            IF LENGTH(v_old_list) > 4000 THEN
                v_old_list := SUBSTR(v_old_list, 1, 3997) || '...';  -- Truncate if needed
            END IF;
            DBMS_OUTPUT.PUT_LINE(RPAD(rec.new_tablespace, 25) || ' | ' || v_old_list);
            
            -- Generate pairs: OLD1:NEW,OLD2:NEW,...
            SELECT LISTAGG(old_tablespace || ':' || rec.new_tablespace, ',')
                   WITHIN GROUP (ORDER BY old_tablespace)
            INTO v_pairs
            FROM ts_mappings
            WHERE new_tablespace = rec.new_tablespace;
            
            -- Append to clause
            IF NOT v_first_pair THEN
                v_remap_clause := v_remap_clause || ',' || v_pairs;
            ELSE
                v_remap_clause := v_remap_clause || v_pairs;
                v_first_pair := FALSE;
            END IF;
        END;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(RPAD('-', 25, '-') || '-+-' || RPAD('-', 60, '-'));
    
    -- Output the final remap clause
    IF LENGTH(v_remap_clause) > 14 THEN  -- If any mappings exist
        DBMS_OUTPUT.PUT_LINE('');  -- Blank line
        DBMS_OUTPUT.PUT_LINE('Use this in impdp:');
        DBMS_OUTPUT.PUT_LINE(v_remap_clause);
    ELSE
        DBMS_OUTPUT.PUT_LINE('');  -- Blank line
        DBMS_OUTPUT.PUT_LINE('No mappings found. No REMAP_TABLESPACE needed.');
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error querying ts_mappings: ' || SQLERRM);
        RAISE;
END;
/

DECLARE
    CURSOR c_mappings IS
        SELECT new_tablespace,
               LISTAGG(old_tablespace, ', ') WITHIN GROUP (ORDER BY old_tablespace) AS old_tablespaces
        FROM ts_mappings
        GROUP BY new_tablespace
        ORDER BY new_tablespace;
    v_missing_tablespaces NUMBER;
    v_table_exists NUMBER;
BEGIN
    -- Validate ts_mappings table existence
    SELECT COUNT(*) INTO v_table_exists
    FROM user_tables
    WHERE table_name = 'TS_MAPPINGS';
    IF v_table_exists = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Table ts_mappings does not exist. Run populate_ts_mappings.sql first.');
    END IF;
    
    -- Check for unmapped tablespaces in schemas
    SELECT COUNT(*) INTO v_missing_tablespaces
    FROM (
        SELECT DISTINCT tablespace_name
        FROM dba_segments
        WHERE owner IN ('SCHEMA_01', 'SCHEMA_02', 'SCHEMA_03', 'SCHEMA_04', 'SCHEMA_05', 'SCHEMA_06', 'SCHEMA_07')
        MINUS
        SELECT old_tablespace
        FROM ts_mappings
    );
    IF v_missing_tablespaces > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Warning: ' || v_missing_tablespaces || ' tablespaces used by schemas are not mapped in ts_mappings');
    END IF;
    
    -- Output header with fixed-width columns (adjusted for better alignment)
    DBMS_OUTPUT.PUT_LINE(RPAD('NEW_TABLESPACE NAME', 25) || ' | ' || RPAD('OLD_TABLESPACE NAME(S)', 60));
    DBMS_OUTPUT.PUT_LINE(RPAD('-', 25, '-') || '-+-' || RPAD('-', 60, '-'));
    
    -- Output mappings with formatted columns (truncate long lists to avoid buffer issues)
    FOR rec IN c_mappings LOOP
        DECLARE
            v_old_list VARCHAR2(4000);
        BEGIN
            v_old_list := rec.old_tablespaces;
            -- Truncate if too long (for Oracle < 12c compatibility; remove if using 12c+)
            IF LENGTH(v_old_list) > 4000 THEN
                v_old_list := SUBSTR(v_old_list, 1, 3997) || '...';
            END IF;
            DBMS_OUTPUT.PUT_LINE(RPAD(rec.new_tablespace, 25) || ' | ' || v_old_list);
        END;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(RPAD('-', 25, '-') || '-+-' || RPAD('-', 60, '-'));
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error querying ts_mappings: ' || SQLERRM);
        RAISE;
END;
/

To handle your consolidation (40 old tablespaces to 8 new ones, with groupings like 5 old to 1 new), we'll start by creating a mapping table if you don't have one already. This is similar to the ts_config from the optimized approach but tailored to your needs. It ensures all old tablespaces are accounted for, with a fallback to a default new tablespace (e.g., 'NEW_DATA8') for any unmapped ones.

Create and Populate Mapping Table

Run this on your source database to set up the mappings. Customize the INSERT statements with your actual 40 old tablespaces and their groupings to the 8 new ones. The example below uses your sample (assuming PLO_DATA_01, PLO_DATA_02, ILO_DATA_01, ILA_INDEX_02, PLA_DATA_01 all map to TRU_DATA_01 as a group).

sql
-- Create mapping table (if not exists)
CREATE TABLE ts_mappings (
    old_tablespace VARCHAR2(30) NOT NULL,
    new_tablespace VARCHAR2(30) NOT NULL,
    mapping_group NUMBER,  -- Optional: Group ID for 5-to-1 style consolidations
    notes VARCHAR2(200),   -- Description or reason
    CONSTRAINT pk_ts_mappings PRIMARY KEY (old_tablespace)
);

-- Populate with your mappings (example for one group; repeat for all 40 old -> 8 new)
-- Group 1: 5 old to TRU_DATA_01
INSERT INTO ts_mappings (old_tablespace, new_tablespace, mapping_group, notes) VALUES ('PLO_DATA_01', 'TRU_DATA_01', 1, 'Group 1 consolidation');
INSERT INTO ts_mappings (old_tablespace, new_tablespace, mapping_group, notes) VALUES ('PLO_DATA_02', 'TRU_DATA_01', 1, 'Group 1 consolidation');
INSERT INTO ts_mappings (old_tablespace, new_tablespace, mapping_group, notes) VALUES ('ILO_DATA_01', 'TRU_DATA_01', 1, 'Group 1 consolidation');
INSERT INTO ts_mappings (old_tablespace, new_tablespace, mapping_group, notes) VALUES ('ILA_INDEX_02', 'TRU_DATA_01', 1, 'Group 1 consolidation');
INSERT INTO ts_mappings (old_tablespace, new_tablespace, mapping_group, notes) VALUES ('PLA_DATA_01', 'TRU_DATA_01', 1, 'Group 1 consolidation');

-- Add more groups (e.g., for other 35 old tablespaces mapping to remaining 7 new ones)
-- INSERT INTO ts_mappings ... for Group 2 to 8

-- Auto-populate any missing old tablespaces from dba_tablespaces with fallback
MERGE INTO ts_mappings tm
USING (SELECT tablespace_name FROM dba_tablespaces WHERE tablespace_name NOT IN (SELECT old_tablespace FROM ts_mappings)) src
ON (tm.old_tablespace = src.tablespace_name)
WHEN NOT MATCHED THEN
    INSERT (old_tablespace, new_tablespace, mapping_group, notes)
    VALUES (src.tablespace_name, 'NEW_DATA8', 8, 'Fallback for unmapped tablespaces');

COMMIT;

Script to List Old to New Mappings

This query outputs the mappings in a grouped format, similar to your example (old tablespaces listed under their new one). It uses hierarchical formatting for readability.

sql
-- Query to display mappings (grouped by new_tablespace)
SELECT 
    new_tablespace || ' (Group ' || mapping_group || '):' AS new_group,
    LISTAGG(old_tablespace, ', ') WITHIN GROUP (ORDER BY old_tablespace) AS old_tablespaces
FROM ts_mappings
GROUP BY new_tablespace, mapping_group
ORDER BY mapping_group;

-- Alternative flat output (like your example)
SELECT old_tablespace || '. ' || new_tablespace AS mapping
FROM ts_mappings
ORDER BY new_tablespace, old_tablespace;

Example Output (Based on Your Sample):

text
PLO_DATA_01. TRU_DATA_01
PLO_DATA_02. TRU_DATA_01
ILO_DATA_01. TRU_DATA_01
ILA_INDEX_02. TRU_DATA_01
PLA_DATA_01. TRU_DATA_01

Validation Check: Run this to ensure no old tablespaces are missed:

sql
SELECT tablespace_name AS missing_old_ts
FROM dba_tablespaces
MINUS
SELECT old_tablespace FROM ts_mappings;

Step 2: Script to List Users/Tables/Indexes Using Old Tablespaces and Suggested New Mappings

This is a comprehensive, robust script that queries DBA views to find:

  • Users with default tablespaces set to old ones, and suggested new defaults.
  • Tables in old tablespaces, with suggested new tablespaces.
  • Indexes in old tablespaces, with suggested new tablespaces.

It handles multi-schema environments, includes counts for summary, and generates ALTER statements for remapping (e.g., for post-import execution). Run this on the source database.

sql
-- Comprehensive query for users, tables, and indexes with old -> new mappings
WITH mappings AS (
    SELECT old_tablespace, new_tablespace
    FROM ts_mappings
)
-- Users with default old tablespaces
SELECT 'USER' AS object_type,
       du.username AS object_name,
       du.default_tablespace AS old_tablespace,
       NVL(m.new_tablespace, 'NEW_DATA8') AS suggested_new_tablespace,
       'ALTER USER "' || du.username || '" DEFAULT TABLESPACE ' || NVL(m.new_tablespace, 'NEW_DATA8') || 
       ' QUOTA UNLIMITED ON ' || NVL(m.new_tablespace, 'NEW_DATA8') || ';' AS alter_script
FROM dba_users du
LEFT JOIN mappings m ON m.old_tablespace = du.default_tablespace
WHERE du.default_tablespace IN (SELECT old_tablespace FROM mappings)
UNION ALL
-- Tables in old tablespaces
SELECT 'TABLE' AS object_type,
       dt.owner || '.' || dt.table_name AS object_name,
       dt.tablespace_name AS old_tablespace,
       NVL(m.new_tablespace, 'NEW_DATA8') AS suggested_new_tablespace,
       'ALTER TABLE "' || dt.owner || '"."' || dt.table_name || '" MOVE TABLESPACE ' || NVL(m.new_tablespace, 'NEW_DATA8') || ';' AS alter_script
FROM dba_tables dt
LEFT JOIN mappings m ON m.old_tablespace = dt.tablespace_name
WHERE dt.tablespace_name IN (SELECT old_tablespace FROM mappings)
UNION ALL
-- Indexes in old tablespaces
SELECT 'INDEX' AS object_type,
       di.owner || '.' || di.index_name AS object_name,
       di.tablespace_name AS old_tablespace,
       NVL(m.new_tablespace, 'NEW_DATA8') AS suggested_new_tablespace,
       'ALTER INDEX "' || di.owner || '"."' || di.index_name || '" REBUILD TABLESPACE ' || NVL(m.new_tablespace, 'NEW_DATA8') || 
       ' PARALLEL 4;' AS alter_script  -- Parallel for performance
FROM dba_indexes di
LEFT JOIN mappings m ON m.old_tablespace = di.tablespace_name
WHERE di.tablespace_name IN (SELECT old_tablespace FROM mappings)
ORDER BY object_type, old_tablespace, object_name;

-- Summary counts
SELECT object_type,
       old_tablespace,
       COUNT(*) AS count,
       NVL(m.new_tablespace, 'NEW_DATA8') AS suggested_new_tablespace
FROM (
    SELECT 'USER' AS object_type, default_tablespace AS old_tablespace FROM dba_users WHERE default_tablespace IN (SELECT old_tablespace FROM ts_mappings)
    UNION ALL
    SELECT 'TABLE', tablespace_name FROM dba_tables WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings)
    UNION ALL
    SELECT 'INDEX', tablespace_name FROM dba_indexes WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings)
) sub
LEFT JOIN ts_mappings m ON m.old_tablespace = sub.old_tablespace
GROUP BY object_type, old_tablespace, NVL(m.new_tablespace, 'NEW_DATA8')
ORDER BY object_type, old_tablespace;

Key Features:

  • Robustness: Uses LEFT JOIN with NVL for fallbacks; handles cases where old tablespace isn't explicitly mapped.
  • Output Includes ALTER Scripts: Ready-to-run statements for manual remapping if needed (e.g., if not using Data Pump).
  • Filtering: Add WHERE du.username IN ('SCHEMA1', 'SCHEMA2') or similar to focus on specific schemas.
  • Other Places Default Tablespace is Used: Users' default tablespaces affect new object creation (e.g., tables without specified tablespace). The script captures this. Also check temporary tablespaces (du.temporary_tablespace), but they're not remapped by default—add if needed.

Validation for LOBs/Partitions (Bonus): These often reference tablespaces indirectly.

sql
-- LOBs using old tablespaces
SELECT owner || '.' || table_name || '.' || column_name AS lob_object,
       tablespace_name AS old_tablespace,
       NVL(m.new_tablespace, 'NEW_DATA8') AS suggested_new
FROM dba_lobs
LEFT JOIN ts_mappings m ON m.old_tablespace = tablespace_name
WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings);

-- Partitions using old tablespaces
SELECT table_owner || '.' || table_name || '.' || partition_name AS partition_object,
       tablespace_name AS old_tablespace,
       NVL(m.new_tablespace, 'NEW_DATA8') AS suggested_new
FROM dba_tab_partitions
LEFT JOIN ts_mappings m ON m.old_tablespace = tablespace_name
WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings);

Step 3: Does Data Pump (Export/Import) with REMAP_TABLESPACE Handle User Defaults, Tables, Indexes, etc.?

No, not completely—here's a clear explanation and script to handle it.

  • What REMAP_TABLESPACE Handles:
    • It automatically remaps the tablespace for schema objects like tables, indexes, LOBs, and partitions during import. For example, if a table is in old_tablespace 'PLO_DATA_01', it will be placed in 'TRU_DATA_01' if you specify REMAP_TABLESPACE=PLO_DATA_01:TRU_DATA_01.
    • This works for metadata-only exports (CONTENT=METADATA_ONLY) or full exports.
    • It does not remap:
      • Users' default tablespaces (e.g., where new objects go if no tablespace is specified).
      • Quotas on tablespaces.
      • Temporary tablespaces or other user attributes.
    • For consolidations (multiple old to one new), you need multiple REMAP_TABLESPACE clauses (e.g., REMAP_TABLESPACE=PLO_DATA_01:TRU_DATA_01,REMAP_TABLESPACE=PLO_DATA_02:TRU_DATA_01,...).
  • Limitations and Manual Steps:
    • After import, run ALTER USER statements on the target to update defaults and quotas.
    • Indexes may need rebuilding if invalid post-import.
    • Test with a small export/import to verify.

Script to Generate REMAP_TABLESPACE Clause for Data Pump

Run this on source to get the clause for impdp:

sql
SELECT LISTAGG('REMAP_TABLESPACE=' || old_tablespace || ':' || new_tablespace, ',') WITHIN GROUP (ORDER BY old_tablespace) AS remap_clause
FROM ts_mappings;

Example Data Pump Commands

  • Export metadata only: expdp system/password@source_db DIRECTORY=DATA_PUMP_DIR DUMPFILE=metadata.dmp LOGFILE=export.log FULL=Y CONTENT=METADATA_ONLY
  • Import with remap: impdp system/password@target_db DIRECTORY=DATA_PUMP_DIR DUMPFILE=metadata.dmp LOGFILE=import.log [paste remap_clause from above]

Post-Import Script for Users (Run on Target)

Use the ALTER USER statements from Step 2's output. Also:

sql
-- Validate post-import
SELECT username, default_tablespace FROM dba_users WHERE default_tablespace IN (SELECT old_tablespace FROM ts_mappings@source_dblink);  -- Use dblink if comparing source/target

If you provide your full list of 40 old tablespaces and their exact mappings to 8 new ones (or patterns), I can pre-populate the INSERTs or refine further. Also specify schemas or Oracle version for tweaks!

Since you're using the Oracle Data Pump API (e.g., DBMS_DATAPUMP) in an RDS environment for your tablespace consolidation (40 old tablespaces to 8 new ones), and expdp/impdp commands are not viable, I'll tailor the solution to leverage the Data Pump API while addressing your need to identify where default tablespaces are used (users, tables, indexes, and other objects) and confirm how REMAP_TABLESPACE handles these during migration. I'll also ensure the scripts are robust, RDS-compatible, and cover all places where default tablespaces are relevant.

Key Considerations for RDS and Data Pump API

  • RDS Constraints: In Oracle RDS, expdp/impdp require specific configurations (e.g., S3 integration via rdsadmin.rdsadmin_s3_tasks). The Data Pump API (DBMS_DATAPUMP) is supported but must use an RDS DATA_PUMP_DIR or S3 bucket. You need the RDS master user or a user with DATAPUMP_EXP_FULL_DATABASE/DATAPUMP_IMP_FULL_DATABASE roles.
  • Default Tablespaces: As previously discussed, default tablespaces are set at the user level (default_tablespace, temporary_tablespace), database level (DEFAULT_PERMANENT_TABLESPACE, DEFAULT_TEMP_TABLESPACE), and quotas (dba_ts_quotas). Other objects (tables, indexes, LOBs, etc.) have assigned tablespaces, which are remapped via DBMS_DATAPUMP.METADATA_REMAP.
  • REMAP_TABLESPACE with Data Pump API: The API supports remapping tablespaces for objects (tables, indexes, LOBs, partitions) but does not update user default tablespaces or quotas. These require separate ALTERstatements.
  • Your Ask:
    1. Identify all places where default tablespaces are used (users, database, quotas, and objects like tables/indexes).
    2. Provide scripts to list old-to-new tablespace mappings for users/tables/indexes.
    3. Confirm how Data Pump API with REMAP_TABLESPACE handles these during migration.

Below, I'll provide:

  1. A script to list all places default tablespaces are used, with old-to-new mappings.
  2. A script to identify users/tables/indexes in old tablespaces and their suggested new mappings.
  3. A Data Pump API script for migration, including REMAP_TABLESPACE, with post-migration steps for user defaults/quotas.

Step 1: Identify Where Default Tablespaces Are Used

Default tablespaces are relevant in several places. Since your target RDS is empty, we'll query the source database to capture these and generate setup scripts for the target. This assumes you have a ts_mappings table (as previously discussed) with mappings like PLO_DATA_01 -> TRU_DATA_01, etc., covering your 40 old to 8 new tablespaces.

sql
-- Create ts_mappings if not exists (run on source)
CREATE TABLE ts_mappings (
    old_tablespace VARCHAR2(30) NOT NULL,
    new_tablespace VARCHAR2(30) NOT NULL,
    mapping_group NUMBER, -- For 5-to-1 consolidation tracking
    notes VARCHAR2(200),
    CONSTRAINT pk_ts_mappings PRIMARY KEY (old_tablespace)
);

-- Populate with your mappings (example; customize for all 40)
INSERT INTO ts_mappings (old_tablespace, new_tablespace, mapping_group, notes)
VALUES ('PLO_DATA_01', 'TRU_DATA_01', 1, 'Group 1 consolidation');
INSERT INTO ts_mappings (old_tablespace, new_tablespace, mapping_group, notes)
VALUES ('PLO_DATA_02', 'TRU_DATA_01', 1, 'Group 1 consolidation');
INSERT INTO ts_mappings (old_tablespace, new_tablespace, mapping_group, notes)
VALUES ('ILO_DATA_01', 'TRU_DATA_01', 1, 'Group 1 consolidation');
INSERT INTO ts_mappings (old_tablespace, new_tablespace, mapping_group, notes)
VALUES ('ILA_INDEX_02', 'TRU_DATA_01', 1, 'Group 1 consolidation');
INSERT INTO ts_mappings (old_tablespace, new_tablespace, mapping_group, notes)
VALUES ('PLA_DATA_01', 'TRU_DATA_01', 1, 'Group 1 consolidation');
-- Add remaining 35 old tablespaces, mapping to TRU_DATA_02 to TRU_DATA_08

-- Auto-populate unmapped tablespaces with fallback
MERGE INTO ts_mappings tm
USING (SELECT tablespace_name FROM dba_tablespaces WHERE tablespace_name NOT IN (SELECT old_tablespace FROM ts_mappings)) src
ON (tm.old_tablespace = src.tablespace_name)
WHEN NOT MATCHED THEN
    INSERT (old_tablespace, new_tablespace, mapping_group, notes)
    VALUES (src.tablespace_name, 'NEW_DATA8', 8, 'Fallback for unmapped tablespaces');
COMMIT;
-- This script requires a temporary table `ts_mappings` to be created and populated
-- before it is run. This table defines the mapping from old to new tablespace names.
-- Example of creating and populating ts_mappings:
-- CREATE GLOBAL TEMPORARY TABLE ts_mappings (
-- old_tablespace VARCHAR2(30),
-- new_tablespace VARCHAR2(30)
-- ) ON COMMIT PRESERVE ROWS;
--
-- INSERT INTO ts_mappings (old_tablespace, new_tablespace) VALUES ('USERS', 'NEW_DATA8');
-- INSERT INTO ts_mappings (old_tablespace, new_tablespace) VALUES ('TEMP', 'NEW_TEMP8');
-- COMMIT;
-- Part 1: List all tablespace usages that are in the ts_mappings table
-- This section helps you verify what will be changed.
WITH mappings AS (
SELECT old_tablespace, new_tablespace FROM ts_mappings
)
SELECT 'DATABASE_DEFAULT_PERMANENT' AS context,
property_value AS old_tablespace_or_object,
NVL((SELECT new_tablespace FROM mappings WHERE old_tablespace = property_value), 'NEW_DATA8') AS suggested_new_tablespace
FROM database_properties
WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE'
AND property_value IN (SELECT old_tablespace FROM mappings)
UNION ALL
SELECT 'DATABASE_DEFAULT_TEMPORARY',
property_value,
NVL((SELECT new_tablespace FROM mappings WHERE old_tablespace = property_value), 'NEW_TEMP8')
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE'
AND property_value IN (SELECT old_tablespace FROM mappings)
UNION ALL
SELECT 'USER_DEFAULT',
username,
NVL((SELECT new_tablespace FROM mappings WHERE old_tablespace = default_tablespace), 'NEW_DATA8')
FROM dba_users
WHERE default_tablespace IN (SELECT old_tablespace FROM mappings)
UNION ALL
SELECT 'USER_TEMPORARY',
username,
NVL((SELECT new_tablespace FROM mappings WHERE old_tablespace = temporary_tablespace), 'NEW_TEMP8')
FROM dba_users
WHERE temporary_tablespace IN (SELECT old_tablespace FROM mappings)
UNION ALL
SELECT 'USER_QUOTA',
username || ': ' || tablespace_name || ' (Quota: ' ||
CASE WHEN max_bytes = -1 THEN 'UNLIMITED' ELSE TO_CHAR(max_bytes / 1024 / 1024) || 'M' END || ')',
NVL((SELECT new_tablespace FROM mappings WHERE old_tablespace = tablespace_name), 'NEW_DATA8')
FROM dba_ts_quotas
WHERE tablespace_name IN (SELECT old_tablespace FROM mappings)
UNION ALL
-- New section for Materialized Views
SELECT 'MATERIALIZED_VIEW',
owner || '.' || mview_name,
NVL((SELECT new_tablespace FROM mappings WHERE old_tablespace = dba_segments.tablespace_name), 'NEW_DATA8')
FROM dba_mviews
JOIN dba_segments ON dba_mviews.owner = dba_segments.owner AND dba_mviews.mview_name = dba_segments.segment_name
WHERE dba_segments.tablespace_name IN (SELECT old_tablespace FROM mappings)
AND dba_segments.segment_type = 'MATERIALIZED VIEW'
ORDER BY context, old_tablespace_or_object;
-- Part 2: Generate setup scripts for the target environment (e.g., RDS)
-- You can copy and paste the output of this section to run the ALTER statements.
SELECT 'ALTER DATABASE DEFAULT TABLESPACE ' ||
NVL((SELECT new_tablespace FROM ts_mappings WHERE old_tablespace =
(SELECT property_value FROM database_properties WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE')), 'NEW_DATA8') || ';'
FROM dual
WHERE (SELECT property_value FROM database_properties WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE') IN (SELECT old_tablespace FROM ts_mappings)
UNION ALL
SELECT 'ALTER DATABASE DEFAULT TEMPORARY TABLESPACE ' ||
NVL((SELECT new_tablespace FROM ts_mappings WHERE old_tablespace =
(SELECT property_value FROM database_properties WHERE property_name = 'DEFAULT_TEMP_TABLESPACE')), 'NEW_TEMP8') || ';'
FROM dual
WHERE (SELECT property_value FROM database_properties WHERE property_name = 'DEFAULT_TEMP_TABLESPACE') IN (SELECT old_tablespace FROM ts_mappings)
UNION ALL
SELECT 'ALTER USER "' || username || '" DEFAULT TABLESPACE ' ||
NVL((SELECT new_tablespace FROM ts_mappings WHERE old_tablespace = default_tablespace), 'NEW_DATA8') || ';'
FROM dba_users
WHERE default_tablespace IN (SELECT old_tablespace FROM ts_mappings)
UNION ALL
SELECT 'ALTER USER "' || username || '" TEMPORARY TABLESPACE ' ||
NVL((SELECT new_tablespace FROM ts_mappings WHERE old_tablespace = temporary_tablespace), 'NEW_TEMP8') || ';'
FROM dba_users
WHERE temporary_tablespace IN (SELECT old_tablespace FROM ts_mappings)
UNION ALL
SELECT 'ALTER USER "' || username || '" QUOTA ' ||
CASE WHEN max_bytes = -1 THEN 'UNLIMITED' ELSE TO_CHAR(max_bytes / 1024 / 1024) || 'M' END ||
' ON ' || NVL((SELECT new_tablespace FROM ts_mappings WHERE old_tablespace = tablespace_name), 'NEW_DATA8') || ';'
FROM dba_ts_quotas
WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings)
UNION ALL
-- New section for Materialized Views
SELECT 'ALTER MATERIALIZED VIEW "' || owner || '"."' || mview_name || '" MOVE TABLESPACE ' ||
NVL((SELECT new_tablespace FROM ts_mappings WHERE old_tablespace = dba_segments.tablespace_name), 'NEW_DATA8') || ';'
FROM dba_mviews
JOIN dba_segments ON dba_mviews.owner = dba_segments.owner AND dba_mviews.mview_name = dba_segments.segment_name
WHERE dba_segments.tablespace_name IN (SELECT old_tablespace FROM ts_mappings)
AND dba_segments.segment_type = 'MATERIALIZED VIEW';





Output Includes:

  • Database-level default permanent and temporary tablespaces.
  • User-level default and temporary tablespaces.
  • User quotas on old tablespaces.
  • Suggested new mappings with fallback (NEW_DATA8/NEW_TEMP8).

Notes:

  • Adjust NEW_TEMP8 if your temporary tablespaces have a different naming convention (e.g., TRU_TEMP_01).
  • Save the output ALTER statements to run on the target after creating the new tablespaces.

Step 2: List Users/Tables/Indexes Using Old Tablespaces and New Mappings

This script identifies all users, tables, indexes, and other objects (LOBs, partitions, materialized views) in old tablespaces and maps them to new ones. It also generates ALTER statements for manual adjustments if needed (though REMAP_TABLESPACE handles most objects).

sql
WITH mappings AS (
    SELECT old_tablespace, new_tablespace FROM ts_mappings
)
SELECT 'USER' AS object_type,
       username AS object_name,
       default_tablespace AS old_tablespace,
       NVL(m.new_tablespace, 'NEW_DATA8') AS suggested_new_tablespace,
       'ALTER USER "' || username || '" DEFAULT TABLESPACE ' || NVL(m.new_tablespace, 'NEW_DATA8') || ';' AS alter_script
FROM dba_users
LEFT JOIN mappings m ON m.old_tablespace = default_tablespace
WHERE default_tablespace IN (SELECT old_tablespace FROM mappings)
UNION ALL
SELECT 'TABLE',
       owner || '.' || table_name,
       tablespace_name,
       NVL(m.new_tablespace, 'NEW_DATA8'),
       'ALTER TABLE "' || owner || '"."' || table_name || '" MOVE TABLESPACE ' || NVL(m.new_tablespace, 'NEW_DATA8') || ';'
FROM dba_tables
LEFT JOIN mappings m ON m.old_tablespace = tablespace_name
WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings)
UNION ALL
SELECT 'INDEX',
       owner || '.' || index_name,
       tablespace_name,
       NVL(m.new_tablespace, 'NEW_DATA8'),
       'ALTER INDEX "' || owner || '"."' || index_name || '" REBUILD TABLESPACE ' || NVL(m.new_tablespace, 'NEW_DATA8') || ' PARALLEL 4;'
FROM dba_indexes
LEFT JOIN mappings m ON m.old_tablespace = tablespace_name
WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings)
UNION ALL
SELECT 'LOB',
       owner || '.' || table_name || '.' || column_name,
       tablespace_name,
       NVL(m.new_tablespace, 'NEW_DATA8'),
       'LOBs are remapped automatically by Data Pump'
FROM dba_lobs
LEFT JOIN mappings m ON m.old_tablespace = tablespace_name
WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings)
UNION ALL
SELECT 'PARTITION',
       table_owner || '.' || table_name || '.' || partition_name,
       tablespace_name,
       NVL(m.new_tablespace, 'NEW_DATA8'),
       'Partitions are remapped automatically by Data Pump'
FROM dba_tab_partitions
LEFT JOIN mappings m ON m.old_tablespace = tablespace_name
WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings)
UNION ALL
SELECT 'MVIEW',
       owner || '.' || mview_name,
       tablespace_name,
       NVL(m.new_tablespace, 'NEW_DATA8'),
       'ALTER MATERIALIZED VIEW "' || owner || '"."' || mview_name || '" MOVE TABLESPACE ' || NVL(m.new_tablespace, 'NEW_DATA8') || ';'
FROM dba_mviews
LEFT JOIN mappings m ON m.old_tablespace = tablespace_name
WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings)
ORDER BY object_type, old_tablespace, object_name;

-- Summary counts
SELECT object_type,
       old_tablespace,
       COUNT(*) AS count,
       NVL(m.new_tablespace, 'NEW_DATA8') AS suggested_new_tablespace
FROM (
    SELECT 'USER' AS object_type, default_tablespace AS old_tablespace FROM dba_users WHERE default_tablespace IN (SELECT old_tablespace FROM ts_mappings)
    UNION ALL
    SELECT 'TABLE', tablespace_name FROM dba_tables WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings)
    UNION ALL
    SELECT 'INDEX', tablespace_name FROM dba_indexes WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings)
    UNION ALL
    SELECT 'LOB', tablespace_name FROM dba_lobs WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings)
    UNION ALL
    SELECT 'PARTITION', tablespace_name FROM dba_tab_partitions WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings)
    UNION ALL
    SELECT 'MVIEW', tablespace_name FROM dba_mviews WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings)
) sub
LEFT JOIN ts_mappings m ON m.old_tablespace = sub.old_tablespace
GROUP BY object_type, old_tablespace, NVL(m.new_tablespace, 'NEW_DATA8')
ORDER BY object_type, old_tablespace;

Output Includes:

  • Users, tables, indexes, LOBs, partitions, and materialized views in old tablespaces.
  • Suggested new tablespaces based on ts_mappings.
  • ALTER scripts for manual moves (though Data Pump handles tables/indexes/LOBs/partitions via REMAP_TABLESPACE).

Step 3: Data Pump API Migration with REMAP_TABLESPACE

The Data Pump API (DBMS_DATAPUMP) will handle remapping tablespaces for schema objects (tables, indexes, LOBs, partitions, materialized views) but not user defaults or quotas. Below is a PL/SQL script for export and import, tailored for RDS with S3 integration, followed by post-migration steps for user defaults.

Export Script (Run on Source)

This exports the full database or specific schemas to an S3 bucket via DATA_PUMP_DIR.

sql
DECLARE
    hdl NUMBER; -- Data Pump job handle
    job_state VARCHAR2(30);
    log_file VARCHAR2(100) := 'export.log';
    dump_file VARCHAR2(100) := 'full_export_%U.dmp';
BEGIN
    -- Open export job
    hdl := DBMS_DATAPUMP.OPEN(
        operation => 'EXPORT',
        job_mode => 'FULL', -- or 'SCHEMA' for specific schemas
        remote_link => NULL,
        job_name => 'FULL_EXPORT_' || TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
    );

    -- Specify dump file and log file in DATA_PUMP_DIR
    DBMS_DATAPUMP.ADD_FILE(
        handle => hdl,
        filename => dump_file,
        directory => 'DATA_PUMP_DIR',
        filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
    );
    DBMS_DATAPUMP.ADD_FILE(
        handle => hdl,
        filename => log_file,
        directory => 'DATA_PUMP_DIR',
        filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
    );

    -- Enable compression for efficiency
    DBMS_DATAPUMP.SET_PARAMETER(handle => hdl, name => 'COMPRESSION', value => 'ALL');

    -- Parallelism for performance (adjust based on RDS instance)
    DBMS_DATAPUMP.SET_PARALLEL(handle => hdl, degree => 4);

    -- Start the job
    DBMS_DATAPUMP.START_JOB(handle => hdl);

    -- Monitor job status
    job_state := 'UNDEFINED';
    WHILE (job_state != 'COMPLETED' AND job_state != 'STOPPED') LOOP
        DBMS_DATAPUMP.GET_STATUS(
            handle => hdl,
            mask => DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR + DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS,
            job_state => job_state
        );
        DBMS_LOCK.SLEEP(10); -- Wait 10 seconds
    END LOOP;

    -- Detach when done
    DBMS_DATAPUMP.DETACH(handle => hdl);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_DATAPUMP.DETACH(handle => hdl);
        RAISE;
END;
/

-- Upload dump files to S3 (run as RDS master user)
BEGIN
    rdsadmin.rdsadmin_s3_tasks.upload_to_s3(
        p_bucket_name => 'your-rds-s3-bucket',
        p_prefix => 'dmp_files/',
        p_directory_name => 'DATA_PUMP_DIR'
    );
END;
/

Import Script (Run on Target RDS)

This imports the data, applying REMAP_TABLESPACE for objects. Run after creating the 8 new bigfile tablespaces (e.g., CREATE BIGFILE TABLESPACE TRU_DATA_01 DATAFILE SIZE 100M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;).

sql
DECLARE
    hdl NUMBER;
    job_state VARCHAR2(30);
    log_file VARCHAR2(100) := 'import.log';
    dump_file VARCHAR2(100) := 'full_export_%U.dmp';
BEGIN
    -- Download dump files from S3
    rdsadmin.rdsadmin_s3_tasks.download_from_s3(
        p_bucket_name => 'your-rds-s3-bucket',
        p_s3_prefix => 'dmp_files/',
        p_directory_name => 'DATA_PUMP_DIR'
    );

    -- Open import job
    hdl := DBMS_DATAPUMP.OPEN(
        operation => 'IMPORT',
        job_mode => 'FULL', -- or 'SCHEMA'
        remote_link => NULL,
        job_name => 'FULL_IMPORT_' || TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
    );

    -- Specify dump file and log file
    DBMS_DATAPUMP.ADD_FILE(
        handle => hdl,
        filename => dump_file,
        directory => 'DATA_PUMP_DIR',
        filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
    );
    DBMS_DATAPUMP.ADD_FILE(
        handle => hdl,
        filename => log_file,
        directory => 'DATA_PUMP_DIR',
        filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
    );

    -- Apply REMAP_TABLESPACE for all mappings
    FOR rec IN (SELECT old_tablespace, new_tablespace FROM ts_mappings) LOOP
        DBMS_DATAPUMP.METADATA_REMAP(
            handle => hdl,
            name => 'REMAP_TABLESPACE',
            old_value => rec.old_tablespace,
            value => rec.new_tablespace
        );
    END LOOP;

    -- Transform LOBs to SecureFile (RDS best practice)
    DBMS_DATAPUMP.METADATA_TRANSFORM(
        handle => hdl,
        name => 'LOB_STORAGE',
        value => 'SECUREFILE'
    );

    -- Parallelism
    DBMS_DATAPUMP.SET_PARALLEL(handle => hdl, degree => 4);

    -- Start the job
    DBMS_DATAPUMP.START_JOB(handle => hdl);

    -- Monitor job status
    job_state := 'UNDEFINED';
    WHILE (job_state != 'COMPLETED' AND job_state != 'STOPPED') LOOP
        DBMS_DATAPUMP.GET_STATUS(
            handle => hdl,
            mask => DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR + DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS,
            job_state => job_state
        );
        DBMS_LOCK.SLEEP(10);
    END LOOP;

    DBMS_DATAPUMP.DETACH(handle => hdl);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_DATAPUMP.DETACH(handle => hdl);
        RAISE;
END;
/

Post-Import: Update User Defaults and Quotas

REMAP_TABLESPACE remaps tables, indexes, LOBs, partitions, and materialized views but not user defaults or quotas. Run the ALTER statements from Step 1's output on the target:

sql
-- Example (from Step 1 output; customize based on your mappings)
ALTER DATABASE DEFAULT TABLESPACE TRU_DATA_01;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TRU_TEMP_01;
ALTER USER "SCHEMA1" DEFAULT TABLESPACE TRU_DATA_01;
ALTER USER "SCHEMA1" TEMPORARY TABLESPACE TRU_TEMP_01;
ALTER USER "SCHEMA1" QUOTA UNLIMITED ON TRU_DATA_01;
-- Repeat for all users/quotas

Post-Import Validation

sql
-- Validate object placement
SELECT object_type,
       tablespace_name AS new_tablespace,
       COUNT(*) AS count
FROM dba_objects
WHERE tablespace_name IN (SELECT new_tablespace FROM ts_mappings@source_dblink) -- Use dblink if needed
GROUP BY object_type, tablespace_name
ORDER BY object_type, tablespace_name;

-- Check for invalid objects
SELECT object_type, owner, object_name
FROM dba_objects
WHERE status = 'INVALID';

-- Verify user defaults
SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE default_tablespace IN (SELECT new_tablespace FROM ts_mappings@source_dblink);

-- Gather statistics
EXEC DBMS_STATS.GATHER_DATABASE_STATS(cascade => TRUE, degree => 4);

How Data Pump API with REMAP_TABLESPACE Handles Your Ask

  • What It Handles:
    • Tables, Indexes, LOBs, Partitions, Materialized Views: DBMS_DATAPUMP.METADATA_REMAPwith REMAP_TABLESPACE automatically moves these to the new tablespaces (e.g., PLO_DATA_01 -> TRU_DATA_01).
    • Metadata-Only Export: If you export metadata only (CONTENT => 'METADATA'), it still remaps tablespace assignments for these objects.
  • What It Doesn't Handle:
    • User Default Tablespaces: default_tablespace and temporary_tablespace in dba_users remain unchanged; use ALTER USER.
    • Quotas: Quotas on old tablespaces aren't carried over; reapply with ALTER USER ... QUOTA.
    • Database Defaults: DEFAULT_PERMANENT_TABLESPACE and DEFAULT_TEMP_TABLESPACEneed ALTER DATABASE.
  • RDS Notes: Ensure the target tablespaces exist before import. Use rdsadmin.rdsadmin_s3_tasks for dump file transfers. SecureFile LOBs are enforced for RDS compatibility.

Recommendations

  • Before Migration:
    1. Create the 8 new bigfile tablespaces on the target RDS.
    2. Run Step 1 scripts on source to capture all default tablespace usages.
    3. Save the ALTER statements for target setup.
  • During Migration: Use the Data Pump API scripts above, ensuring S3 bucket access and sufficient RDS storage.
  • After Migration: Run the ALTER statements and validation queries.
  • If You Share:
    • Your full list of 40 old tablespaces and their mappings to 8 new ones.
    • Specific schemas or Oracle version (e.g., 19c).
    • Whether temporary tablespaces are consolidated separately. I can generate precise INSERTs for ts_mappings or adjust scripts.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)