Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Question Unanswered: How to Retrieve Oracle table statistics prior to a reorg?

    Hello, I am working with Oracle in an SAP enviroment and I am about to start a bunch of table reorgs to claim alot of unused space created after a huge archiving project. SAP has a DB utility called SAPDBA that provides a great output that displays exactly what I need to determine which tables will have the greatest impact for a reorg. However with the SAPDBA utility I must run the analyze for each table one at a time. I have been trying to figure out how to create the same sort of report via sql so I can script the anaylze for the over 600 tables.

    I have been playing with dba_tables and dba_segments views but cannot get the output similiar to SAPDBA.

    Here is the result if a SAPDBA table analyze.


    TABLE_NAME : S260BIW1

    TOTAL SPACE : 1495048 K
    FREE WITHIN TABLE : 82528 K
    USED WITHIN TABLE : 1412520 K

    HAVE BEEN USED IN TABLE : 1482376 K
    NEVER BEEN USED IN TABLE : 12680 K
    AVERAGE BLOCK FREE SPACE : 19 %
    AVERAGE ROW LENGTH : 618 byte

    CHAINED ROWS : 0
    NUMBER_OF_ROWS : 1942185

    ANALYSIS METHOD : COMPUTE STATISTICS

    Can someone tell me how to report similar results using sql. All i really need is Total space and Used space for each table using a compute method.

    Thank you much..
    Mez

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Here's a method ... SQL Script ... At the top, enter your schema name
    and table_name... It will produce you the size of the table, and the
    used size of the table ...

    HTH
    Gregg
    Attached Files Attached Files

  3. #3
    Join Date
    Nov 2003
    Posts
    4
    Thanks for the reply. That is close to what I was looking for but it still only reports the space that was never used. I am not sure how it does it but SAPDBA reports the space never used plus the space free within the table.


    Here is the SAPDBA result.

    TABLE_NAME : COSB

    TOTAL SPACE : 1409912 K
    FREE WITHIN TABLE : 380024 K
    USED WITHIN TABLE : 1029888 K

    HAVE BEEN USED IN TABLE : 1391480 K
    NEVER BEEN USED IN TABLE : 18440 K
    AVERAGE BLOCK FREE SPACE : 36 %
    AVERAGE ROW LENGTH : 365 byte

    CHAINED ROWS : 10768
    NUMBER_OF_ROWS : 2435661

    ANALYSIS METHOD : COMPUTE STATISTICS


    Here is the result of the table_free_space.sql script.

    Object Name: COSB - TABLE Tablespace:
    .
    Number of Extents: Initial: Next:
    ......Approx Rows: Avg Row Len:
    Blocks in segment: 176240
    Bytes in segment(Size): 1443758080
    MegaBytes in segment: 1376.875
    Blocks not used: 2305
    Bytes not used: 18882560
    MegaBytes not used: 18.0078125
    File ID - last extent with data(Block): 397
    File ID - last extent with data(Byte): 204952
    Last block with data: 10495

    PL/SQL procedure successfully completed.

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    It must be reporting the space above the high water mark ...

    Gregg

  5. #5
    Join Date
    Nov 2003
    Posts
    4
    Thank you again Gregg..

    Can that be scripted to report via sql?

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    yes ... you can spool to an output file ...

    Gregg

  7. #7
    Join Date
    Nov 2003
    Posts
    4
    I am sorry but I am missing what you are telling me here. I do not see a "high water mark" reported when i run the @table_free_space.sql script. I cannot get the FREE WITHIN TABLE : 774816K result from the below script. I am sure I must be missing something...


    SQL> @table_free_space.sql
    Object Name: KOCLU - TABLE Tablespace: PSAPCLUD
    .
    Number of Extents: 47 Initial: 158638080 Next: 41943040
    ......Approx Rows: 301433 Avg Row Len: 368
    Blocks in segment: 112805
    Bytes in segment(Size): 924098560
    MegaBytes in segment: 881.2890625
    Blocks not used: 4780
    Bytes not used: 39157760
    MegaBytes not used: 37.34375
    File ID - last extent with data(Block): 300
    File ID - last extent with data(Byte): 245882
    Last block with data: 340

    PL/SQL procedure successfully completed.


    SAPDBA RESULTS AGAIN

    TABLE_NAME : KOCLU

    TOTAL SPACE : 902432 K
    FREE WITHIN TABLE : 774816 K
    USED WITHIN TABLE : 127616 K

    HAVE BEEN USED IN TABLE : 864192 K
    NEVER BEEN USED IN TABLE : 38240 K
    AVERAGE BLOCK FREE SPACE : 86 %
    AVERAGE ROW LENGTH : 368 byte

    CHAINED ROWS : 1683
    NUMBER_OF_ROWS : 301433

    ANALYSIS METHOD : COMPUTE STATISTICS

    Thank you much

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    They may be getting that from the dbms_space.free_blocks Proc.
    You can add that package into the above script if you desire and
    output it in the same manner as I did with the dbms_space proc.

    Gregg

Posting Permissions

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