PLSQL - 사용자의 모든 데이터베이스 개체 삭제

PLSQL - 사용자의 모든 데이터베이스 개체 삭제

절차(파라미터 없음)를 사용하여 절차가 시작된 스키마 내에 있는 모든 사용자 생성 데이터베이스 개체를 삭제하려고 하는데, 어떻게 해야 할지 잘 모르겠습니다.지금까지 제가 가진 것은 이렇습니다만, 제가 잘못된 길로 가고 있다고 생각합니다.

create or replace procedure CLEAN_SCHEMA is
cursor schema_cur is
select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects;
schema_rec schema_cur%rowtype;
begin
select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
into schema_rec
from user_objects;
end;
/

create or replace
FUNCTION                DROP_ALL_SCHEMA_OBJECTS RETURN NUMBER AS
PRAGMA AUTONOMOUS_TRANSACTION;
cursor c_get_objects is
  select object_type,'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_name
  from user_objects
  where object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','SYNONYM', 'MATERIALIZED VIEW')
  order by object_type;
cursor c_get_objects_type is
  select object_type, '"'||object_name||'"' obj_name
  from user_objects
  where object_type in ('TYPE');
BEGIN
  begin
    for object_rec in c_get_objects loop
      execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
    end loop;
    for object_rec in c_get_objects_type loop
      begin
        execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
      end;
    end loop;
  end;
  RETURN 0;
END DROP_ALL_SCHEMA_OBJECTS;

위의 함수(함수를 통해 DDL을 호출할 수 있도록 자율)를 만든 다음 다음과 같이 할 수 있습니다.

select DROP_ALL_SCHEMA_OBJECTS from dual;

모든 객체를 드롭하고 싶을 때 실행 중인 proc를 드롭하지 않도록 하십시오(object_type 목록에 proc 또는 함수가 없는 이유는 proc에 상관 없음).

당신이 필요한 모든 것을 삭제하고 싶다면 익명의 블록.

하지만 저는 (plsql이 아닌) ansi sql만 허용하는 도구에서 이것을 할 수 있어야 했기 때문에 저장된 proc.

즐거운 시간 되세요.

declare
  cursor ix is
    select *
      from user_objects
     where object_type in ('TABLE', 'VIEW', 'FUNCTION', 'SEQUENCE');
begin
 for x in ix loop
   execute immediate('drop '||x.object_type||' '||x.object_name);
 end loop;
end;

사용자가 권한을 다시 적용하기 어려운 경우가 아니면 사용자를 삭제하고 다시 만드는 것이 더 쉬울 수 있습니다.

마틴 브램블리에게 감사합니다

다음과 같은 방법으로 답변을 간소화할 수 있을 것 같습니다.

CREATE OR REPLACE
procedure  DROP_ALL_SCHEMA_OBJECTS AS
PRAGMA AUTONOMOUS_TRANSACTION;
cursor c_get_objects is
  select object_type,'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_name
  FROM USER_OBJECTS
  where object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','SYNONYM', 'MATERIALIZED VIEW', 'TYPE')
  order by object_type;
BEGIN
  begin
    for object_rec in c_get_objects loop
      execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
    end loop;
  end;
END DROP_ALL_SCHEMA_OBJECTS;

/

execute DROP_ALL_SCHEMA_OBJECTS;

당신이 가진 것은 좋은 시작입니다.

나머지는 다음과 같습니다.

• 커서와 선택 문이 있습니다.커서만 있으면 됩니다.
• 다음 단계는 동적 PLSQL을 사용하여 drop 문을 호출하는 것입니다.EXECUTE Immediate Immediate 명령문을 사용합니다.떨어뜨리는 것의 이름을 선택하고 즉시 실행할 바인딩 변수로 제출하는 것이 더 우아하고 성능에 친숙합니다.
• 메서드를 소유하는 스키마가 아닌 메서드를 호출하는 스키마의 개체를 삭제하려면 "AUTHID CURRENT_USER"를 사용해야 합니다.자세한 내용은 Oracle 설명서를 참조하십시오.
• 패키지, 기능, 프로시저(이 메서드를 실행하는 동안 삭제하려고 하면 시스템이 중단되고 시간이 초과될 수 있음), Java 클래스, 트리거, 보기, 유형

마지막으로, 이 방법은 매우 위험한 방법이므로 데이터베이스에 다른 사용자가 실행할 수 없도록 저장 프로시저 대신 스크립트에 저장하는 것을 고려해 보는 것이 좋습니다.

다른 사용자가 언급했듯이, 문에 대해 "즉각 실행"이 필요합니다.고려해야 할 사항:

• 실행 중인 프로시저를 삭제하는 문제가 없도록 프로시저를 만드는 대신 익명 PL/SQL 블록으로 실행합니다.

• TABLE의 개체 유형에 대한 테스트를 추가하고 이 경우 외부 키 제약 조건을 통해 다른 테이블의 "부모"인 테이블을 처리하도록 캐스케이드 옵션을 포함하도록 drop 문을 수정합니다.커서 목록은 삭제를 차단하는 종속성을 고려하지 않는 순서로 생성됩니다.

• 또한 종속성 문제에 대해서도 테이블을 먼저 삭제하는 것이 가장 좋습니다(이 개체 유형에 더 낮은 숫자 값을 할당하는 DECODE를 커서에 추가하고 이 값을 기준으로 커서를 선택합니다).테이블 정의에서 열 유형으로 사용되는 유형의 Oracle 개체가 있는 경우 먼저 테이블을 삭제해야 합니다.

• Oracle Advanced Queuing을 사용하는 경우 AQ 패키지 API 호출을 사용하여 이와 관련된 개체를 삭제해야 합니다.일반 DROP TABLE을 사용하여 대기열 지원을 위해 Oracle 생성 테이블을 삭제할 수는 있지만, 관련 대기열을 삭제하거나 다시 추가할 수 없는 catch-22 위치에 있게 됩니다.버전 10g까지는 적어도 이 상황이 존재할 때 데이터베이스를 특수 모드로 전환하지 않고 포함된 스키마를 삭제할 수 없었습니다.

마틴 브램블리와 비자얀 스리니바산에게 감사드립니다!

그러나 Vijayan Srinivasan의 버전은 올바르지 않습니다. 'TYPE' 유형의 종속 개체가 삭제하는 동안 오류를 생성하는 경우가 있기 때문입니다.

ORA-02303: 형식을 삭제하거나 형식 또는 테이블 종속자로 대체할 수 없습니다.

내 버전은 스키마에서 모든 개체를 추가로 삭제합니다.

• 절차 및 기능을 삭제합니다('DROP_ALL_SCHEMA_OBJECTS' 예상).
• 모든 작업 및 dbms_dll 삭제
• 모든 db_dll 삭제
• 중첩 테이블의 Droping이 지원되지 않으므로 중첩 테이블을 삭제하지 않음

작성 또는 바꾸기프로시저 DROP_ALL_SCHEMA_OBJES ASPRAPGMA 자율_트랜잭션;커서 c_get_cursor isselect uo.object_type object_type_2,'||uo.object_name|'||uo(uo.object_type),'TABLE', '캐스케이드 제약 조건', null) obj_name2시작 사용자_OBJECTSuo여기서 uo.object_type은('TABLE', 'VIEW', 'PACKAGE', 'SEQUENCE', 'SYNonymous', 'Materialized VIEW', 'FUNCTION', 'Procedure')에 있습니다.및 없음(uo.object_type = 'TABLE') 및 존재함(uo.object_name = unt.table_name 사용자_table_name에서 1 선택)및 그렇지 않습니다(uo.object_type = 'PROCEDure' 및 uo.object_name = 'DROP_ALL_SCHEMA_OBJECTS').uo.object_type을 기준으로 정렬;커서 c_get_cursor_type은object_type 선택, '|object_name||'obj_nameuser_filename여기서 object_type('TYPE');커서 c_get_dblinks는선택'||db_link||'" obj_namefrom user_db_dll;커서 c_get_cursor is'"||object_name||'" obj_name을 선택합니다.user_filename여기서 object_type = 'JOB';커서 c_get_dbms_cursor is작업 obj_number_id 선택user_filename여기서 schema_user!= 'SYSMAN';시작한다.시작한다.for object_recin c_get_recin 루프즉시 실행('drop '|object_rec.object_type_2|' ||object_rec.obj_name2);엔드 루프;object_recin c_get_get_type 루프의 경우시작한다.즉시 실행('drop '|object_rec.object_type|' ||object_rec.obj_name);
끝;엔드 루프;forobject_recin c_get_dblinks 루프즉시 실행('데이터베이스 링크 삭제' ||object_rec.obj_name);
엔드 루프;for object_recin c_get_recin 루프DBMS_Scheduler.DROP_JOB(job_name => object_rec.obj_name);
엔드 루프;커밋;object_recin c_get_dbms_dll 루프의 경우dbms_job.remove(object_rec.obj_number_id);
엔드 루프;커밋;끝;END Drop_ALL_SCHEMA_OBJES;
/
DROP_ALL_SCHEMA_OBJECTS 실행;드롭 프로시저 DROP_ALL_SCHEMA_OBJECTS;
종료;

언급URL : https://stackoverflow.com/questions/842530/plsql-drop-all-database-objects-of-a-user