Category: ORA- ERRORS

ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

Recycle the sequence to start from minimum value by “alter sequence cycle”.

SQL> CREATE SEQUENCE test_seq
     START WITH 1
     INCREMENT BY 1
     MAXVALUE 10; 

Sequence created.
SQL> select test_seq.currval from dual;
 select test_seq.currval from dual
 *
ERROR at line 1:
ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 1

SQL> /

NEXTVAL
----------
 2

...
..
.

NEXTVAL
----------
 9

SQL> /

NEXTVAL
----------
 10

SQL> /
 select test_seq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

1)Increase maximum value:

SQL> alter sequence test_seq maxvalue 15;

Sequence altered.

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 11

..
.

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 14

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 15

SQL> select test_seq.nextval from dual;
 select test_seq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

2)Increment by -1.

SQL> alter sequence test_seq increment by -1;

Sequence altered.

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 14

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 13

...
..
.

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 2

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 1

SQL> select test_seq.nextval from dual;
 select test_seq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence TEST_SEQ.NEXTVAL goes below MINVALUE and cannot be instantiated

3) Recycle the sequence starting from minimum value ( 1 ) by:

alter sequence cycle
SQL>select test_seq.nextval from dual;


NEXTVAL
----------
 15

SQL> /
 select test_seq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated


SQL> select SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,
            INCREMENT_BY,CACHE_SIZE,CYCLE_FLAG 
      from  dba_sequences 
     where  SEQUENCE_NAME='TEST_SEQ';

SEQUENCE_NAM MIN_VALUE  MAX_VALUE  INCREMENT_BY CACHE_SIZE C
------------ ---------- ---------- ------------ ---------- -
TEST_SEQ     1          15         1            20         N

SQL> alter sequence TEST_SEQ cycle cache 10;

Sequence altered.

SQL> select test_seq.nextval from dual ;

NEXTVAL
----------
 1

ORA-01758: table must be empty to add mandatory (NOT NULL) column

Table must be empty to add mandatory (NOT NULL) column.

While adding NOT NULL column into a table, the following error occurred:

SQL> Alter table TEST_TBL add NAME  CHAR(12) NOT NULL;
Alter table TEST_TBL add NAME  CHAR(12) NOT NULL
            *
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

The workarounds could be either of the following:

1) Default value is given:

SQL> Alter table TEST_TBL add NAME  CHAR(12) DEFAULT 'EMPTY' NOT NULL;

2) Remove NOT NULL:

SQL> Alter TEST_TBL add NAME CHAR(12);

3) Backup the table to another staging table, truncate the table and then add the NOT NULL column, insert the records back to the table from the staging table.

ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group

Before dropping a temporary tablespace, make sure it is not a default temporary tablespace.

Trying to drop a temporary tablespace which belongs to a default temporary tablespace group.

SQL> select * from DBA_TABLESPACE_GROUPS;

GROUP_NAME          TABLESPACE_NAME
------------------- ------------------------------
TEMP_OLTP           TEMP
TEMP_OLTP           TEMP2
TEMP_OLTP           TEMP3

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group


SQL> DROP TABLESPACE temp3 INCLUDING CONTENTS AND DATAFILES;
 DROP TABLESPACE temp3 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group

Change default temporary tablespace from  temporary tablespace group TEMP_OLTP to a temporary tablespace TEMP.:

SQL> alter pluggable database default temporary tablespace temp;

Pluggable database altered.

Now drop temporary tablespaces:

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> DROP TABLESPACE temp3 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> select * from DBA_TABLESPACE_GROUPS;

GROUP_NAME      TABLESPACE_NAME
--------------- -----------------
TEMP_OLTP        TEMPA

Make temporary tablespace group as default again:

SQL> alter pluggable database default temporary tablespace TEMP_OLTP;

Pluggable database altered.

ORA-01105 ORA-19808 When Start Up RAC Instance

When tries to start up one instance of the RAC , the following errors occur.

.....
....
ORA-01105: mount is incompatible with mounts by other instances
ORA-19808: recovery destination parameter mismatch
...

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

“ORA-12505, TNS:listener does not currently know of SID given in connect descriptor” from ORMB ouafDatabasePatch

ouafDatabasePatch.cmd requires Instance Name ( SID ) instead of Service Name.

SYMPTOMS

When  installing Rollup Pack for Oracle Utilities Application Framework Version 4.3.0.1.0, ran ouafDatabasePatch.cmd command , and got the following errors:

D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>set TOOLSBIN=D:\dbpatch_tools\bin
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>set JAVA_HOME=D:\java
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>ouafDatabasePatch.cmd
"CMDLINE::: D:\java\bin\java.exe -cp D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\dbpatch_tools\lib\*;D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\dbpatch_tools\config com.oracle.ouaf.database.patch.OUAFPatch"

Enter the target database type (O/M/D) [O]: O
Enter the username that owns the schema: CISADM
Enter the password for the CISADM user: XXXXXX
Enter the name of the Oracle Database Connection String: ractest-scan.ractest.local:1521:ORMB

Couldn't connect to database ORACLE ractest-scan.ractest.local:1521:ORMB CISADM : java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

In connection string “ractest-scan.ractest.local:1521:ORMB”, ORMB is a service name instead of instance name.

SOLUTION

1)Apply patch 22505470: PATCHES APPEND EXTRA SPACE TO STRINGS AND ADD DBSERVICE CONNECTION SUPPORT.

2) Replace D:\dbpatch_tools\lib with new ones.

OR

2) Add below parameter into LISTENER.ora , and bounce/reload the listener.

USE_SID_AS_SERVICE_listener=on