OraFAQ Forum: Server Administration » Delete Records Without Table Drop

Home » RDBMS Server » Server Administration » Delete Records Without Table Drop

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

Delete Records Without Table Drop [message #55989]

Fri, 28 February 2003 03:02

G Venu Gopal
Messages: 2
Registered: February 2003

Junior Member

I need to delete thousands of records from an existing database.
Can I do it without dropping the table and then re-creating the table?
Does Oracle have any tool or sql command to do it ?
PS : This Database was meant for testing. Now I want a fresh one with all the tables empty.
Thanx
Venu

Report message to a moderator

Re: Delete Records Without Table Drop [message #55990 is a reply to message #55989]

Fri, 28 February 2003 03:10

Remi Visser
Messages: 44
Registered: December 2002

Member

Hi

DML: (rollback is possible)

SQL> delete from table_name;

DDL: (no rollback possible)

SQL> truncate table table_name

http://askremi.ora-0000.com

Report message to a moderator

Re: Delete Records Without Table Drop [message #55992 is a reply to message #55989]

Fri, 28 February 2003 03:28

G Venu Gopal
Messages: 2
Registered: February 2003

Junior Member

Not Just One or Two, there are some 275 tables in the database, data from all the tables needs to be deleted. I know "On Delete cascade" option.. but for so many tables .. how can i ?

Is there Any way/tool I can write the whole database/table structure ( dependencies + all tables ) into a script file, delete all tables and re-create from script file ?

Thanx Remi
Urz
Venu

Report message to a moderator

Re: Delete Records Without Table Drop [message #55997 is a reply to message #55989]

Fri, 28 February 2003 04:25

Lambros Ntalkos
Messages: 2
Registered: February 2003

Junior Member

command:
delete from
;
removes all records from the table without dropping it.
Command:
select table_name from user_tables lists all the tables that you (the user) own.
You can combine these two statements, but be careful not to drop records from tables you need.

Report message to a moderator

Re: Delete Records Without Table Drop [message #56001 is a reply to message #55989]

Fri, 28 February 2003 06:50

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

you can just truncate the records...
write a simple pl/sql routine to do that..
I truncate 1800 tables ( around 15 gb data) for lawson applications...
Just make sure u have enough Rollback Segment...else u will be hitting ora-1555

Report message to a moderator

Previous Topic:	Mounting a datafile from a different DB.
Next Topic:	Re: Delete Records Without Table Drop

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 28 20:50:14 CST 2024