DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01524 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01524: cannot create rollback segment - public rollback segment exists

ORA-01524 is raised when you attempt to create a rollback segment using a name that is already registered as a public rollback segment in the Oracle data dictionary. This error is most commonly encountered in legacy Oracle environments (pre-10g) where DBAs manage rollback segments manually, or in RAC configurations during segment reconfiguration. Since Oracle 10g, Automatic Undo Management (AUM) largely eliminates this class of errors, but many production systems still run in manual undo mode.

Top 3 Causes

1. A Public Rollback Segment with the Same Name Already Exists

The most common cause: a public rollback segment was previously created and was not dropped before a new creation attempt was made.

-- Check existing rollback segments before creating a new one
SELECT segment_name,
       owner,
       tablespace_name,
       status
FROM   dba_rollback_segs
WHERE  owner = 'PUBLIC'
ORDER  BY segment_name;
Enter fullscreen mode Exit fullscreen mode

If your target name already appears in this result, you must drop it first.

2. Incomplete DROP Left a Ghost Entry in the Data Dictionary

If a previous DROP ROLLBACK SEGMENT command failed or the segment was left in NEEDS RECOVERY or PARTLY AVAILABLE status, the data dictionary still holds the segment name, causing any new creation attempt with that name to fail.

-- Identify rollback segments in problematic states
SELECT segment_name,
       status,
       tablespace_name
FROM   dba_rollback_segs
WHERE  status NOT IN ('ONLINE', 'OFFLINE')
AND    owner = 'PUBLIC';
Enter fullscreen mode Exit fullscreen mode

3. RAC Environment Conflict Between Instances

In Oracle RAC, one instance may have already registered a public rollback segment that another instance is trying to recreate independently during a failover or maintenance procedure.

-- Check which instance owns active rollback segment transactions
SELECT s.inst_id,
       r.segment_name,
       t.status,
       t.used_ublk
FROM   gv$transaction t,
       gv$session     s,
       dba_rollback_segs r
WHERE  s.taddr   = t.addr
AND    t.xidusn  = r.segment_id
AND    s.inst_id = t.inst_id;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 – Take the existing segment offline and drop it:

-- Offline the conflicting rollback segment
ALTER ROLLBACK SEGMENT rbs_public_01 OFFLINE;

-- Drop it
DROP PUBLIC ROLLBACK SEGMENT rbs_public_01;
Enter fullscreen mode Exit fullscreen mode

Step 2 – Recreate the rollback segment:

-- Recreate with proper storage settings
CREATE PUBLIC ROLLBACK SEGMENT rbs_public_01
    TABLESPACE rbs_tbs
    STORAGE (
        INITIAL    1M
        NEXT       1M
        MINEXTENTS 2
        MAXEXTENTS UNLIMITED
        OPTIMAL    4M
    );

-- Bring it online
ALTER ROLLBACK SEGMENT rbs_public_01 ONLINE;
Enter fullscreen mode Exit fullscreen mode

Step 3 – Handle NEEDS RECOVERY state (if applicable):

-- Add to offline rollback segments parameter and restart
ALTER SYSTEM SET "_OFFLINE_ROLLBACK_SEGMENTS" = 'RBS_PUBLIC_01'
    SCOPE=SPFILE;
-- Restart the database, then drop the segment
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Always query DBA_ROLLBACK_SEGS before creating a new rollback segment. Make this a mandatory step in your runbook. A simple pre-check script prevents this error entirely: 
-- Pre-creation check
SELECT COUNT(*) AS already_exists
FROM   dba_rollback_segs
WHERE  segment_name = UPPER('RBS_PUBLIC_01')
AND    owner        = 'PUBLIC';
-- Proceed only if result is 0
Enter fullscreen mode Exit fullscreen mode
  1. Migrate to Automatic Undo Management (AUM) on Oracle 10g+. AUM completely removes the need to manually manage rollback segments and eliminates this category of errors: 
-- Verify current undo mode
SHOW PARAMETER UNDO_MANAGEMENT;

-- Switch to AUM
ALTER SYSTEM SET UNDO_MANAGEMENT  = 'AUTO'     SCOPE=SPFILE;
ALTER SYSTEM SET UNDO_TABLESPACE  = 'UNDOTBS1' SCOPE=SPFILE;
-- Restart database to apply
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-01534 Rollback segment does not exist
ORA-01545 Specified rollback segment not available
ORA-30036 Unable to extend undo segment in undo tablespace
ORA-01555 Snapshot too old – related to insufficient undo retention

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)