Oracle 18c Private Temporary Tables

Oracle introduced a new feature in 18c release called “Private Temporary Tables”, This feature will essentially enable you to create a temporary memory based table, and this table will ONLY be viewed by the database session created this table. Moreover, here I will compare it with another old Oracle feature “Global Temporary Tables”.


The parameter for the predefined naming is PRIVATE_TEMP_TABLE_PREFIX



To simulate an example of creating a temporary private table:

SQL> alter session set container=ORCLPDB1;

Create a local database user in the pluggable database:


SQL> grant create session to hr;

SQL> grant resource to hr;

SQL> alter user hr quota unlimited on USERS;

SQL> alter user hr default role all;

Connect to the pluggable database as hr user and execute the following to create a private temporary table:


(time_id      DATE,

quantity_of_sale  NUMBER(10,2))



I will execute the following queries to insert a record in the temporary table:

SQL> insert into ORA$PTT_sales_records values(sysdate,1000);

SQL> commit;


If I open another session with the same database account, I can’t query the newly created private temporary table as shown below:


the following are the database views:




Its worth mentioning that if you attempt to create a private temporary table using SYS account, the following error will be thrown:

ORA-14451: unsupported feature with temporary table





















Oracle 19c Easy Connect Plus

In Oracle Database 19c a new feature is introduced called “Easy Connect Plus” that will enhance connection string syntax.

Easy Connect Plus will make it easier to use features such as TLS connections, wallets, load balancing, connection timeouts, and to tune network buffer sizes without external configuration.

The following is an example of the traditional easy connect syntax against a pluggable database:

SQL> connect emad/

Where port number is “optional” in the command syntax, in my example its 1521

The following is Easy Connect syntax structure that is available in Oracle Database drivers (JDBC,

ODP.Net, cx_Oracle, node-oracledb etc) that use Oracle Client 19c and connect to Oracle Database 11.2 or later.






Protocol: transport protocol to be used while connecting to the database

host. In 19c, the supported values of protocol are TCP and TCPS. The default is TCP.


Hosts: The host list is a comma-separated list of host names or IP addresses which are used to connect to a database.

Ports: Port numbers indicate the ports that the database services are listening on.

When using multiple hosts, if the same port is used on each host, then you can use:

host1,host2:port Otherwise you can indicate different ports should be used:


Service Name:The service name is the service the database is known by. It is a name comprised of the database name and domain name, entered during installation or database creation.Service names can be found by running “lsnrctl services” on the database host.Note that database “system identifiers“ (SIDs) cannot be used in Easy Connect or Easy Connect Plus syntax.


Server Type: The server type specifies what kind of server is used on the database host to handle the connection. It can be one of DEDICATED, SHARED, or POOLED.

Instance Name: to connect to a specific instance that the service represents, you can use an instance name.

Parameters: Parameters are name-value pairs that control the behavior of connections. The syntax uses ‘?’ to indicate start of parameters and a ‘&’ delimiter between each parameter. Leading and trailing white spaces are ignored within parameter values. If whitespace is required as part of the value, it should be placed within double quotes. Parameters are described in the next section.

Examples of 19c Easy Connect Plus Syntax:

sqlplus c##emad/emad_123@tcp://oracle-19c-vagrant:1521/ORCLCDB

sqlplus c##emad/emad_123@[fe80::a00:27ff:fed0:4f43]:1521/ORCLCDB

sqlplus c##emad/emad_123@tcps://oracle-19c-vagrant:1521/ORCLCDB


Reference Link:

Oracle 19c RMAN Recovery Catalog Upgrade error RMAN-07539

After successfully upgrading an Oracle database to 19c release, the following error was thrown after attempting to upgrade the recovery catalog:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-07539: insufficient privileges to upgrade the catalog schema


connect to Recovery Catalog Database, and grant recovery catalog user the following permissions :

SQL> grant CREATE ANY CONTEXT to catalog_user;

SQL> grant drop ANY CONTEXT to catalog_user;

Then, go to the 19c upgraded database and upgrade the catalog:

rman target /

RMAN> connect catalog catalog_user/XXXXXX@catalogdb

RMAN> upgrade catalog;

RMAN> upgrade catalog;

I hope this helps !

ORA-00600 and Severity Level

if you are a DBA, then you must have faced “ORA-00600” error. This is a generic error that potentially represent a defect and you will need to contact Oracle Support for help to fix the faced problem.

The error can be found in “alert.log” file , however there are different severity levels of ORA-600….. to find out you can use the very useful view: V$diag_alert_ext

The view has a column called “message_level”  that indicates the severity level as shown below:

1 : CRITICAL: critical errors

2 : SEVERE: severe errors

8 : IMPORTANT: important message

16 : NORMAL: normal message

In a production system you will need monitor and check if either severity “1” or “2” is being thrown in the alert log file. So, basically this useful view provides more detailed information about the error…. having ORA-600 with severity level “16” is normal message alert and can be ignored.

*** This query will catch ORA-600 with severity level 1

SQL> select originating_timestamp,detailed_location,message_level,message_text,problem_key from V$diag_alert_ext where message_level=1 and message_text like’%ORA-00600%’ order by originating_timestamp desc;










RMAN List Backup Summary command

one of the useful RMAN commands is:

RMAN> list backup summary;



so what are these columns represent ?

* LV column lists the following:

0 = incremental level 0 backup

1 = incremental level 1 backup

A = archivelogs

f = full – backup database command

* S is the status:

A = available

U = unavailable

As mentioned above F can represent either “FULL” backup or “Control File” Backup. If your RMAN configuration for controlfile auto-backup is set to “ON” , the control file backup will be taken automatically each time RMAN backup runs as shown in the above picture.

The RMAN configuration is:












ArcGIS 10.6.1 error after Oracle 18c upgrade

In ESRI ArcGIS Geodatabase environment its very important to maintain your product life-cycle support. ArcGIS 10.6.1 is compatible with Oracle 18c ….however when you upgrade both environments (ArcSDE & Oracle)  and attempt to connect through ArcGIS Desktop , the following error will be thrown:


DBMS error[Unkown errorNo extended error]


This is a known bug by ESRI, the solution is to apply the following patch on your ArcGIS Desktop software:



Oracle oraversion

Since Oracle 18c after you successfully apply the patches on the database binaries you will notice that when you invoke sqlplus after that…the version is immediately changed/reflected:

In 19c, after patching the database binaries sqlplus is referencing 19.4 version number


This is due to changes being reflected in a file called “oraversion” under $ORACLE_HOME/bin directory


For example:

$ORACLE_HOME/bin/oraversion -baseVersion


$ORACLE_HOME/bin/oraversion -compositeVersion
















in Oracle 12cR2 database the following errors were thrown after upgrade:


ORA-00604: error occurred at recursive SQL level 1

ORA-00904: “ACDRROWTSINTCOL#”: invalid identifier


Error 704 happened during db open, shutting down database


Errors in file /orcl/oracc01/diag/rdbms/oracc01/oracc01/trace/oracc01_ora_116397.trc (incident=2040067):

ORA-00603: ORACLE server session terminated by fatal error

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-00904: “ACDRROWTSINTCOL#”: invalid identifier

Incident details in: /orcl/oracc01/diag/rdbms/oracc01/oracc01/incident/incdir_2040067/oracc01_ora_116397_i2040067.trc


Non critical error ORA-48913 caught while writing to trace file “/orcl/oracc01/diag/rdbms/oracc01/oracc01/incident/incdir_2040067/oracc01_ora_116397_i2040067.trc”



SQL> startup upgrade;

SQL> exit;

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl -n 4 catupgrd.sql

SQL> @?/rdbms/admin/utlrp.sql

SQL> startup force;

SQL> select comp_name,version from dba_registry where status = ‘VALID’;