/*
* Script para SQL*Plus - Elimina todos los objetos del usuario actual - v2015-11-29
*
* Basado en un script de Michel Cadot: https://groups.google.com/forum/?fromgroups=#!msg/comp.databases.oracle.misc/XA0Yhv9-3Rs/sSeNMcpN600J
*/
SET ECHO OFF
SET HEADING OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SET TIMING OFF
SET TIME OFF
--SET TRIMSPOOL ON
--SET TRIMOUT ON
SET LINESIZE 100
PROMPT Vaciando papelera de reciclaje...
--PURGE RECYCLEBIN;
--SET TERMOUT OFF
--PROMPT Generando fichero...
SPOOL eliminar-todo-1
SELECT 'PROMPT - ' || MVIEW_NAME || '
' || 'DROP MATERIALIZED VIEW "' || MVIEW_NAME || '";'
FROM SYS.USER_MVIEWS ORDER BY MVIEW_NAME;
SELECT 'PROMPT - ' || MASTER || ' (MATERIALIZED VIEW LOG)
' || 'DROP MATERIALIZED VIEW LOG ON "' || MASTER || '";'
FROM SYS.USER_MVIEW_LOGS ORDER BY MASTER;
SPOOL OFF
SET TERMOUT ON
@eliminar-todo-1.LST
SET TERMOUT OFF
SPOOL eliminar-todo-2
SELECT 'PROMPT - ' || OBJECT_NAME || '
' || 'DROP ' || OBJECT_TYPE || ' "' || OBJECT_NAME || '"' ||
DECODE(OBJECT_TYPE,
'CLUSTER', ' INCLUDING TABLES CASCADE CONSTRAINTS',
'OPERATOR', ' FORCE',
'TABLE', ' CASCADE CONSTRAINTS PURGE',
'TYPE', ' FORCE',
'VIEW', ' CASCADE CONSTRAINTS',
'') || ';'
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE IN (
'CLUSTER', 'CONTEXT','DATABASE LINK','DIMENSION','DIRECTORY','FUNCTION','INDEX TYPE',
'JAVA', 'LIBRARY', 'OPERATOR', 'OUTLINE', 'PACKAGE', 'PROCEDURE',
'SEQUENCE', 'SYNONYM', 'TABLE', 'TYPE', 'VIEW'
)
ORDER BY OBJECT_TYPE, OBJECT_NAME;
SPOOL OFF
SET TERMOUT ON
@'eliminar-todo-2.LST'