Drop Cascade mais rápido

Pessoal para que precisa limpar um esquema bem mais rápido que o drop cascade convencional, o script abaixo que desenvolvi ira te ajudar:

 

SET SERVEROUTPUT ON
DECLARE V_ESQUEMA VARCHAR2(30) := 'PROTHEUS';
BEGIN
  FOR APAGAR IN (  
					SELECT 'DROP TABLE '||'"'||OWNER||'"'||'.'||'"'||OBJECT_NAME||'"'|| ' CASCADE CONSTRAINTS PURGE' DDL FROM DBA_OBJECTS WHERE OWNER = V_ESQUEMA AND OBJECT_TYPE='TABLE'
					UNION ALL
					SELECT 'DROP SEQUENCE '||'"'||OWNER||'"'||'.'||'"'||OBJECT_NAME||'"' DDL FROM DBA_OBJECTS WHERE OWNER = V_ESQUEMA AND OBJECT_TYPE='SEQUENCE'
					UNION ALL
					SELECT 'DROP VIEW '||'"'||OWNER||'"'||'.'||OBJECT_NAME DDL FROM DBA_OBJECTS WHERE OWNER = V_ESQUEMA AND OBJECT_TYPE='VIEW'
					UNION ALL
					SELECT 'DROP PROCEDURE '||'"'||OWNER||'"'||'.'||OBJECT_NAME DDL FROM DBA_OBJECTS WHERE OWNER = V_ESQUEMA AND OBJECT_TYPE='PROCEDURE'
					UNION ALL
					SELECT 'DROP FUNCTION '||'"'||OWNER||'"'||'.'||OBJECT_NAME DDL FROM DBA_OBJECTS WHERE OWNER = V_ESQUEMA AND OBJECT_TYPE='FUNCTION'
					UNION ALL
					SELECT 'DROP INDEX '||'"'||OWNER||'"'||'.'||'"'||INDEX_NAME||'"'|| ';' DDL FROM DBA_INDEXES WHERE OWNER = V_ESQUEMA
			    ) 
  LOOP
	 DBMS_OUTPUT.PUT_LINE(APAGAR.DDL||' EXECUTADO.');
     EXECUTE IMMEDIATE APAGAR.DDL;
  END LOOP;
-- MATAR SESSOES E LIMPAR O RESTO
	BEGIN
		 FOR SESSIONS IN ( 
							SELECT SID, SERIAL# 
							FROM   V$SESSION 
							WHERE  USERNAME = V_ESQUEMA
						)
		  LOOP
			 DBMS_OUTPUT.PUT_LINE('KILL SESSOES: '||V_ESQUEMA);
			 EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION '''||SESSIONS.SID||','||SESSIONS.SERIAL#||'''';
			 EXECUTE IMMEDIATE 'DROP USER '||V_ESQUEMA||' CASCADE';
		  END LOOP;
	END;
-- DROP DO ESQUEMA
	DBMS_OUTPUT.PUT_LINE('DROP USER '||V_ESQUEMA||' CASCADE');
	EXECUTE IMMEDIATE 'DROP USER '||V_ESQUEMA||' CASCADE';
END;

 

Anúncios