40114

How to delete all data in Oracle Database with SQL?

<h3>Question</h3>

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.

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


<h3>Answer1:</h3>

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; /
<h3>Answer2:</h3>

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; /
<h3>Answer3:</h3>
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


<h3>Answer4:</h3>

You have two ways:

<ol><li>

you can use function delete instead truncate for 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; </li> <li>

use truncate it 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; </li> </ol>

来源:https://stackoverflow.com/questions/31890032/how-to-delete-all-data-in-oracle-database-with-sql

Recommend

  • Zoom levels in kml files on google maps
  • How to set a proxy auto-configuration script (.pac) to be used in C#?
  • error: initialization with “{…}” expected for aggregate object - c++
  • Local sequence cannot be used in LINQ to SQL implementation
  • Nuget crash in visual studio 2015
  • Keras metric produces unexpected values
  • Sizes calculation with JavaScript and jQuery
  • check the difference between two arrays of objects in javascript
  • Overlay histograms in R
  • Permission for an image from Gallery is lost after re-launch
  • candlestick plot from pandas dataframe, replace index by dates
  • Repeatable job for Laravel json api
  • Insert statement not working using execute(array()) of PDO Extension
  • How to manipulate content of a comment with Apache POI
  • Using django-multiupload within a ModelForm
  • integrity constraint violation: NOT NULL check constraint
  • jQuery YQL SELECT FROM rss variable
  • Jekyll - How do I create pages in the root directory?
  • Java 11 and E(fx)clipse JavaFX plugin on Eclipse 4.9: An error has occurred - see the log file
  • Jenkins - could not read Username for 'https://github.com': No such device or address
  • Classes in Python
  • Stop an element moving with padding on hover
  • How to check if a database and tables exist in sql server in a vb .net project?
  • Annotate objects in a queryset with next and previous object ids
  • Rotating Towards Path in OpenGL
  • Is there a better way for handling SpatialPolygons that cross the antimeridian (date line)?
  • How to change user identity when git pushing via ssh?
  • matrix multiplication apache pig
  • Firebase: How to read from external DB?
  • WPF custom control and direct content support
  • media foundation H264 decoder not working properly
  • Create/delete users from text file using Bash script
  • Time Complexity of Fibonacci Algorithm [duplicate]
  • How to check if object is null in Java?