code

Oracle 스키마의 모든 콘텐츠 삭제

starcafe 2023. 3. 14. 21:46
반응형

Oracle 스키마의 모든 콘텐츠 삭제

Oracle에서 스킴의 모든 내용을 삭제할 수 있습니까?이 스크립트를 찾았습니다.

Begin
    for c in (select table_name from user_tables) loop
    execute immediate ('drop table "'||c.table_name||'" cascade constraints');
    end loop;
End;
/

하지만 스키마, 인덱스, 테이블, 제약사항의 모든 것을 드롭할 수 있는 것이 있는지 알고 싶습니다.스키마(drop user...)는 사용할 수 없습니다.

감사해요.

일반적으로 사용자를 삭제하고 추가하는 것이 가장 간단합니다.데이터베이스에 대한 시스템 또는 sysdba 액세스 권한이 있는 경우 이 방법을 사용하는 것이 좋습니다.

시스템 수준 액세스 권한이 없고 스키마를 스크럽하려는 경우 다음 SQL에서 일련의 drop 문이 생성되며 이 문을 실행할 수 있습니다. 

select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS','') || ';'  from user_objects

그리고 나서, 나는 보통 쓰레기통을 청소해서 물건을 치운다.솔직히 오라클 휴지통은 별로 쓸모가 없을 것 같아서 비활성화할 수 있으면 좋겠지만, 어쨌든:

purge recyclebin;

그러면 drop 문의 목록이 생성됩니다.모든 것이 실행되는 것은 아닙니다.cascade를 사용하여 드롭하면 PK_* 인덱스를 드롭할 수 없습니다.하지만 결국에는 꽤 깔끔한 스키마를 갖게 될 것이다.확인처:

select * from user_objects

또한 질문의 P/sql 블록은 테이블만 삭제하며 다른 개체는 모두 삭제되지 않습니다.

ps: 어떤 웹사이트에서 복사한 것이 도움이 되었습니다.마력처럼 테스트되고 작동한다.

바로 사용할 수 있는 github에서 다음 스크립트를 찾았습니다(SQL*Plus: Release 12.2.0.1.0 프로덕션).

https://gist.github.com/rafaeleyng/33eaef673fc4ee98a6de4f70c8ce3657

작가 라파엘 앵 덕분이다.

개체를 삭제할 스키마에 로그인하기만 하면 됩니다.

BEGIN
   FOR cur_rec IN (SELECT object_name, object_type
                     FROM user_objects
                    WHERE object_type IN
                             ('TABLE',
                              'VIEW',
                              'PACKAGE',
                              'PROCEDURE',
                              'FUNCTION',
                              'SEQUENCE',
                              'TYPE',
                              'SYNONYM',
                              'MATERIALIZED VIEW'
                             ))
   LOOP
      BEGIN
         IF cur_rec.object_type = 'TABLE'
         THEN
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '" CASCADE CONSTRAINTS';
         ELSE
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '"';
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'FAILED: DROP '
                                  || cur_rec.object_type
                                  || ' "'
                                  || cur_rec.object_name
                                  || '"'
                                 );
      END;
   END LOOP;
END;
/

PUBLIC SYNONYMS가 드롭된 테이블을 가리키고 있을 수 있습니다.다음 스크립트는 이러한 항목도 삭제합니다.

BEGIN
   FOR cur_syn IN (SELECT synonym_name
                     FROM all_synonyms
                    WHERE table_owner = 'MY_USER')
   LOOP
      BEGIN
         EXECUTE IMMEDIATE 'drop public synonym ' || cur_syn.synonym_name ;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.PUT_LINE ('Failed to drop the public synonym ' || cur_syn.synonym_name || '! ' || sqlerrm);
      END;
   END LOOP;
END;
/

다음과 같이 선택:

select 'drop '||object_type||' '|| object_name || ';' from user_objects where 
object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX')

다음 SQLplus 스크립트는 원하는 사용자로부터 모든 스키마 개체를 삭제하는 데 필요한 SQL 문을 생성합니다.

set heading off
set pagesize 0
set feedback off

-- wipe out all scheduler jobs
select 'exec dbms_scheduler.drop_job(job_name => '''||j.job_creator||'.'||j.job_name||''');'
from user_scheduler_jobs j
/

-- wipe out all XML schemas
select 'exec dbms_xmlschema.deleteSchema(schemaURL => '''||s.qual_schema_url||''',delete_option => dbms_xmlschema.DELETE_CASCADE_FORCE);'
from user_xml_schemas s
/

-- wipe out all remaining objects
select 'drop '
       ||o.object_type
       ||' '||object_name
       ||case o.object_type when 'TABLE' then ' cascade constraints' when 'TYPE' then ' force' else '' end
       ||';'
from user_objects o
where o.object_type not in ('JOB','LOB','PACKAGE BODY','INDEX','TRIGGER')
and not exists (select 1
                from user_objects r
                where r.object_name = o.object_name 
                and   r.object_type = 'MATERIALIZED VIEW'
                and   o.object_type != 'MATERIALIZED VIEW'
               )
/

-- empty the recycle bin
select 'purge recyclebin;' from dual
/

스크립트는 그대로 100% 기능합니다.다만, 어떠한 이유로 완전하지 않은 경우는, 다음과 같이 가상 머신(VM)을 사용해 간단하게 확장할 수 있습니다.

  1. [비울 스키마 사용자]로 로그온합니다.
  2. VM의 스냅샷 생성
  3. 위의 스크립트를 실행하여 삭제문을 만듭니다.
  4. 삭제문을 실행합니다(스크립트 삭제문보다 먼저 일부 개체가 자동으로 삭제되므로 "object does not exist" 오류는 무시해도 됩니다). 소유 개체가 제거되면 이 문제가 발생합니다.)
  5. 로그오프하다
  6. SYS로 로그온하여 "drop user [your schema user to empty];" -- cascade 옵션을 사용하지 않고 실행합니다.

스텝 6이 실패했을 경우 사용자가 삭제되지 않도록 하는 나머지 개체를 식별하여 위의 스크립트에 추가해야 합니다.사용자가 드롭 할 때까지 반복합니다(즉,스크립트가 포괄적)인 경우 스크립트를 저장합니다.

VM을 스냅샷으로 롤백하고 (업데이트된 스크립트를 사용하여) 순서 3과 4를 반복합니다.그러면 스키마가 100% 비워집니다.

네, 가능합니다.사용자를 삭제하여 스키마 개체를 삭제할 수 있습니다.DROP USER 문은 Oracle 데이터베이스에서 사용자를 제거하고 해당 사용자가 소유한 모든 개체를 제거하는 데 사용됩니다.

DROP USER TestDB;

이 문은 정상적으로 실행되며 TestDB라는 이름의 사용자는 TestDB가 스키마 내에 개체를 소유하지 않은 경우에만 드롭합니다.감지 테이블 및 뷰 등의 객체.오브젝트가 포함되어 있는 경우 DROP USER 스테이트먼트 실행 후 다음 오류 메시지가 나타납니다.

Error starting at line : 1 in command -
DROP USER TestDB
Error report -
SQL Error: ORA-01922: CASCADE must be specified to drop 'TESTDB'
01922. 00000 -  "CASCADE must be specified to drop '%s'"
*Cause:    Cascade is required to remove this user from the system.  The
           user own's object which will need to be dropped.
*Action:   Specify cascade.

TestDB가 스키마 내에 개체를 소유하고 있는 경우 대신 다음 DROP USER 문을 실행해야 합니다.

DROP USER TestDB CASCADE;

이 문은 TestDB가 소유한 모든 개체를 폐기하고 TestDB 개체의 참조 무결성 제약 조건도 모두 폐기합니다.

사용법은 다음과 같습니다.

set echo off feedback off serverout on

spool drop_all_objects.sql

declare  l_object varchar2(32000);

begin

  for i in (select object_name, object_type from dba_objects where owner='<owner>') loop

    if i.object_type='JOB' then

          l_object := 'begin dbms_scheduler.drop_job (job_name => ''<owner>'||i.object_name||'''); end;';

elsif i.object_type='PROGRAM' then

      l_object := 'begin dbms_scheduler.drop_program (program_name => ''<owner>'||i.object_name||'''); end;';

elsif i.object_type='RULE' then

      l_object := 'begin dbms_rule_adm.drop_rule (rule_name => ''<owner>'||i.object_name||''', force => TRUE); end;';

elsif i.object_type='RULE SET' then

      l_object := 'begin dbms_rule_adm.drop_rule_set (rule_set_name => ''<owner>'||i.object_name||''', delete_rules => TRUE); end;';

elsif i.object_type='CHAIN' then

      l_object := 'begin dbms_scheduler.drop_chain (chain_name => ''<owner>'||i.object_name||''', force => TRUE); end;';

elsif i.object_type='RULE' then

      l_object := 'begin dbms_rule_adm.drop_evaluation_context (evaluation_context_name => ''<owner>'||i.object_name||''', force => TRUE); end;';

else

          l_object := 'drop '||i.object_type||'<owner>'||i.object_name||';';

end if;

dbms_output.put_line(i_object);

dbms_output.put_line('/');

end loop;

end;

/

@drop_all_objects

언급URL : https://stackoverflow.com/questions/29926262/delete-all-contents-in-a-schema-in-oracle

반응형

'code' 카테고리의 다른 글

반응 버튼 리디렉션 페이지 클릭  (0) 2023.03.14
JS/jQuery TypeError: jQuery(...).datepicker는 함수가 아닙니다.  (0) 2023.03.14
Angularjs는 1.2에서 템플릿에 바인딩되지 않습니다.  (0) 2023.03.14
JSON.stringify 함수  (0) 2023.03.09
Swift 4 Codable, 단일 루트 레벨 키로 객체를 디코딩하는 방법  (0) 2023.03.09

'code'의 다른글

  • 현재글Oracle 스키마의 모든 콘텐츠 삭제

관련글

댓글

티스토리툴바