ORA-01650: (long!)

From: Charlie Mengler <charliem_at_mwh.com>
Date: Mon, 07 Aug 2000 07:35:20 -0700
Message-Id: <10582.113970@fatcity.com>

I'm somewhat at a loss & could use a fresh pair of eyes & a new brain to analyze this situation.

On EDW the SALES_FACT table has about 19703669 rows and of those 2826908 are chained rows. I've ported the same PL/SQL procedure that is used on PROD & CAN that successfully unchains rows to the DSS system to use on SALES_FACT table. In summary the goal is to unchain about 15% of the rows from a table that is about 2GB in size as a single transaction while using a tablespace for rollback segments that is 6GB in size.

As a refresher, this procedure creates a "temporary table", disables all constraints & triggers, inserts the chained rows into the "temp" table, deleted the chain rows from the original table, & then (re)inserts the rows from the "temp" table back into the original table. It does all this as a single transaction.

The original tablespace holding the rollback segments started out at 500MB. It is currently at 6GB! HOWEVER the High Water Mark (HWM) within this tablespace shows the highest value at around 1.8GB & the sum is less than the total available. For both of these reasons I don't understand how/why this error is (still) being generated.

Any ideas, suggestions or feedback would be appreciated. I'm asking for input because I am NOT convinced that adding even more space to the RBS tablespace will prevent these errors from occurring.

SQL> 9 rows selected.
SQL> select sum(HWMSIZE) from v$rollstat;

SUM(HWMSIZE)

5703811072

SQL> select usn, HWMSIZE from v$rollstat;

USN HWMSIZE
---------- ----------

         0     638976
         1   93372416
         2 1887617024
         6  203472896
         7  203931648
         8   39632896
        14 1356546048
        15 1717420032
        16  201179136

9 rows selected.

Oracle Home wrote:
>
> ORA-1650: unable to extend rollback segment RBS02 by 16 in tablespace ROLLBACK1
> ORA-12012: error on auto execute of job 3
> ORA-01562: failed to extend rollback segment number 2
> ORA-01650: unable to extend rollback segment RBS02 by 16 in tablespace ROLLBACK1
>
> tantalus:EDW 4 errors

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
CLUSTER_NAME                   IOT_NAME                         PCT_FREE
------------------------------ ------------------------------ ----------

PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS ---------- ---------- ---------- -------------- ----------- ----------- MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B NUM_ROWS BLOCKS ----------- ------------ ---------- --------------- --- - ---------- ---------- EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS

------------ ---------- ---------- ----------- -------------------------
NUM_FREELIST_BLOCKS DEGREE     INSTANCES  CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL
------------------- ---------- ---------- ----- -------- ----------- ---------
PAR IOT_TYPE     T S NES BUFFER_ ROW_MOVE GLO USE DURATION        SKIP_COR MON
--- ------------ - - --- ------- -------- --- --- --------------- -------- ---
SALES_FACT                     DW_SALES_FACT
                                                                       5
        40          1        255      377487360    20971520           1
        511            0          1               1 YES N   19703669     125124
         315        193    2826908          99                     10549
                  1          1          1     N ENABLED           30 06-AUG-00
NO               N N NO  DEFAULT DISABLED NO  NO                  DISABLED NO


-- 
Charlie Mengler                               Maintenance Warehouse  
charliem_at_mwh.com                              10641 Scripps Summit Ct   
858-831-2229                                  San Diego, CA 92131

Received on Mon Aug 07 2000 - 09:35:20 CDT