“ORA-01031: insufficient privileges” from SYS User Creating Materialized Views

A client try to create a materialized view by SYS user with the following errors:

SQL> show user
USER is "SYS"

SQL> create materialized view USER1.MV_TABLE2
BUILD IMMEDIATE
REFRESH FAST ON commit
with primary key
as select * from USER2.TABLE2;


ERROR at line 6:
ORA-01031: insufficient privileges

SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

“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 pluggable database rename global_name to NEWNAME;

Pluggable database altered

SQL>select * from global_name;

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