Tablespaces in multitenancy

›
Note: In a non-CDB, all the tablespaces belong to one database.  In the CDB, one set of tablespaces belong to the root container, and each P...

Using glogin.sql to have friendly and nice SQLPLUS multitenancy environment

›
When we are working at multitenancy it is good to have glogin configured to show container info. I am having following glogin.sql. You can f...

Cost Based Optimizer (CBO) Parameters Setting

›
The parameter that control the optimizer behaviors are: CURSOR_SHARING determines what kind of SQL statements can shared the same cursors. T...

SQL Tuning Tools

›
Several tuning tools to detect and rectify SQL errors and performance issue Automatic Database Diagnostic Monitor The Automatic Database Dia...

General Database Tuning Task

›
Many SQL Tuning tasks should be performed on a regular basis. You may see a way to rewrite a WHERE clause, but it may depend on a new index ...

Poor performance SQL Statements

›
SQL statements can perform poorly for a variety of reason: 1. Stale optimizer statistics SQL execution plans are generated by the cost-based...

What is PGA (Program Global Area)

›
PGAs are memory regions that contain data and control information about a server or background process. A PGA is suballocated from the aggre...

Automatically Open PDBs When CDB started

›
To automatically open PDB when CDB is started, we can use trigger at CDB level. 1. Login to CDB root SQL> show con_name CON_NAME --------...

Install Oracle 12c Client/Database on Windows [INS-30131]

›
I need to upgrade all the clients station to 12c, however I encountered  following error [INS-30131] Initial setup required for the executio...

List All Users Created

›
There are two types of users as following: Local User A user in a non-CDB maps to a local user in a PDB. A local user is defined in the PDB’...

Oracle 12c Data Dictionary Views CDB_xxx, DBA_xxx, ALL_xxx, USER_xxx

›
In a CDB, for every DBA_* view, a CDB_* view is defined. In the root, CDB views can be used to obtain information about tables, tablespaces,...

Create PDB from existing PDB (Clone PDB)

›
1. Connect to CDB as root [oracle@ora12cn1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Sun Dec 1 19:12:07 2013 Copyri...

Create PDB from PDB$SEED (appropriate PDB directory)

›
This is an extension example from previous post of Create PDB from PDB$SEED The objective is to convert the data files and temp files to pdb...

Drop Pluggable Database

›
To remove pluggable database permanently from CDB can use following command: 1. Connect to root and close the pluggable database [oracle@ora...

Create PDB from PDB$SEED

›
The creation of a new PDB from the seed is nearly instantaneous. The operation copies the data files from the READ ONLY seed PDB to the targ...

Understand Server Pool

›
When Oracle Clusterware is installed, two server pools are created automatically: Generic and Free . All servers in a new installation are a...

To View 12c Tablespaces

›
To view tablespace and it's corresponding container SQL> col name form a12 SQL> SELECT file#, tbs.name, tbs.ts#, tbs.con_id   2  F...

All Files of the CDB

›
To view logfile that we can query v$logfile, we can see the con_id. In this example I am using ASM +DATA volume. SQL> col member format a...

To Know Your Current Connected Container

›
You can use SHOW CON_NAME and CON_ID to know which container you are connected to. SQL> show con_name CON_NAME --------------------------...