RE: deletion of data from a large table

2001-07-26 Thread Thomas, Kevin
If I remember rightly, deleting rows from the table does NOT free up tablespace. In order to do that you have to trunctate the table (although this of course deletes all data from the table)...I can't for the life of me remember how you adjust the space the table is actually using after doing a

RE: deletion of data from a large table

2001-07-26 Thread Cale, Rick T (Richard)
I think this is happening because when you DELETE data the High Water Mark(HWM) is not lowered. Essentially you have not gained any freespace. If possible you could export remaining data truncate table then re-import. Correct me if I am wrong here. Rick -Original Message- Sent:

RE: deletion of data from a large table

2001-07-26 Thread Ramasamy, Baskar
Rukmini, Try to do coalesce on the tablespace on which the table is sitting in after deleting rows. alter tablespace TSNAME coalesce; Baskar -Original Message- Sent: 26 July 2001 12:41 To: Multiple recipients of list ORACLE-L Hi All, I have deleted 3 lakhs records from a large

Re: deletion of data from a large table

2001-07-26 Thread Rukmini Devi
This can be done . But what about the constraints ? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, July 26, 2001 5:41 PM I think this is happening because when you DELETE data the High Water Mark(HWM) is not lowered. Essentially you

RE: deletion of data from a large table

2001-07-26 Thread MHately
| || | |+-- -| | | | To: Multiple recipients of list ORACLE-L | | [EMAIL PROTECTED]| | cc: (bcc: Mike Hately/ETECH) | | Subject: RE: deletion of data from a large table

Re: deletion of data from a large table

2001-07-26 Thread Raghu Kota
Rukmini If you have good PCTUSED you are safe!! The space will be reused, Other wise You have to reorganize table!! From: Rukmini Devi [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: deletion of data from a large table

RE: deletion of data from a large table

2001-07-26 Thread Hillman, Alex
]| | cc: (bcc: Mike Hately/ETECH) | | Subject: RE: deletion of data from a large table | -| Rukmini, Try to do coalesce on the tablespace on which the table is sitting

RE: deletion of data from a large table

2001-07-26 Thread Miller, Jay
You would need to drop any foreign key constraints pointing to that table before truncating and then recreate them after reloading the data. If I remember correctly, simply disabling them won't work since Oracle treats Truncate Table the same as Drop Table in many ways. Other constraints (NOT

RE: deletion of data from a large table

2001-07-26 Thread Miller, Jay
Oops, meant to say you can RESIZE the datafile down to 1 Meg after the truncate -Original Message- Sent: Thursday, July 26, 2001 11:01 AM To: Multiple recipients of list ORACLE-L You would need to drop any foreign key constraints pointing to that table before truncating and then

RE: deletion of data from a large table

2001-07-26 Thread Rakesh Gupta
This is what I use for truncating data from a large table with foreign key constraints. select 'alter table ' ||b.table_name ||' disable constraint '||b.constraint_name ||';' from user_constraints a, user_constraints b where a.table_name = tablename and a.constraint_name = b.r_constraint_name and

Re: deletion of data from a large table

2001-07-26 Thread Terry Ball
Well, I have disabled FK constraints, done a truncate and re-enabled. Terry Miller, Jay wrote: You would need to drop any foreign key constraints pointing to that table before truncating and then recreate them after reloading the data. If I remember correctly, simply disabling them won't