How to delete all data in Oracle Database with SQL?

How to delete all data in Oracle Database with SQL?

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

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;

/

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;
/

How to delete all data in Oracle Database with SQL?

   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