Posted by: R Manimaran | June 3, 2009

Delete all the Oracle tables data’s in the database

Procedure:

 

create or replace FUNCTION TRUNC_SCHEMA RETURN NUMBER AS

CURSOR SELECT_TABLE IS SELECT TABLE_NAME AS TNAME FROM USER_TABLES ORDER BY 1;

sTableName varchar2(128);

sUser VARCHAR2(128);

sConstraintName varchar2(128);

plsql_block VARCHAR2(128);

BEGIN

  SELECT USER INTO suser FROM Dual;

  IF((suser=’SYSTEM’) or (suser=’SYS’)) then

     RETURN 1;

  END IF;

 

–EXECUTE IMMEDIATE ‘PURGE RECYLCLEBIN’;

 

–Disalbe Table’s Constraints

FOR C1 IN (SELECT CONSTRAINT_NAME,TABLE_NAME FROM user_constraints where STATUS=

‘ENABLED’ AND constraint_type in (‘P’,’R’) ORDER BY R_CONSTRAINT_NAME)

LOOP

sConstraintName := C1.CONSTRAINT_NAME;

sTableName := C1.TABLE_NAME;

plsql_block :=’ALTER TABLE ‘|| sTableName ||’DISABLE CONSTRAINT’||sConstraintName;

EXECUTE IMMEDIATE plsql_block;

END LOOP;

 

FOR D in SELECT_TABLE LOOP

–get the table name

sTableName:=D.TNAME;

–clear Table

plsql_block := ‘TRUNCATE TABLE ‘||sTableName;

EXECUTE IMMEDIATE plsql_block;

END LOOP;

 

–Enable Table CONSTRAINTS

FOR C2 IN (SELECT CONSTRAINT_NAME,TABLE_NAME FROM user_constraints where STATUS=

‘ENABLED’ AND constraint_type in (‘P’,’R’) ORDER BY R_CONSTRAINT_NAME)

LOOP

sConstraintName := C2.CONSTRAINT_NAME;

sTableName := C2.TABLE_NAME;

plsql_block :=’ALTER TABLE ‘|| sTableName ||’ENABLE CONSTRAINT ‘||sConstraintName;

EXECUTE IMMEDIATE plsql_block;

END LOOP;

 

RETURN 0;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line(‘ERROR:’||SQLERRM);

RETURN 1;

END TRUNC_SCHEMA;

Advertisements

Responses

  1. Thx you!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: