--borrar_objetos_usr

SET ECHO OFF;
SET SHOW OFF;
SET DEFINE OFF;
SET SERVEROUTPUT ON;

conn xxxx/xxxx@xxxx



DECLARE
      err_num          NUMBER;
      err_msg          VARCHAR2 (255);

V_USER VARCHAR2(100) := 'xxxxx';
BEGIN

--TABLAS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP TABLE '||OWNER||'.'||OBJECT_NAME ||' CASCADE CONSTRAINTS PURGE' AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'TABLE') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN
      err_num := SQLCODE;
      err_msg := SQLERRM;
      DBMS_OUTPUT.PUT_LINE(substr('ERROR:' || ERR_NUM || '-' || ERR_MSG, 1, 255));
END;

END LOOP;

--PURGAR TABLAS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'PURGE TABLE '||OWNER||'."'||OBJECT_NAME||'"' AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'TABLE') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN
      err_num := SQLCODE;
      err_msg := SQLERRM;
      DBMS_OUTPUT.PUT_LINE(substr('ERROR:' || ERR_NUM || '-' || ERR_MSG, 1, 255));
END;

END LOOP;

--PROCEDIMIENTOS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP PROCEDURE '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'PROCEDURE') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN
   NULL;
END;
END LOOP;


--FUNCIONES-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP FUNCTION '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'FUNCTION') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN
   NULL;
END;
END LOOP;


--SINONIMOS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP SYNONYM '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'SYNONYM') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN
   NULL;
END;
END LOOP;


--PACKAGE-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP PACKAGE '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'PACKAGE') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN
   NULL;
END;
END LOOP;


--SECUENCIAS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP SEQUENCE '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'SEQUENCE') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN
   NULL;
END;
END LOOP;



--TIPOS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP TYPE '||OWNER||'."'||OBJECT_NAME||'"''' AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'TYPE') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN
   NULL;
END;
END LOOP;


--VISTAS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP VIEW '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'VIEW') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN
   NULL;
END;
END LOOP;

--VISTAS MATERIALIZADAS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP MATERIALIZED VIEW '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'MATERIALIZED VIEW') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN
   NULL;
END;
END LOOP;

--TIPOS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP TYPE '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'TYPE') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN
   NULL;
END;
END LOOP;

--BODY TIPOS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP TYPE BODY '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'TYPE BODY') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN
   NULL;
END;
END LOOP;



--SINÓNIMOS PROPIOS Y DEPENDIENTES

-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP '||DECODE (OWNER,'PUBLIC','PUBLIC SYNONYM ','SYNONYM '||OWNER||'.')||SYNONYM_NAME AS COMANDO
FROM DBA_SYNONYMS
WHERE TABLE_OWNER = V_USER
OR OWNER = V_USER) LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN
   NULL;
END;
END LOOP;



END;
/

DISCONN;