------ SET LONG 100000 SET PAGESIZE 1000 SET SERVEROUTPUT ON; DECLARE v_ddl CLOB; v_modified_ddl CLOB; v_index_name VARCHAR2(100); BEGIN DBMS_OUTPUT.PUT_LINE('Extracting and modifying DDL for all indexes under schema: '); -- Extract DDL for regular and partitioned indexes FOR i IN ( SELECT index_name FROM dba_indexes WHERE owner = UPPER('') ) LOOP BEGIN v_index_name := i.index_name; -- Get the DDL for the index v_ddl := DBMS_METADATA.GET_DDL('INDEX', v_index_name, ''); -- Modify the parallel degree in the DDL IF INSTR(UPPER(v_ddl), 'PARALLEL') > 0 THEN -- If PARALLEL clause exists, replace it with PARALLEL 2 v_modified_ddl := REGEXP_REPLACE(v_ddl, 'PARALLEL\s+\d+', 'PARALLEL 2', 1, 0, 'i'); ELSE -- If no PARALLEL clause, add PARALLEL 2 at the end of the DDL v_modified_ddl := v_ddl || CHR(10) || ' PARALLEL 2'; END IF; -- Append the ALTER INDEX statement to set parallel degree back to 1 v_modified_ddl := v_modified_ddl || CHR(10) || 'ALTER INDEX .' || v_index_name || ' PARALLEL 1;' || CHR(10); -- Print the modified DDL DBMS_OUTPUT.PUT_LINE(v_modified_ddl || '----------------------------------------'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error extracting DDL for index: ' || v_index_name || ' - ' || SQLERRM); END; END LOOP; DBMS_OUTPUT.PUT_LINE('DDL extraction and modification completed.'); END; / ------------------ SET SERVEROUTPUT ON; DECLARE v_sql VARCHAR2(1000); BEGIN DBMS_OUTPUT.PUT_LINE('Rebuilding Regular Indexes with Parallel Degree 2...'); -- Rebuild regular table indexes with parallel degree 2 FOR i IN ( SELECT index_name, table_name FROM dba_indexes WHERE owner = UPPER('') AND partitioned = 'NO' ) LOOP BEGIN v_sql := 'ALTER INDEX ' || '' || '.' || i.index_name || ' REBUILD PARALLEL 2'; EXECUTE IMMEDIATE v_sql; DBMS_OUTPUT.PUT_LINE('Rebuilt Index: ' || i.index_name || ' on Table: ' || i.table_name || ' with Parallel Degree 2'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error rebuilding index: ' || i.index_name || ' - ' || SQLERRM); END; END LOOP; DBMS_OUTPUT.PUT_LINE('Rebuilding Partitioned Indexes with Parallel Degree 2...'); -- Rebuild partitioned table indexes with parallel degree 2 FOR i IN ( SELECT index_name, table_name, partition_name FROM dba_ind_partitions WHERE index_owner = UPPER('') ) LOOP BEGIN v_sql := 'ALTER INDEX ' || '' || '.' || i.index_name || ' REBUILD PARTITION ' || i.partition_name || ' PARALLEL 2'; EXECUTE IMMEDIATE v_sql; DBMS_OUTPUT.PUT_LINE('Rebuilt Partitioned Index: ' || i.index_name || ' Partition: ' || i.partition_name || ' on Table: ' || i.table_name || ' with Parallel Degree 2'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error rebuilding partitioned index: ' || i.index_name || ' Partition: ' || i.partition_name || ' - ' || SQLERRM); END; END LOOP; DBMS_OUTPUT.PUT_LINE('Reverting Regular Indexes to Parallel Degree 1...'); -- Revert parallel degree for regular indexes FOR i IN ( SELECT index_name, table_name FROM dba_indexes WHERE owner = UPPER('') AND partitioned = 'NO' ) LOOP BEGIN v_sql := 'ALTER INDEX ' || '' || '.' || i.index_name || ' PARALLEL 1'; EXECUTE IMMEDIATE v_sql; DBMS_OUTPUT.PUT_LINE('Reverted Parallel Degree to 1 for Index: ' || i.index_name || ' on Table: ' || i.table_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error reverting parallel degree for index: ' || i.index_name || ' - ' || SQLERRM); END; END LOOP; DBMS_OUTPUT.PUT_LINE('Reverting Partitioned Indexes to Parallel Degree 1...'); -- Revert parallel degree for partitioned indexes FOR i IN ( SELECT DISTINCT index_name FROM dba_ind_partitions WHERE index_owner = UPPER('') ) LOOP BEGIN v_sql := 'ALTER INDEX ' || '' || '.' || i.index_name || ' PARALLEL 1'; EXECUTE IMMEDIATE v_sql; DBMS_OUTPUT.PUT_LINE('Reverted Parallel Degree to 1 for Partitioned Index: ' || i.index_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error reverting parallel degree for partitioned index: ' || i.index_name || ' - ' || SQLERRM); END; END LOOP; DBMS_OUTPUT.PUT_LINE('Index rebuilding and parallel degree reversion complete.'); END; / --------------------------------------------------------------------------- SET SERVEROUTPUT ON; DECLARE v_sql VARCHAR2(1000); v_initial_count NUMBER; v_final_count NUMBER; v_confirmation VARCHAR2(3); -- Procedure to count objects in the schema PROCEDURE count_objects(out_count OUT NUMBER) IS BEGIN SELECT COUNT(*) INTO out_count FROM dba_objects WHERE owner = UPPER(''); END; BEGIN -- Prompt for confirmation DBMS_OUTPUT.PUT_LINE('Are you sure you want to drop all objects under schema ? (YES/NO)'); DBMS_OUTPUT.PUT_LINE('Enter your confirmation:'); v_confirmation := UPPER('&CONFIRMATION'); IF v_confirmation != 'YES' THEN DBMS_OUTPUT.PUT_LINE('Operation cancelled by the user.'); RETURN; END IF; -- Count total objects before dropping count_objects(v_initial_count); DBMS_OUTPUT.PUT_LINE('Total objects before dropping: ' || v_initial_count); -- Drop tables FOR t IN (SELECT object_name FROM dba_objects WHERE owner = UPPER('') AND object_type = 'TABLE') LOOP BEGIN v_sql := 'DROP TABLE ' || '' || '.' || t.object_name || ' CASCADE CONSTRAINTS'; EXECUTE IMMEDIATE v_sql; DBMS_OUTPUT.PUT_LINE('Dropped Table: ' || t.object_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error dropping table: ' || t.object_name || ' - ' || SQLERRM); END; END LOOP; -- Drop materialized views FOR mv IN (SELECT object_name FROM dba_objects WHERE owner = UPPER('') AND object_type = 'MATERIALIZED VIEW') LOOP BEGIN v_sql := 'DROP MATERIALIZED VIEW ' || '' || '.' || mv.object_name; EXECUTE IMMEDIATE v_sql; DBMS_OUTPUT.PUT_LINE('Dropped Materialized View: ' || mv.object_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error dropping materialized view: ' || mv.object_name || ' - ' || SQLERRM); END; END LOOP; -- Drop views FOR v IN (SELECT object_name FROM dba_objects WHERE owner = UPPER('') AND object_type = 'VIEW') LOOP BEGIN v_sql := 'DROP VIEW ' || '' || '.' || v.object_name; EXECUTE IMMEDIATE v_sql; DBMS_OUTPUT.PUT_LINE('Dropped View: ' || v.object_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error dropping view: ' || v.object_name || ' - ' || SQLERRM); END; END LOOP; -- Drop sequences FOR s IN (SELECT object_name FROM dba_objects WHERE owner = UPPER('') AND object_type = 'SEQUENCE') LOOP BEGIN v_sql := 'DROP SEQUENCE ' || '' || '.' || s.object_name; EXECUTE IMMEDIATE v_sql; DBMS_OUTPUT.PUT_LINE('Dropped Sequence: ' || s.object_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error dropping sequence: ' || s.object_name || ' - ' || SQLERRM); END; END LOOP; -- Drop procedures, functions, and packages FOR p IN (SELECT object_name, object_type FROM dba_objects WHERE owner = UPPER('') AND object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE')) LOOP BEGIN v_sql := 'DROP ' || p.object_type || ' ' || '' || '.' || p.object_name; EXECUTE IMMEDIATE v_sql; DBMS_OUTPUT.PUT_LINE('Dropped ' || p.object_type || ': ' || p.object_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error dropping ' || p.object_type || ': ' || p.object_name || ' - ' || SQLERRM); END; END LOOP; -- Drop indexes FOR i IN (SELECT index_name FROM dba_indexes WHERE owner = UPPER('')) LOOP BEGIN v_sql := 'DROP INDEX ' || '' || '.' || i.index_name; EXECUTE IMMEDIATE v_sql; DBMS_OUTPUT.PUT_LINE('Dropped Index: ' || i.index_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error dropping index: ' || i.index_name || ' - ' || SQLERRM); END; END LOOP; -- Drop synonyms FOR syn IN (SELECT synonym_name FROM dba_synonyms WHERE owner = UPPER('')) LOOP BEGIN v_sql := 'DROP SYNONYM ' || '' || '.' || syn.synonym_name; EXECUTE IMMEDIATE v_sql; DBMS_OUTPUT.PUT_LINE('Dropped Synonym: ' || syn.synonym_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error dropping synonym: ' || syn.synonym_name || ' - ' || SQLERRM); END; END LOOP; -- Count total objects after dropping count_objects(v_final_count); DBMS_OUTPUT.PUT_LINE('Total objects after dropping: ' || v_final_count); -- Ensure cleanup is complete IF v_final_count = 0 THEN DBMS_OUTPUT.PUT_LINE('All objects have been successfully dropped.'); ELSE DBMS_OUTPUT.PUT_LINE('Some objects were not dropped. Review the log above for details.'); END IF; END; / ---------------------------------------------------------------------------------------------- SET SERVEROUTPUT ON; DECLARE v_diff_found BOOLEAN := FALSE; v_source_count NUMBER; v_target_count NUMBER; -- Procedure to display differences PROCEDURE log_difference(obj_type IN VARCHAR2, obj_name IN VARCHAR2, msg IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE('Difference in ' || obj_type || ': ' || obj_name || ' - ' || msg); v_diff_found := TRUE; END; BEGIN DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------'); DBMS_OUTPUT.PUT_LINE('Comparing Schema Objects...'); DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------'); -- Compare tables (object existence) FOR t IN ( SELECT object_name FROM dba_objects@ WHERE owner = UPPER('') AND object_type = 'TABLE' MINUS SELECT object_name FROM dba_objects WHERE owner = UPPER('') AND object_type = 'TABLE' ) LOOP log_difference('Table', t.object_name, 'Exists in source but not in target'); END LOOP; FOR t IN ( SELECT object_name FROM dba_objects WHERE owner = UPPER('') AND object_type = 'TABLE' MINUS SELECT object_name FROM dba_objects@ WHERE owner = UPPER('') AND object_type = 'TABLE' ) LOOP log_difference('Table', t.object_name, 'Exists in target but not in source'); END LOOP; -- Compare row counts for tables DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------'); DBMS_OUTPUT.PUT_LINE('Comparing Table Row Counts...'); DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------'); FOR t IN ( SELECT table_name FROM dba_tables@ WHERE owner = UPPER('') ) LOOP BEGIN -- Get row count for source table EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || '' || '.' || t.table_name@ INTO v_source_count; -- Get row count for target table EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || '' || '.' || t.table_name INTO v_target_count; -- Compare row counts IF v_source_count != v_target_count THEN log_difference('Table Row Count', t.table_name, 'Source Count = ' || v_source_count || ', Target Count = ' || v_target_count); END IF; EXCEPTION WHEN OTHERS THEN log_difference('Table Row Count', t.table_name, 'Error retrieving row counts (check if table exists in both schemas)'); END; END LOOP; -- Compare views DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------'); DBMS_OUTPUT.PUT_LINE('Comparing Views...'); DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------'); FOR v IN ( SELECT object_name FROM dba_objects@ WHERE owner = UPPER('') AND object_type = 'VIEW' MINUS SELECT object_name FROM dba_objects WHERE owner = UPPER('') AND object_type = 'VIEW' ) LOOP log_difference('View', v.object_name, 'Exists in source but not in target'); END LOOP; FOR v IN ( SELECT object_name FROM dba_objects WHERE owner = UPPER('') AND object_type = 'VIEW' MINUS SELECT object_name FROM dba_objects@ WHERE owner = UPPER('') AND object_type = 'VIEW' ) LOOP log_difference('View', v.object_name, 'Exists in target but not in source'); END LOOP; -- Compare procedures DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------'); DBMS_OUTPUT.PUT_LINE('Comparing Procedures...'); DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------'); FOR p IN ( SELECT object_name FROM dba_objects@ WHERE owner = UPPER('') AND object_type = 'PROCEDURE' MINUS SELECT object_name FROM dba_objects WHERE owner = UPPER('') AND object_type = 'PROCEDURE' ) LOOP log_difference('Procedure', p.object_name, 'Exists in source but not in target'); END LOOP; FOR p IN ( SELECT object_name FROM dba_objects WHERE owner = UPPER('') AND object_type = 'PROCEDURE' MINUS SELECT object_name FROM dba_objects@ WHERE owner = UPPER('') AND object_type = 'PROCEDURE' ) LOOP log_difference('Procedure', p.object_name, 'Exists in target but not in source'); END LOOP; -- Summary DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------'); IF NOT v_diff_found THEN DBMS_OUTPUT.PUT_LINE('No differences found between the two schemas.'); ELSE DBMS_OUTPUT.PUT_LINE('Differences found. Review the above log for details.'); END IF; END; /