468,110 Members | 1,505 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,110 developers. It's quick & easy.

Regarding tablespace issues

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?
Jun 16 '08 #1
2 4428
debasisdas
8,127 Expert 4TB
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.
Jun 17 '08 #2
Dave44
153 100+
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
Expand|Select|Wrap|Line Numbers
  1. [115]ers_core@DEV01> create table t (col1 int, col2 int);
  2.  
  3. Table created.
  4.  
  5. Elapsed: 00:00:00.07
  6. > select bytes,blocks from user_segments where segment_name = 'T';
  7.  
  8.      BYTES     BLOCKS
  9. ---------- ----------
  10.      65536          8
  11.  
  12. Elapsed: 00:00:00.09
  13. > insert into t select level,level*3 from dual connect by level <= 3000;
  14.  
  15. 3000 rows created.
  16.  
  17. Elapsed: 00:00:00.06
  18. > select bytes,blocks from user_segments where segment_name = 'T';
  19.  
  20.      BYTES     BLOCKS
  21. ---------- ----------
  22.     131072         16
  23.  
  24. Elapsed: 00:00:00.07
  25. > delete from t;
  26.  
  27. 3000 rows deleted.
  28.  
  29. Elapsed: 00:00:00.11
  30. > select bytes,blocks from user_segments where segment_name = 'T';
  31.  
  32.      BYTES     BLOCKS
  33. ---------- ----------
  34.     131072         16
  35.  
  36. Elapsed: 00:00:00.09
  37. > alter table t enable row movement;
  38.  
  39. Table altered.
  40.  
  41. Elapsed: 00:00:00.17
  42. > alter table t shrink space;
  43.  
  44. Table altered.
  45.  
  46. Elapsed: 00:00:00.15
  47. > select bytes,blocks from user_segments where segment_name = 'T';
  48.  
  49.      BYTES     BLOCKS
  50. ---------- ----------
  51.      65536          8
  52.  
  53. Elapsed: 00:00:00.09
  54.  
Jun 19 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Joachim Klassen | last post: by
4 posts views Thread by Joachim Klassen | last post: by
2 posts views Thread by Raghava | last post: by
reply views Thread by sat | last post: by
1 post views Thread by Ravi Padmakar | last post: by
3 posts views Thread by Michel Esber | last post: by
1 post views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.