首页 文章

Oracle Dynamic查询删除模式的所有对象，除了排除两个表及其相关对象，如触发器函数，索引或过程

提问于
浏览 次
0

我想删除与架构相关的所有对象，这可以通过我之前使用的以下查询实现： -

USER_SCHM_NAME=Employee
$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
select 'drop table ${USER_SCHM_NAME}.'||table_name||' cascade constraints;' from dba_tables where owner='${USER_SCHM_NAME}' order by table_name;
select 'drop '||object_type||' ${USER_SCHM_NAME}.'||object_name||';' from dba_objects where owner='${USER_SCHM_NAME}' order by object_type;
select 'drop type ${USER_SCHM_NAME}.'||object_name||';' from dba_objects where owner='${USER_SCHM_NAME}' and object_type='TYPE';
select 'drop operator ${USER_SCHM_NAME}.CONVERTTYPES;' from dual;
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

现在我想删除所有对象，排除与该表相关的两个表和对象，如索引，触发器，过程，函数，包..

我使用下面的查询来排除这两个表： -

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;

但是如何排除与这两个表相关的对象，请帮助我

oracle shell plsql

1 回答

  • 0

    我建议你单独删除单个 object_types （索引，触发器，过程，函数，包），而不是像这样使用单个drop语句 .

    drop  '||object_type||' ${USER_SCHM_NAME}.'||object_name||';'

    您也可以使用类似的东西排除约束，索引，触发器，过程，函数，包，而不是明确地使用上面的语句 .

    user_source 排除应该处理触发器，包，过程，函数

    SELECT 'drop ' || object_type || ' ${USER_SCHM_NAME}.' || object_name || ';'
FROM dba_objects d
WHERE OWNER = '${USER_SCHM_NAME}'
AND object_name not in ('EMPLOYEE_DETAIL', 'EMPLOYEE_ACCOUNT_DETAILS')
    AND NOT EXISTS (
        SELECT 1
        FROM (
            SELECT constraint_name AS object_name
            FROM dba_constraints
            WHERE table_name in('EMPLOYEE_DETAIL', 'EMPLOYEE_ACCOUNT_DETAILS')
                AND OWNER = '${USER_SCHM_NAME}'
               UNION ALL
            SELECT index_name AS object_name
            FROM dba_indexes
            WHERE table_name IN (
                    'EMPLOYEE_DETAIL'
                    ,'EMPLOYEE_ACCOUNT_DETAILS'
                    )
                AND OWNER = '${USER_SCHM_NAME}'
                 UNION ALL
            SELECT NAME AS object_name
            FROM dba_source
            WHERE (
                    upper(TEXT) LIKE '%EMPLOYEE_DETAIL%'
                    OR upper(TEXT) LIKE '%EMPLOYEE_ACCOUNT_DETAILS%'
                    )
                AND OWNER = '${USER_SCHM_NAME}'
            ) c
        WHERE c.object_name = d.object_name
        );
    回复于

相关问题