Oracle database experiences

Oracle database blog

DBA_VIEWS in Non-CDB and PDB databases

When you are connected to a PDB database the behavior should be nearly the same as if you were connected to a Non-CDB database e.g. Oracle 11gR2 database. Nevertheless we will see that there are some minor differences.

Suppose that you are connected to a PDB database as the common user c##sa who has been granted the DBA role in all the containers:

>sqlplus c##sa@pdb1

SQL*Plus: Release 12.1.0.1.0 Production on Thu Mar 20 06:41:02 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Thu Mar 20 2014 06:40:13 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

> show con_id;

CON_ID
------------------------------
3
> show con_name;

CON_NAME
------------------------------
PDB1

> select * from session_roles;

ROLE
------------------------------------------------------------
DBA
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
CAPTURE_ADMIN
EXP_FULL_DATABASE
IMP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
XS_RESOURCE
GATHER_SYSTEM_STATISTICS
OPTIMIZER_PROCESSING_RATE
EM_EXPRESS_BASIC
EM_EXPRESS_ALL
SCHEDULER_ADMIN
HS_ADMIN_SELECT_ROLE
HS_ADMIN_EXECUTE_ROLE
XDBADMIN
XDB_SET_INVOKER
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
OLAP_XS_ADMIN
OLAP_DBA

24 rows selected.

You want to create a table named TEST_VIEWS that will contain an excerpt of the view DBA_VIEWS. We first count the number of rows of the query:

> SELECT COUNT(*) FROM DBA_VIEWS
  2  WHERE VIEW_NAME LIKE 'DBA%';

  COUNT(*)
----------
       996

1 row selected.

Now we will store the rows in the table and then we will count again the number of rows:

> CREATE TABLE TEST_VIEWS
  2  AS
  3  SELECT OWNER
  4  ,      VIEW_NAME
  5  FROM DBA_VIEWS
  6  WHERE VIEW_NAME LIKE 'DBA%';

Table created.

> SELECT COUNT(*) FROM TEST_VIEWS;

  COUNT(*)
----------
         0

1 row selected.

The same behavior occurs with an INSERT statement:

> INSERT INTO TEST_VIEWS(
  2     OWNER,
  3     VIEW_NAME        )
  4  SELECT OWNER
  5  ,      VIEW_NAME
  6  FROM DBA_VIEWS
  7  WHERE VIEW_NAME LIKE 'DBA%';

0 rows created.

What happened? The SELECT statement has the same behavior in a PDB and in a non-CDB database meanwhile the INSERT statement in our case is linked to the container context. We execute the following query:

> SELECT ORIGIN_CON_ID
  2  ,      COUNT(*) CO
  3  FROM DBA_VIEWS
  4  WHERE VIEW_NAME LIKE 'DBA%'
  5  GROUP BY ORIGIN_CON_ID;

ORIGIN_CON_ID         CO
------------- ----------
            1        996

1 row selected.

It means that all the rows are linked to the container 1 (CDB$ROOT). No row will be inserted in the TEST_VIEWS table. We can modify the previous query:

> SELECT ORIGIN_CON_ID
  2  ,      COUNT(*) CO
  3  FROM DBA_VIEWS
  4  GROUP BY ORIGIN_CON_ID;

ORIGIN_CON_ID         CO
------------- ----------
            1       6155
            3         65

2 rows selected.

It shows that only 65 rows are linked to the current container.

Conclusion
When you will migrate a Non-CDB database as a pluggable database in a CDB database you should pay attention to all the queries involving a table or a view which are container sensitive’s.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: