Here is a very useful production-style script to drop multiple tables automatically using a loop in Oracle. This is commonly used in data migration, backup cleanup, or refresh scripts.
1️⃣ Drop All Tables Matching a Pattern
Example: Drop all backup tables ending with _BKP.
BEGIN
FOR r IN (
SELECT table_name
FROM user_tables
WHERE table_name LIKE '%\_BKP' ESCAPE '\'
)
LOOP
EXECUTE IMMEDIATE 'DROP TABLE ' || r.table_name || ' PURGE';
DBMS_OUTPUT.PUT_LINE('Dropped table: ' || r.table_name);
END LOOP;
END;
/
Example tables dropped
CUSTOMER_BKP
ORDER_BKP
CO_DATA_NZ_BKP
FTTH_LEAD_BKP
2️⃣ Drop Tables Starting With a Prefix
Example: tables starting with TEMP_
BEGIN
FOR r IN (
SELECT table_name
FROM user_tables
WHERE table_name LIKE 'TEMP_%'
)
LOOP
EXECUTE IMMEDIATE 'DROP TABLE ' || r.table_name || ' PURGE';
END LOOP;
END;
/
3️⃣ Drop Tables From a List
BEGIN
FOR r IN (
SELECT table_name
FROM user_tables
WHERE table_name IN ('TABLE1','TABLE2','TABLE3')
)
LOOP
EXECUTE IMMEDIATE 'DROP TABLE ' || r.table_name || ' PURGE';
END LOOP;
END;
/
4️⃣ Drop Old Backup Tables Automatically (Advanced)
Example: Drop tables older than 30 days.
BEGIN
FOR r IN (
SELECT table_name
FROM user_tables
WHERE table_name LIKE '%BKP%'
AND created < SYSDATE - 30
)
LOOP
EXECUTE IMMEDIATE 'DROP TABLE ' || r.table_name || ' PURGE';
END LOOP;
END;
/
⚡ Pro Developer Tip (Very Useful)
Before dropping tables, you can print them first:
SELECT table_name
FROM user_tables
WHERE table_name LIKE '%BKP%';
This prevents accidental deletion.
⭐ Very Powerful Trick (Used in Data Warehouses)
You can generate drop statements automatically:
SELECT 'DROP TABLE ' || table_name || ' PURGE;'
FROM user_tables
WHERE table_name LIKE '%BKP%';
Tags:
Oracle