Oracle数据库能否用正则表达式批量删除指定前缀表？

Absolutely! You don’t have to list every temp_ table manually—you can use dynamic SQL to generate and run drop statements in one go. Here’s a safe, step-by-step approach tailored for Oracle:

1. First, Generate & Verify Drop Statements (Critical!) #

Before deleting anything, always confirm you’re targeting the right tables. Run this query to generate all the DROP TABLE commands you’ll need:

SELECT 'DROP TABLE ' || TABLE_NAME || ' CASCADE CONSTRAINTS;' AS drop_command
FROM USER_TABLES
WHERE UPPER(TABLE_NAME) LIKE 'TEMP_%';

• UPPER() ensures you catch tables even if they were created with lowercase letters (like temp_exp3), since Oracle’s default case sensitivity depends on your NLS settings.
• CASCADE CONSTRAINTS handles any foreign key links to these temp tables—without it, you’ll hit errors if other tables reference your temp_ tables.

Review the output carefully. Double-check that every table listed is actually a temporary/expired table you want to remove.

2. Execute the Drop Commands #

Once you’re confident the list is correct, choose one of these options:

Option A: Run Generated Commands Manually #

Copy all the DROP TABLE lines from the query result, paste them into your SQL client, and execute. This gives you full control over exactly what runs.

Option B: Automate with a PL/SQL Block #

If you prefer to run everything in one script (and trust your verified table list), use this PL/SQL block:

SET SERVEROUTPUT ON;
BEGIN
  FOR table_rec IN (SELECT TABLE_NAME FROM USER_TABLES WHERE UPPER(TABLE_NAME) LIKE 'TEMP_%') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE ' || table_rec.TABLE_NAME || ' CASCADE CONSTRAINTS';
    DBMS_OUTPUT.PUT_LINE('Successfully dropped: ' || table_rec.TABLE_NAME);
  END LOOP;
END;
/

SET SERVEROUTPUT ON lets you see a log of which tables were dropped, which is helpful for debugging.

Key Notes #

• Cross-Schema Deletes: If you’re targeting tables in another user’s schema, replace USER_TABLES with ALL_TABLES or DBA_TABLES, and add AND OWNER = 'YOUR_TARGET_SCHEMA' to the WHERE clause. You’ll need the DROP ANY TABLE privilege for this.
• Skip Cascade If Safe: If you’re sure no other tables reference your temp_ tables, you can remove CASCADE CONSTRAINTS—but it’s safer to keep it to avoid unexpected errors.
• No Direct Regex in DROP: Oracle doesn’t support regex directly in DROP TABLE statements, which is why we use the data dictionary view (USER_TABLES) to filter and generate dynamic SQL instead.

内容的提问来源于stack exchange，提问作者Volokh