【Oracle】DROP SQL生成用のSQL（10g用）

-- シノニム一覧
SELECT 'DROP SYNONYM "' || OBJECT_NAME || '" FORCE;' AS SQL FROM USER_OBJECTS WHERE OBJECT_TYPE = 'SYNONYM'
UNION ALL
-- シーケンス一覧
SELECT 'DROP SEQUENCE "' || OBJECT_NAME || '";' AS SQL FROM USER_OBJECTS WHERE OBJECT_TYPE = 'SEQUENCE'
UNION ALL
-- プロシージャ一覧
SELECT 'DROP PROCEDURE "' || OBJECT_NAME || '";' AS SQL FROM USER_OBJECTS WHERE OBJECT_TYPE = 'PROCEDURE'
UNION ALL
-- パッケージ一覧(BODY含む)
SELECT 'DROP PACKAGE "' || OBJECT_NAME || '";' AS SQL FROM USER_OBJECTS WHERE OBJECT_TYPE = 'PACKAGE'
UNION ALL
-- ファンクション一覧
SELECT 'DROP FUNCTION "' || OBJECT_NAME || '";' AS SQL FROM USER_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION'
UNION ALL
-- マテビュー一覧
SELECT 'DROP MATERIALIZED VIEW "' || OBJECT_NAME || '";' AS SQL FROM USER_OBJECTS WHERE OBJECT_TYPE = 'MATERIALIZED VIEW'
UNION ALL
-- ビュー一覧
SELECT 'DROP VIEW "' || OBJECT_NAME || '" CASCADE CONSTRAINTS;' AS SQL FROM USER_OBJECTS WHERE OBJECT_TYPE = 'VIEW'
UNION ALL
-- テーブル一覧
SELECT 'DROP TABLE "' || OBJECT_NAME || '" CASCADE CONSTRAINTS;' AS SQL FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'
UNION ALL
-- データベース・リンク
SELECT 'DROP DATABASE LINK "' || OBJECT_NAME || '";' AS SQL FROM USER_OBJECTS WHERE OBJECT_TYPE = 'DATABASE LINK'

-- 上記で作成されたSQLを実行後、ゴミ箱クリア
PURGE RECYCLEBIN;