Delete all data from schema


WITH const AS
 (SELECT rconst.table_name,
 rconst.constraint_name
 FROM user_constraints rconst,
 user_constraints pconst
 WHERE rconst.constraint_type = 'R'
 AND rconst.r_constraint_name = pconst.constraint_name
 AND pconst.constraint_type = 'P' 
 )
SELECT 'ALTER TABLE '||const.table_name||' DISABLE CONSTRAINT '||const.constraint_name||';' rnme FROM const UNION ALL
SELECT 'TRUNCATE TABLE '||ut.table_name||';' FROM user_tables ut UNION ALL
SELECT 'ALTER TABLE ' ||const.table_name ||' ENABLE CONSTRAINT ' ||const.constraint_name ||';' rnme FROM const;
Advertisements

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

%d bloggers like this: