ORA-1650

From: Charlie Mengler <charliem_at_mwh.com>
Date: Wed, 14 Jun 2000 10:53:45 -0700
Message-Id: <10528.109272@fatcity.com>

FWIW - This is a V8.1.6 instance on Solaris V2.6 with block size of 16384. Frequently on this "development" data warehouse instance we get an ORA-1650 errors. The total disk space for the single ROLLBACK tablespace is about 1.3GB. There are only TWO developers working in this instance & rarely do they have more than a single task/job/query/update going concurrently. All the RBS are sized/configured the same as R05 as shown below.

By my calculations R05 should be able to grow to over 1GB, but the HWM shows it never got larger than 134MB. So why the ORA-1650?

I dislike the idea of just making the tablespace larger. What can I do/run to get more visibility in what is happening when the ORA-1650 occurs?

STAGE.ROLLING_CUST_PRD_METRICS.PRD = 20005 DELETE FROM DW.SS_CUST_PRD_METRICS
*
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 9 ORA-01650: unable to extend rollback segment R05 by 8 in tablespace ROLLBACK

BCM: EDWDEV.WORLD>select name, hwmsize
2 from v$rollname rn, v$rollstat rs
3 where rn.usn = rs.usn;

NAME                              HWMSIZE
------------------------------ ----------
SYSTEM                             638976
R01                             102645760
R02                              51462144
R03                             145047552
R04                             245825536
R05                             134266880
R06                             102055936
R07                             147161088
R08                             149159936

9 rows selected.

1 SELECT * FROM DBA_ROLLBACK_SEGS
2* WHERE SEGMENT_NAME = 'R05'
BCM: EDWDEV.WORLD>/

SEGMENT_NAME                   OWNER  TABLESPACE_NAME                SEGMENT_ID
------------------------------ ------ ------------------------------ ----------

FILE_ID BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS ---------- ---------- -------------- ----------- ----------- ----------- PCT_INCREASE STATUS INSTANCE_NUM

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

RELATIVE_FNO

R05                            SYS    ROLLBACK                                9
        47       4842         131072      131072         121        8192

Received on Wed Jun 14 2000 - 12:53:45 CDT