DBMS_LOCK.SLEEP is depreciated in Oracle 18c and DBMS_SESSION.SLEEP is introduced, and is available with no additional grants needed.

SLEEP procedure suspends the session for the specified number of seconds.

DBMS_LOCK procedure was  not accessible by default and user must be granted execute permission on the dbms_lock package. Continue reading → Oracle 18c – DBMS_SESSION.SLEEP

Oracle 12c – Delete Pluggable Database with DBCA

We can delete Pluggable database using Database Configuration Assistant (DBCA) OR manually from SQL prompt as sysdba privilege.

Here I have covered DBCA to delete Pluggable database in silent and GUI mode.

Continue reading → Oracle 12c – Delete Pluggable Database with DBCA

Oracle Database 18c – Schema only Accounts

From 18c onwards,  now you can create schema without a password. These are called “Schema Only Accounts”.  These account don’t allow direct connections but can proxy in a single session proxy. This new feature can  allow administrators to further secure their databases by not allowing direct connections to application schemas for any reason. There are few points to note:

  • Schema only accounts can be used for both administrator and non-administrator accounts.
  • You can grant system privileges ( create ant table)  and admin roles (like DBA) to schema only accounts.  But note that administrative privileges like sysdba/sysoper/sysasm   can’t be granted to schema only accounts.
  • Schema only accounts can’t connect through db links.
  • Schema only accounts can be created for database instance only. Same is not valid for ASM environment.

Continue reading → Oracle Database 18c – Schema only Accounts

Oracle 18c – Scalable Sequences

From Oracle 18c, Sequences can be made scalable by using SCALE clause in CREATE/ ALTER SEQUENCE statement. Scalable sequences optimize the sequence generation by using a unique combination of instance number and session number to reduce the impact of Index leaf block contention during massive loads. This is  one of the few features that is not automatically enabled as it requires some intervention by DBA’s to ensure this does not change their implemented business logic.

Here is the syntax for defining a scalable sequence.

Create /Alter  sequence sequence_name …… scale [extend | noextend] | noscale

You can check DBA_SEQUENCES/USER_SEQUENCE/ALL_SEQUENCE dictionaries to know whether sequence is scalable or not. By default sequences are not scalable as you can see in dictionary both columns scale_flag/extend_flag is set to N.


Now create the sequence with scale clause. When the scale clause is specified, a 6 digit numeric scalable number is prefixed to the digit of the sequence. Out of 6 digits, three digits  are instance numbers followed by a three digit session number. These are generated by: Instance number is [(instance id % 100) + 100] Session number is [session id % 1000]

Continue reading → Oracle 18c – Scalable Sequences

Oracle 18c – Cancel a SQL Statement in a Session

From Oracle 18c, you can terminate a SQL query consuming excessive resources in a session like runaway sql queries. This is an alternative of killing the whole session. When you cancel a DML statement, the statement is rolled back.

Here is the syntax of cancelling a SQL statement.


  • If @INST_ID is not specified , the instance id of the current session is used.
  • If SQL_ID is not specified, the currently running SQL statement in the specified session is canceled.

Continue reading → Oracle 18c – Cancel a SQL Statement in a Session

Oracle 18c – Preplugin Backups including restore and recovery test

Preplugin backups are backups taken of a non-CDB or a PDB before they are plugged into a target CDB. You can relocate the PDB by unplugging it from a source CDB or NON-CDB and plugging it into target CDB. RMAN can perform PDB restore and recovery operations on the target CDB by using preplugin backups. From 18c, RMAN now supports use of the PREPLUGIN for commands like CATALOG, CROSSCHECK, LIST, CHANGE etc. Here is a complete explanantion of preplugin PDB backup on source CDB and restore/recovery on target CDB after plugging in.

Source: CDB18 and PDB18 ( PDB Backup and unplug from source CDB)

Destination: CDB_dest and PDB18 ( plug-in to dest CDB and restore/recover test)

Creating Preplugin Backups of PDBs Using RMAN On source CDB:

Connect to the PDB as a local or common user with SYSDBA/SYSBACKUP privilege. Continue reading → Oracle 18c – Preplugin Backups including restore and recovery test