Category: ORA- ERRORS

“ORA-06533: Subscript beyond count” from DBMS_STATS

DBMS_STATS threw the following errors:

SQL> EXEC DBMS_STATS.gather_table_stats('USERNAME','TABLENAME');
BEGIN DBMS_STATS.gather_table_stats('USERNAME','TABLENAME'); END;

*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at "SYS.DBMS_STATS", line 34634
ORA-06512: at line 1

WORKAROUND

Use a numeric value for estimate_percent instead of default dbms_stats.auto_sample_size:

SQL> exec dbms_stats.gather_table_stats( ownname=>'USERNAME',tabname=>'EVENT',estimate_percent=>100);

PL/SQL procedure successfully completed.

ORA-01450: maximum key length (3215) exceeded

A client is trying to rebuild an index online with the following errors:

SQL> alter index indx_cdr rebuild online;
alter index IDX_CDR rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded



SQL> select max(length( call_message)) from  cdr;

MAX(LENGTH(CALL_MESSAGE))
-------------------------
               3586

WORKAROUND

Build the index without ONLINE option:

SQL> alter index indx_cdr rebuild;

Index altered.

ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> DROP TABLE CDR;
DROP TABLE CDR;
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SOLUTION

Check offending reference(s)

SQL> SELECT  TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
     FROM    DBA_CONSTRAINTS
     WHERE   OWNER = 'TEST'
     AND     CONSTRAINT_TYPE='R'
     AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='CDR');

TABLE_NAME     CONSTRAINT_NAME  C R_CONSTRAINT_NAME   STATUS
-------------- ---------------- - ------------------- --------
TRANSACTION    FK_TRANSACTION   R SYS_C0096900        ENABLED

Drop offecdning reference(s)

By just disabling reference(s), it is still not working.

SQL>  alter table TRANSACTION disable constraint FK_TRANSACTION;

Table altered.

SQL> DROP TABLE CDR;
DROP TABLE CDR;
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

Having to drop offending reference(s), then it is working.

SQL>  alter table TRANSACTION drop constraint FK_TRANSACTION;

Table altered.

SQL> DROP TABLE CDR;

Table dropped.

OR

Drop the table with “CASCADE CONSTRAINTS” option.

SQL> DROP TABLE CDR CASCADE CONSTRAINTS;

Table dropped.

ORA-65025 when Renaming Global_Name in PDB

SQL>select * from global_name;

GLOBAL_NAME
--------------------------------------------------
OLDNAME

SQL>  alter pluggable database rename global_name to NEWNAME;
 alter pluggable database rename global_name to NEWNAME
                                                *
ERROR at line 1:
ORA-65045: pluggable database not in a restricted mode

SOLUTION

Open PDB in restricted mode, and close all other instances in RAC environment.

SQL> alter pluggable database OLDNAME open restricted;

Pluggable database altered.

SQL> alter session set container=OLDNAME;

Session altered.

SQL>alter pluggable database rename global_name to NEWNAME;

Pluggable database altered

SQL>select * from global_name;

GLOBAL_NAME
--------------------------------------------------
NEWNAME

ORA-02085: database link DB_LINK connects to SOURCE_DB

After successfully created the database link called “DB_LINK”, the try to access remote table with the following errors:

SQL>  select  count(*) from remote_user.table_name@db_link;
 select  count(*) from REMOTE_USER.TABLE_NAME@DB_LINK                                                         *
ERROR at line 1:
ORA-02085: database link DB_LINK connects to SOURCE_DB

 $ oerr ora 02085
02085, 00000, "database link %s connects to %s"
// *Cause: a database link connected to a database with a different name.
//  The connection is rejected.
// *Action: create a database link with the same name as the database it
//  connects to, or set global_names=false.
//

SQL>  show parameter global_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
global_names                         boolean     TRUE

SOLUTION

1)Reset parameter global_names to FALSE, and recreate the database link again with any name you want.

SQL> alter system set global_names=false;

SQL> Create public database link DB_link connect to ....

SQL>  select  count(*) from remote_user.table_name@db_link;

  COUNT(*)
----------
      1000

OR

2) Create database link with the same name as source database / PDB GLOBAL_NAME.

SQL> select * from global_name;

GLOBAL_NAME
-----------------------------------------------------------
SOURCE_DB

SQL> Create public database link SOURCE_DB connect to ....

SQL>  select  count(*) from remote_user.table_name@source_db;

  COUNT(*)
----------
      1000

For connecting to PDB, you need be in the PDB to get right GLOBAL_NAME:

SQL> show con_name;

CON_NAME
------------------------------
SOURCEDBPDB

SQL>  select * from global_name;

GLOBAL_NAME
--------------------------------------------
SOURCEDBPDB

SQL>Create public database link SOURCEDBPDB connect to ....

SQL>  select  count(*) from remote_user.table_name@sourcedbpdb;

  COUNT(*)
----------
      1000