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”.

Picture1

The parameter for the predefined naming is PRIVATE_TEMP_TABLE_PREFIX

SQL> show parameter PRIVATE_TEMP_TABLE_PREFIX

Picture2

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> CREATE USER HR IDENTIFIED BY hr_123 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

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:

SQL> CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_records

(time_id      DATE,

quantity_of_sale  NUMBER(10,2))

ON COMMIT PRESERVE DEFINITION;

Picture3

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;

Picture4

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

Picture5

the following are the database views:

USER_PRIVATE_TEMP_TABLES

DBA_PRIVATE_TEMP_TABLES

CDB_PRIVATE_TEMP_TABLES

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 18c new feature: ALTER SYSTEM CANCEL SQL

In the past when you wanted to terminate an Oracle database session that is causing a problem we used the classical SQL command:

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;

In 18c a new command is introduced , that will cancel the SQL statement being executed in the session without actually terminating the session:

SQL> ALTER SYSTEM CANCEL SQL ‘SID, SERIAL,SQLID’;

To simulate:

To find the 3 values SID, SERIAL, and SQLID you can use the following query:

SELECT s.inst_id,

s.sid,

s.serial#,

s.sql_id,

p.spid,

s.username,

s.program

FROM   gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.type != ‘BACKGROUND’;

PIC1

SQL> ALTER SYSTEM CANCEL SQL ‘874,51416,a5ks9fhw2v9s1’;

PIC2

The user session will generate the following error:

ORA-01013: user requested cancel of current operation

However, the user will still continue working fine (his session will not be terminated as shown below)

PIC3

 

 

 

 

 

 

Oracle 18c sqlplus new features

This is a short blog about new features introduced in Oracle 18c sqlplus ….that could be helpful for you !

SET ROWLIMIT

The objective of this command is to display certain number of rows, especially with large tables

 

In the below example the COUNTRIES table has 25 records, when I set ROWLIMIT to 3 , only three rows will be displayed when querying the table (the first 3 rows)

Picture1

The below command will display the current setting:

Picture2

SET FEEDBACK ON SQL_ID

This command will return the sql_id for the SQL or PL/SQL statements that is currently being executed. This will consequently help in debugging activities.

As shown below the executed query will generate the SQL_ID with the query result:

Picture3

You can check it from v$sql by executing:

SQL> SELECT sql_text,sql_id FROM v$sql WHERE sql_id = ‘agjjdvhgdzd1q’;

Picture4

 

 

 

 

Oracle 18c new feature: Cloning PDB using DBCA (Database Configuration Assistant)

Here I am cloning a local PDB using DBCA, which is a new feature in oracle 18c:

Go to

cd $ORACLE_HOME/bin

Execute the command:

dbca

make sure X-windows is configured: export DISPLAY=PCXXXX:0.0

Picture1Picture2Picture3Picture4Picture5Picture6Picture7Picture8

You can use silent installation:

In Linux:

./dbca -silent -createpluggabledatabase -sourcedb ORACLE -createpdbfrom PDB -pdbName PDB_TEST_CLONE2 -sourcepdb PDB_TEST

In Windows:

dbca -silent -createpluggabledatabase -sourcedb ORACLE -createpdbfrom PDB -pdbName PDB_TEST_CLONE2 -sourcepdb PDB_TEST

silent_install