YesXYZ

아딸이 함께 만드는 세상

[Oracle] 스키마 삭제 시 반드시 체크해야 할 10가지 핵심 항목

by · 2025년 12월 19일

회사에서 사용하는 데이터베이스에 Locked and/or Expired Schema가 너무 많아 데이터베이스를 Full Backup받은 이후에 삭제하기로 하였습니다. 데이터베이스의 Migration과 신규 시스템 구축을 반복하면서 서비스가 종료되었거나 이관하지 않은 시스템에서 사용하던 스키마를 정리하지 않고 그대로 사용한 결과, “Rolling a snowball”처럼 감당할 수 없을 만큼 쌓여버렸습니다. 

set verify off
set escape '\';
col schema_type for a25 heading "Schema Type";
col locked for 9999999 heading "Locked";
col expired for 9999999 heading "Expired";
col locked_expired for 9999999 heading "Locked|Expired";
col active for 9999999 heading "Active";
SELECT 
    CASE 
        WHEN username IN (
        -- 핵심 관리자 및 보안
        'SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'XS$NULL', 'DIP', 'ORACLE_OCM',
        -- XML 및 검색 엔진
        'XDB', 'ANONYMOUS', 'CTXSYS',
        -- 멀티미디어 및 공간 데이터
        'MDSYS', 'MDDATA', 'ORDDATA', 'ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
        -- 워크플로우 및 데이터 관리
        'WMSYS', 'EXFSYS', 'APPQOSSYS', 'OWBSYS', 'OWBSYS_AUDIT',
        -- 분석 및 OLAP
        'OLAPSYS', 'MGMT_VIEW'
        ) 
        THEN 'INTERNAL SCHEMA' 
        ELSE 'USER SCHEMA' 
    END AS schema_type,
    SUM(CASE WHEN account_status = 'LOCKED' THEN 1 ELSE 0 END) AS locked,
    SUM(CASE WHEN account_status = 'EXPIRED' THEN 1 ELSE 0 END) AS expired,
    SUM(CASE WHEN account_status = 'EXPIRED \& LOCKED' THEN 1 ELSE 0 END) AS locked_expired,
    SUM(CASE WHEN account_status = 'OPEN' THEN 1 ELSE 0 END) AS active
FROM dba_users
GROUP BY 
    CASE 
        WHEN username IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'XS$NULL', 'DIP', 'ORACLE_OCM', 'XDB', 'ANONYMOUS', 'CTXSYS',
                          'MDSYS', 'MDDATA', 'ORDDATA', 'ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 
                          'WMSYS', 'EXFSYS', 'APPQOSSYS', 'OWBSYS', 'OWBSYS_AUDIT', 'OLAPSYS', 'MGMT_VIEW')
        THEN 'INTERNAL SCHEMA' 
        ELSE 'USER SCHEMA' 
    END;

                                              Locked
Schema Type                 Locked  Expired  Expired   Active
------------------------- -------- -------- -------- --------
INTERNAL SCHEMA                  0        0        0        2
USER SCHEMA                     ??        ?        0       14


SELECT USERNAME, ACCOUNT_STATUS, DEFAULT_TABLESPACE, CREATED
FROM   DBA_USERS
WHERE  USERNAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'XS$NULL', 'DIP', 'ORACLE_OCM', 'XDB', 'ANONYMOUS', 'CTXSYS',
                        'MDSYS', 'MDDATA', 'ORDDATA', 'ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
                        'WMSYS', 'EXFSYS', 'APPQOSSYS', 'OWBSYS', 'OWBSYS_AUDIT', 'OLAPSYS', 'MGMT_VIEW')
ORDER BY USERNAME;

큰 맘을 먹고 정리하기로 했지만, 막상 정리하려니 겁이 덜컥. “전임자들도 하지 않는 일을 내가 굳이”라는 생각이 들더군요. 그래도 할 건 해야죠. 2026년 새해 목표 중의 하나로 설정해서 차근차근 하나하나 삭제해 나가야겠습니다.
먼저 삭제 대상 스키마의 객체를 참조하는 View, Synonyms, Stored procedures, Functions, Packages 또는 Database links 와 같은 다른 스키마의 종속 객체를 확인해야 합니다. CASCADE 옵션을 사용하면 이러한 객체가 무효화되거나 삭제된 스키마의 테이블을 가리키는 참조 무결성 제약 조건(다른 스키마의 Foreign keys)이 삭제되어 다른 애플리케이션에서 오류가 발생할 수 있기 때문입니다. 그리고 스키마를 바로 삭제하지 말고 ALTER USER 삭제할_스키마_명 LOCK; 을 먼저 수행하여 며칠간 애플리케이션 오류가 발생하는지 지켜보는 게 좋습니다.
데이터 정합성과 서비스 영향도를 최소화하기 위해 Query data dictionary views(예: DBA_DEPENDENCIES, DBA_OBJECTS)를 쿼리하여 이러한 종속성을 식별하는 등 오라클에서 특정 스키마를 삭제(DROP USER 삭제할_스키마_명 CASCADE;)하기 전에 반드시 체크해야 할 10가지 핵심 항목을 정리하고자 합니다.

  1. 객체 권한 (Grants)
  2. 외래 키 참조 (Foreign Key)
  3. 공용 시노님 (Public Synonym)
  4. 데이터베이스 링크 (Database Link)
  5. 스케줄러 및 잡 (Scheduler Jobs)
  6. 컴파일 의존성 (Object Dependencies)
  7. 소스코드 확인 (Source Code)
  8. 테이블스페이스 확인 (Tablespace)
  9. 디렉토리 객체 확인 (Directory)
  10. 트리거 이벤트 확인 (Trigger)
1. 객체 권한 (Grants)
  • 해당 스키마의 오브젝트(테이블, 뷰 등)를 다른 누가 사용하고 있는지 확인합니다. 삭제 시 이 모든 권한이 사라져 관련 프로그램이 중단될 수 있습니다.
  • 체크 대상: DBA_TAB_PRIVS, ROLE_TAB_PRIVS, DBA_ROLE_PRIVS, DBA_SYS_PRIVS, DBA_COL_PRIVS 
    SELECT GRANTEE, TABLE_NAME, PRIVILEGE 
FROM DBA_TAB_PRIVS 
WHERE OWNER = '삭제할_스키마_명';

SELECT ROLE, TABLE_NAME, PRIVILEGE 
FROM ROLE_TAB_PRIVS 
WHERE OWNER = '삭제할_스키마_명';

SELECT GRANTEE, GRANTED_ROLE 
FROM DBA_ROLE_PRIVS 
WHERE GRANTED_ROLE IN (SELECT ROLE FROM ROLE_TAB_PRIVS WHERE OWNER = '삭제할_스키마_명');

SELECT *
FROM DBA_SYS_PRIVS 
WHERE GRANTEE = '삭제할_스키마_명';

SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE 
FROM DBA_COL_PRIVS 
WHERE OWNER = '삭제할_스키마_명';
2. 외래 키 참조 (Foreign Key)
  • 삭제하려는 스키마의 테이블을 다른 스키마에서 외래 키(Foreign Key)로 참조하고 있는지 확인해야 합니다. CASCADE 옵션을 쓰면 제약 조건은 끊기지만, 참조하던 쪽의 데이터 정합성에 문제가 생길 수 있습니다.
  • 체크 대상: DBA_CONSTRAINTS (Constraint Type = ‘R’) 
    SELECT LPAD(' ', 2 * (LEVEL - 1)) || CHILD_OWNER AS CHILD_OWNER,
       CHILD_TABLE, CONSTRAINT_NAME, PARENT_OWNER, PARENT_TABLE
FROM (
    SELECT
        A.OWNER AS CHILD_OWNER, 
        A.TABLE_NAME AS CHILD_TABLE,
        A.CONSTRAINT_NAME, 
        B.OWNER AS PARENT_OWNER,
        B.TABLE_NAME AS PARENT_TABLE
    FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B
    WHERE A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
      AND A.R_OWNER = B.OWNER
      AND A.CONSTRAINT_TYPE = 'R'
)
CONNECT BY NOCYCLE PRIOR CHILD_OWNER = PARENT_OWNER 
               AND PRIOR CHILD_TABLE = PARENT_TABLE
START WITH PARENT_OWNER = UPPER('삭제할_스키마_명');
3. 공용 시노님 (Public Synonym)
  • 삭제할 스키마의 오브젝트를 가리키는 Public Synonym이 있는지 확인하세요. 스키마가 삭제되면 이 시노님들은 ‘껍데기(Invalid)’만 남게 되어, 이를 사용하는 다른 쿼리들이 모두 실패하게 됩니다. DROP PUBLIC SYNONYM '삭제할_시니님_명'; 명령어를 실행하여 공용 시노님을 삭제합니다.
  • 체크 대상: DBA_SYNONYMS 
    SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROM DBA_SYNONYMS
WHERE TABLE_OWNER = '삭제할_스키마_명';
4. 데이터베이스 링크 (Database Links)
  • 해당 사용자가 생성한 DB Link가 있는지 확인해야 합니다. 특히 다른 사용자들이 이 유저가 만든 DB Link를 통해 외부 데이터를 가져오고 있었다면 서비스 장애로 이어집니다.
  • Private DB Link는 스키마 삭제와 함께 삭제되지만, SYS/SYSTEM 스키마에 의해 생성된 Public DB Link는 DROP PUBLIC DATABASE LINK '삭제한_DB링크_명'; 명령어를 실행하여 삭제합니다.
  • 체크 대상: DBA_DB_LINKS 
    SELECT OWNER, DB_LINK, USERNAME, HOST 
FROM DBA_DB_LINKS 
WHERE OWNER = '삭제할_스키마_명' OR USERNAME = '&v_owner' OR DB_LINK LIKE '%삭제할_스키마_명%' ;
5. 스케줄러 잡 (Scheduler Jobs)
  • 해당 스키마 소유로 돌아가고 있는 자동화 작업이 있는지 확인하세요. DBMS_SCHEDULER나 DBMS_JOB에 등록된 작업은 사용자가 삭제되면 함께 사라지거나 중단됩니다.
  • 체크 대상: DBA_SCHEDULER_JOBS 
    SELECT OWNER, JOB_NAME, STATE, LAST_START_DATE
FROM DBA_SCHEDULER_JOBS
WHERE OWNER = '삭제할_스키마_명';
  • 삭제할 스키마의 현재 실행 중인 스케줄러 잡이 있는지 아래와 같이 확인 및 삭제합니다. DROP USER를 하기 전에 DBMS_SCHEDULER.DROP_JOB으로 먼저 지워주는 것이 Best Practice라고 하네요! 
    -- 현재 실행 중인 잡 확인
SELECT OWNER, JOB_NAME, SESSION_ID 
FROM DBA_SCHEDULER_RUNNING_JOBS 
WHERE OWNER = '삭제할_스키마';

-- 실행 중인 잡 강제 중지
EXEC DBMS_SCHEDULER.STOP_JOB('스키마명.잡이름');

-- 잡 비활성화
EXEC DBMS_SCHEDULER.DISABLE('스키마명.잡이름');

-- 잡 삭제
EXEC DBMS_SCHEDULER.DROP_JOB('스키마명.잡이름');
  • 데이터의 이동이나 외부 자원 접근 시에 Credential을 확인합니다. 7단계 소스코드 확인과 동일한 절차로 Credential이 사용되는 Procedure나 Function 등을 검색합니다. 
    SELECT OWNER, CREDENTIAL_NAME, USERNAME, WINDOWS_DOMAIN
FROM   DBA_SCHEDULER_CREDENTIALS
WHERE  OWNER = '삭제할_스키마';
6. 컴파일 의존성 (Object Dependencies, Invalid Objects)
  • 다른 스키마의 프로시저, 함수, 뷰가 삭제될 스키마의 테이블을 사용하고 있는지 확인해야 합니다. 이 경우 스키마 삭제 후 다른 유저의 프로그램이 INVALID 상태가 됩니다.
  • 체크 대상: DBA_DEPENDENCIES 
    SELECT OWNER, NAME, TYPE, REFERENCED_OWNER, REFERENCED_TYPE
FROM   DBA_DEPENDENCIES
WHERE  REFERENCED_OWNER = '삭제할_스키마_명'
AND    OWNER != '삭제할_스키마_명';
7. 소스코드 확인 (Source Codes)
  • 스키마 삭제 전 영향도를 평가할 때, 관리자는 반드시 이 뷰를 봐야 합니다. 그래야 내가 모르는 다른 유저의 숨겨진 로직까지 다 찾아낼 수 있기 때문입니다.
  • 다른 스키마가 작성한 Procedure나 Function 등에 삭제하고자 하는 스키마의 오브젝트가 참조되어 있을 수 있습니다.
  • 체크 대상: DBA_SOURCE 
    SELECT DISTINCT OWNER, NAME, TYPE
FROM   DBA_SOURCE
WHERE  UPPER(TEXT) LIKE '%'||'삭제할_스키마_명'||'.%'
AND    OWNER <> '삭제할_스키마_명';
8. 테이블스페이스 확인 (Tablespace)
  • DROP TABLESPACE 테이블스페이스명을 사용하여 테이블스페이스를 삭제하기 전에 다른 스키마에서도 해당 테이블스페이스를 사용하고 있는지 확인해야 합니다.
  • DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES 명령를 실행하면 데이터파일까지 삭제됩니다.
  • 체크 대상: DBA_SEGMENTS, DBA_DATA_FILES 
    SELECT A.OWNER, A.TABLESPACE_NAME, A.TS_USED_MB,
       B.FILE_NAME, B.BYTES / 1024 / 1024 AS FILE_MB
FROM  (SELECT OWNER, TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024,0) AS TS_USED_MB
       FROM   DBA_SEGMENTS
       WHERE  OWNER = '삭제할_스키마_명'
       GROUP BY OWNER, TABLESPACE_NAME) A,
       DBA_DATA_FILES B
WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY 1, 2, 4;
9. 디렉토리 객체 확인 (Directory)
  • 디렉토리는 특정 유저 소유가 아니라 DB 전체 공유 객체입니다. 특정 유저의 파일 입출력을 위해 생성된 디렉토리는 DROP DIRECTORY '삭제할_디렉토리_명'; 명령어를 실행하여 수동으로 지워줘야 합니다.
  • ls -d DIRECTORY_PATH를 실행하여 해당 디렉토리가 물리적으로 존재하는지 확인합니다.
  • 체크 대상: DBA_DIRECTORIES 
    SELECT OWNER, DIRECTORY_NAME, 'ls -d ' || DIRECTORY_PATH AS PATH_CHECK
FROM   DBA_DIRECTORIES
WHERE  OWNER = '삭제할_스키마_명';
10. 트리거 이벤트 확인 (Trigger)
  • 다른 유저(User_B)가 삭제 대상 스키마(User_A)의 테이블을 감시하는 트리거를 생성한 경우, User_A를 삭제하면 그 테이블이 사라지므로 User_B가 소유한 트리거는 INVALID가 되며 삭제되지 않고 찌꺼기로 남습니다.
  • 특히 SYS 유저가 소유한 트리거에 User_A의 테이블이 포함되되어 있는 경우, 이 시스템 트리거는 INVALID 상태가 되며, 최악의 경우 다른 모든 유저의 로그인이나 DDL 작업이 중단되는 장애가 발생할 수 있습니다.
  • ALTER TRIGGER SYS.트리거_이름 DISABLE; 명령어를 먼저 실행한 후 며칠간 모니터링 후 문제가 없다면, DROP TRIGGER SYS.트리거_이름; 명령어를 실행합니다.
  • 체크 대상: DBA_TRIGGERS 
    SELECT OWNER, TRIGGER_NAME, TABLE_OWNER, TABLE_NAME, TRIGGER_TYPE, TRIGGERING_EVENT
FROM   DBA_TRIGGERS
WHERE  OWNER <> '&v_owner'
AND    TABLE_OWNER = '&v_owner';
아래는 10가지 항목을 한꺼번에 점검할 수 있는 쉘 스크립트입니다. 많은 활용바랍니다.
#!/bin/bash

SCRIPT_NAME=$(basename "$0")
OUT_FILE="${SCRIPT_NAME%.*}"

read -p "(Input schema name you want delete): " target_schema
#upper case
#target_schema=$(echo "$target_schema" | tr '[:lower:]' '[:upper:]')
target_schema=${target_schema^^}

# Run the SQL script using sqlplus
sqlplus -s  "/as sysdba" <"${OUT_FILE}_${target_schema}_$(date +%Y%m%d_%H%M).log"
set sqlprompt ""
set feedback off
set linesize 9000
set pagesize 50000
set verify off
set escape '\'

DEFINE v_owner = '${target_schema}'

prompt
prompt *********************************************************
prompt [0] &v_owner Information(Schema & Grant DDL)
prompt *********************************************************
set long 20000;
COL SCHEMA_DDL FOR A150 WORD_WRAPPED;
COL ROLE_GRANT_DDL FOR A150 WORD_WRAPPED;
SELECT DBMS_METADATA.GET_DDL('USER', '&v_owner') AS SCHEMA_DDL FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', '&v_owner') AS ROLE_GRANT_DDL FROM DUAL;

prompt
prompt *********************************************************
prompt [0] Active Sessions (Must be terminated before DROP)
prompt *********************************************************
COL MACHINE FOR A30
COL PROGRAM FOR A50
SELECT SID, SERIAL#, STATUS, MACHINE, PROGRAM
FROM   V\$SESSION
WHERE  USERNAME = '&v_owner';

prompt
prompt *********************************************************
prompt [1] Object Grants (Direct, Role, System, Column)
prompt *********************************************************
prompt 1-1. Direct Object Grants
prompt (SELECT * FROM DBA_SOURCE WHERE UPPER(TEXT) LIKE '%'||'\&table_name'||'%' AND OWNER <> '&v_owner';)
COL GRANTEE FOR A20
COL TABLE_NAME FOR A30
COL PRIVILEGE FOR A25
COL GRANTABLE FOR A10
SELECT GRANTEE, TABLE_NAME, PRIVILEGE, GRANTABLE
FROM   DBA_TAB_PRIVS
WHERE  OWNER = '&v_owner' AND GRANTEE != '&v_owner'
ORDER BY 1, 2;

prompt
prompt 1-2. Role Grants
COL ROLE FOR A20
SELECT ROLE, TABLE_NAME, PRIVILEGE
FROM   ROLE_TAB_PRIVS
WHERE  OWNER = '&v_owner';

prompt
prompt 1-3. System Grants
COL ADMIN_OPTION FOR A15
SELECT *
FROM   DBA_SYS_PRIVS
WHERE  GRANTEE = '&v_owner';

prompt
prompt 1-4. Column Grants
SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE
FROM   DBA_COL_PRIVS
WHERE  OWNER = '&v_owner';

prompt
prompt *********************************************************
prompt [2] Foreign Keys
prompt *********************************************************
COL CHILD_OWNER FOR A20
COL CHILD_TABLE FOR A30
COL CONSTRAINT_NAME FOR A30
COL PARENT_OWNER FOR A20
COL PARENT_TABLE FOR A30
SELECT LPAD(' ', 2 * (LEVEL - 1)) || CHILD_OWNER AS CHILD_OWNER,
       CHILD_TABLE, CONSTRAINT_NAME, PARENT_OWNER, PARENT_TABLE
FROM (
    SELECT
        A.OWNER AS CHILD_OWNER, 
        A.TABLE_NAME AS CHILD_TABLE,
        A.CONSTRAINT_NAME, 
        B.OWNER AS PARENT_OWNER,
        B.TABLE_NAME AS PARENT_TABLE
    FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B
    WHERE A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
      AND A.R_OWNER = B.OWNER
      AND A.CONSTRAINT_TYPE = 'R'
)
CONNECT BY NOCYCLE PRIOR CHILD_OWNER = PARENT_OWNER 
               AND PRIOR CHILD_TABLE = PARENT_TABLE
START WITH PARENT_OWNER = '&v_owner';

prompt
prompt *********************************************************
prompt [3] Public Synonym
prompt *********************************************************
COL OWNER FOR A20
COL SYNONYM_NAME FOR A30
COL TABLE_OWNER FOR A20
COL TABLE_NAME FOR A30
SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROM   DBA_SYNONYMS
WHERE  TABLE_OWNER = '&v_owner';

prompt
prompt *********************************************************
prompt [4] Database Links
prompt *********************************************************
prompt 4-1. DB Links Owned by '&v_owner' (Will be dropped)
COL DB_LINK FOR A50
COL USERNAME FOR A20
COL HOST FOR A50
SELECT OWNER, DB_LINK, USERNAME, HOST
FROM   DBA_DB_LINKS
WHERE  OWNER = '&v_owner';

prompt
prompt 4-2. DB Links Using '&v_owner' as Credentials (Will be broken)
prompt (DROP PUBLIC DATABASE LINK \&v_public_dblink;)
COL PUBLIC_DB_LINK FOR A50
SELECT OWNER, DB_LINK AS PUBLIC_DB_LINK, USERNAME, HOST
FROM   DBA_DB_LINKS
WHERE  OWNER <> '&v_owner' 
AND   (USERNAME = '&v_owner' OR DB_LINK LIKE '%'||'&v_owner'||'%');

prompt
prompt *********************************************************
prompt [5] Scheduler Jobs
prompt *********************************************************
prompt 5-1. Scheduler Jobs Owned by '&v_owner' (Will be dropped)
COL JOB_NAME FOR A30
COL JOB_TYPE FOR A16
COL STATE FOR A15
COL LAST_START_DATE FOR A15
SELECT OWNER, JOB_NAME, STATE, TO_CHAR(LAST_START_DATE, 'YYYY-MM-DD') AS LAST_START_DATE
FROM   DBA_SCHEDULER_JOBS
WHERE  OWNER = '&v_owner';

prompt
prompt 5-2. SYS's Scheduler Jobs Using '&v_owner' as Credentials (Will be broken)
COL JOB_ACTION FOR A50
SELECT OWNER, JOB_NAME, JOB_TYPE, JOB_ACTION, STATE, TO_CHAR(LAST_START_DATE, 'YYYY-MM-DD') AS LAST_START_DATE
FROM   DBA_SCHEDULER_JOBS
WHERE (
    UPPER(JOB_ACTION) LIKE '%' || UPPER('&v_owner') || '%'
    OR UPPER(PROGRAM_NAME) IN (SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER = UPPER('&v_owner'))
)
AND OWNER IN ('SYS', 'SYSTEM');

prompt
prompt 5-3. Credentials Owned by '&v_owner' (External Auth Info)
prompt (SELECT * FROM DBA_SOURCE WHERE UPPER(TEXT) LIKE '%'||'\&v_credential'||'%' AND OWNER <> '&v_owner';)
COL OWNER FOR A15
COL CREDENTIAL_NAME FOR A25
COL USERNAME FOR A20
COL WINDOWS_DOMAIN FOR A20
SELECT OWNER, CREDENTIAL_NAME, USERNAME, WINDOWS_DOMAIN
FROM   DBA_SCHEDULER_CREDENTIALS
WHERE  OWNER = UPPER('&v_owner');

prompt
prompt *********************************************************
prompt [6] Object Dependencies
prompt (SELECT DBMS_METADATA.GET_DDL('\&TYPE', '\&NAME', '\&OWNER') FROM DUAL;)
prompt *********************************************************
COL TYPE FOR A18
COL NAME FOR A30
COL REFERENCED_OWNER FOR A20
COL REFERENCED_TYPE FOR A18
SELECT OWNER, NAME, TYPE, REFERENCED_OWNER, REFERENCED_TYPE
FROM   DBA_DEPENDENCIES
WHERE  REFERENCED_OWNER = '&v_owner'
AND    OWNER != '&v_owner';

prompt
prompt *********************************************************
prompt [7] External Source Code References (Will be Invalidated)
prompt *********************************************************
COL NAME FOR A30
COL TYPE FOR A12
SELECT DISTINCT OWNER, NAME, TYPE
FROM   DBA_SOURCE
WHERE  UPPER(TEXT) LIKE '%'||'&v_owner'||'.%'
AND    OWNER <> '&v_owner';

prompt
prompt *********************************************************
prompt [8] Storage Impact Analysis
prompt *********************************************************
prompt 8-1. Tablespaces and Datafiles occupied by '&v_owner'
prompt (SELECT DBMS_METADATA.GET_DDL('TABLESPACE', '\&ts_name') AS TS_DDL FROM DUAL;)
COL TABLESPACE_NAME FOR A20
COL TS_USED_MB FOR 999,999,990
COL FILE_NAME FOR A80
COL FILE_MB FOR 999,999,990
SELECT A.OWNER, A.TABLESPACE_NAME, A.TS_USED_MB,
       B.FILE_NAME, B.BYTES / 1024 / 1024 AS FILE_MB
FROM  (SELECT OWNER, TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024,0) AS TS_USED_MB
       FROM   DBA_SEGMENTS
       WHERE  OWNER = '&v_owner'
       GROUP BY OWNER, TABLESPACE_NAME) A,
       DBA_DATA_FILES B
WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY 1, 2, 4;

prompt
prompt 8-2. Owners sharing tablespaces with &v_owner
SELECT DISTINCT OWNER, TABLESPACE_NAME
FROM   DBA_SEGMENTS
WHERE  OWNER <> '&v_owner'
AND    TABLESPACE_NAME IN (SELECT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER = '&v_owner')
ORDER BY 1, 2;

prompt
prompt *********************************************************
prompt [9] Check Directory objects and Delete invalid directories
prompt     (DROP DIRECTORY \&v_directory);)
prompt *********************************************************
COL DIRECTORY_NAME FOR A30
COL PATH_CHECK FOR A100
SELECT OWNER, DIRECTORY_NAME, 'ls -d ' || DIRECTORY_PATH AS PATH_CHECK
FROM   DBA_DIRECTORIES
WHERE  OWNER = '&v_owner';

prompt
prompt *********************************************************
prompt [10] Check Triggering Events
prompt     (ALTER TRIGGER SYS.\&v_trigger DISABLE;)
prompt     (DROP TRIGGER SYS.\&v_trigger;)
prompt *********************************************************
COL OWNER FOR A20
COL TRIGGER_NAME FOR A30
COL TABLE_OWNER FOR A20
COL TABLE_NAME FOR A30
COL TRIGGER_TYPE FOR A16
COL TRIGGERING_EVENT FOR A50
SELECT OWNER, TRIGGER_NAME, TABLE_OWNER, TABLE_NAME, TRIGGER_TYPE, TRIGGERING_EVENT
FROM   DBA_TRIGGERS
WHERE  OWNER <> '&v_owner'
AND    TABLE_OWNER = '&v_owner';

exit;
EOF1

Tags:

You may also like...

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다