如何使用 SQL 删除 Oracle 数据库中的所有数据？

How to delete all data in Oracle Database with SQL?

提问by Forrest

I had created a lot of tables and users while testing some SQL command and now I dont remember exactly all the tables and user's name.

我在测试一些 SQL 命令时创建了很多表和用户，现在我不记得所有的表和用户名。

But now I want to delete all of it for my big project.So is it possible to delete it in SQL command line ?

但是现在我想为我的大项目删除所有这些。那么是否可以在 SQL 命令行中删除它？

回答by Bathri Nathan

This PL/SQL block will be useful to delete all the data in oracle data base

此 PL/SQL 块将有助于删除 oracle 数据库中的所有数据

BEGIN
   FOR cur_rec IN (SELECT object_name, object_type
                     FROM user_objects
                    WHERE object_type IN
                             ('TABLE',
                              'VIEW',
                              'PACKAGE',
                              'PROCEDURE',
                              'FUNCTION',
                              'SEQUENCE',
                              'SYNONYM',
                              'PACKAGE BODY'
                             ))
   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;

/

回答by Saeed Shahsavan

execute this command:

执行这个命令：

BEGIN
   FOR table_ IN (SELECT * FROM dba_tables where owner like 'YOUR_SCHEMA') LOOP
       execute immediate 'truncate table ' || table_.owner || '.' || table_.table_name ||' cascade';
   END LOOP;
END;
/

回答by HAYMbl4

You have two ways:

你有两种方法：

1. you can use function deleteinstead truncatefor this function you should not disable constraints, but it work more slowly because you can rollback this operation if you will go this way:

   BEGIN
   
     FOR table_ IN (SELECT * FROM dba_tables where owner like 'YOUR_SCHEMA') LOOP
      execute immediate 'delete from table ' || table_.owner || '.' || table_.table_name;
     END LOOP;
   END;
   

2. use truncateit is more faster, but you must diable constraints on table:

   begin
   
     for disable_constraint_ in
       (select * from dba_constraints where owner= 'YOUR_SCHEMA'
     )
     loop
     execute immediate 'alter table ' || disable_constraint_.owner || '.' || disable_constraint_.table_name ||' disable constraint '|| disable_constraint_.constraint_name;
     end loop;
     for table_ in (select * from dba_tables where owner = 'YOUR_SCHEMA')
     loop
       execute immediate 'truncate table ' || table_.owner || '.' ||table_.table_name ||' cascade';
     end loop;
     for enable_constaint_ in (select * from dba_constraints where owner= 'YOUR_SCHEMA')
     loop
     execute immediate 'alter table ' || enable_constaint_.owner || '.' || enable_constaint_.table_name ||' enable constraint '|| enable_constaint_.constraint_name;
    end loop;
   end;
   

1. 你可以使用 functiondelete代替truncate这个函数，你不应该禁用约束，但它的工作速度会更慢，因为如果你这样做，你可以回滚这个操作：

   BEGIN
   
     FOR table_ IN (SELECT * FROM dba_tables where owner like 'YOUR_SCHEMA') LOOP
      execute immediate 'delete from table ' || table_.owner || '.' || table_.table_name;
     END LOOP;
   END;
   

2. 使用truncate它会更快，但您必须禁用表的约束：

   begin
   
     for disable_constraint_ in
       (select * from dba_constraints where owner= 'YOUR_SCHEMA'
     )
     loop
     execute immediate 'alter table ' || disable_constraint_.owner || '.' || disable_constraint_.table_name ||' disable constraint '|| disable_constraint_.constraint_name;
     end loop;
     for table_ in (select * from dba_tables where owner = 'YOUR_SCHEMA')
     loop
       execute immediate 'truncate table ' || table_.owner || '.' ||table_.table_name ||' cascade';
     end loop;
     for enable_constaint_ in (select * from dba_constraints where owner= 'YOUR_SCHEMA')
     loop
     execute immediate 'alter table ' || enable_constaint_.owner || '.' || enable_constaint_.table_name ||' enable constraint '|| enable_constaint_.constraint_name;
    end loop;
   end;
   

回答by Tareq

   BEGIN

        FOR T in (SELECT table_name FROM user_tables) LOOP
          EXECUTE IMMEDIATE 'ALTER TABLE '||T.table_name||' DISABLE ALL CONSTRAINTS';
          --This will disable all the constraint  
        END LOOP;


        FOR T in (SELECT table_name FROM user_tables) LOOP
          EXECUTE IMMEDIATE 'TRUNCATE TABLE '||T.table_name;
        END LOOP;

        FOR T in (SELECT table_name FROM user_tables) LOOP
          EXECUTE IMMEDIATE 'ALTER TABLE '||T.table_name||' ENABLE ALL CONSTRAINTS';
        END LOOP;
    END;

Alter table ... disable all constraints throws a ORA-01735: invalid ALTER TABLE option if there is no constraint defined for the table, which would cause the script to fail to truncate every table if there is at least one table without constraint. You might want to place the execute immediate within a begin -exception block

如果没有为表定义约束，则更改表...禁用所有约束会抛出 ORA-01735：无效的 ALTER TABLE 选项，如果至少有一个表没有约束，这将导致脚本无法截断每个表。您可能希望将立即执行放在 begin -exception 块中