How can I delete all data from all tables in Oracle?

1 year ago
Jackson Davis
1 minute

There are two methods to clear data from all tables in an Oracle database.

Option 1: Implementing the truncate statement.

  1. Log in to the administrator account in the Oracle database.
  2. Execute the following SQL statement:
BEGIN
  FOR cur_rec IN (SELECT table_name FROM all_tables WHERE owner = '用户名') LOOP
    EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || cur_rec.table_name;
  END LOOP;
END;
/

Please make sure to replace “username” with the name of the database user whose table data needs to be cleared.

Option 2: Utilize the delete statement.

  1. Log in to the administrator account in the Oracle database.
  2. Execute the following SQL statement:
BEGIN
  FOR cur_rec IN (SELECT table_name FROM all_tables WHERE owner = '用户名') LOOP
    EXECUTE IMMEDIATE 'DELETE FROM ' || cur_rec.table_name;
  END LOOP;
END;
/

Please make sure to replace “username” with the name of the database user whose table data needs to be cleared.

Regardless of the method used, it is necessary to have sufficient privileges to access and modify tables in the database. Additionally, be sure to back up the database before carrying out these operations to prevent any unexpected accidents.

bannerAds