Home » SQL & PL/SQL » SQL & PL/SQL » Tablespace issues (Oracle10g,Linux)
Tablespace issues [message #327488] Mon, 16 June 2008 10:10 Go to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi ,
I am deleting the large amount of data in table using the delete statements .After that I have checked tablespace, there is no change in tablespace memory. But when using truncate statements,I do see change in tablespaces.

My steps are below
1)CREATE TABLE CSA_BANK_RESPONSE
(
BANK_KEY NUMBER(9) NOT NULL,
BANK_ID VARCHAR2(30 BYTE) NOT NULL,
BANK_PSH_SUFFIX VARCHAR2(3 BYTE) NOT NULL,
RESPONSE_XML_BANK CLOB
)
TABLESPACE CSA_BANK_RESPONSE_TBS;


ALTER TABLE CSA_BANK_RESPONSE ADD (
CONSTRAINT CSA_BANK_RESPONSE
PRIMARY KEY
(BANK_KEY)
USING INDEX
TABLESPACE CSA_BANK_RESPONSE_NDX);

2) inserted large amount of data.

3) select sum(bytes)/1024/1024
from user_segments
where tablespace_name= CSA_BANK_RESPONSE_NDX;

The size is 345.654 MB.

4) deleted data what we inserted.

Delete from CSA_BANK_RESPONSE;
Commit;

After that I am checking tablespace,it shows a like

select sum(bytes)/1024/1024
from user_segments
where tablespace_name= CSA_BANK_RESPONSE ';

The size is 345.654 MB.

5) once again,I am inserting large amount of data into ' CSA_BANK_RESPONSE '.
Now size is

select sum(bytes)/1024/1024
from user_segments
where tablespace_name=' CSA_BANK_RESPONSE’;

the size is 645.456MB.

What I expect is, similar to truncate statements if delete statements are used then there should be a decrease in tablespace.
How can we resolve this issue?
Re: Tablespace issues [message #327490 is a reply to message #327488] Mon, 16 June 2008 10:12 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>What I expect is, similar to truncate statements if delete statements are used then there should be a decrease in tablespace.
Your expectations are incorrect.
DELETE does NOT reset the High Water Mark; as occurs with TRUNCATE.
Re: Tablespace issues [message #327492 is a reply to message #327488] Mon, 16 June 2008 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You posted the same question in another way 4 hours before in:
http://www.orafaq.com/forum/m/327414/102589/#msg_327414
and you showed that you perfectly knows the answer.
4 hours later, this did not change, delete still does not release space.
Try another time in 4 hours...

Regards
Michel
Re: Tablespace issues [message #327638 is a reply to message #327488] Tue, 17 June 2008 02:13 Go to previous message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi,

I have resolved tablespace issues.

After deleteing data, i have execute shrink space command.

command:

alter table lob_test modify lob (data) (shrink space);

before excuteing shrink space command:

365 MB.

after executig shrink space command;

5MB only.

Thanks for your help.

thanks,
Michael.
Previous Topic: sql join
Next Topic: Meta data
Goto Forum:
  


Current Time: Thu Dec 08 23:54:58 CST 2016

Total time taken to generate the page: 0.06921 seconds