如何删除Oracle Schema所有对象但保留指定两表及关联对象

Let's break down how to adjust your script to exclude EMPLOYEE_DETAIL, EMPLOYEE_ACCOUNT_DETAILS, and all objects tied to them. We'll target indexes, triggers, stored procedures, functions, packages, and any other dependent objects by leveraging Oracle's built-in data dictionary views.

Key Oracle Views to Use #

Oracle tracks object relationships in these critical views:

• dba_indexes: Links indexes directly to their parent tables
• dba_triggers: Shows which tables triggers are attached to
• dba_dependencies: Tracks cross-object dependencies (e.g., a procedure that references a table)

Modified Script with Full Exclusions #

Here's the updated script with comments explaining each change. I've also added a DATE2 definition in case you hadn't already set it:

USER_SCHM_NAME=Employee
DATE2=$(date +%Y%m%d_%H%M%S) # Ensure timestamp is defined for unique filenames

$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF >>$ORA_ERR
set feedback off
set echo off
set trimspool on
set termout off
set serveroutput on size 100000 format wrapped
set lines 500
set pages 0
spool /tmp/drop_obj_$ORACLE_SID_$DATE2.sql

-- 1. Drop all tables EXCLUDING the two specified ones
select 'drop table ${USER_SCHM_NAME}.'||table_name||' cascade constraints;' 
from dba_tables 
where owner='${USER_SCHM_NAME}' 
and table_name not in ('EMPLOYEE_DETAIL', 'EMPLOYEE_ACCOUNT_DETAILS') 
order by table_name;

-- 2. Drop indexes EXCLUDING those linked to the two protected tables
select 'drop index ${USER_SCHM_NAME}.'||index_name||';' 
from dba_indexes 
where owner='${USER_SCHM_NAME}' 
and table_name not in ('EMPLOYEE_DETAIL', 'EMPLOYEE_ACCOUNT_DETAILS');

-- 3. Drop triggers EXCLUDING those attached to the two protected tables
select 'drop trigger ${USER_SCHM_NAME}.'||trigger_name||';' 
from dba_triggers 
where owner='${USER_SCHM_NAME}' 
and table_name not in ('EMPLOYEE_DETAIL', 'EMPLOYEE_ACCOUNT_DETAILS');

-- 4. Drop PL/SQL objects (procedures/functions/packages) EXCLUDING those dependent on the two tables
select distinct 'drop '||object_type||' ${USER_SCHM_NAME}.'||object_name||';' 
from dba_objects o
left join dba_dependencies d
  on o.owner = d.owner 
  and o.object_name = d.name 
  and o.object_type = d.type
where o.owner='${USER_SCHM_NAME}'
and o.object_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY')
and (d.referenced_name not in ('EMPLOYEE_DETAIL', 'EMPLOYEE_ACCOUNT_DETAILS') 
     or d.referenced_name is null); -- Include objects with no dependencies

-- 5. Drop types EXCLUDING those that depend on the two protected tables
select distinct 'drop type ${USER_SCHM_NAME}.'||object_name||';' 
from dba_objects o
left join dba_dependencies d
  on o.owner = d.owner 
  and o.object_name = d.name 
  and o.object_type = d.type
where o.owner='${USER_SCHM_NAME}'
and o.object_type='TYPE'
and (d.referenced_name not in ('EMPLOYEE_DETAIL', 'EMPLOYEE_ACCOUNT_DETAILS') 
     or d.referenced_name is null);

-- 6. Drop the operator (unchanged unless it depends on excluded tables; adjust if needed)
select 'drop operator ${USER_SCHM_NAME}.CONVERTTYPES;' from dual;

-- 7. Purge the recycle bin to clean up dropped objects
select 'purge dba_recyclebin;' from dual;

spool off
spool /tmp/op_drop_obj_$ORACLE_SID_$DATE2.log
@/tmp/drop_obj_$ORACLE_SID_$DATE2.sql
spool off
EOF

Critical Notes #

• Dependency Handling: The dba_dependencies join ensures we skip any PL/SQL objects that reference the two protected tables. Using distinct avoids duplicate drop statements if an object depends on both tables.
• Execution Order: We drop tables first (excluding the target ones), then indexes/triggers tied to other tables, then dependent PL/SQL objects, then types. This order prevents errors from dependent objects being dropped before their parent tables.
• Case Sensitivity: This script uses uppercase object names, which matches Oracle's default behavior. If you created objects with quoted identifiers (mixed case), adjust the table names in the not in clauses to match exactly.

内容的提问来源于stack exchange，提问作者Abhinav