OraFAQ Forum: Server Administration » How to delete all data from the database

Home » RDBMS Server » Server Administration » How to delete all data from the database (RDBMS, 12.1.0.2.0, Linux)

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

How to delete all data from the database [message #666187]

Wed, 18 October 2017 14:27

youngb912
Messages: 56
Registered: October 2007
Location: New York

Member

Hello,

I have a dev database that I will like to delete/truncate/purge all the data. I don't want to drop the database. I want to delete all data for all schema and all tables. How can I do this?

Report message to a moderator

Re: How to delete all data from the database [message #666188 is a reply to message #666187]

Wed, 18 October 2017 14:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

The fastest would be DROP USER SCOTT CASCADE;

https://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_9008.htm

Otherwise you need to drop each object individually.

Report message to a moderator

Re: How to delete all data from the database [message #666189 is a reply to message #666187]

Wed, 18 October 2017 16:02

John Watson
Messages: 8927
Registered: January 2010
Location: Global Village

Senior Member

Are you saying that you want to remove the rows but keep the tables? What about sequences, stored pl/sql, views, all the rest?

Report message to a moderator

Re: How to delete all data from the database [message #666195 is a reply to message #666189]

Thu, 19 October 2017 02:58

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

I do this regularly at my current site to refresh Dev/Test schemas from the Live environment. Essentially, I've written scripts that build dynamic SQL to:

1. Drop all tables in the relevant user schemas (i.e. exclude Oracle built-in/system ones)
2. Drop all objects in the relevant user schemas (i.e. exclude Oracle built-in/system ones)
3. Drop all public synonyms pertaining to those schemas.

Run a Data Pump export from the Live environment into the environment.

I don't do a DROP USER as BlackSwan suggests as some SYS grants can be lost and can be a pain to recreate. However, BlackSwan's DROP USER option has its advantages over my approach as Oracle will not drop a connected user i.e. it's difficult to run against the wrong environment by mistake!

[Edit: typo]

[Updated on: Thu, 19 October 2017 03:13]

Report message to a moderator

Re: How to delete all data from the database [message #666196 is a reply to message #666195]

Thu, 19 October 2017 04:13

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

Senior Member

If you just want to clear out the data then the simplest thing to do is
1) loop over all foreign keys in user_constraints (constraint_type = 'R') and use dynamic sql to disable them
2) loop over user_tables and use dynamic sql to truncate each table
3) repeat step 1 but this time enable the foreign keys

Report message to a moderator

Re: How to delete all data from the database [message #666202 is a reply to message #666196]

Thu, 19 October 2017 09:54

youngb912
Messages: 56
Registered: October 2007
Location: New York

Member

Thanks for the responses. My main goal is to make sure that the DDL for each table and schemas remain after the cleanup. I want to present the empty database to a third party without the data but they need the schemas and tables DDL.

Report message to a moderator

Re: How to delete all data from the database [message #666203 is a reply to message #666202]

Thu, 19 October 2017 10:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SPOOL TRUNCATE.SQL
SELECT 'TRUNCATE TABLE ' || TABLE_NAME ||';' FROM USER_TABLES;
SPOOL OFF
@TRUNCATE.SQL
@TRUNCATE.SQL

Report message to a moderator

Re: How to delete all data from the database [message #666204 is a reply to message #666203]

Thu, 19 October 2017 10:19

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

Senior Member

And that'll fail if you have any foreign keys - hence my expanded approach above.

Report message to a moderator

Re: How to delete all data from the database [message #666205 is a reply to message #666202]

Thu, 19 October 2017 10:42

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

Sorry, speed reading was never my forte... My approach detailed above is if you wanted to refresh a database from an export of a production one which was not your actual question.

Apologies all.

Report message to a moderator

Re: How to delete all data from the database [message #666206 is a reply to message #666202]

Thu, 19 October 2017 12:36

John Watson
Messages: 8927
Registered: January 2010
Location: Global Village

Senior Member

youngb912 wrote on Thu, 19 October 2017 15:54

Thanks for the responses. My main goal is to make sure that the DDL for each table and schemas remain after the cleanup. I want to present the empty database to a third party without the data but they need the schemas and tables DDL.

I wouldn't do it your way at all. I'ld use Data Pump: export and import, using the option to exclude the rows.

Report message to a moderator

Re: How to delete all data from the database [message #666207 is a reply to message #666203]

Thu, 19 October 2017 12:58

youngb912
Messages: 56
Registered: October 2007
Location: New York

Member

Thank you very much.

This will be a good option but I would have to go through the spool file to exclude system tables. I have too much tables to go thru. I am trying the exp/imp route.

Report message to a moderator

Re: How to delete all data from the database [message #666208 is a reply to message #666207]

Thu, 19 October 2017 14:10

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

youngb912 wrote on Thu, 19 October 2017 12:58

Thank you very much.

This will be a good option but I would have to go through the spool file to exclude system tables. I have too much tables to go thru. I am trying the exp/imp route.

If you are referring to BlackSwan's comment:
Quote:

SPOOL TRUNCATE.SQL
SELECT 'TRUNCATE TABLE ' || TABLE_NAME ||';' FROM USER_TABLES;
SPOOL OFF
@TRUNCATE.SQL
@TRUNCATE.SQL

It already excludes system tables. It is making its select from USER_tables, not ALL_tables or DBA_tables.

Report message to a moderator

Re: How to delete all data from the database [message #666209 is a reply to message #666207]

Thu, 19 October 2017 15:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

youngb912 wrote on Thu, 19 October 2017 10:58

Thank you very much.

This will be a good option but I would have to go through the spool file to exclude system tables. I have too much tables to go thru. I am trying the exp/imp route.

please post your exp & imp solution

BTW - TRUNCATE will be MUCH faster.

Report message to a moderator

Re: How to delete all data from the database [message #666216 is a reply to message #666209]

Fri, 20 October 2017 06:42

youngb912
Messages: 56
Registered: October 2007
Location: New York

Member

Issue resolved. Thanks to everyone for their contribution especially "BlackSwan" for his timely responses.

Here is the route I took:

Performed a Data Pump Export (to capture all newly created table in prod) from the prod db to the dev environment using the following:
compression=ALL
directory=data_pump_dir
dumpfile=full_PPRD-%U.dmp
reuse_dumpfiles=Y
filesize=4G
parallel=3
full=Y
content=METADATA_ONLY
logfile=full_export.log

Used the following to perform a Data Pump Import:
impdp system directory=data_pump_dir_dir dumpfile=full_PPRD_%U.dmp full=y content=METADATA_ONLY TABLE_EXISTS_ACTION=TRUNCATE parallel=3 logfile=full_import.log

My initial Data Pump command didn't include "TABLE_EXISTS_ACTION=TRUNCATE" which caused data to still exist in the tables after the import. I thought using content=METADATA_ONLY would have automatically truncate tables with existing data but I was wrong. To eliminate the data the TABLE_EXISTS_ACTION option must be used.

Thank you all.

Report message to a moderator

Re: How to delete all data from the database [message #666218 is a reply to message #666216]

Fri, 20 October 2017 07:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

youngb912 wrote on Fri, 20 October 2017 04:42

Issue resolved. Thanks to everyone for their contribution especially "BlackSwan" for his timely responses.

Here is the route I took:

Performed a Data Pump Export (to capture all newly created table in prod) from the prod db to the dev environment

Above is SERIOUSLY wrong procedurally!

All application code changes should be developed in DEV, tested in QA, before being released into Production.

Moving DDL changes from Prod to Dev shows a totally unprofessional & amateurish IT staff.

Report message to a moderator

Previous Topic:	I need to create 2 tables for below data how to map between the data values
Next Topic:	Block change tracking in standby database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 03:59:41 CDT 2024