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

How to prevent a user from login into a postgres database

alter role scott with nologin;
ALTER ROLE

Remember that in postgres, "users" and "roles" are used interchangably, so this would also work:

alter user scott with nologin;
ALTER ROLE

In either case, the postgres server will echo "ALTER ROLE" back to the administrator.

To see the result of such an operation:

echo "\du" | psql

Example output:

                                               List of roles
            Role name            |                         Attributes                         |      Member of
---------------------------------+------------------------------------------------------------+---------------------
 scott                           | Cannot login                                              +| {business_users}
 

You can also query the postgres data dictionary for the answer, like this:

postgres=# select rolcanlogin from pg_roles where rolname='jim';
 rolcanlogin
-------------
 t
(1 row)

postgres=# select rolcanlogin from pg_roles where rolname='scott';
 rolcanlogin
-------------
 f
(1 row)

where t and f indicates true if the user can login and false if the user cannot, respectively.