How to drop all the tables , views from the Oracle DB schema for recreating the repository?

1-

Create one sql file and name it -createdroptables.sql and write below given queries into this file


SET SERVEROUTPUT ON;

SPOOL C:\droptables.LOG;

SELECT * FROM (SELECT 'DROP TABLE '||table_name||' CASCADE CONSTRAINTS;' FROM user_tables UNION
SELECT 'DROP VIEW '||VIEW_NAME||';' FROM user_views UNION
SELECT 'DROP SEQUENCE '|| SEQUENCE_NAME||';' FROM user_sequences UNION
SELECT 'DROP SYNONYM ' || SYNONYM_NAME ||';' FROM user_synonyms UNION
SELECT 'DROP FUNCTION ' || OBJECT_NAME ||';' FROM user_procedures UNION
SELECT 'PURGE RECYCLEBIN;' FROM dual) ORDER BY 1 ASC;

SPOOL OFF

2 Run this sql file on sqlplus console

SQL> @C:\createdroptables.sql

3 Rename the droptables.log file to droptables.sql which is created in c:\ drive

4 Run this sql file on sqlplus console

SQL> @C:\createdroptables.sql

5 Check the tables in schema by running below given query

select table_name from user_tables;

6 You should get no table name ie its now empty schema.

Good Luck recreating repository.