Oracle 18c – PDB Snapshot Carousel

A PDB snapshot is a named copy of a PDB at a specific point in time. When a PDB is enabled for PDB snapshots, you can create up to eight snapshots of it. The set of snapshots is called a snapshot carousel. PDB Snapshot Carousel is a new feature of Oracle Database 18c. When maximum limit of 8 snapshots of PDB is reached, then  new snapshot overwrite the oldest copy.

PDB snapshot carousel keeps the external log for the purpose of using it in the following cases:

  • Generate non-productive environments.
  • Recovery of a Productive PDB before a problem.

The snapshots include the copy of the data files of the original PDB, excluding the archived redo logs. This instant copy is stored on disk and by default it is in the same directory as the datafile.

Snapshot Configuration of a PDB:

The MAX_PDB_SNAPSHOTS property specifies the maximum number of snapshots permitted in the carousel. The current setting is visible in the CDB_PROPERTIES view.

You can change the maximum PDB snapshot value and setting value to Zero will drop all the snapshots. Continue reading → Oracle 18c – PDB Snapshot Carousel


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