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
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:
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
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
|
|| |
|+--
-|
| |
| To: Multiple recipients of list ORACLE-L |
| [EMAIL PROTECTED]|
| cc: (bcc: Mike Hately/ETECH) |
| Subject: RE: deletion of data from a large table
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
]|
| 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
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
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
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
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
11 matches
Mail list logo