Confused with Deaalocate Unused !!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Confused with Deaalocate Unused !!

  1. #1
    Join Date
    May 2002
    Posts
    163

    Confused with Deaalocate Unused !!

    Hi Guys,

    I would like to clarify and discuss my doubts based on my testing scenarios as below.

    Object Definition:
    ==================
    1. A cluster table with PCTUSED 70, PCTFREE 1 and FREELIST 98.
    2. Table has a Date column.
    3. New table with no records.

    Test Scenario No. 1:
    ====================
    1. Inserted total 5 Million records based on the above DATE column
    (Asc order, old date first). Committed after every 100 records.
    2. Count total Extent as below

    Segment Name Tablespace Name Extents Bytes
    AA_XXXX_3 AA_XXXXX_TS_1 17 1,782,579,200

    3. Then Deleted 20% of the above records.

    4. Count Total Extent and Size as below (No CHANGE)
    Segment Name Tablespace Name Extents Bytes
    AA_XXXX_3 AA_XXXXX_TS_1 17 1,782,579,200

    5. Then Insert 19% of the above deleted records.

    6. Count the Extent and Size as below. (Extent increased by 1)

    Segment Name Tablespace Name Extents Bytes
    AA_XXXX_3 AA_XXXXX_TS_1 18 1,887,436,800

    7. See the details:

    TOTAL_BLOCKS 230400
    TOTAL_BYTES 1887436800
    UNUSED_BLOCKS 11575
    UNUSED_BYTES 94822400
    LAST_USED_EXTENT_FILE_ID 24
    LAST_USED_EXTENT_BLOCK_ID 256009
    LAST_USED_BLOCK 1225





    Test Scenario No. 2:
    ====================

    1. TRUNCATED THE TABLE.

    2. Inserted total 5 Million records based on the above DATE column
    (Asc order, old date first). Committed after every 100 records.
    3. Count total Extent as below

    Segment Name Tablespace Name Extents Bytes
    AA_XXXX_3 AA_XXXXX_TS_1 17 1,782,579,200

    4. Then Deleted 20% of the above records.

    4. Count Total Extent and Size as below (No CHANGE)
    Segment Name Tablespace Name Extents Bytes
    AA_XXXX_3 AA_XXXXX_TS_1 17 1,782,579,200

    5. Then use the following command: (My understanding is the below command will release the unused space till the HWM)

    ALter cluster dealllocate unused;

    6. Count Total Extent and Size as below (No CHANGE)

    Segment Name Tablespace Name Extents Bytes
    AA_XXXX_3 AA_XXXXX_TS_1 17 1,782,579,200


    7.Then Insert 19% of the above deleted records.

    8. Count the Extent and Size as below. (Extent did not increase)

    Segment Name Tablespace Name Extents
    AA_XXXX_3 AA_XXXXX_TS_1 17




    Question:

    1. What is the impact of the DEALLOCATE UNUSED command here? I did not find any impact though!! Bcoz the extent was same before and after delete and using the command.

    2. Why there is a different in number of extent between TWO (2) test scenario, where I followed the same steps. Beside the DEALLOCATE UNUSED command?


    Appreciate for reading my long thread!!



    Thanks and Regards in advance.
    Nwcomer
    Student

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    1) deallocate unused will bring back your HWM to the last block used
    if all the blocks in an extent is unused then u may see some change in the number of extents.. Try deleting all records of last extent.. then u can see some change..

    2) oracle will pick up and free block for inserts & may be in second case you had too many blocks below that PCTUSED 70 and not in first case where in you were required to allocate new extent.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    May 2002
    Posts
    163
    Thanks.
    You said:
    =========
    try to delete all records from the last extent.

    I said:
    =======

    1. I deleted based on Old Dates. So, I delted the records from the blocks in front of the datafile.

    2. Secondly, if I want to delete the records from the last extent, how to know which are the records are there in the last extent?



    You Said:
    ==========
    May be second time the blocks were free below PCTUSED.

    I Said:
    =======
    Based on my record size, one block can contain 30 -32 records. And I deleted based on date (As i Inserted). So I believe all the time (after delete process) the total block supposed to be free (surely below PCTSUED).


    Regards
    Nwcomer
    Student

  4. #4
    Join Date
    May 2002
    Posts
    163
    Any more answer please!!
    Nwcomer
    Student

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if your tablespaces are LMT then that's expected behaviour

    Code:
    select tablespace_name, initial_extent, next_extent,extent_management from dba_tablespaces;
    
    TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN
    ------------------------------ -------------- ----------- ----------
    SYSTEM                                  65536       65536 DICTIONARY
    UNDOTBS1                                65536             LOCAL
    TEMP                                  1048576     1048576 LOCAL
    DRSYS                                   65536             LOCAL
    EXAMPLE                                131072      131072 LOCAL
    INDX                                   131072      131072 LOCAL
    TOOLS                                   65536             LOCAL
    USERS                                  131072      131072 LOCAL
    XDB                                     65536             LOCAL
    OEM                                    262144      262144 LOCAL
    STATSPACK                              131072      131072 LOCAL
    DMT                                    131072      131072 DICTIONARY
    
    create table a tablespace statspack as select * from dba_objects;
    
    create table b tablespace dmt as select * from dba_objects;
    
    elect segment_name, initial_extent, next_extent, extents from user_segments where segment_name in ('A', 'B');
    
    SEGMENT_NAME                                                                      INITIAL_EXTENT NEXT_EXTENT    EXTENTS
    --------------------------------------------------------------------------------- -------------- ----------- ----------
    A                                                                                         131072      131072          6
    B                                                                                         131072      131072          5
    
    select bytes from user_extents where segment_name = 'A';
    
         BYTES
    ----------
        131072
        131072
        131072
        131072
        131072
        131072
    
    select bytes from user_extents where segment_name = 'B';
    
         BYTES
    ----------
        163840
        163840
        163840
        163840
        163840
    
    alter table a deallocate unused;
    
    select bytes from user_extents where segment_name = 'A';
    
         BYTES
    ----------
        131072
        131072
        131072
        131072
        131072
        131072
    
    alter table b deallocate unused;
    
    select bytes from user_extents where segment_name = 'B';
    
         BYTES
    ----------
        163840
        163840
        163840
        163840
         90112

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width