Delete all objects in a schema

Question

Delete all objects in a schema

Navigation

#1 by (3 votes)

2

I have a database on oracle where I need it delete all objects from a particular Schema , I can not delete the Schema , since the system validation is done based on an algorithm that takes into account the Schema ID .

sql oracle oracle11g

asked by anonymous 08.03.2016 / 13:53

1 answer

Problem with urllib3 and API Twitter Error 400 when creating InputStreamReader for URL with spaces or accents

score 3 · Accepted Answer

Below is a script to delete all objects from an owner, take control of what they are deleting, and generate a log of each object to be deleted.

set verify off
set heading off
set feedback off

accept   vo prompt 'Informe o Owner: '
accept   dir prompt 'Diretorio de spool: '
column global_name new_value instancia noprint
select replace(global_name, '.WORLD', '') global_name from global_name;
spool "&dir\&Vo._&instancia._drop_obj_schema.sql"
prompt spool "&dir\&Vo._&instancia._drop_obj_schema.log"

prompt
prompt set echo on
prompt

select   'ALTER TABLE '||OWNER||'."'||TABLE_NAME||'" DROP CONSTRAINT "'||CONSTRAINT_NAME||'";'
from     dba_constraints
where    owner = upper('&Vo')
and      constraint_type = 'R'
/

select   DISTINCT 'DROP SEQUENCE '||SEQUENCE_OWNER||'."'||SEQUENCE_NAME||'";'
from     dba_sequences
where    sequence_owner = upper('&Vo')
/

select   DISTINCT 'DROP '||TYPE||' '||OWNER||'."'||NAME||'";'
from     dba_source
where    owner = upper('&Vo')
/

select   'DROP VIEW '||OWNER||'."'|| VIEW_NAME||'";'
from     dba_views
where    owner = upper('&Vo')
/

select   'DROP TABLE '||OWNER||'."'||TABLE_NAME||'" PURGE;'
from     dba_tables
where    owner = upper('&Vo')
/

select   'DROP SYNONYM '||OWNER||'."'||SYNONYM_NAME||'";'
from     dba_synonyms
where    owner = upper('&Vo')
/

select   'DROP TYPE '||OWNER||'."'||TYPE_NAME||'";'
from     dba_types
where    owner = upper('&Vo')
/

select   'DROP MATERIALIZED VIEW '||OWNER||'."'||MVIEW_NAME||'";'
from     dba_mviews
where    owner = upper('&Vo')
/

SELECT 'PURGE TABLE ' || OWNER || '."' || ORIGINAL_NAME  || '";'
FROM dba_recyclebin 
WHERE owner = upper('&Vo')
and type = 'TABLE'
/

prompt
prompt set echo off
prompt

prompt
prompt spool off
spool off

spool "&dir\&Vo._&instancia._drop_syn.sql"
prompt spool "&dir\&Vo._&instancia._drop_syn.log"

prompt
prompt set echo on
prompt

select 'drop public synonym ' || synonym_name || ';'
from dba_synonyms
where table_owner = upper('&Vo')
and owner = 'PUBLIC'
and db_link is null
order by synonym_name
/
prompt
prompt
prompt

select 'drop synonym ' || owner || '.' || synonym_name || ';'
from dba_synonyms
where table_owner = upper('&Vo')
and owner != 'PUBLIC'
and db_link is null
order by owner, synonym_name
/

prompt
prompt
prompt set echo off
prompt

prompt
prompt spool off
spool off

undef vo
undef dir
undef instancia

set verify on
set heading on
set feedback on

Source: link