CODEWITHSUNDEEP
oracle-databaseplsql
user8487380
user8487380

Reputation:

How to drop all contents like Tables, Packages, Indexes, Sequences, LOB all these at one time from user

I create one user name as MD. Unfortunately I copy the database from SYS to user MD using SQL developer. SO that lots of Tables, Indexes, Sequences, Objects and lot more has been copied. I want to delete all these at one time. How it is possible, While i tried to delete but not all has to be deleted.

Upvotes: 1

Views: 763

Answers (1)

Mehdi Ghasri
Mehdi Ghasri

Reputation: 498

If you have grant to 'DROP USER', You can drop MD user with this command:

DROP USER MD CASCADE;

and create MD user again.

Otherwise, You can make drop commands with following select:

SELECT 'DROP ' || T.OBJECT_TYPE || ' ' || T.OBJECT_NAME || ';' || CHR(10) COMMANDS
FROM USER_OBJECTS T
WHERE T.OBJECT_TYPE IN ('TABLE', 'VIEW', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER');

You can add or remove object types in where clause.

Upvotes: 2

Related Questions