Thursday, March 20, 2008

How to truncate all tables in a schema which contain foreign key constraints:

1. Connect as system to a database, however it is possible to perform these scripts from another account which has got enough privileges.
2. Prepare scripts for disabling and enabling foreign key constraints.
2.1. The first script includes the output of the following query:

SELECT 'ALTER TABLE'||' '||owner||'.'||table_name||' DISABLE CONSTRAINT '||constraint_name||' ;' FROM dba_constraints WHERE constraint_type = 'R' and owner='name of schema' and status = 'ENABLED';

Let's name the output of this query as disable_constraints.sql

2.2. The second script includes the output of a very similar query.

SELECT 'ALTER TABLE'||' '||owner||'.'||table_name||' ENABLE CONSTRAINT '||constraint_name||' ;' FROM dba_constraints WHERE constraint_type = 'R' and owner='
name of schema' and status = 'ENABLED';

Let's name the output of this query as enable_constraints.sql

3. Prepare scripts for truncating all tables in the schema.
SELECT 'TRUNCATE TABLE '||OWNER||'.'||TABLE_NAME||' ;' FROM DBA_TABLES WHERE OWNER='name of schema';

Name the output of this query as truncate_tables.sql

4. Run these scripts in the following order:
@
disable_constraints.sql
@
truncate_tables.sql
@
enable_constraints.sql

That's it.

Thanks,
Sergey.







44 comments:

Anonymous said...

Thank these scripts helped me, but the second one is wrong the where should be for DISABLE constraints not for ENABLE

Shane said...

No, the 'ENABLE' for the second query's where cause is correct. This query should be run before you truncate the tables. It will give you statements to enable only the constraints that were enabled before disabling any.

Jay Khimani said...

Thanks for these scripts.

Anonymous said...

should be 'DISABLED' in the second query?

Anonymous said...

in a first time you needs prepare scripts and run it after,
so ENABLED is correct for both of them

Unknown said...
This comment has been removed by the author.
Anonymous said...

DECLARE
CURSOR ENA
IS
SELECT * FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R' AND STATUS = 'ENABLED';

CURSOR TAB
IS
SELECT * FROM USER_TABLES;
BEGIN

FOR I IN ENA
LOOP
EXECUTE IMMEDIATE ('ALTER TABLE '||I.OWNER||'.'||I.TABLE_NAME||' DISABLE CONSTRAINT '||I.CONSTRAINT_NAME);
END LOOP;

FOR I IN TAB
LOOP
EXECUTE IMMEDIATE ('TRUNCATE TABLE '||I.TABLE_NAME);
END LOOP;

FOR I IN ENA
LOOP
EXECUTE IMMEDIATE ('ALTER TABLE '||I.OWNER||'.'||I.TABLE_NAME||' ENABLE CONSTRAINT '||I.CONSTRAINT_NAME);
END LOOP;

END;

Unknown said...

Thanks for the script.
Oracle Training in Chennai

Anonymous said...

I get a lot of great information from this blog. Recently I did oracle certification course at a leading academy. If you are looking for best Oracle Course in Chennai visit FITA IT training and placement academy which offer PL SQL Training in Chennai.

Unknown said...

Hadoop Training Chennai

Hi, I am Christina lives in Chennai. I am technology freak. I did Hadoop Training in Chennai at FITA. This is useful for me to make a bright career in IT field.

Hadoop Course in Chennai

Unknown said...

I get a lot of great information from this blog. Thank you for your sharing this informative blog. Just now I have completed hadoop certification course at a leading academy. If you are interested to learn Hadoop Training in Chennai visit FITA IT training and placement academy which offer Big Data Training in Chennai.

Unknown said...

Your posts is really helpful for me.Thanks for your wonderful post. I am very happy to read your post. AWS Training in chennai | AWS Training chennai | AWS course in chennai

surangacloud said...

Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing. cloud computing training in chennai | cloud computing training chennai | cloud computing course in chennai | cloud computing course chennai

Unknown said...

Nice article i was really impressed by seeing this article, it was very interesting and it is very useful for me.. VMWare Training in chennai | VMWare Training chennai | VMWare course in chennai | VMWare course chennai

Unknown said...

There are lots of information about latest technology and how to get trained in them, like Big Data Training in Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies(Big Data Training). By the way you are running a great blog. Thanks for sharing this. cloud computing training

Unknown said...

Latest Govt Bank Railway Jobs 2016

The information mentioned in the blog are several of the very best available...............

Unknown said...

Latest Govt Bank Jobs Notification 2016

Major thanks for the blog post, thanks Again. Keep writing

Unknown said...

It’s too informative blog and I am getting conglomerations of info’s. Thanks for sharing; I would like to see your updates regularly so keep blogging. If anyone looking car just get here
Regards,
sas training in Chennai|sas course in Chennai|sas institutes in Chennai

Unknown said...

Thanks Admin for sharing such a useful post, I hope it’s useful to many individuals for developing their skill to get good career.
Regards,
Oracle Training in Chennai|Oracle DBA Training in Chennai|Oracle Training Institutes in Chennai

Sgraph Infotech said...

Thanks for the information admin i would like visit your blog more and more so keep sharing the information.



pl sql training institutes in bangalore

DBA training courses said...

Great effort about truncate method with schema. Keep updating.
DBA course syllabus | Oracle dba course

Unknown said...

Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.


Best Java Training Institute Chennai


janani said...

Thanks a lot for sharing us about this update. Hope you will not get tired on making posts as informative as this. 
Java training in Chennai | Java training in Omr

Oracle training in Chennai

Java training in Chennai | Java training in Annanagar

Java training in Chennai | Java training institute in Chennai | Java course in Chennai

Mirnalini Sathya said...

Superbbb blog!!! Thanks for sharing your knowledge with us.

Selenium Training in Chennai
Best Selenium Training Institute in Chennai
ios developer training in chennai
Digital Marketing Training in Chennai
.Net coaching centre in chennai
Best Hadoop Training in Chennai
Best hadoop training institute in chennai

Unknown said...

We are a group of volunteers and starting a new initiative in a community. Your blog provided us valuable information to work on.You have done a marvellous job!
Data Science course in kalyan nagar | Data Science Course in Bangalore
Data Science course in OMR | Data Science Course in Chennai
Data Science course in chennai | Best Data Science training in chennai
Data science course in velachery | Data Science course in Chennai
Data science course in jaya nagar | Data Science course in Bangalore
Data Science interview questions and answers

Mounika said...

After reading your post I understood that last week was with full of surprises and happiness for you. Congratz! Even though the website is work related, you can update small events in your life and share your happiness with us too.

python training Course in chennai
python training in Bangalore
Python training institute in bangalore

Unknown said...

I appreciate your efforts because it conveys the message of what you are trying to say. It's a great skill to make even the person who doesn't know about the subject could able to understand the subject . Your blogs are understandable and also elaborately described. I hope to read more and more interesting articles from your blog. All the best.
Selenium training in Pune | Selenium training institute in Pune | Selenium course in Pune

Selenium Online training | Selenium Certification Online course-Gangboard

Selenium interview questions and answers

Selenium interview questions and answers

tamilsasi said...

Inspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you.
Keep update more information..


Selenium training in bangalore
Selenium training in Chennai
Selenium training in Bangalore
Selenium training in Pune
Selenium Online training
Selenium interview questions and answers

SRI said...

This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me.. 
microsoft azure training in bangalore
rpa training in bangalore
best rpa training in bangalore
rpa online training

Priyanka said...

Attend The Python Training in Bangalore From ExcelR. Practical Python Training in Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Python Training in Bangalore.

saketh said...


I have express a few of the articles on your website now, and I really like your style of Python training in pune blogging. I added it to my favorite’s blog site list and will be checking back soon…

Tech News said...

nice blog
devops training in bangalore
hadoop training in bangalore
iot training in bangalore
machine learning training in bangalore
uipath training in bangalore

Tech News said...

Visit here - Devops training in bangalore

Tech News said...

For Hadoop Training in Bangalore Visit:
Big Data And Hadoop Training In Bangalore

Priyanka said...

Attend The Data Analytics Courses in Bangalore From ExcelR. Practical Data Analytics Courses in Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analytics Courses in Bangalore.
ExcelR Data Analytics Courses in Bangalore

eTechno Soft Solutions said...

Really i appreciate the effort you made to share the knowledge. The topic here i found was really effective...

Looking for Cloud Computing Training in Bangalore , learn from eTechno Soft Solutions Cloud Computing Training on online training and classroom training. Join today!

latchu kannan said...

Your information is very useful to me.
AngularJS training in chennai | AngularJS training in anna nagar | AngularJS training in omr | AngularJS training in porur | AngularJS training in tambaram | AngularJS training in velachery





saran said...

"This was a well written blog. Was great reading your blog. Thanks for sharing.
Digital Marketing Training Course in Chennai | Digital Marketing Training Course in Anna Nagar | Digital Marketing Training Course in OMR | Digital Marketing Training Course in Porur | Digital Marketing Training Course in Tambaram | Digital Marketing Training Course in Velachery

"

Jayalakshmi said...




The strategy you have posted on this technology helped me to get into the next level and had lot of information in it. The angular js programming language is very popular which are most widely used.



Dot Net Training in Chennai | Dot Net Training in anna nagar | Dot Net Training in omr | Dot Net Training in porur | Dot Net Training in tambaram | Dot Net Training in velachery







M. Faramawy said...

Thanks too much ,
But I found an other solution helped me :
it's here https://stackoverflow.com/questions/832160/sql-delete-all-the-data-from-all-available-tables

aarthi said...

All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information,
I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates.
Java training in Chennai

Java training in Bangalore

Java training in Hyderabad

Java Training in Coimbatore

Java Online Training

Devi said...

Really awesome blog. Your blog is really useful for me. oracle training in chennai

Devi said...

Reach to the best software training institute in Chennai, Infycle Technologies, to enter the IT industry with well-defined skills. Infycle Technologies is the rapidly developing software training cum placement center in Chennai and is generally known for its significance in providing quality hands-on practical training with 200% guaranteed outcomes! Call 7502633633 to book a free demo and to avail the best offers.Best Software Training Institute in Chennai | Infycle Technologies

Pavithra Devi said...


This post is so interactive and informative.keep update more information…
IELTS Coaching in anna nagar
IELTS Coaching in Chennai