No question at this time
DBA Top 10
1 B. Vroman 15700
2 M. Cadot 10200
3 T. Boles 8350
4 J. Schnackenberg 8300
5 A. Kavsek 7800
6 M. Hidayathullah ... 2500
7 P. Wisse 1200
8 G. Lambregts 1100
9 T. P 1000
10 B. Derous 500
10 R. Pattyn 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48277
Total active users1612
Act. users last 24h4
Act. users last hour0
Registered user hits last week185
Registered user hits last month836
Go up

Does truncate/delete requires table reorgnization or not ?
Next thread: Special Charecters
Prev thread: Schema Anylises

Message Score Author Date
Hi Guru's Expert advice is needed in one issue....... Raj Kumar Mar 28, 2018, 18:54
DELETE yes, as DELETE just marks the rows as del...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Michel Cadot Mar 28, 2018, 20:33
Hello Rajkumar, to add something to the info yo...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Mar 29, 2018, 07:47
Hi Michel, Thankyou very much for your help and...... Raj Kumar Mar 29, 2018, 13:05
Hi Bruno, Many thanks for your recommendations....... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Raj Kumar Mar 29, 2018, 13:07
Hello RajKumar, thank you for the feedback. I'...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Mar 29, 2018, 17:59
...continued... Well, we can do the truncate and ...... Score: 800 PtsScore: 800 PtsScore: 800 PtsScore: 800 PtsScore: 800 Pts Bruno Vroman Mar 29, 2018, 18:00
Hi Bruno, Thankyou so much for taking time and ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Raj Kumar Mar 29, 2018, 18:39

Follow up by mail Click here


Subject: Does truncate/delete requires table reorgnization or not ?
Author: Raj Kumar, India
Date: Mar 28, 2018, 18:54, 207 days ago
Message: Hi Guru's

Expert advice is needed in one issue.

We have one large table 470 GB in size.

Just wanted to know if we truncate this table OR delete all the data from this table, do we need to reorganization of the table to reclaim the free space in tablespace ?

Thanks in advance.

Thanks,
RajKumar
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Does truncate/delete requires table reorgnization or not ?
Author: Michel Cadot, France
Date: Mar 28, 2018, 20:33, 207 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message:
DELETE yes, as DELETE just marks the rows as deleted in the table blocks but the blocks are still allocated to the table.

TRUNCATE no, unless you specify REUSE STORAGE.
But it is not a good idea to free space of a big table if this one will soon grow up to about the same size.

If you want to delete ALL rows of a big table, better use TRUNCATE.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Does truncate/delete requires table reorgnization or not ?
Author: Bruno Vroman, Belgium
Date: Mar 29, 2018, 07:47, 206 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Rajkumar,

to add something to the info you received: if you want you can keep a volume close to what you like (example: keep close to 100GB) with TRUNCATE if you play with the "MINEXTENTS" of the table...

But seeing the question my guess is that you just want to TRUNCATE it. Note that this will free the space in the tablespace but will not reduce the size of the datafiles...

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Does truncate/delete requires table reorgnization or not ?
Author: Raj Kumar, India
Date: Mar 29, 2018, 13:05, 206 days ago
Message: Hi Michel,

Thankyou very much for your help and valuable recommendations.

Thanks,
RajKumar.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Does truncate/delete requires table reorgnization or not ?
Author: Raj Kumar, India
Date: Mar 29, 2018, 13:07, 206 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi Bruno,

Many thanks for your recommendations.

Can you please elaborate in details-

if you want you can keep a volume close to what you like (example: keep close to 100GB) with TRUNCATE if you play with the "MINEXTENTS" of the table...

It will be great help.


Thanks,
RajKumar

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Does truncate/delete requires table reorgnization or not ?
Author: Bruno Vroman, Belgium
Date: Mar 29, 2018, 17:59, 206 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello RajKumar,

thank you for the feedback.
I'm deeply sorry... As most probably you are using "AUTOALLOCATE" in your tablespace my idea doesn't work...

Here is a complete example leading to the problem...
CREATE TABLE t ( n varchar2(4000) ) pctfree 60 pctused 20 tablespace drop_me;

select bytes from user_extents where segment_name = 'T'
no rows selected
(indeed: deferred creation)
insert into t values ( rpad( 'x', 4000, 'x' ) );
1 row created.
select bytes from user_extents where segment_name = 'T';
BYTES
----------
65536
insert into t select * from t;
1 row created.
insert into t select * from t;
2 rows created.
insert into t select * from t;
4 rows created.
select bytes from user_extents where segment_name = 'T';
BYTES
----------
65536
65536
We are now using 2 extents...
insert into t select * from t;
8 rows created.
select bytes from user_extents where segment_name = 'T';
BYTES
----------
65536
65536
65536
I continue a few steps:
...
256 rows created.
SQL> select bytes from user_extents where segment_name = 'T';
BYTES
----------
65536
65536
65536
65536
65536
65536
65536
65536
65536
65536
65536
65536
65536
65536
65536
65536
1048576
1048576
1048576
1048576
20 rows selected.
so we are now at 20 extents for a total size of 64KB * 16 + 1MB * 4 = 5MB.
Adding some more steps:
...
65536 rows created.
and asking for a summary of the space used:
COMPUTE SUM OF nb summb ON REPORT
BREAK ON REPORT
SELECT bytes / 1024 / 1024 mb, COUNT(*) nb, bytes / 1024 / 1024 * COUNT(*) summb
FROM user_extents
WHERE segment_name = 'T'
GROUP BY bytes
ORDER BY bytes
;
MB NB SUMMB
---------- ---------- ----------
.0625 16 1
1 69 69
5 2 10
8 118 944
49 1 49
---------- ----------
sum 206 1073
The table uses now 206 extents for a total size of 1073MB (i.e. 1.05GB)

Now we want to truncate the table but keep "about 100GB"...
If we specify that we want to keep the first 16 extents, the resulting size will be 1MB (16*0.0625MB);
If we specify that we want to keep the first 20 extents, the resulting size will be 16*0.0625+4*1MB) = 5MB;
... If we keep the first (16+69+2) extents the size will be 1+69+10=80MB
and 118 next extents are 8MB large, so we would liek to keep 20/8~2 of them, then the size will be 96MB.
To do this: we have to keep 16+69+2+2 extents, i.e. 89 extents
ALTER TABLE t STORAGE( MIN_EXTENTS 89 );
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

25150, 00000, "ALTERING of extent parameters not permitted"
// *Cause: An attempt was made to alter the extent parameters for a segment
// in a tablespace with autoallocate or uniform extent allocation
// policy.
// *Action: Remove the appropriate extent parameters from the command.
Alas! Last time I did this was about 20 years ago with Oracle 7 and not using "autoallocate"...
Otherwise we just had to truncate the table now and it would have kept the 89 extents, hence the size would have been 96MB.
...continued...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Does truncate/delete requires table reorgnization or not ?
Author: Bruno Vroman, Belgium
Date: Mar 29, 2018, 18:00, 206 days ago
Score:   Score: 800 PtsScore: 800 PtsScore: 800 PtsScore: 800 PtsScore: 800 Pts
Message: ...continued...
Well, we can do the truncate and then allocate extents:
TRUNCATE TABLE t;

Table truncated. (can take a while if you have a large table...)
SELECT bytes / 1024 / 1024 mb, COUNT(*) nb, bytes / 1024 / 1024 * COUNT(*) summb
FROM user_extents
WHERE segment_name = 'T'
GROUP BY bytes
ORDER BY bytes
;
MB NB SUMMB
---------- ---------- ----------
.0625 1 .0625
---------- ----------
sum 1 .0625
Now I will allocate 100 more extents:
BEGIN
FOR i IN 1..100
LOOP
EXECUTE IMMEDIATE 'alter table t allocate extent';
END LOOP;
END;
/
PL/SQL procedure successfully completed.
(this is very quick)
SELECT bytes / 1024 / 1024 mb, COUNT(*) nb, bytes / 1024 / 1024 * COUNT(*) summb
FROM user_extents
WHERE segment_name = 'T'
GROUP BY bytes
ORDER BY bytes
;
MB NB SUMMB
---------- ---------- ----------
.0625 16 1
1 85 85
---------- ----------
sum 101 86
Hmmm, allocate some more until I reach
MB NB SUMMB
---------- ---------- ----------
.0625 16 1
1 99 99
---------- ----------
sum 115 100
(I don't know how Oracle decides of the size of these extents...)
Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Does truncate/delete requires table reorgnization or not ?
Author: Raj Kumar, India
Date: Mar 29, 2018, 18:39, 206 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi Bruno,

Thankyou so much for taking time and explaining this concept in detail.

You helped me understand this concept very quickly and very politely.

Thankyou,
RajKumar
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here