OraFAQ Forum: SQL & PL/SQL » How to delete data from all the tables with one Query?

Home » SQL & PL/SQL » SQL & PL/SQL » How to delete data from all the tables with one Query? (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to delete data from all the tables with one Query? [message #633616]

Mon, 23 February 2015 03:56

manisngpl
Messages: 42
Registered: December 2014
Location: Faisalabad, Pakistan

Member

Dear Members,

Please help me in the subject matter that how can I delete data from all the tables with one query.

Regards,

Usman

Report message to a moderator

Re: How to delete data from all the tables with one Query? [message #633619 is a reply to message #633616]

Mon, 23 February 2015 04:17

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can't.

Report message to a moderator

Re: How to delete data from all the tables with one Query? [message #633620 is a reply to message #633619]

Mon, 23 February 2015 04:24

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You can however write a bit of code to query user_tables, then loop over the results and use execute immediate to issue a dynamic delete (or truncate) to get rid of all the data.

Report message to a moderator

Re: How to delete data from all the tables with one Query? [message #633621 is a reply to message #633620]

Mon, 23 February 2015 04:26

manisngpl
Messages: 42
Registered: December 2014
Location: Faisalabad, Pakistan

Member

would you please let me provide the code?

Report message to a moderator

Re: How to delete data from all the tables with one Query? [message #633625 is a reply to message #633621]

Mon, 23 February 2015 05:03

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Why don't you try to write it?
Clearly note that loop on delete is NOT a single delete.
So what are you trying to achieve in the end?

[Updated on: Mon, 23 February 2015 08:39]

Report message to a moderator

Re: How to delete data from all the tables with one Query? [message #633634 is a reply to message #633625]

Mon, 23 February 2015 08:31

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

@OP,

Why delete? Better truncate the tables, it would reset the HWM(High water mark) back to zero. How tedious is it to develop the script to do so? As already suggested, you need to use *_TABLES view. Depends whether you want it for a schema or entire DB.

For example,

select 'truncate table ' || table_name || ';' from user_tables

If you don't have any other objects other than just tables, then just drop the user. Make sure you generate the DDL of all the tables using DBMS_METADATA. Recreate the user and execute the DDL script thus generated.

The question is too broad, could you narrow down to some specific requirement.

[Updated on: Mon, 23 February 2015 08:34]

Report message to a moderator

Re: How to delete data from all the tables with one Query? [message #633637 is a reply to message #633634]

Mon, 23 February 2015 09:54

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Just keep in mind you can't truncate table if there are FKs referencing that table.

SY.

Report message to a moderator

Re: How to delete data from all the tables with one Query? [message #633638 is a reply to message #633637]

Mon, 23 February 2015 09:59

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Yes, agreed SY.

There could be multiple solutions to such a broad question. Some might apply, some may not.

Report message to a moderator

Re: How to delete data from all the tables with one Query? [message #633641 is a reply to message #633634]

Mon, 23 February 2015 11:56

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Lalit Kumar B wrote on Mon, 23 February 2015 08:31

@OP,

If you don't have any other objects other than just tables, then just drop the user. Make sure you generate the DDL of all the tables using DBMS_METADATA. Recreate the user and execute the DDL script thus generated.

Just to empty some tables? And don' forget you also lose any GRANTs .....
All in all, that's a LOT Of work just to empty (delete/truncate) some tables.

Report message to a moderator

Re: How to delete data from all the tables with one Query? [message #633642 is a reply to message #633641]

Mon, 23 February 2015 12:34

Bill B
Messages: 1971
Registered: December 2004

Senior Member

show us what you have written and we will give you hints. We do not do homework but we will help with homework.

Report message to a moderator

Previous Topic:	ora-22288
Next Topic:	Table Updates

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 17:46:01 CDT 2024