Most of the time one faces the issues like one need to delete all the tables, views, indexes or sequences in the database.
So following the query which generates DROP statements for all the components present in the Oracle database:
SELECT 'DROP TABLE ' || table_name || ';' AS statement FROM user_tables
Union
SELECT 'DROP VIEW ' || view_name || ';' AS statement from user_views
Union
SELECT 'DROP INDEX ' || index_name || ';' AS statement from user_indexes
Union
SELECT 'DROP SEQUENCE ' || sequence_name || ';' AS statement from user_sequences
Union
SELECT 'DROP SYNONYM ' || SYNONYM_name || ';' AS statement from user_SYNONYMs;
Cheers :)
So following the query which generates DROP statements for all the components present in the Oracle database:
SELECT 'DROP TABLE ' || table_name || ';' AS statement FROM user_tables
Union
SELECT 'DROP VIEW ' || view_name || ';' AS statement from user_views
Union
SELECT 'DROP INDEX ' || index_name || ';' AS statement from user_indexes
Union
SELECT 'DROP SEQUENCE ' || sequence_name || ';' AS statement from user_sequences
Union
SELECT 'DROP SYNONYM ' || SYNONYM_name || ';' AS statement from user_SYNONYMs;
Cheers :)
3 comments:
BEGIN
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects
WHERE object_type IN
('TABLE',
'VIEW',
'PACKAGE',
'PROCEDURE',
'FUNCTION',
'SEQUENCE'
))
LOOP
BEGIN
IF cur_rec.object_type = 'TABLE'
THEN
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '" CASCADE CONSTRAINTS';
ELSE
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"';
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'FAILED: DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"'
);
END;
END LOOP;
END;
/
If need to drop package or type, use this:
SELECT 'DROP PACKAGE ' || OBJECT_NAME || ';' AS statement from SYS.ALL_OBJECTS a where UPPER(a.OBJECT_TYPE) = 'PACKAGE' and a.owner = 'ABHI_DB';
select 'DROP TYPE ' || OBJECT_NAME || ';' from SYS.ALL_OBJECTS where UPPER(OBJECT_TYPE) = 'TYPE' and owner = 'ABHI_DB' order by OWNER, OBJECT_NAME ;
Useful link : to build other types https://razorsql.com/articles/oracle_system_queries.html
Post a Comment