How to correct an identity column sequence after import

Thanks to the authors behind Grassroots-oracle for sharing this trick.

I finished an import of a table with an identity column, and for some reason, the sequence started to hand out number at the bottom of the range, although it had reached 10632546 in the source table.

My table's DDL:

CREATE TABLE MYTABLE
(
  MYID                   NUMBER GENERATED ALWAYS AS IDENTITY ( START WITH 21 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP NOSCALE) NOT NULL,
  EVENT_TYPE             VARCHAR2(75 BYTE)      NOT NULL,
  SOURCE                 VARCHAR2(50 BYTE)      NOT NULL,
  FORMAT                 VARCHAR2(75 BYTE)      NOT NULL,
  EVKEY                  VARCHAR2(60 BYTE)      NOT NULL,
  TITLE                  VARCHAR2(40 BYTE)      NOT NULL,
  TIMESTAMP              TIMESTAMP(6)           DEFAULT current_timestamp,
  DOCUMENT               CLOB                   NOT NULL,
  FILEREF                VARCHAR2(200 BYTE)     DEFAULT 'some text' NOT NULL,
  TRANNUM                NUMBER
)
LOB (DOCUMENT) STORE AS SECUREFILE (
  TABLESPACE  LOB_DATA
)
TABLESPACE DATA;

After import, the developers tested an insert and the row was inserted with MYID set to 1.

There is no Primary Key on the table, which I pointed out to the developers.

The sequence name was ISEQ$$_88174, and the query below returned the number 2 after import in the destination database:

select "ISEQ$$_88174".nextval
from dual;

To "start where the sequence left off" in the source database, all I had to do was to execute:

ALTER TABLE MYTABLE
MODIFY (MYID  GENERATED ALWAYS AS IDENTITY ( START WITH LIMIT VALUE) );

If I queried the sequence's next value now, it had changed from 2 to 10632547.

I then checked the maximum value of the column:

select max(MYID) from MYTABLE;

And it returned 10632546, in other words, the next value would be the current max value + 1.

Getting ORA-01031: insufficient privileges when creating a view that is refering to another schema's table

If you get PRA-0131 during creation of a view refering to a schema object in a different table, try the following:

grant select on foreing_schema.foreign_table to scott with grant option;

Listener output showing multiple instances when only one exists

In one of my multitenant setups, I saw the following output from the listener:

lsnrctl status cdb

Service "sales" has 2 instance(s).
  Instance "cdb", status READY, has 2 handler(s) for this service...
  Instance "sales", status UNKNOWN, has 1 handler(s) for this service...

There is only one instance in my setup, the container database called "cdb".
What's causing the listener to view the pdb "sales" as a separate instance, then?

See this post where Uwe Hesse explains:

It is nothing to worry about when you see the same instance showing up twice, once with status READY and once with status UNKNOWN. READY means listener knows this because PMON of that instance told him so (dynamic registration) UNKNOWN means listener knows this because of SID_LIST entry in the listener.ora (static registration)

Quite correctly, I had a static entry in my listener.ora file:

SID_LIST_cdb =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /u01/orasoft/product/19c)
      (SID_NAME = sales)
    )
  )

If I remove the following part

(SID_NAME = sales)

and restart the listener, the extra instance will be removed from the listener output:

lsnrctl stop cdb
lsnrctl start cdb

Output:

Service "sales" has 1 instance(s).
  Instance "cdb", status READY, has 2 handler(s) for this service...

Workaround for error ERROR: OCI Error DPI (status = 7-DPI-007: invalid OCI handle or descriptor) when starting Golden Gate replicate process

When trying to login to the golden gate cli interface, I got the following error:

GGSCI (myserver1.oric.no) 7> dblogin useridalias mydb  domain admin
ERROR: OCI Error DPI (status = 7-DPI-007: invalid OCI handle or descriptor).

Cause:

Incorrectly specified network configuration:
sqlnet.ora

names.default_domain = world

tnsnames.ora

mydb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver1.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb)
    )
  )

To correct the error, you need to do either of the following:

1. remove the names.default_domain from sqlnet.ora

or

2. add an alias to your tnsnames.ora file

mydb.world =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver1.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb)
    )
  )

How to clear resolved errors from PDB_PLUG_IN_VIOLATIONS

The query below shows 4 resolved messages in the view PDB_PLUG_IN_VIOLATIONS:

SELECT NAME,CAUSE,TYPE,MESSAGE,STATUS
FROM PDB_PLUG_IN_VIOLATIONS
WHERE NAME='PDB$SEED'

NAME	CAUSE	TYPE	MESSAGE	STATUS
PDB$SEED	SQL Patch	ERROR	Interim patch 31424070/24854845 (APPSST19C XTTS PDB - TABLE IMPORT/CREATION FAILED WITH ORA-39083 ORA-14334): Installed in the CDB but not in the PDB	RESOLVED
PDB$SEED	SQL Patch	ERROR	Interim patch 34086870/24803071 (OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870)): Not installed in the CDB but installed in the PDB	RESOLVED
PDB$SEED	SQL Patch	ERROR	'19.16.0.0.0 Release_Update 2207030222' is installed in the CDB but no release updates are installed in the PDB	RESOLVED
PDB$SEED	SQL patch error	ERROR	Interim patch 34086870/24803071 (OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870)): ROLLBACK with status INITIALIZE in the CDB.	RESOLVED

The resolved messages can be easily removed with the procedure DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS:

SYS@CDB$ROOT SQL> exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS( pdb_name => 'PDB$SEED');

Useful resources:

Mike Dietrich on the same topic

The Oracle 19c Documentation for the PDB_PLUG_IN_VIOLATIONS view

Note that there is an Unpublished Bug 16192980 : NO SIMPLE WAY TO CLEAN ROWS FROM PDB_PLUG_IN_VIOLATIONS AFTER DBMS_PDB CALL that prevents you from discarding no-impact warnings.
According to Oracle there should be a fix for this in version 19.10, but I still strugle to remove messages like

  Database option DV mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.
  

which can be ignored. Read more about Database option mismatch errors in Doc ID 2020172.1

Rename schema in PostgreSQL: syntax

\connect db01
alter schema schema1 rename to schema1_old;

Documented here

Rename database in PostgreSQL: syntax

alter database db01 rename to db01;

Documented here

See this post for how to create a new database in PostgreSQL