— CUIDADO QUE ELE DROPA!!!!!!! EXECUTAR NO SCHEMA ONDE DESEJA DELETAR OS OBJETOS.

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;

Em seguida, executar o seguinte comando parada executar a funcao:
select drop_all_schema_objects from dual;

Obs: Por uma questão de enconding, deverá ser trocado todos os pickles por aspas simples antes de executar o comando acima .


Anúncios

Logar com o usuário onde deseja que os sinônimos privados sejam criados

select ‘create or replace synonym ‘ || s.object_name || ‘ for ‘ || s.owner || ‘.’ || s.object_name || ‘;’ from all_objects s where s.owner = ‘Schema‘ and s.object_type in (‘TABLE’, ‘PROCEDURE’, ‘PACKAGE’, ‘FUNCTION’) ;

Obs: Por uma questão de enconding, deverá ser trocado todos os pickles por aspas simples antes de executar o comando acima .

– Conectar com Sys as sysdba na instância desejada e executar os seguintes comandos:
select ‘drop view ‘ || v.owner || ‘.’ || v.view_name || ‘;’   from all_views v where owner = ‘SCHEMA_NAME’;
Select ‘DROP TABLE ‘ || t.owner || ‘.’ || t.table_name || ‘ cascade constraints ;’ from all_tables t where t.owner = ‘SCHEMA_NAME’;
Select ‘drop procedure ‘ || p.owner || ‘.’ || p.object_name || ‘;’ from all_procedures p where p.owner = ‘SCHEMA_NAME’ and p.object_type = ‘PROCEDURE’;
select ‘drop function ‘ || p.owner || ‘.’ || p.object_name || ‘;’ from all_procedures p where p.owner = ‘SCHEMA_NAME’ and p.object_type = ‘FUNCTION’;
select ‘drop trigger ‘  || p.owner || ‘.’ || p.object_name || ‘;’ from all_procedures p where p.owner = ‘SCHEMA_NAME’ and p.object_type = ‘TRIGGER’;

Onde SCHEMA_NAME = nome do schema, o qual, deseja deletar todos os objetos.

Obs: Por uma questão de enconding, poderá ser necessário trocar todos os pickles por aspas simples antes de executar o comando acima .