My Oracle Support Banner

Drop Partition In A Partitioned Table failing with ORA-08181: Specified Number Is Not A Valid System (Doc ID 2745424.1)

Last updated on FEBRUARY 01, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

On : 19.0.0.0 version, RDBMS

Unable to drop partition in a partitioned table (ORA-08181: specified number is not a valid system)

When trying to remove specific partitions on a partitioned table, we get following error.

It's not happening when dropping a partition in a different table on same database.

ALTER TABLE <owner>.<table_name> DROP PARTITION <partition_name> UPDATE INDEXES;
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number


ERROR
-----------------------
ORA-08181

No Flashback data archive enabled on table.

Affected table has MView which has not refreshed for long time.

MView refresh created with commit SCN option

Changes

 *** 2021-01-13 14:52:45.233
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-08181: specified number is not a valid system change number
----- Current SQL Statement for this session (sql_id=1mgtp89n2b83c) -----
select 1 from (select * from "<owner>"."MLOG$<table_name>" as of snapshot (:1) where xid$$ not in (select xid from sys.snap_xcmt$ as of snapshot(:1)))  where rownum = 1

-
    ksedst <- dbkedDefDump <- ksedmp <- dbkdaKsdActDriver <- dbgdaExecuteAction
       <- dbgdaRunAction <- dbgdRunActions <- dbgdProcessEventActions <- dbgdChkEventKgErr <- dbkdChkEventRdbmsErr
        <- ksfpec <- dbgePostErrorKGE <- dbkePostKGE_kgsf <- kgeade <- kgeselv
         <- ksesecl0 <- ktf_find_mapping_impl <- ktfcsnp <- qertbStart <- qerstStart
          <- rwsstd <- qerstStart <- qercoStart <- qerstStart <- selexe0
           <- opiexe <- opiefn0 <- opiefn <- opiall0 <- opikpr
            <- opiodr <- rpidrus <- skgmstack <- rpidru <- rpiswu2
             <- kprball <- kkzlclgisics <- kkzlpslg0 <- atbdrv <- opiexe
              <- opiosq0 <- kpooprx <- kpoal8 <- opiodr <- ttcpip
               <- opitsk <- opiino <- opiodr <- opidrv <- sou2o
                <- opimai_real <- ssthrdmain <- main <- start
-
Current cursor: 5, pgadep=1

Open cursors(pls, sys, hwm, max): 6(0, 5, 64, 1000)
NULL=1 SYNTAX=0 PARSE=0 BOUND=4 FETCH=0 ROW=1
Cached frame pages(total, free):
4k(20, 11), 8k(1, 1), 16k(0, 0), 32k(0, 0)
-
-
============
Plan Table
============
---------------------------------------------------------+-----------------------------------+
| Id  | Operation            | Name                      | Rows  | Bytes | Cost  | Time      |
---------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT     |                           |       |       |       |           |
| 1   |  COUNT STOPKEY       |                           |       |       |       |           |
| 2   |   FILTER             |                           |       |       |       |           |
| 3   |    TABLE ACCESS FULL | MLOG$<tablename>|       |       |       |           |
| 4   |    TABLE ACCESS FULL | SNAP_XCMT$                |       |       |       |           |
---------------------------------------------------------+-----------------------------------+

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.