How to reclaim the waste space using brspace

[Former Member]

Hi All,

I determined Top 50 table which has highest wasted space
using below query

SELECT * FROM

(SELECT

    SUBSTR(TABLE_NAME,
1, 21) TABLE_NAME,

    NUM_ROWS,

       AVG_ROW_LEN
ROWLEN,

      BLOCKS,

     
ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,

       ROUND(BLOCKS *
(8000 - 23 * INI_TRANS) *

          (1 -
PCT_FREE / 100) / 1000000, 0) GROSS_MB,

       ROUND((BLOCKS *
(8000 - 23 * INI_TRANS) * (1 - PCT_FREE / 100) -

         (AVG_ROW_LEN
+ 1) * NUM_ROWS) / 1000000) "WASTED_MB"

     FROM DBA_TABLES

      WHERE

     NUM_ROWS IS NOT
NULL AND

         PARTITIONED = 'NO' AND

       (IOT_TYPE !=
'IOT' OR IOT_TYPE IS NULL)

     ORDER BY 7 DESC)

  WHERE ROWNUM
<=50;

when I used try to reorg the using brspace on the top 50
table but it did not reduce the waste space.

Is it correct way to reorg the table?if any other please let
me know.

[former_member206552]

Hi Dhavan,

Yes i also do the reorgs using BRTOOLS which works well

Just one question once the reorg is done do you update the stats of the table as this is required ?

Best Regards

Marius

[Former Member]

Thanks Burger...after update stats ...It reduced

[former_member206552]

Since the physical attributes of your tables and indexes may have changed, table and index statistics should be re-gathered.

glad to hear, can you please close the call if you are satisfied with the answer

Best Regards

Marius

[Former Member]

Hi all,

I try same process but it did not reduce the waste space.

Please see.

Before Reorg table PCL2.

TABLE_NAME   NUM_ROWS

ROWLEN

BLOCKS

NET_MB   GROSS_MB  WASTED_MB

---------- ---------- ---------- ---------- ---------- ---------- ----------

PCL2

1112590

3041

637958

3384

4580

1196

After reorg table PCL2

TABLE_NAME   NUM_ROWS

ROWLEN

BLOCKS

NET_MB   GROSS_MB  WASTED_MB

---------- ---------- ---------- ---------- ---------- ---------- ----------

PCL2

1112590

3041

637958

3384

4580

1196

After update stats

TABLE_NAME   NUM_ROWS

ROWLEN

BLOCKS

NET_MB   GROSS_MB  WASTED_MB

---------- ---------- ---------- ---------- ---------- ---------- ----------

PCL2

1115780

3048

637954

3402

4580

1178

Please help,

Regads,

Manas.

[former_member206552]

Hi Manas,

please read : http://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm

for that table you can

alter table SAPSR3.PCL2 enable row movement;

alter table SAPSR3.PCL2 shrink space cascade;

alter table SAPSR3.PCL2 disable row movement;

Please see the example below

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SAPSR3','PCL2');

TABLE_NAME                                                        NUM_ROWS     ROWLEN     BLOCKS     NET_MB   GROSS_MB  WASTED_MB

--------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------

REPOLOAD                                                            125868        241      32537         30        234        203

07:39:23 SQL> select count(*) from sapsr3.REPOLOAD;

  COUNT(*)
----------
    126334

07:39:56 SQL> alter table sapsr3.repoload enable row movement;

Table altered.

07:40:13 SQL> alter table sapsr3.repoload shrink space cascade;

Table altered.

07:40:58 SQL> alter table sapsr3.repoload disable row movement;

Table altered.

07:41:53 SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SAPSR3','REPOLOAD');

PL/SQL procedure successfully completed.

TABLE_NAME                                                        NUM_ROWS     ROWLEN     BLOCKS     NET_MB   GROSS_MB  WASTED_MB

--------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------

REPOLOAD                                                            126334       1656      32329        209        232         23

Best Regards

Marius