Monday, September 22, 2025

recheck




-- Log row count before reset
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_count
  FROM index_build
  WHERE build_status = 'NEED_BUILD'
  AND index_type = 'NORMAL'
  AND owner IN ('DS1_01', 'DS1_04', 'DS1_05', 'DS1_06', 'DS1_02', 'DS1_07', 'DS1_03');

  DBMS_OUTPUT.PUT_LINE('Rows to reset for DMS: ' || v_count);
END;
/

-- Reset start_time, end_time, and error_message for DMS run
UPDATE index_build
SET start_time = NULL,
    end_time = NULL,
    error_message = NULL
WHERE build_status = 'NEED_BUILD'
AND index_type = 'NORMAL'
AND owner IN ('DS1_01', 'DS1_04', 'DS1_05', 'DS1_06', 'DS1_02', 'DS1_07', 'DS1_03');

-- Verify reset
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_count
  FROM index_build
  WHERE build_status = 'NEED_BUILD'
  AND index_type = 'NORMAL'
  AND owner IN ('DS1_01', 'DS1_04', 'DS1_05', 'DS1_06', 'DS1_02', 'DS1_07', 'DS1_03')
  AND (start_time IS NOT NULL OR end_time IS NOT NULL OR error_message IS NOT NULL);

  IF v_count != 0 THEN
    RAISE_APPLICATION_ERROR(-20003, 'Expected 0 rows with non-NULL start_time, end_time, or error_message, but found ' || v_count);
  END IF;
END;
/

-- Drop error_message column
ALTER TABLE index_build DROP COLUMN error_message;

-- Verify column dropped
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_count
  FROM dba_tab_columns
  WHERE table_name = 'INDEX_BUILD'
  AND column_name = 'ERROR_MESSAGE';

  IF v_count != 0 THEN
    RAISE_APPLICATION_ERROR(-20004, 'Expected error_message column to be dropped, but it still exists');
  END IF;
END;
/

-- Commit changes
COMMIT;
=====================

WITH index_build_data AS (
    SELECT DISTINCT owner, index_name
    FROM index_build
    WHERE build_status = 'need_build'  -- Adjust if you want all rows
),
dba_index_data AS (
    SELECT DISTINCT owner, index_name
    FROM dba_indexes
    WHERE owner NOT IN ('SYS', 'SYSTEM')  -- Exclude system schemas; adjust as needed
),
comparison AS (
    SELECT 
        COALESCE(ib.owner, di.owner) AS owner,
        COALESCE(ib.index_name, di.index_name) AS index_name,
        CASE 
            WHEN ib.index_name IS NOT NULL AND di.index_name IS NOT NULL THEN 'Match'
            WHEN ib.index_name IS NOT NULL AND di.index_name IS NULL THEN 'In index_build only'
            WHEN ib.index_name IS NULL AND di.index_name IS NOT NULL THEN 'In DBA_INDEXES only'
        END AS status
    FROM index_build_data ib
    FULL OUTER JOIN dba_index_data di
        ON ib.owner = di.owner AND ib.index_name = di.index_name
)
SELECT 
    owner,
    index_name,
    status,
    COUNT(*) OVER (PARTITION BY owner, status) AS count_per_status
FROM comparison
ORDER BY owner, status, index_name;

=================
WITH index_build_data AS (
    SELECT DISTINCT owner, index_name
    FROM index_build
    WHERE build_status = 'need_build'  -- Adjust if you want all rows
),
dba_index_data AS (
    SELECT DISTINCT owner, index_name
    FROM dba_indexes
    WHERE owner NOT IN ('SYS', 'SYSTEM')  -- Exclude system schemas; adjust as needed
),
comparison AS (
    SELECT 
        COALESCE(ib.owner, di.owner) AS owner,
        COALESCE(ib.index_name, di.index_name) AS index_name,
        CASE 
            WHEN ib.index_name IS NOT NULL AND di.index_name IS NOT NULL THEN 'Match'
            WHEN ib.index_name IS NOT NULL AND di.index_name IS NULL THEN 'In index_build only (potential invalid index)'
            WHEN ib.index_name IS NULL AND di.index_name IS NOT NULL THEN 'In DBA_INDEXES only (potential missed index)'
        END AS status
    FROM index_build_data ib
    FULL OUTER JOIN dba_index_data di
        ON ib.owner = di.owner AND ib.index_name = di.index_name
)
SELECT 
    owner,
    index_name,
    status,
    COUNT(*) OVER (PARTITION BY owner, status) AS count_per_status
FROM comparison
ORDER BY owner, status, index_name;

=============================================================
-- 1. List distinct tablespaces in index_build (for need_build)
SELECT DISTINCT tablespace_name
FROM index_build
WHERE build_status = 'need_build'
ORDER BY tablespace_name;

-- 2. Identify old tablespaces in index_build with no mapping in ts_mappings
SELECT DISTINCT ib.tablespace_name
FROM index_build ib
WHERE ib.build_status = 'need_build'
AND ib.tablespace_name NOT IN (SELECT old_tablespace FROM ts_mappings)
ORDER BY ib.tablespace_name;

-- 3. Preview the mapping (show old -> new for matching tablespaces)
SELECT ib.tablespace_name AS old_tablespace, tsm.new_tablespace
FROM index_build ib
JOIN ts_mappings tsm ON ib.tablespace_name = tsm.old_tablespace
WHERE ib.build_status = 'need_build'
GROUP BY ib.tablespace_name, tsm.new_tablespace
ORDER BY ib.tablespace_name;

-- 4. Count rows in index_build that will be updated vs. flagged as errors
SELECT 
    SUM(CASE WHEN tsm.old_tablespace IS NOT NULL THEN 1 ELSE 0 END) AS will_be_updated,
    SUM(CASE WHEN tsm.old_tablespace IS NULL THEN 1 ELSE 0 END) AS will_be_flagged_error
FROM index_build ib
LEFT JOIN ts_mappings tsm ON ib.tablespace_name = tsm.old_tablespace
WHERE ib.build_status = 'need_build';

SELECT new_tablespace
FROM ts_mappings
WHERE new_tablespace NOT IN (SELECT tablespace_name FROM DBA_TABLESPACES)
ORDER BY new_tablespace;

-- Add error_message column if it doesn't exist
ALTER TABLE index_build ADD (error_message VARCHAR2(4000));

-- Clear prior errors for need_build rows
UPDATE index_build SET error_message = NULL WHERE build_status = 'need_build';

-- Merge to update tablespace_name
MERGE INTO index_build ib
USING ts_mappings tsm
ON (ib.tablespace_name = tsm.old_tablespace AND ib.build_status = 'need_build')
WHEN MATCHED THEN
    UPDATE SET
        ib.tablespace_name = tsm.new_tablespace,
        ib.error_message = NULL  -- Clear error if matched
WHEN NOT MATCHED THEN
    UPDATE SET
        ib.build_status = 'error_no_mapping',
        ib.error_message = 'No mapping found for old tablespace: ' || ib.tablespace_name;

-- Commit
COMMIT;

-- Check for errors post-MERGE
SELECT COUNT(*) AS error_count, error_message
FROM index_build
WHERE build_status = 'error_no_mapping'
GROUP BY error_message;


==========

Handling Issues Found in Checks

  • If Query 2 shows missing mappings:
    • Add the missing old_tablespace → new_tablespace pairs to ts_mappings:
      sql
      INSERT INTO ts_mappings (old_tablespace, new_tablespace)
VALUES ('OLD_TBS_NAME', 'NEW_TBS_NAME');
    • Alternatively, exclude those indexes from rebuilding by updating their status:
      sql
      UPDATE index_build
SET build_status = 'skipped_no_mapping',
    error_message = 'No tablespace mapping available'
WHERE tablespace_name NOT IN (SELECT old_tablespace FROM ts_mappings)
AND build_status = 'need_build';
  • If Query 3 shows incorrect new tablespaces:
    • Update ts_mappings to correct the new_tablespace values:
      sql
      UPDATE ts_mappings
SET new_tablespace = 'CORRECT_TBS_NAME'
WHERE old_tablespace = 'OLD_TBS_NAME';
  • If Query 4 shows many errors:
    • Investigate why so many index_build rows lack mappings. It could indicate incomplete ts_mappings data or that some indexes don’t need tablespace changes (in which case, you might adjust the MERGE to skip them).

Step 5: Next Steps After MERGE

After the MERGE, you can proceed with the validation against DBA_INDEXES and DBA_IND_COLUMNS (as in the original response) to ensure index definitions match the database before rebuilding. Here’s a quick recap of that step:

sql
-- Example validation (simplified; see original response for full PL/SQL)
SELECT ib.index_name, ib.table_name, ib.column_names, di.index_type, di.uniqueness
FROM index_build ib
JOIN DBA_INDEXES di ON ib.owner = di.owner AND ib.index_name = di.index_name
WHERE ib.build_status = 'need_build';

Additional Notes

  • Why need_build?: The condition ensures you’re only updating tablespaces for indexes flagged for DMS-related rebuilds. If you want to check/update all rows in index_build (not just need_build), remove the AND ib.build_status = 'need_build' condition from the MERGE’s ON clause and the UPDATE for clearing errors.
  • Test First: Run the mapping checks and MERGE on a small subset (e.g., add WHERE ROWNUM <= 10 to queries) in a test environment to confirm behavior.
  • DMS Context: If need_build isn’t the right filter or you have a different flag (e.g., dms_job IS NOT NULL), let me know, and I can adjust the queries.

If you want to see sample output from the mapping checks or need help fixing specific issues (e.g., missing mappings), share the results of the above queries or more details about your DMS process, and I’ll tailor the solution further!




-- Update matched rows UPDATE index_build ib SET ib.tablespace_name = (SELECT tsm.new_tablespace FROM ts_mappings tsm WHERE tsm.old_tablespace = ib.tablespace_name), ib.error_message = NULL WHERE ib.build_status = 'need_build' AND EXISTS (SELECT 1 FROM ts_mappings tsm WHERE tsm.old_tablespace = ib.tablespace_name); -- Update non-matched rows UPDATE index_build ib SET ib.build_status = 'error_no_mapping', ib.error_message = 'No mapping found for old tablespace: ' || ib.tablespace_name WHERE ib.build_status = 'need_build' AND NOT EXISTS (SELECT 1 FROM ts_mappings tsm WHERE tsm.old_tablespace = ib.tablespace_name); COMMIT;




-- Verify index_build against DBA_INDEXES and DBA_IND_COLUMNS WITH index_build_data AS ( SELECT owner, index_name, table_name, column_names, index_type, uniqueness, partitioned, degree, ini_trans, pct_free, build_status, error_message FROM index_build WHERE build_status = 'need_build' -- Adjust if you want all rows ), dba_index_data AS ( SELECT di.owner, di.index_name, di.table_name, di.index_type, di.uniqueness, di.partitioned, di.degree, di.ini_trans, di.pct_free, LISTAGG(dic.column_name, ',') WITHIN GROUP (ORDER BY dic.column_position) AS dba_column_names FROM dba_indexes di LEFT JOIN dba_ind_columns dic ON di.owner = dic.index_owner AND di.index_name = dic.index_name GROUP BY di.owner, di.index_name, di.table_name, di.index_type, di.uniqueness, di.partitioned, di.degree, di.ini_trans, di.pct_free ), comparison AS ( SELECT ib.owner, ib.index_name, ib.table_name, ib.build_status, -- Compare attributes and build error messages CASE WHEN ib.index_name IS NULL OR di.index_name IS NULL THEN 'Index not found in DBA_INDEXES' WHEN ib.table_name != di.table_name THEN 'Table name mismatch: index_build=' || ib.table_name || ', DBA=' || di.table_name WHEN ib.index_type != di.index_type THEN 'Index type mismatch: index_build=' || ib.index_type || ', DBA=' || di.index_type WHEN ib.uniqueness != di.uniqueness THEN 'Uniqueness mismatch: index_build=' || ib.uniqueness || ', DBA=' || di.uniqueness WHEN ib.partitioned != di.partitioned THEN 'Partitioned mismatch: index_build=' || ib.partitioned || ', DBA=' || di.partitioned WHEN ib.degree != di.degree THEN 'Degree mismatch: index_build=' || ib.degree || ', DBA=' || di.degree WHEN ib.ini_trans != di.ini_trans THEN 'Ini_trans mismatch: index_build=' || ib.ini_trans || ', DBA=' || di.ini_trans WHEN ib.pct_free != di.pct_free THEN 'Pct_free mismatch: index_build=' || ib.pct_free || ', DBA=' || di.pct_free WHEN ib.column_names != di.dba_column_names THEN 'Column names mismatch: index_build=' || ib.column_names || ', DBA=' || di.dba_column_names ELSE NULL END AS mismatch_reason, ib.column_names AS ib_column_names, di.dba_column_names AS dba_column_names, ib.index_type AS ib_index_type, di.index_type AS dba_index_type, ib.uniqueness AS ib_uniqueness, di.uniqueness AS dba_uniqueness, ib.partitioned AS ib_partitioned, di.partitioned AS dba_partitioned, ib.degree AS ib_degree, di.degree AS dba_degree, ib.ini_trans AS ib_ini_trans, di.ini_trans AS dba_ini_trans, ib.pct_free AS ib_pct_free, di.pct_free AS dba_pct_free FROM index_build_data ib FULL OUTER JOIN dba_index_data di ON ib.owner = di.owner AND ib.index_name = di.index_name ) SELECT owner, index_name, table_name, build_status, mismatch_reason, ib_column_names, dba_column_names, ib_index_type, dba_index_type, ib_uniqueness, dba_uniqueness, ib_partitioned, dba_partitioned, ib_degree, dba_degree, ib_ini_trans, dba_ini_trans, ib_pct_free, dba_pct_free FROM comparison WHERE mismatch_reason IS NOT NULL ORDER BY owner, index_name;

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)