Home » RDBMS Server » Server Administration » How to delete all data from the database (RDBMS, 12.1.0.2.0, Linux)
How to delete all data from the database [message #666187] Wed, 18 October 2017 14:27 Go to next message
youngb912
Messages: 53
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?

Re: How to delete all data from the database [message #666188 is a reply to message #666187] Wed, 18 October 2017 14:50 Go to previous messageGo to next message
BlackSwan
Messages: 25716
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.
Re: How to delete all data from the database [message #666189 is a reply to message #666187] Wed, 18 October 2017 16:02 Go to previous messageGo to next message
John Watson
Messages: 7148
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?
Re: How to delete all data from the database [message #666195 is a reply to message #666189] Thu, 19 October 2017 02:58 Go to previous messageGo to next message
gazzag
Messages: 905
Registered: November 2010
Location: Bristol, 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 Go to previous messageGo to next message
cookiemonster
Messages: 12927
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
Re: How to delete all data from the database [message #666202 is a reply to message #666196] Thu, 19 October 2017 09:54 Go to previous messageGo to next message
youngb912
Messages: 53
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.
Re: How to delete all data from the database [message #666203 is a reply to message #666202] Thu, 19 October 2017 10:03 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
SPOOL TRUNCATE.SQL
SELECT 'TRUNCATE TABLE ' || TABLE_NAME ||';' FROM USER_TABLES;
SPOOL OFF
@TRUNCATE.SQL
@TRUNCATE.SQL
Re: How to delete all data from the database [message #666204 is a reply to message #666203] Thu, 19 October 2017 10:19 Go to previous messageGo to next message
cookiemonster
Messages: 12927
Registered: September 2008
Location: Rainy Manchester
Senior Member
And that'll fail if you have any foreign keys - hence my expanded approach above.
Re: How to delete all data from the database [message #666205 is a reply to message #666202] Thu, 19 October 2017 10:42 Go to previous messageGo to next message
gazzag
Messages: 905
Registered: November 2010
Location: Bristol, 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.
Re: How to delete all data from the database [message #666206 is a reply to message #666202] Thu, 19 October 2017 12:36 Go to previous messageGo to next message
John Watson
Messages: 7148
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.
Re: How to delete all data from the database [message #666207 is a reply to message #666203] Thu, 19 October 2017 12:58 Go to previous messageGo to next message
youngb912
Messages: 53
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.
Re: How to delete all data from the database [message #666208 is a reply to message #666207] Thu, 19 October 2017 14:10 Go to previous messageGo to next message
EdStevens
Messages: 848
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.
Re: How to delete all data from the database [message #666209 is a reply to message #666207] Thu, 19 October 2017 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 25716
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.
Re: How to delete all data from the database [message #666216 is a reply to message #666209] Fri, 20 October 2017 06:42 Go to previous messageGo to next message
youngb912
Messages: 53
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.
Re: How to delete all data from the database [message #666218 is a reply to message #666216] Fri, 20 October 2017 07:56 Go to previous message
BlackSwan
Messages: 25716
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.
Previous Topic: user_tab_modifications not showing data for new table
Next Topic: I need to create 2 tables for below data how to map between the data values
Goto Forum:
  


Current Time: Mon Nov 20 09:27:37 CST 2017

Total time taken to generate the page: 0.01739 seconds