Regarding tablespace issues

[holdingbe]

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?

[debasisdas]

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?

what you expect is against structure of oracle.

That is not an issue at all.

[Dave44]

One solution is to have the table in a bigfile tablespace. this way you can enable row movement on the table and perform a shrink space against it as follows

[115]ers_core@DEV01> create table t (col1 int, col2 int);
 
Table created.
 
Elapsed: 00:00:00.07
> select bytes,blocks from user_segments where segment_name = 'T';
 
     BYTES     BLOCKS
---------- ----------
     65536          8
 
Elapsed: 00:00:00.09
> insert into t select level,level*3 from dual connect by level <= 3000;
 
3000 rows created.
 
Elapsed: 00:00:00.06
> select bytes,blocks from user_segments where segment_name = 'T';
 
     BYTES     BLOCKS
---------- ----------
    131072         16
 
Elapsed: 00:00:00.07
> delete from t;
 
3000 rows deleted.
 
Elapsed: 00:00:00.11
> select bytes,blocks from user_segments where segment_name = 'T';
 
     BYTES     BLOCKS
---------- ----------
    131072         16
 
Elapsed: 00:00:00.09
> alter table t enable row movement;
 
Table altered.
 
Elapsed: 00:00:00.17
> alter table t shrink space;
 
Table altered.
 
Elapsed: 00:00:00.15
> select bytes,blocks from user_segments where segment_name = 'T';
 
     BYTES     BLOCKS
---------- ----------
     65536          8
 
Elapsed: 00:00:00.09