• Arun Kumar

Drop all schema objects

The below script will drop all the objects owned by a schema. This will not delete the user but only deletes the objects

SET SERVEROUTPUT ON SIZE 1000000
set verify off
BEGIN
FOR c1 IN (SELECT OWNER,table_name, constraint_name FROM dba_constraints 
WHERE constraint_type = 'R' and owner=upper('&shema_name')) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '||' "'||c1.owner||'"."'||c1.table_name||'" DROP CONSTRAINT ' || c1.constraint_name;
END LOOP;
FOR c1 IN (SELECT owner,object_name,object_type FROM dba_objects where owner=upper('&shema_name')) LOOP
BEGIN
IF c1.object_type = 'TYPE' THEN
EXECUTE IMMEDIATE 'DROP '||c1.object_type||' "'||c1.owner||'"."'||c1.object_name||'" FORCE';
END IF;
IF c1.object_type != 'DATABASE LINK' THEN
EXECUTE IMMEDIATE 'DROP '||c1.object_type||' "'||c1.owner||'"."'||c1.object_name||'"';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
EXECUTE IMMEDIATE('purge dba_recyclebin');
END;
/


Recent Posts

See All

How to plan database creation

Database creation process involves several operating system files to work together. You create database only once and later on keep on adding more data files. Before you can simply go ahead and start

Oracle Transportable Tablespace

Assume that you have a schema (IQS) on production server and you want to import the schema into test server. The ideal approach would be to perform entire schema export and import using data pump. Rig

Grant Select on all tables in a schema

I encountered this situation where I wanted to grant SELECT on all the tables owned by one user to another user. There are two simple ways to achieve this: Generate SQL for each table In this method,

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback