Home » SQL & PL/SQL » SQL & PL/SQL » after delete, how we can "release space" like truncate  () 1 Vote
after delete, how we can "release space" like truncate [message #249069] Tue, 03 July 2007 04:31 Go to next message
asmani
Messages: 47
Registered: February 2007
Member
Dear All,
Good morning.

Sorry to bother you, I have a quick question.

I am working on a daily job which update some record but its not working properly (I don't know why)so I think if I delete all records and then reinsert them. Its working fine but I know after couple of months the size of table will be huge.

so I am think if is there anything can I do to release that space which table holds after deletion or if you have any other suggestion then please let me know.

Thank you for your time and help.

Take care and have a nice day.
Usman.
Re: after delete, how we can "release space" like truncate [message #249070 is a reply to message #249069] Tue, 03 July 2007 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I think if I delete all records and then reinsert them. Its working fine but I know after couple of months the size of table will be huge.

No, if you delete all and insert, Oracle will reuse the space so the table will not grow more.

Oracle does not release space after a delete. If you want it you have to shrink the table (if you are in 10g) or export/import the table (if the lowest version).

Regards
Michel
Re: after delete, how we can "release space" like truncate [message #249081 is a reply to message #249069] Tue, 03 July 2007 05:40 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
To reclaim space, you may also consider ALTER TABLE <table_name> MOVE;
Re: after delete, how we can "release space" like truncate [message #249184 is a reply to message #249081] Tue, 03 July 2007 10:38 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
in 10g

Alter table My_table enable rowmovment;

alter table tm_table shrink space;

alter table tm_table shrink space cascade >>>>alld ependents object (indexes)
Re: after delete, how we can "release space" like truncate [message #249290 is a reply to message #249069] Wed, 04 July 2007 00:02 Go to previous messageGo to next message
prabhuapps
Messages: 79
Registered: June 2005
Location: Bangalore
Member
I have one small question here.

SQL> SELECT banner FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

SQL> create table emp(empno number);

Table created.

SQL> insert into emp select level from dual connect by level < 101;

100 rows created.

SQL> commit;

Commit complete.

SQL> delete from emp where empno < 51;

50 rows deleted.

SQL> commit;

Commit complete.


Will these deleted records space reused for other records?

Regards,
Prabhu
Re: after delete, how we can "release space" like truncate [message #249291 is a reply to message #249290] Wed, 04 July 2007 00:05 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

A commit will automatically release the space for other records to be saved. It is not the truncate that releases the space, the DDL activity is what is involved here in releasing the space.
Re: after delete, how we can "release space" like truncate [message #249297 is a reply to message #249069] Wed, 04 July 2007 00:27 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>A commit will automatically release the space for other records to be saved.

Exactly WHAT space is "released" by commit & released to where?

>It is not the truncate that releases the space, the DDL activity is what is involved here in releasing the space.

Rhetorical question - How do you differentiate between "truncate" & DDL?

Please post reproducable test case(s) to support these questionable assertions.
Re: after delete, how we can "release space" like truncate [message #249301 is a reply to message #249297] Wed, 04 July 2007 00:45 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

I don't mean that Truncate is not a DDL statement. But i meant to say all DDL statements release space which is done implicitely by oracle once a DDL is fired. If i am wrong, can you tell me what actually happens when 10 million records are deleted and then a commit is done.How that memory is reused?
Re: after delete, how we can "release space" like truncate [message #249303 is a reply to message #249069] Wed, 04 July 2007 00:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
OP's title was "after delete, how we can "release space" like truncate"

IMO, the ONLY way is to use TRUNCATE.
DELETE does NOT reduce the amount of physical storage consumed by Oracle.
After TRUNCATE you may be able to SHRINK the OS space consumed.

Please post reproducable test case(s) to support these questionable assertions.

Please post reproducable test case(s) to support these questionable assertions.

Please post reproducable test case(s) to support these questionable assertions.

[Updated on: Wed, 04 July 2007 01:03] by Moderator

Report message to a moderator

Re: after delete, how we can "release space" like truncate [message #249305 is a reply to message #249301] Wed, 04 July 2007 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
all DDL statements release space which is done implicitely by oracle once a DDL is fired

A DDL releases space if it is designed to. A create statement does not release space. Wink
Moreover, if a DDL unexpectedly fails it does not release space it possibly allocated for its execution.

Regards
Michel
Re: after delete, how we can "release space" like truncate [message #249334 is a reply to message #249069] Wed, 04 July 2007 02:13 Go to previous messageGo to next message
prabhuapps
Messages: 79
Registered: June 2005
Location: Bangalore
Member
In the below eg what I gave is just a (DML)delete statement to delete 50 records and I issued a commit.

Will it be reused... ? Please confirm

Regards,
Prabhu
Re: after delete, how we can "release space" like truncate [message #249337 is a reply to message #249334] Wed, 04 July 2007 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Confirmed.
Immediately for an update.
When the percentage of used space in the block decreases to below PCTUSED for insert (and if you use MSSM).

Regards
Michel
Re: after delete, how we can "release space" like truncate [message #249342 is a reply to message #249069] Wed, 04 July 2007 02:27 Go to previous messageGo to next message
prabhuapps
Messages: 79
Registered: June 2005
Location: Bangalore
Member
Thanks Michel
Re: after delete, how we can "release space" like truncate [message #250247 is a reply to message #249069] Mon, 09 July 2007 05:37 Go to previous message
asmani
Messages: 47
Registered: February 2007
Member
Thanks everyone.

Its was a very informative question and now that concept is clear like a day to me.

But I will bother you all if I got any other problem regrading Oracle.

Thanks again Oracles.


Note: Please rate this question.

[Updated on: Mon, 09 July 2007 05:38]

Report message to a moderator

Previous Topic: "sub"sequence generator problem
Next Topic: Doubt in Null and Empty String
Goto Forum:
  


Current Time: Sun Dec 11 04:40:09 CST 2016

Total time taken to generate the page: 0.09828 seconds