Row limiting clause in oracle 12c

 Row limiting clause clause allows sql queries to limit the number of rows returned and to specify a starting row for the return set. 1. Fetch first N rows:

2. Fetch 5 rows after offset of 4 rows

3. Fetch percentage of rows from a query:

IN-MEMORY in oracle 12c

Introduction: Oracle in-memory concept has been introduced in oracle 12c. This feature enables tables, partitions, materialized views be stored in memory using column format, which delivers fast SQL processing for the Analytical purpose. To understand Database In-Memory feature and its benefits we first need to understand the unique “dual format” architecture that enables Oracle Database […]

How to setup listener for PDBS in Multitenant database.

If you wish to setup listener for a particular PDB in the multitenant database, then follow as below. Create a listener and start it.

Now connect to the PDB for which you are setting up listener.

Set listener_networks: use the listener port and the hosname

Check the status of the listener:


Common User vs Local User in 12c Multitenant

With oracle 12c Multitenant architecture, New terminologies for users has been introduced. LOCAL USER and COMMON USER. SEE ALSO: Oracle 12c Multitenant Architecture COMMON USER: 1.A common user is a DB user, which work perform an activity in all the containers including root container of the CDB. 2.A common user can only be created in […]

Local/Shared Undo mode in oracle 12.2 multitenant database

There are two undo modes in oracle 12.2 Multitenant database 1. Local undo mode 2. Shared undo mode Local undo mode: In this mode, each container ( i.e PDB ) in multitenant will have their own active undo tablespace. Share Undo mode: In this mode, There will be only one undo tablespace for the instance. […]

Split partition online oracle 12.2

In Oracle 12.2, We can split partitions or subpartitions online without impacting the DML statements.

Read only Partition in Oracle 12.2

Till now we can make a table read only, But what about setting few partitions of a table to read-only. Thanks to Oracle 12.2 Release, We can do this. This is usually helpful, If requirement is to make the historical data not editable.


If you tried […]

Move table online in Oracle 12.2

In previous Releases, To move a table to a different tablespace or segment, we need to take downtime for this activity And after moving the table, we have to rebuild/recreate the indexes. Now with Oracle 12.2, We can move the table online to a different segment or tablespace, without impacting the DML activities and without […]

VARIABLE new feature in Oracle 12.2

You may use SQL*Plus to test queries with bind variables. Here is what you do before 12.2:

Now in 12.2 is so simple

spool CSV in Oracle 12.2

Now we can spool spool CSV or JSON from Oracle Database. Prior to 12.2 , we can spool as text or html, but from 12.2 onward, the SET MARKUP command now has a CSV option to output data in CSV format. Syntax CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}] Traditionally you wiill get formatted output (without […]

Page 2 of 612345...Last »
Skip to toolbar