ORA-01775: Looping chain of synonyms during repository installation

During installation of a repository for an Oracle tool, the following error occurs

Select distinct mrc_name from schema_version_registry;

Select distinct mrc_name from schema_version_registry
                              *
ERROR on line 1:
ORA-01775: Looping chain of synonyms

Cause: There was only a synonym present in the database, and no underlying base object:

SQL> SELECT owner, SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from DBA_SYNONYMS where table_name='SCHEMA_VERSION_REGISTRY'

OWNER                SYNONYM_NAME               TABLE_OWNER          TABLE_NAME
-------------------- -------------------------- -------------------- ----------------------------------------
PUBLIC               SCHEMA_VERSION_REGISTRY    SYSTEM               SCHEMA_VERSION_REGISTRY

Solution is to drop the public synonym:

SQL> drop public synonym SCHEMA_VERSION_REGISTRY;

Synonym dropped.

Source: ORA-01775 or ORA-980 from Public Synonym when Base Table is Missing (Doc ID 392705.1)

How to import only users over a network link

Use the following parameter file to import only the user definitions from your source database to your test database:
Remember that the database link proddb01 must be correctly defined and working before you import the data.

userid='system/yourpassword
network_link=proddb1.mydomain.com
logfile=DATA_PUMP_DIR:user_definitions.log
full=Y
include=USER

To import a specific user only:

INCLUDE=USER:"='SCOTT'"

To include a specific user and remap it to another user name during import:

REMAP_SCHEMA=SCOTT:JIM
INCLUDE=USER:"='SCOTT'"

Remember to check if the SCOTT user is assigned a default tablespace that doesn't exist in the target database.
If this is the case, you need to use remap_schema directive to make it work:

REMAP_TABLESPACE=SCOTT_DATA:JIM_DATA

... or potentiall pre-create the tablespace SCOTT_DATA in the target database, but this may not be what you wish.

How to create a startup-trigger

In this example, I needed to write a simple trigger that opens the wallet upon startup of the instance:

create or replace trigger
   open_wallet
after startup on database
begin
   execute immediate 'ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "mysecretpassword"';
end;
/

How to solve ORA-01031: insufficient privileges when creating a materialized view

At first, I got the following error from the Oracle server when exeuting the "CREATE MATERIALIZED VIEW" script:

where    tab2.year >   to_number(to_char(sysdate, 'YYYY')) - 4
                                       *
ERROR at line 36:
ORA-01031: insufficient privileges

Solution:
If you try to create a materialized view based on tables in a different schema, you need the privilege

GLOBAL QUERY REWRITE

as well as

CREATE TABLE
CREATE MATERIALIZED VIEW

Grant the privileges:

grant GLOBAL QUERY REWRITE to scott;
grant CREATE TABLE to scott;
grant CREATE MATERIALIZED VIEW to scott;

Verify your users privileges:

connect scott/tiger

select * from user_sys_privs;


USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
EKSTERN                        CREATE SESSION                           NO
EKSTERN                        CREATE TABLE                             NO
EKSTERN                        CREATE MATERIALIZED VIEW                 NO
EKSTERN                        GLOBAL QUERY REWRITE                     NO

This should fix your ORA-01031 problems.

Source: Oracle Support "Create Local Materialized View with Query Rewrite Option Fails with ORA-1031 Insufficient Privileges" (Doc ID 1079983.6)

How to redefine a table that is created with an identity column

You have the following non-partitioned table defined:

CREATE TABLE MYTABLE
(
  ENTRY_SEQ_NUM        NUMBER Generated as Identity ( START WITH 1180965539 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP) NOT NULL,
  ENTRY_ID             VARCHAR2(50 BYTE)            NULL,
  DOC_NAME             VARCHAR2(100 BYTE)           NULL,
  CREATED              TIMESTAMP(6)                 NULL,
  REVISION#            INTEGER                      NULL,
  APPLICABLE_YEAR      INTEGER                      NULL,
  PERIOD               VARCHAR2(10 BYTE)            NULL,
  DOCUMENT_STATUS      VARCHAR2(50 BYTE)            NULL,
  PHONE#               VARCHAR2(11 BYTE)            NULL
);

The table is growing, and your customer want it partitioned.

Solution:

Create an interim table. Make sure to use a datatype of NUMBER for the column ENTRY_SEQ_NUM, instead of the Identity column:

CREATE TABLE MYTABLE_INTERIM
(
  ENTRY_SEQ_NUM        NUMBER,
  ENTRY_ID             VARCHAR2(50 BYTE),
  DOC_NAME             VARCHAR2(100 BYTE)           NULL,
  CREATED              TIMESTAMP(6)                 NULL,
  REVISION#            INTEGER                      NULL,
  APPLICABLE_YEAR      INTEGER                      NULL,
  PERIOD               VARCHAR2(10 BYTE)            NULL,
  DOCUMENT_STATUS      VARCHAR2(50 BYTE)            NULL,
  PHONE#               VARCHAR2(11 BYTE)            NULL
)
PARTITION BY RANGE (PERIOD)
INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))
(
  PARTITION P_INIT VALUES LESS THAN (TO_DATE('2013-01', 'YYYY-MM'))
)
TABLESPACE USERS
;

Start redefinition. Note that you need to use the TO_DATE function on the partition key column, together with proper masking of the date format string:

begin
DBMS_REDEFINITION.start_redef_table(uname=>'SCOTT',
orig_table=>'MYTABLE',
int_table=>'MYTABLE_INTERIM',
col_mapping =>'ENTRY_SEQ_NUM ENTRY_SEQ_NUM,ENTRY_ID ENTRY_ID,DOC_NAME DOC_NAME,CREATED CREATED,REVSION# REVSION#,APPLICABLE_YEAR APPLICABLE_YEAR,TO_DATE(PERIOD,''YYYY-MM'') PERIOD,DOCUMENT_STATUS DOCUMENT_STATUS,PHONE# PHONE#',
options_flag=>dbms_redefinition.cons_use_pk);
end;
/

Finish the redefinition:

begin
 DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>'SCOTT',orig_table=>'MYTABLE',int_table=>'MYTABLE_INTERIM);
end;
/

Add a new column, of type "Identity":

alter session force parallel ddl;
alter session force parallel dml;
alter table mytable
add ENTRY_SEQ_NUM_X NUMBER Generated as Identity ( START WITH 1
                                                   MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20
                                                   NOORDER NOKEEP);

Drop the old column:

alter table mytable drop column ENTRY_SEQ_NUM;

Rename the Identity column back to the name of the column you just dropped:

alter table mytable rename column ENTRY_SEQ_NUM_X to ENTRY_SEQ_NUM;

The syntax for enabling and disabling a constraint

To enable a constraint:

ALTER TABLE mytable ENABLE CONSTRAINT mytable_c1;

or, if you wish to enable the constraint and validate its correctness at the same time:

ALTER TABLE mytable MODIFY CONSTRAINT mytable_c1 ENABLE VALIDATE;

Note that the following syntax is invalid and will throw ORA-00933: SQL command not properly ended:

ALTER TABLE mytable ENABLE CONSTRAINT mytable_c1 VALIDATE;

To disable a constraint:

ALTER TABLE mytable DISABLE CONSTRAINT mytable_c1;

or

ALTER TABLE mytable MODIFY CONSTRAINT mytable_c1 DISABLE;

How to add a check constraint to a table using ALTER TABLE

The following statement adds a check constraint to the table and allows Oracle to give it a system generated name:

ALTER TABLE MYTABLE ADD 
 CHECK (COL1 IS NOT NULL)
 ENABLE
 VALIDATE;

Note that when you execute the following DDL, Oracle will throw ORA-02438: Column check constraint cannot reference other columns:

ALTER TABLE MYTABLE ADD
  CONSTRAINT CHECK (COL1 IS NOT NULL)
  ENABLE VALIDATE;

The above error only happens when you let Oracle give the constraint a system generated name.


If you are explisitly naming your constraint, the use of the CONSTRAINT keyword will work:

ALTER TABLE MYTABLE ADD
  CONSTRAINT mytable_cc2 CHECK (COL1 IS NOT NULL)
  ENABLE VALIDATE;