How to Decide if column should be indexed?

Many people ask these Questions,
Q. How to identify which column is candidate for Index creation?
Q. How to determine index will really help to perform query faster?
Q. Which columns should be indexed in oracle?
Q. How to identify index will give benefit to optimizer to generate better access path?
Q. How do i know when to create index on column?

Answer for all above Questions is,
SQL Tuning Advisor

Q. How ???
Ans. Let’s check below test case for the same. Below method/approach one can use to diagnose problem and resolve performance issue.

I have below Query which is running poorly.

SELECT * FROM TXN_MASTER,TXN_DETAIL
WHERE TXN_DETAIL.TXN_REFNO=TXN_MASTER.TXN_REFNO
AND TXN_MASTER.STATUS ='F'
AND TXN_MASTER.TXN_DATE <=sysdate
AND TXN_MASTER.FILE_NO is null;

Check the below explain plan for the same.

SELECT STATEMENT, GOAL = ALL_ROWS                                                                                     Cost=115161    Cardinality=2    Bytes=582
     NESTED LOOPS                                                                                                                                Cost=115161    Cardinality=2    Bytes=582
      NESTED LOOPS                                                                                                                               Cost=115161    Cardinality=2    Bytes=582
   TABLE ACCESS FULL        Object owner=TSTSCHEMA    Object name=TXN_MASTER           Cost=115160    Cardinality=2    Bytes=196
   INDEX UNIQUE SCAN    Object owner=TSTSCHEMA    Object name=PK01_TXN_DETAIL     Cost=1        Cardinality=1   
  TABLE ACCESS BY INDEX ROWID        Object owner=TSTSCHEMA    Object name=TXN_DETAIL    Cost=1        Cardinality=1    Bytes=193

My Pre-checks to diagnose problem area.
1)    Check joining conditions are proper.
2)    Check of statistics for both the tables.
3)    Check of indexes created for columns which used in where clause.

Findings,
1)    Joining conditions are proper. No extra condition require to join both the tables.
2)    Statistics of table TXN_DETAILis stale.
3)    Indexes are not present for column STATUS, TXN_DATE, FILE_NO

Now, let’s check SQL Tuning Advisor. What are the recommendations provided by advisor.

Step 1: Create Tuning Task.

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2    l_sql               VARCHAR2(4000);
  3    l_sql_tune_task_id  VARCHAR2(100);
  4  BEGIN
  5    l_sql := 'SELECT * FROM TXN_MASTER,TXN_DETAIL
  6  WHERE TXN_DETAIL.TXN_REFNO=TXN_MASTER.TXN_REFNO
  7  AND TXN_MASTER.STATUS =''F'''||
  8  'AND TXN_MASTER.TXN_DATE <=sysdate
  9  AND TXN_MASTER.FILE_NO is null
 10  ';
 11 
 12    l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
 13                            sql_text    => l_sql,
 14                            user_name   => 'TSTSCHEMA',
 15                            scope       => DBMS_SQLTUNE.scope_comprehensive,
 16                            time_limit  => 60,
 17                            task_name   => 'My_Qry_tuning_Tsk1',
 18                            description => 'Tuning task for an PM Query.');
 19    DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
 20  END;
 21  /
l_sql_tune_task_id: My_Qry_tuning_Tsk1

PL/SQL procedure successfully completed

Step 2: Execute Tuning Task created in above step.

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'My_Qry_tuning_Tsk1');
PL/SQL procedure successfully completed

SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = 'TSTSCHEMA';
TASK_NAME                                                                        STATUS
-------------------------------------------------------------------------------- -----------
My_Qry_tuning_Tsk1                                                              COMPLETED
Step 3: Check recommendations of Tuning Advisor.

SQL>
SQL> SET LONG 10000;
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 200

SQL> SELECT DBMS_SQLTUNE.report_tuning_task('My_Qry_tuning_Tsk1') AS recommendations FROM dual;

RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : My_Qry_tuning_Tsk1
Tuning Task Owner  : TSTSCHEMA
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 08/24/2016 10:58:28
Completed at       : 08/24/2016 10:59:21
-------------------------------------------------------------------------------
Schema Name: TSTSCHEMA
SQL ID     : 44b0kssyj6npj
SQL Text   : SELECT * FROM TXN_MASTER,TXN_DETAIL
             WHERE TXN_DETAIL.TXN_REFNO=TXN_MASTER.TXN_REFNO
             AND TXN_MASTER.STATUS ='F'AND
             TXN_MASTER.TXN_DATE <=sysdate
             AND TXN_MASTER.FILE_NO is null
-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
  Optimizer statistics for table "TSTSCHEMA"."TXN_DETAIL" are stale.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table and its indices.
    execute dbms_stats.gather_table_stats(ownname => 'TSTSCHEMA', tabname =>
            'TXN_DETAIL', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table and its indices
    in order to select a good execution plan.

2- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 77.79%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile to use parallel execution
    for this statement.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'My_Qry_tuning_Tsk1', task_owner => 'TSTSCHEMA', replace =>
            TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);

  Executing this query parallel with DOP 5 will improve its response time
  77.79% over the original plan. However, there is some cost in enabling
  parallel execution. It will increase the statement's resource consumption by
  an estimated 11.04% which may result in a reduction of system throughput.
  Also, because these resources are consumed over a much smaller duration, the
  response time of concurrent statements might be negatively impacted if
  sufficient hardware capacity is not available.

  The following data shows some sampled statistics for this SQL from the past
  week and projected weekly values when parallel execution is enabled.

                                 Past week sampled statistics for this SQL
                                 -----------------------------------------
  Number of executions                                                   0
  Percent of total activity                                              0
  Percent of samples with #Active Sessions > 2*CPU                       0
  Weekly DB time (in sec)                                                0

                              Projected statistics with Parallel Execution
                              --------------------------------------------
  Weekly DB time (in sec)                                                0

3- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 99.99%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index TSTSCHEMA.IDX$$_61A90001 on
    TSTSCHEMA.TXN_MASTER("FILE_NO","STATUS","TXN_DATE");

  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.

SQL>

SQL Tuning Advisor shown three recommendations.
1)    Gather Stats for table TXN_DETAIL.
2)    Use Parallel Query Execution
3)    Create Index for three columns i.e. STATUS, TXN_DATE, FILE_NO

1st and 3rd recommendations we have identified as problem in our basic analysis above. SQL Tuning Advisor has suggested the same and also provided % of benefits we get after implementing it.

Here I have implemented on 1st & 3rd recommendations which I has diagnosed and will check how it works.
I have executed below statement which is provided in above recommendations number 1.

execute dbms_stats.gather_table_stats(ownname => 'TSTSCHEMA', tabname =>  'TXN_DETAIL', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);=

Now, check Explain Plan if any difference.

SELECT STATEMENT, GOAL = ALL_ROWS                                                                                                       Cost=115161    Cardinality=2    Bytes=582
     NESTED LOOPS                                                                                                                                                  Cost=115161    Cardinality=2    Bytes=582
      NESTED LOOPS                                                                                                                                                 Cost=115161    Cardinality=2    Bytes=582
   TABLE ACCESS FULL        Object owner=TSTSCHEMA    Object name=TXN_MASTER                            Cost=115160    Cardinality=2    Bytes=196
   INDEX UNIQUE SCAN    Object owner=TSTSCHEMA    Object name=PK01_TXN_DETAIL                       Cost=1        Cardinality=1   
  TABLE ACCESS BY INDEX ROWID        Object owner=TSTSCHEMA    Object name=TXN_DETAIL         Cost=1        Cardinality=1    Bytes=193


There is no difference found in Explain Plan.
Now, Let’s Run SQL Tuning Advisor again to check his recommendation.

Step 1: Execute Tuning Task created in above step.

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'My_Qry_tuning_Tsk1');
PL/SQL procedure successfully completed

SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = 'TSTSCHEMA';
TASK_NAME                                                                        STATUS
-------------------------------------------------------------------------------- -----------
My_Qry_tuning_Tsk1                                                              COMPLETED
Step 2: Check recommendations of Tuning Advisor.

SQL>
SQL> SET LONG 10000;
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 200

SQL> SELECT DBMS_SQLTUNE.report_tuning_task('My_Qry_tuning_Tsk1') AS recommendations FROM dual;

RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : My_Qry_tuning_Tsk1
Tuning Task Owner  : TSTSCHEMA
Workload Type      : Single SQL Statement
Execution Count    : 2
Current Execution  : EXEC_25749
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 08/24/2016 11:45:40
Completed at       : 08/24/2016 11:46:20

-------------------------------------------------------------------------------
Schema Name: TSTSCHEMA
SQL ID     : 44b0kssyj6npj
SQL Text   : SELECT * FROM TXN_MASTER,TXN_DETAIL
             WHERE TXN_DETAIL.TXN_REFNO=TXN_MASTER.TXN_REFNO
             AND TXN_MASTER.STATUS ='F'AND
             TXN_MASTER.TXN_DATE <=sysdate
             AND TXN_MASTER.FILE_NO is null

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 77.79%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile to use parallel execution
    for this statement.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'My_Qry_tuning_Tsk1', task_owner => 'TSTSCHEMA', replace =>
            TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);

  Executing this query parallel with DOP 5 will improve its response time
  77.79% over the original plan. However, there is some cost in enabling
  parallel execution. It will increase the statement's resource consumption by
  an estimated 11.04% which may result in a reduction of system throughput.
  Also, because these resources are consumed over a much smaller duration, the
  response time of concurrent statements might be negatively impacted if
  sufficient hardware capacity is not available.

  The following data shows some sampled statistics for this SQL from the past
  week and projected weekly values when parallel execution is enabled.

                                 Past week sampled statistics for this SQL
                                 -----------------------------------------
  Number of executions                                                   0
  Percent of total activity                                              0
  Percent of samples with #Active Sessions > 2*CPU                       0
  Weekly DB time (in sec)                                                0

                              Projected statistics with Parallel Execution
                              --------------------------------------------
  Weekly DB time (in sec)                                                0

2- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 99.99%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index TSTSCHEMA.IDX$$_61A90001 on
    TSTSCHEMA.TXN_MASTER("FILE_NO","STATUS","TXN_DATE");

  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.
See, Now SQL Tuning Advisor shown two recommendations.
1)    Use Parallel Query Execution
2)    Create Index for three columns i.e. STATUS, TXN_DATE, FILE_NO

Now Let’s create Index as below only for two columns i.e. STATUS, TXN_DATE which is most important filter criteria of given query. We will not create index on FILE_NO, this is because we know that FILE_NO is not much important filter criteria.

Create bitmap index ind_patboutSTATUS on pm1222day0.TXN_MASTER(“STATUS”);
(Bitmap index created because this field contains single character “F” & ”S” only.)

Create index ind_patbouttxn_date on pm1222day0.TXN_MASTER(“TXN_DATE”);
(Normal index created.)

Now, let’s check the Explain Plan

SELECT STATEMENT, GOAL = ALL_ROWS                                                                                                                    Cost=5    Cardinality=2    Bytes=582
     NESTED LOOPS                                                                                                                                                               Cost=5    Cardinality=2    Bytes=582
    NESTED LOOPS                                                                                                                                                                Cost=5    Cardinality=2    Bytes=582
TABLE ACCESS BY INDEX ROWID BATCHED    Object owner=TSTSCHEMA    Object name=TXN_MASTER    Cost=4    Cardinality=2    Bytes=196
    BITMAP CONVERSION TO ROWIDS                   
         BITMAP INDEX SINGLE VALUE    Object owner=TSTSCHEMA    Object name=IND_PATBOUTSTATUS           
       INDEX UNIQUE SCAN        Object owner=TSTSCHEMA    Object name=PK01_TXN_DETAIL                            Cost=1    Cardinality=1   
  TABLE ACCESS BY INDEX ROWID        Object owner=TSTSCHEMA    Object name=TXN_DETAIL                     Cost=1    Cardinality=1    Bytes=193
Waaaww…Tremendous changes in CPU Cost and plan is using index ind_patboutSTATUS.

Now let’s check SQL Tuning Advisor again.

Step 1: Execute Tuning Task created in above step.
 SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'My_Qry_tuning_Tsk1');
PL/SQL procedure successfully completed

SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = 'TSTSCHEMA';
TASK_NAME                                                                        STATUS
-------------------------------------------------------------------------------- -----------
My_Qry_tuning_Tsk1                                                              COMPLETED
Step 2: Check recommendations of Tuning Advisor.

SQL>
SQL> SET LONG 10000;
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 200

SQL> SELECT DBMS_SQLTUNE.report_tuning_task('My_Qry_tuning_Tsk1') AS recommendations FROM dual;

RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : My_Qry_tuning_Tsk1
Tuning Task Owner  : TSTSCHEMA
Workload Type      : Single SQL Statement
Execution Count    : 4
Current Execution  : EXEC_25752
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 08/24/2016 12:10:25
Completed at       : 08/24/2016 12:10:28
-------------------------------------------------------------------------------
Schema Name: TSTSCHEMA
SQL ID     : 44b0kssyj6npj
SQL Text   : SELECT * FROM TXN_MASTER,TXN_DETAIL
             WHERE TXN_DETAIL.TXN_REFNO=TXN_MASTER.TXN_REFNO
             AND TXN_MASTER.STATUS ='F'AND
             TXN_MASTER.TXN_DATE <=sysdate
             AND TXN_MASTER.FILE_NO is null
-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------
SQL>

Great, there are NO recommendations now. We have completed this test case successfully.

Take away from this article.
-    Identify root cause of poor SQL performance.
-    Identify how much benefits we get from indexes before creating it in production.
-    Usage of SQL Tuning Advisor.
-    Step by step approach to implement SQL tuning Advisor recommendations.

Hope this article will help you. Please provide your comments on this. Also feel free to ask any question for the same.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

Oracle Database 12c FAQs. Part 1. Basics Concept of Oracle Database 12c.

Oracle Database 12c FAQs. Part 1. Basics of Oracle Database 12c.

I have prepared this Article to clear basic concepts of Oracle Database 12c and its Multitenant feature. This article is in the form of Question and Answer. Every DBA who is learning Oracle Database 12c is having following question. I have just tried to answer it as my best.

One of my friends told me that your article is looks like Interview Question and Answer; I laughed  ;) , and said Yea, its look like that.

Let’s start exploring Oracle Database 12c.
Common FAQs of Oracle Database 12c, to understand very basic concepts.

Q. What is Oracle multitenant database?
Ans. In very simple meaning, => Multiple Databases, Single Instance <=

Let me describe and compare this with previous versions.

Before Oracle Database 12c, each database is having their own instance and each database requires its own processor, memory and other server resources. Most of the efforts were going to manage those individual instances, in terms of patching, migration, upgrades and performance tuning.

In addition to management issues, these databases often waste server resources because they use only a very small portion of dedicated server. Hence consolidation of these databases into a single database Oracle introduces Multitenant option, which run one or more Pluggable Databases (PDBs) within a single Oracle instance, called a multitenant Container Database (CDB).

Q. What is pluggable database?
Ans. Pluggable Database (PDB) is a database which resides in Oracle’s Container Database (CDB). PDB uses the resources of CDB i.e background processes and memory.

A PDB is a user-created set of schemas, objects, and related structures that appears logically to an application as a separate database.

Refer following architecture diagram.

Q. How SGA is define in Multitenant Database?
Ans. Container Database (CDB) is having instance, which is shared to all Pluggable databases (PDBs).
The central CDB instance, with its background processes, SGA allocation, and data dictionary, is part of the root container. This instance is common to all the PDBs within that CDB.

Q. What is root container, Seed and PDB as shown in above diagram of Multitenant?
Ans.
When you create a CDB, it’s the root container that gets created first. The root container is mandatory and contains Oracle-created metadata, common users, and common roles. Here it refers as CDB$ROOT. CDB$ROOT which is holding all PDBs.

Seed also called PDB$SEED, which is a template to create Pluggable Database (PDB). There is only one seed PDB in a CDB.

Pluggable Database (PDB) is a database which holds all user schemas and user metadata information. PDB is actual database created by user.

Q. Is root container stores user data?
Ans. No, the root does not store user data.

Q. How many PDB’s one can create in a single CDB?
Ans. One can create 252 PDBs in a single CDB.

Q. How can one segregate user of CDB & PDB?
Ans.
In multitenant database architecture users can be segregate like the local user and the common user.
Local Users:-  A local user is defined in a specific PDB and can only connect to that PDB and not to any other PDBs or the CDB.
Common user:- A common user belongs in the root container (ROOT$CDB) directory’s data dictionary and is known in all the PDBs that belong to the CDB. A common user has the same identity in the CDB as well as in all the PDBs, and can log in to any PDB in which that user has privileges. The common user performs administrative tasks for the entire CDB or for the individual PDBs.
These tasks include the plugging/ unplugging of databases and the starting/ stopping of the CDB, for example. Other tasks that the common user can perform depend on the privileges you grant.
Oracle’s SYS and SYSTEM users serve as Oracle-supplied common users.

Q. Is it mandatory to create CDB & PDB? Can we create normal database just like 9i, 10g & 11g?
Ans. No it is not mandatory to create CDB & PDB. You can still create normal database like previous version of Oracle Database.
At the time of creation of DB, installer will ask you if you want to create Database as Container Database. If you select as Container Database it will Create CDB & PDB.
Refer below screenshot for the same. If you uncheck “Create as Container Database” it will create normal orcl database.

Q. Is SPFILE or PFILE common for CDB & PDB?
Ans. Yes SPFILE or PFILE is common for both CDB & PDB. Here one thing need to understand that in Multitenant Database there is only single Instance. This instance is shared to all PDBs. Hence there is only one SPFILE or PFILE.
SPFILE or PFILE belongs to CDB only, because CDB is the one who create instance at startup.

Q. How can I create PDB after fresh installation of Oracle Database and also how can I create another database (PDB)?
Ans. There are several methods you can create PDB.
1)    Using dbca : You can create new PDB using DBCA.
2)    Clone PDB : You can clone existing PDB to create similar database. Below link will guide you how to clone PDB within CDB.
http://tanwanichandan.blogspot.in/2016/05/step-by-step-clone-pdb-within-cdb-in.html
3)    Plug normal database (non-CDB) to existing CDB.
4)    Un-plug PDB from one CDB and plug it to another CDB.

Q. How GUID is defined for CDB & PDB? Is it same for both?
Ans. GUID is different for both CDB & PDB. Each PDB has it’s own GUID.

Q. Are SYSTEM, SYSAUX & TEMP tablespaces are common for CDB & PDB?
Ans.
No, there are separate SYSTEM and SYSAUX tablespaces for the root CDB and for each PDB. You can specify a separate default tablespace for the root CDB and for each PDB.
Although there is a default temporary tablespace for the entire CDB, you can optionally create additional temporary tablespaces for use by individual PDBs.
Path of data files also be different for both CDB & PDB.

Q. Can I see OS services of CDB & PDB?
Ans.
You can only see the CDB services i.e. root container services. Here in our example ORCL is our CDB/root container whereas PDBORCL is our PDB.
Database services are bound to Database Instance. As in Multitenant there is only single instance services are also single which is bound to an Instance.

Q. What does CON_ID means in v$database, v$containers and other views?
Ans.CON_ID means CONtainer ID.
CON_ID 1 represents the root container, and
CON_ID 2 represents the PDB SEED database also
CON_ID 3 and onward represents to PDBs.

Q. How administrative tasks are different for PDB DBAs & CDB DBAs in 12c?
Ans.
Similar Task for PDB & CDB Admins
•    Managing tablespaces
•    Managing data files and temp files
•    Managing schema objects

CDB Admin only task.
•    Starting and shutting down the CDB instance
•    Creating, plugging/ unplugging, and dropping PDBs
•    Modifying the CDB or the root
•    Executing DDL statements on a CDB or the root
•    Managing instance-level components such as processes, memory, alerts, control files, undo, and the online/ archived redo log files.

Q. Type of views in Oracle 12c
Ans.
•    CDB_ XXX    :-  All objects in the CDB across all the PDBs
•    DBA_ XXX     :-  All objects in a container or PDB
•    CDB_ p dbs    :-  All PDBs in a CDB
•    CDB_ tablespaces :-  All tablespaces in a CDB
•    CDB_ users    :- All users in a CDB (both common and local users)

Q. How to check if Database is CDB or not.
Ans. Run below Query,
select name,cdb,con_id from v$database;

If CDB column returns YES the database is CDB.

Q. Can we assign default tablespace to each PDB & CDB?
Ans. Yes, you can assign default tablesapce to each PDB.

Example:-
For PDB -> conn sys@pdb1
alter pluggable database pdb1 default tablespace users;
For CDB -> conn system@cdb1
alter database default tablesapce users1;

Q. What are common user & local user?

Ans.

Common User	Local User
Common User can create local user & common user.	Local user can create local user but cannot create common user.
Common user start with c##	Local user name cannot start with c##
“container” clause can be omit while creating common user. By default container=all for common user.	“container” clause require to create local user.
One must connected to root container to create common user.	One should provide container clause to create local user.
Common and Local user both can grant privilege locally.	Common and Local user both can grant privilege locally.

Please provide your comments & suggestions on this Article.

Oracle Database 12c FAQs. Part 2. Administration of Oracle Database 12c ... Coming Soon...

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert