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_RESPON SE
(
BANK_KEY NUMBER(9) NOT NULL,
BANK_ID VARCHAR2(30 BYTE) NOT NULL,
BANK_PSH_SUFFIX VARCHAR2(3 BYTE) NOT NULL,
RESPONSE_XML_BA NK CLOB
)
TABLESPACE CSA_BANK_RESPON SE_TBS;


ALTER TABLE CSA_BANK_RESPON SE ADD (
CONSTRAINT CSA_BANK_RESPON SE
PRIMARY KEY
(BANK_KEY)
USING INDEX
TABLESPACE CSA_BANK_RESPON SE_NDX);

2) inserted large amount of data.

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

The size is 345.654 MB.

4) deleted data what we inserted.

Delete from CSA_BANK_RESPON SE;
Commit;

After that I am checking tablespace,it shows a like

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

The size is 345.654 MB.
5) once again,I am inserting large amount of data into ' CSA_BANK_RESPON SE '.
Now size is

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

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