DBMS_SPACE

[shailendra]

HOW CAN I SEE THE EFFECT OF DEALLOCATING UNSED SPACE?

I HAVE A TABLE HAVING 0 ROWS

I EXECUTE COMMAND ALTER TABLE TABLE_NAME DEALLOCATE UNUSED

I ALSO EXECUTE DBMS_SPACE PROCEDURE IT IS SHOWING 31 UNUSED BLOCKS BUT WHEN I DEALLOCATE THE UNUSED SPACE. WHERE CAN I SEE THE EFFECT OF THIS?

SHAILENDRA

[SANJAY_G]

Keep in mind that it will deallocate space only above HWM

Sanjay

[shailendra]

SANJAY G BUT I EXECUTE THE TRUNCATE COMMAND IT MEANS THE HIGH WATER MARK SHOULD COME DOWN

IS IT CORRECT?
BUT STILL I M NOT ABLE TO SEE ANY DIFFERENCE

SHAILENDRA

[SANJAY_G]

TRUNCATE will deallocate all extents except one unless you are using MINEXTENTS clause.

Sanjay

[jmodic]

Apparently your table occupies one extent, its size is 32 blocks. For some reason Oracle can't shrink that initial extent, eventhough it is empty and the HWM has been reset.

One of the reasons could be that your table is located in localy managed tablespace. If so, Oracle can't make initial extent any smaller. Is this the case?

P.S.
Giving it a second tought, I don't think Oracle can actualy shrink the initial extent ever, no matter if the tablespace is localy or dictionary managed.

[Edited by jmodic on 08-29-2002 at 03:03 AM]

[shailendra]

thanks a lot

I want one suggestion from you please help me.

I have soo many tables with very big extents and no rows and these tables I am not using but also I can not drop them now the problem is that whenever I export it, it creates an extent unnscessarly and I want to avoid this.

How can I avoid this help me.

Shailendra

[jmodic]

Very simple, use:

ALTER TABLE xyz MOVE STORAGE (INITIAL x)

This will rebuild your table with size of the initial extent set to x bytes, while remaining any other thing (ref. integrity, constraints, triggers, grants, etc etc...) intact.

The only thing you'll have to do afterwards is to rebuild any of its indexes, because they will be in UNUSABLE state.

[shailendra]

It changes the Initial parameter but the bytes and blocks parameter are still same, the existing one.


I also want to understand one more thing when I create an object I give the initial parameter correct it means the size of the first extent is equal to the size of initial parameter but why is this different from the bytes parameter can you please explain me this


Thanks
Shailendra

[jmodic]

Originally posted by shailendra
It changes the Initial parameter but the bytes and blocks parameter are still same, the existing one.

What "bytes and blocks" parameters? Which parameters are you talking about?

[pando]

he is using LMT I bet hehe

create table x storage(initial 170k) as select * from emp;

SQL> select segment_name, initial_extent, next_extent from user_segments;
SEGMENT_NAME                                                                      INITIAL_EXTENT NEXT_EXTENT
--------------------------------------------------------------------------------- -------------- -----------
EMPTT                                                                                      65536
RUN_STATS                                                                                  65536
C                                                                                          65536
A                                                                                          65536
X                                                                                         180224

SQL> select segment_name, bytes, blocks from user_extents;

SEGMENT_NAME                                                                           BYTES     BLOCKS
--------------------------------------------------------------------------------- ---------- ----------
EMPTT                                                                                  65536          8
EMPTT                                                                                1048576        128
RUN_STATS                                                                              65536          8
RUN_STATS                                                                              65536          8
C                                                                                      65536          8
A                                                                                      65536          8
X                                                                                      65536          8
X                                                                                      65536          8
X                                                                                      65536          8

[Edited by pando on 08-29-2002 at 05:24 AM]