Re: Cursor to drop all tables

The following is code that our developers created and we use to drop all objects for a user. All of our production database objects are owned by a single user/schema. I think you have to be careful if you just drop tables....

Anyway, this is what I use to drop all the objects BEFORE I run the import (IMP) utility to populate my test database with the production data.

Just change the XXXXXXX to your username....and please test before using on production data. It works for me....but.....

Jim

--

• WARNING - THIS SCRIPT WILL REMOVE ALL OBJECTS FOR
• THE SCHEMA/USER 'XXXXXXX'. MAKE SURE YOU ARE IN
• THE CORRECT INSTANCE OF THE DATABASE BEFORE YOU
• RUN THIS SCRIPT!
  --
  

set define ~
set define $

DECLARE
 CURSOR c_Constraints IS
  SELECT CONSTRAINT_NAME, TABLE_NAME
  FROM ALL_CONSTRAINTS
  WHERE OWNER = 'XXXXXXX'
  AND CONSTRAINT_TYPE = 'R';  v_Constraint c_Constraints%ROWTYPE;

 CURSOR c_Tables IS
  SELECT TABLE_NAME
  FROM ALL_TABLES
  WHERE OWNER = 'XXXXXXX';  v_Table c_Tables%ROWTYPE;

 CURSOR c_Views IS
  SELECT View_NAME
  FROM ALL_VIEWS
  WHERE OWNER = 'XXXXXXX';  v_View c_Views%ROWTYPE;

 CURSOR c_Sequences IS
  SELECT SEQUENCE_NAME
  FROM ALL_SEQUENCES
  WHERE SEQUENCE_OWNER = 'XXXXXXX';  v_Sequence c_Sequences%ROWTYPE;

 CURSOR c_Synonyms IS
  SELECT SYNONYM_NAME
  FROM ALL_SYNONYMS
  WHERE OWNER = 'XXXXXXX';  v_Synonym c_Synonyms%ROWTYPE;

 CURSOR c_Procs IS
  SELECT DISTINCT NAME, TYPE
  FROM ALL_SOURCE
  WHERE OWNER = 'XXXXXXX';  v_Proc c_Procs%ROWTYPE;

 v_Stmt VARCHAR2(200);
 v_Cursor NUMBER;
BEGIN
 v_Cursor := DBMS_SQL.OPEN_CURSOR;

 OPEN c_Constraints;
 LOOP
  FETCH c_Constraints INTO v_Constraint;   EXIT WHEN c_Constraints%NOTFOUND;
  v_Stmt := 'ALTER TABLE ' || v_Constraint.TABLE_NAME || ' DROP CONSTRAINT ' || v_Constraint.CONSTRAINT_NAME;
  DBMS_SQL.PARSE(v_Cursor, v_Stmt, DBMS_SQL.V7);  END LOOP;
 CLOSE c_Constraints;

 OPEN c_Tables;
 LOOP
  FETCH c_Tables INTO v_Table;
  EXIT WHEN c_Tables%NOTFOUND;
  v_Stmt := 'DROP TABLE ' || v_Table.TABLE_NAME;   DBMS_SQL.PARSE(v_Cursor, v_Stmt, DBMS_SQL.V7);  END LOOP;
 CLOSE c_Tables;

 OPEN c_Views;
 LOOP
  FETCH c_Views INTO v_View;
  EXIT WHEN c_Views%NOTFOUND;
  v_Stmt := 'DROP VIEW ' || v_View.VIEW_NAME;   DBMS_SQL.PARSE(v_Cursor, v_Stmt, DBMS_SQL.V7);  END LOOP;
 CLOSE c_Views;

 OPEN c_Sequences;
 LOOP
  FETCH c_Sequences INTO v_Sequence;
  EXIT WHEN c_Sequences%NOTFOUND;
  v_Stmt := 'DROP SEQUENCE ' || v_Sequence.SEQUENCE_NAME;   DBMS_SQL.PARSE(v_Cursor, v_Stmt, DBMS_SQL.V7);  END LOOP;
 CLOSE c_Sequences;

 OPEN c_Synonyms;
 LOOP
  FETCH c_Synonyms INTO v_Synonym;
  EXIT WHEN c_Synonyms%NOTFOUND;
  v_Stmt := 'DROP SYNONYM ' || v_Synonym.SYNONYM_NAME;   DBMS_SQL.PARSE(v_Cursor, v_Stmt, DBMS_SQL.V7);  END LOOP;
 CLOSE c_Synonyms;

 OPEN c_Procs;
 LOOP
  FETCH c_Procs INTO v_Proc;
  EXIT WHEN c_Procs%NOTFOUND;
  v_Stmt := 'DROP ' || v_Proc.TYPE || ' ' || v_Proc.NAME;   DBMS_SQL.PARSE(v_Cursor, v_Stmt, DBMS_SQL.V7);  END LOOP;
 CLOSE c_Procs;

 DBMS_SQL.CLOSE_CURSOR(v_Cursor);
END;
/ Received on Wed Dec 29 1999 - 08:58:09 CST