Oracle … as usual

Oracle by Laurent Leturgez

Category Archives: SQL

Executing a SQL Statement on Oracle with Python

In the first blogpost of this series dedicated to Oracle and Python, I described how to connect a Python script to an Oracle Database (see. connecting python to an oracle database). In this second post, I will describe how to query an Oracle database and gets some results by using most popular Python libraries for this stuff: numpy and panda.

So, first thing to do, you have to install panda and numpy libraries in your work environnement (virtualenv, miniconda etc.). And of course, cx_Oracle has to be installed too ūüėČ

In my case, libraries are deployed in my virtualenv hosted in PyCharm project:

In the program I write, in order to load packages and configure basics, I will use the header below:

from __future__ import print_function

import cx_Oracle
import os
import numpy as np
import pandas as pd


oh="D:/tools/Oracle/instantclient_12_2_x8664"
os.environ["ORACLE_HOME"]=oh
os.environ["PATH"]=oh+os.pathsep+os.environ["PATH"]
os.environ["NLS_LANG"]="AMERICAN_AMERICA.AL32UTF8"

First step, write a function to connect the database

So, first thing to write is a function that will return a cx_Oracle connection object (or reuse the function used in the first blogpost ūüėČ ).

This function will user parameters like username, password, datasource name (or url) and optionaly the mode used (SYSDBA for example).

def connectToOracle(url, username, password, mode=None):
    if mode is not None:
        connection = cx_Oracle.Connection (user=username, password=password, dsn=url, mode=mode)
    else:
        connection = cx_Oracle.Connection (user=username, password=password, dsn=url)
    return connection

 

Get a cursor and execute a statement

Once we have a cx_Oracle connection object, we can create a cursor by executing the cursor() function and then execute a statement.

To do this, I wrote a function with two parameters: the connection object and the statement text, and this returns the cursor that has been executed in the function:


def executeStmt(conn, stmt):
    if conn is not None and isinstance (conn, cx_Oracle.Connection):
        cur = conn.cursor()
        cur.execute(stmt)
    return cur

Once the cursor will be executed, we will be able to fetch one, many or all rows, and to describe it to get more metadata.

 

Describe executed cursor to get some metadata

By describing the cursor, we are able to get some information of its structure (column name and types, number precision and scale, nullable column etc.).

To do that, we use a read only attribute of the cursor (named description). In this attribute, there are 7 items that can be read.

These items are the ones below:

  • Item 1: Column names
  • Item 2: Column types (types are cx_Oracle data types)
  • Item 3: Column displayed sizes
  • Item 4: Column internal sizes
  • Item 5: Column precision
  • Item 6: Column scale
  • Item 7: Nullable column

This function code is the one below:


def describeCursor(cur):
    if cur is not None and isinstance (cur, cx_Oracle.Cursor):
        colnames = [row[0] for row in cur.description]
        coltypes = [row[1] for row in cur.description]
        coldisplay_sz = [row[2] for row in cur.description]
        colinternal_sz = [row[3] for row in cur.description]
        colprecision = [row[4] for row in cur.description]
        colscale = [row[5] for row in cur.description]
        colnullok = [row[6] for row in cur.description]
    print("Column names     : ",colnames)
    print("Column types     : ",coltypes)
    print("Display Size     : ",coldisplay_sz)
    print("Internal Size    : ",colinternal_sz)
    print("Column precision : ",colprecision)
    print("Column Scale     : ",colscale)
    print("Null OK?         : ",colnullok)

It can be used like this:


if __name__ == '__main__':
    c=cx_Oracle.Connection
    stmt="select name,value from v$parameter where name like 'log_archive%' and name not like 'log_archive_dest%' "
    try:
        c=connectToOracle("192.168.99.2:1521/orcl","sys","oracle",mode=cx_Oracle.SYSDBA)
        curs=executeStmt(c,stmt)
        describeCursor(curs)
...
        curs.close()
    except cx_Oracle.DatabaseError as ex:
        err, =ex.args
        print("Error code    = ",err.code)
        print("Error Message = ",err.message)
        os._exit(1)
    c.close()

And will produce this kind of result:


Column names     :  ['NAME', 'VALUE']
Column types     :  [<type 'cx_Oracle.STRING'>, <type 'cx_Oracle.STRING'>]
Display Size     :  [80, 4000]
Internal Size    :  [80, 4000]
Column precision :  [None, None]
Column Scale     :  [None, None]
Null OK?         :  [1, 1]

 

Get results of the cursor execution

Once the cursor executed, you can fetch your cursor row by row, you can fetch a set of n rows, or fetch all the rows.

Fetching the cursor

To do that, you have to use these function (from cursor)

  • fetchone() will return a list of one tuple
  • fetchmany(n) will return¬† a list of n tuples
  • fetchall() will return a list of n tuples (n is equal to the number of rows in the cursor)

for example, this piece of code:





<pre>if __name__ == '__main__':
    c=cx_Oracle.Connection
    stmt="select name,value from v$parameter where name like 'log_archive%' and name not like 'log_archive_dest%' "
    try:
        c=connectToOracle("192.168.99.2:1521/orcl","sys","oracle",mode=cx_Oracle.SYSDBA)
        curs=executeStmt(c,stmt)
        r=curs.fetchmany(2)
        print("R=",r)
        curs.close()
    except cx_Oracle.DatabaseError as ex:
        err, =ex.args
        print("Error code    = ",err.code)
        print("Error Message = ",err.message)
        os._exit(1)
    c.close()</pre>

will produce this result:

R= [('log_archive_start', 'FALSE'), ('log_archive_duplex_dest', None)]

 

Displaying the cursor results

Once the cursor has been executed and the rows fetched, you can use numpy or panda to work on the data.

Numpy

Numpy is a library which is primarly used for scientific computing. So, it’s not the best if your resultset is made of string datatypes.

A better way to proceed is:

  1. fetch your cursor
  2. convert the result into a numpy array. And decribe the types of your array.

For example:


if __name__ == '__main__':
    c=cx_Oracle.Connection
    stmt="select SIZE_FOR_ESTIMATE,SIZE_FACTOR,ESTD_PHYSICAL_READS from v$db_cache_advice"
    try:
        c=connectToOracle("192.168.99.2:1521/orcl","sys","oracle",mode=cx_Oracle.SYSDBA)
        curs=executeStmt(c,stmt)
        # Fetch all rows from the cursor
        r=curs.fetchall()
        # convert the r variable into a numpy array and describe it
        n=np.array(r,dtype=[('SIZE_FOR_ESTIMATE','float64'),('SIZE_FACTOR','float64'),('ESTD_PHYSICAL_READS','float64')])
        print("ndim=",n.ndim)
        print("n=",n)
        curs.close()
    except cx_Oracle.DatabaseError as ex:
        err, =ex.args
        print("Error code    = ",err.code)
        print("Error Message = ",err.message)
        os._exit(1)
    c.close()

This piece of code will produce the result above:

n= [(  96., 0.0896, 9690.) ( 192., 0.1791, 9690.) ( 288., 0.2687, 9690.)
 ( 384., 0.3582, 9690.) ( 480., 0.4478, 9690.) ( 576., 0.5373, 9690.)
 ( 672., 0.6269, 9690.) ( 768., 0.7164, 9690.) ( 864., 0.806 , 9690.)
 ( 960., 0.8955, 9690.) (1056., 0.9851, 9690.) (1072., 1.    , 9690.)
 (1152., 1.0746, 9690.) (1248., 1.1642, 9690.) (1344., 1.2537, 9690.)
 (1440., 1.3433, 9690.) (1536., 1.4328, 9690.) (1632., 1.5224, 9690.)
 (1728., 1.6119, 9690.) (1824., 1.7015, 9690.) (1920., 1.791 , 9690.)]

Note: If you want to get more information about type conversion into numpy data type see: 

 

A better way to proceed, specially if you want to execute some joins between two datasets is to use Panda.

Panda

Panda is a python library which provide easy to use data structures. It uses a central data structure named dataframe with which you can execute filters, joins etc.

When you decide to create panda dataframes from data coming from Oracle (or another database, CSV file, JSON etc.), you have to use panda function written specifically for this purpose. And in the case of an Oracle database, you have a function read_sql() that can be executed with a sql statement given in parameter. It will create a panda dataframe and then you will be able to proceed this structure in your python script.

One advantage of panda dataframes is in its data types because they are converted directly depending on the types returned by the cursor, and you don’y need to create a cursor, execute it and then fetch the rows … everything is automatic.

But let’s have a look to a piece of code (I will publish the complete script to show we don’t need explicit cursors etc.):


from __future__ import print_function

import cx_Oracle
import os
import pandas as pd


oh="D:/tools/Oracle/instantclient_12_2_x8664"
os.environ["ORACLE_HOME"]=oh
os.environ["PATH"]=oh+os.pathsep+os.environ["PATH"]
os.environ["NLS_LANG"]="AMERICAN_AMERICA.AL32UTF8"


def connectToOracle(url, username, password, mode=None):
    if mode is not None:
        connection = cx_Oracle.Connection (user=username, password=password, dsn=url, mode=mode)
    else:
        connection = cx_Oracle.Connection (user=username, password=password, dsn=url)
    return connection

# main
if __name__ == '__main__':
    c=cx_Oracle.Connection

    stmt="select SIZE_FOR_ESTIMATE,SIZE_FACTOR,ESTD_PHYSICAL_READS from v$db_cache_advice"
    try:
        c=connectToOracle("192.168.99.2:1521/orcl","sys","oracle",mode=cx_Oracle.SYSDBA)
        dataframe=pd.read_sql(stmt,con=c)
        print("------\nDF : \n ",dataframe)
        print("------\nDF Data types : \n",dataframe.dtypes)
        print("------\nDF Filtered   : \n",dataframe[dataframe['SIZE_FOR_ESTIMATE']>1440])
    except cx_Oracle.DatabaseError as ex:
        err, =ex.args
        print("Error code    = ",err.code)
        print("Error Message = ",err.message)
        os._exit(1)
    c.close()

We just have to instantiate a cx_Oracle connection, and then use panda read_sql function to get a panda dataframe and then process it.

In the previous basic example, I printed it, its datatypes and then run a filter which produces this kind of results:

------
DF : 
      SIZE_FOR_ESTIMATE  SIZE_FACTOR  ESTD_PHYSICAL_READS
0                  96       0.0896                 9766
1                 192       0.1791                 9766
2                 288       0.2687                 9766
3                 384       0.3582                 9766
4                 480       0.4478                 9766
5                 576       0.5373                 9766
6                 672       0.6269                 9766
7                 768       0.7164                 9766
8                 864       0.8060                 9766
9                 960       0.8955                 9766
10               1056       0.9851                 9766
11               1072       1.0000                 9766
12               1152       1.0746                 9766
13               1248       1.1642                 9766
14               1344       1.2537                 9766
15               1440       1.3433                 9766
16               1536       1.4328                 9766
17               1632       1.5224                 9766
18               1728       1.6119                 9766
19               1824       1.7015                 9766
20               1920       1.7910                 9766
------
DF Data types : 
 SIZE_FOR_ESTIMATE        int64
SIZE_FACTOR            float64
ESTD_PHYSICAL_READS      int64
dtype: object
------
DF Filtered   : 
     SIZE_FOR_ESTIMATE  SIZE_FACTOR  ESTD_PHYSICAL_READS
16               1536       1.4328                 9766
17               1632       1.5224                 9766
18               1728       1.6119                 9766
19               1824       1.7015                 9766
20               1920       1.7910                 9766

More information on panda dataframes and their capacities is available at the following url: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html

In a next blogpost (probably shorter than this one), I will explain on how to use bind variables in statement.

That’s it for today !!! ūüėČ

 

 

Advertisements

Connecting Python to an Oracle Database

I decided to write a new series of blogposts related to python programming language and its interactions with Oracle.

Python is a powerful scripting language with a lot of packages that can be installed for various stuff (scientific computing, dataviz, machine learning, deep learning etc.).

In my case, I used python for many things and specially for running my sql tuning scripts on an Oracle database, and then present the results with beautiful graphs.

This first blogpost will present how I use python on my laptop and how to install the required package to connect an Oracle Database.

 

1. Work Environment

I used python on my MS Windows laptop, and I used two versions of python depending on what I have to do or packages I will have to use.

So I installed two distributions of python :

You are free to use the version of Python you want to use and/or bundled with the package installer of your choice.

Next, I use a very well known python IDE name PyCharm (https://www.jetbrains.com/pycharm/download). PyCharm is released with 2 versions : a professional one (not free) and a free distribution (named community edition). The community edition is enough for my needs.

In PyCharm, you can run what is called a virtual-environment which is a kind of clone of your python installation where you will be able to deploy the package you want.

That can be assimilated to a “project” because in PyCharm, when you create a new project, you have to configure an interpreter, and pyCharm will automatically create a new virtual environment (default) based on the interpreter you chose :

Once the project has been created, we have to install the package required to connect to an Oracle Database : cx_Oracle.

In your pyCharm project, you have to open the project settings (in the “File” Menu) and then click on the “Project Interpreter” sub menu. The packages installed in your virtual environment appear.

Now, it’s easy to add a package by clicking on the “+” icon and search for cx_oracle package, and click on Install Package to install it. (For compatibility matrix, visit¬†https://oracle.github.io/python-cx_Oracle/)

Don’t forget to install an Oracle Client (Instant client or full client) and configure your windows environment with ORACLE_HOME and PATH environment variables. In the example below, I decided to set the environment variable in the script (because I have many clients installed on my laptop) :


from __future__ import print_function

import cx_Oracle
import os

oh="D:/tools/Oracle/instantclient_12_2_x8664"
os.environ["ORACLE_HOME"]=oh
os.environ["PATH"]=oh+os.pathsep+os.environ["PATH"]

print("Running tests for cx_Oracle version", cx_Oracle.version,"built at", cx_Oracle.buildtime)
print("File:", cx_Oracle.__file__)
print("Client Version:", ".".join(str(i) for i in cx_Oracle.clientversion()))

If this piece of code runs fine … you have correctly configured your environnent.

 

2. Connect to an Oracle database

Once your development environment configured, connecting a python script to an Oracle Database is really easy.

You just have to execute Connection constructor from cx_Oracle and then you will get a connection object. Of course, you have to catch the correct exception if an error occured during Connection call:


from __future__ import print_function

import cx_Oracle
import os

oh="D:/tools/Oracle/instantclient_12_2_x8664"
os.environ["ORACLE_HOME"]=oh
os.environ["PATH"]=oh+os.pathsep+os.environ["PATH"]
os.environ["NLS_LANG"]="AMERICAN_AMERICA.AL32UTF8"

def printConnectionAttr(connection):
    if connection is not None and isinstance(connection,cx_Oracle.Connection):
        print("Data Source Name  = ",connection.dsn)
        a="true" if connection.autocommit==1 else "False"
        print("Autocommit         = ",a)
        print("Session Edition    = ",connection.edition)
        print("Encoding           = ",connection.encoding)
        print("National Encoding  = ",connection.nencoding)
        print("Logical Tx Id      = ",connection.ltxid)
        print("Server version     = ",connection.version)

def connectToOracle(url, username, password, mode=None):
    if mode is not None:
        connection = cx_Oracle.Connection (user=username, password=password, dsn=url, mode=mode)
    else:
        connection = cx_Oracle.Connection (user=username, password=password, dsn=url)
    
    return connection


# main
if __name__ == '__main__':
    c=cx_Oracle.Connection
    try:
        c=connectToOracle("192.168.99.2:1521/orcl","sys","oracle",mode=cx_Oracle.SYSDBA)
    except cx_Oracle.DatabaseError as ex:
        err, =ex.args
        print("Error code    = ",err.code)
        print("Error Message = ",err.message)
        os._exit(1)

    printConnectionAttr(c)
    c.close()

 

This piece of code will connect to an Oracle Database and will print the connection details:

 

Data Source Name  =  192.168.99.2:1521/orcl
Autocommit         =  False
Session Edition    =  None
Encoding           =  UTF-8
National Encoding  =  UTF-8
Logical Tx Id      =  
Server version     =  12.1.0.2.0

Ok, so that’s it for this first part … in a next blog post, I will present how to execute a basic statement with Python and Cx_Oracle

 

Oracle 12cR1, Shutdown abort of a PDB seems to perform commit

A rapid post to show you a little thing I detect today.

In an oracle pluggable database, syntaxes to control them are :

alter pluggable database open
alter pluggable database open read write
alter pluggable database open read only
alter pluggable database open restrict
alter pluggable database close
alter pluggable database close immediate

But if you are an experienced oracle dba, you usually use STARTUP and SHUTDOWN commands and these ones are still available is a PDB.

To close a PDB, SHUTDOWN and SHUTDOWN IMMEDIATE makes sense, but SHUTDOWN ABORT doesn’t because the transactional layer is managed by the root container. But SHUTDOWN ABORT seems to be functional in a PDB context with a strange behaviour.

[oracle@oel64-12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 10 20:47:08 2014

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

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

SQL> alter session set container=ORCL_PDB;

Session altered.

SQL> alter session set current_schema=HR;

Session altered.

SQL> select * from regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

SQL> update regions set region_name=region_name||'*';

4 rows updated.

SQL> shutdown abort;
Pluggable Database closed.
So, if you are an Oracle DBA with a little bit of oracle knowledge and if I ask you what will be the content of the REGIONS table, you will answer me that each region_name will not have any * at the end.
But ….
SQL> connect / as sysdba
Connected.
SQL> alter session set container=ORCL_PDB;

Session altered.

SQL> startup
Pluggable Database opened.
SQL> show con_name

CON_NAME
------------------------------
ORCL_PDB
SQL> alter session set current_schema=HR;

Session altered.

SQL> select * from regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe*
         2 Americas*
         3 Asia*
         4 Middle East and Africa*
The transaction has been commited even with the shutdown abort command !
Stranger … if you try to do that with a SHUTDOWN IMMEDIATE, the transaction is committed too !
Ok, It’s my fault … I have to write correct statement with the official syntax … let’do it !
[oracle@oel64-12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 10 20:58:41 2014

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

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

SQL> alter session set container=ORCL_PDB;

Session altered.

SQL> alter session set current_schema=HR;

Session altered.

SQL> select * from regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

SQL> update regions set region_name=region_name||'*';

4 rows updated.

SQL> alter pluggable database close immediate;

Pluggable database altered.
Now the result:
SQL> alter pluggable database orcl_pdb open;

Pluggable database altered.

SQL> alter session set container=orcl_pdb;

Session altered.

SQL> alter session set current_schema=HR;

Session altered.

SQL> select * from regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe*
         2 Americas*
         3 Asia*
         4 Middle East and Africa*
Same problem …
Let’s see what’s the official doc say:
IMMEDIATE If you specify the optional IMMEDIATE keyword, then this clause is the PDB equivalent of the SQL*Plus SHUTDOWN command with the immediate mode. Otherwise, the PDB is shut down with the normal mode.
So the pluggable database should have been closed with the immediate behaviour so with a rollback of my transaction, or throw an ORA-01097: cannot shutdown while in a transaction – commit or rollback first … but it’s not the case, worse it acts like an implicit commit.
Fortunately, if you perform the shutdown from another session, the transaction is correctly rolled back.
I don’t know if it’s a bug (I did’nt find anything in MOS) or a feature … but it’s weird !

Oracle 12c invisible columns … behind the scene

In Oracle 12c, there’s a new feature called table’s invisible columns. As indexes in oracle 11g, you can now makes columns visible or invisible.

This feature has a strange behavior that we will see later and see how it really works.

First, I created a table with 3 columns:

SQL> create table test(a number, b number, c number);

Table created.

SQL> insert into test(a,b,c) values(1,2,3);

1 row created.

SQL> desc test
 Name                    Null?    Type
 ----------------------- -------- ----------------
 A                                NUMBER
 B                                NUMBER
 C                                NUMBER
And then, I modified the table to transform the B column as invisible:
SQL> alter table test modify (b invisible);

Table altered.

SQL> desc test
 Name                    Null?    Type
 ----------------------- -------- ----------------
 A                                NUMBER
 C                                NUMBER

SQL> select * from test;

         A          C
---------- ----------
         1          3
If we tried to specifically query the B column, data appear:
SQL> select a,b,c from test;

         A          B          C
---------- ---------- ----------
         1          2          3
If we have a look deep inside the block, we can see that the visibility of a specific column is defined as the dictionary level and not at the block level:
SQL> select dbms_rowid.rowid_relative_fno(rowid) File#,
  2  dbms_rowid.rowid_block_number(rowid) Block#
  3  from test;

     FILE#     BLOCK#
---------- ----------
         1      99241

SQL> alter system dump datafile 1 block 99241;

System altered.

SQL> 
data_block_dump,data header at 0x7f59e1a66a5c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x7f59e1a66a5c
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f94
avsp=0x1f80
tosp=0x1f80
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1f94
block_row_dump:
tab 0, row 0, @0x1f94
tl: 12 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 02
col  1: [ 2]  c1 03 <<<<<<< OUR B COLUMN
col  2: [ 2]  c1 04
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 99241 maxblk 99241

SQL> select dump(a,16),dump(b,16), dump(c,16) from test;

DUMP(A,16)        DUMP(B,16)        DUMP(C,16)
----------------- ----------------- -----------------
Typ=2 Len=2: c1,2 Typ=2 Len=2: c1,3 Typ=2 Len=2: c1,4
Now, let’s modify the B column to be visible:
SQL> alter table test modify (b visible);

Table altered.

SQL> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 C                                                  NUMBER
 B                                                  NUMBER
Well, the columns order seems to have changed. What is more funny is when you try now to insert a new row without specifying the column, it takes the new order definition:
SQL> insert into test values(1,2,3);

1 row created.

SQL> select * from test;

         A          C          B
---------- ---------- ----------
         1          3          2
         1          2          3
Now, let’s dump the block (after a necessary checkpoint).
data_block_dump,data header at 0x7f59e1a66a5c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x7f59e1a66a5c
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f88
avsp=0x1f72
tosp=0x1f72
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1f94
0x14:pri[1]     offs=0x1f88
block_row_dump:
tab 0, row 0, @0x1f94
tl: 12 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 02
col  1: [ 2]  c1 03
col  2: [ 2]  c1 04
tab 0, row 1, @0x1f88
tl: 12 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 02
col  1: [ 2]  c1 04
col  2: [ 2]  c1 03
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 99241 maxblk 99241
We confirm here that column visibility and column order definition are not defined at the block level. So it may be at the dictionary level. Let’s have a closer look to the col$ system table:
SQL> select o.obj#,col#,segcol#,o.name object_name,c.name col_name
  2  from obj$ o, col$ c
  3  where o.obj#=c.obj#
  4  and o.name='TEST'
  5  /

      OBJ#       COL#    SEGCOL# OBJECT_NAME          COL_NAME
---------- ---------- ---------- -------------------- ------------------------------
     92056          1          1 TEST                 A
     92056          3          2 TEST                 B
     92056          2          3 TEST                 C
Column position in the segment (ie. in the block) is defined by the SEGCOL# column. COL# column defines the rank of the column when you perform a SELECT * or an INSERT without specifying the corresponding column. The COL# value can change depending on visibility modifications made on the column.
So be very careful about apps code that makes inserts without column definition (INSERT INTO t VALUES (val1,val2, … , valN)). This could trigger new errors or worse, involve data integrity errors (like in the previous example).

Write SQL statements on internal structures in multitenant databases.

In a multitenant configuration, if you are querying X$ structures and fixed tables (OBJ$, TAB$ etc.), you will face differents behaviour depending on the fact you are connected to the root container or to a pluggable database.

  • If you are connected to a root container (CDB$ROOT)
    • In fixed tables, for example OBJ$, there’s no CON_ID column defined in this table so this¬†will give you all objects for your root container, and not for all containers.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> desc obj$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#                                      NOT NULL NUMBER
 DATAOBJ#                                           NUMBER
 OWNER#                                    NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(128)
 NAMESPACE                                 NOT NULL NUMBER
 SUBNAME                                            VARCHAR2(128)
 TYPE#                                     NOT NULL NUMBER
 CTIME                                     NOT NULL DATE
 MTIME                                     NOT NULL DATE
 STIME                                     NOT NULL DATE
 STATUS                                    NOT NULL NUMBER
 REMOTEOWNER                                        VARCHAR2(128)
 LINKNAME                                           VARCHAR2(128)
 FLAGS                                              NUMBER
 OID$                                               RAW(16)
 SPARE1                                             NUMBER
 SPARE2                                             NUMBER
 SPARE3                                             NUMBER
 SPARE4                                             VARCHAR2(1000)
 SPARE5                                             VARCHAR2(1000)
 SPARE6                                             DATE
 SIGNATURE                                          RAW(16)
 SPARE7                                             NUMBER
 SPARE8                                             NUMBER
 SPARE9                                             NUMBER
    • X$ structures will give you information for all containers (ROOT$CDB, SEED and and all pluggable database). And there’s a CON_ID column defined in all those structures.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> desc x$ksppi
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CON_ID                                             NUMBER
 KSPPINM                                            VARCHAR2(80)
 KSPPITY                                            NUMBER
 KSPPDESC                                           VARCHAR2(255)
 KSPPIFLG                                           NUMBER
 KSPPILRMFLG                                        NUMBER
 KSPPIHASH                                          NUMBER

SQL> select c.name,KSPPINM
  2  from x$ksppi x, v$containers c
  3  where x.con_id=c.con_id and  KSPPINM='open_cursors';

NAME                           KSPPINM
------------------------------ ------------------------------
CDB$ROOT                       open_cursors
PDB$SEED                       open_cursors
PDB1                           open_cursors
PDB2                           open_cursors
  • If you are connected to a PDB
    • In fixed tables, for example OBJ$, there’s still no CON_ID, so this¬†will give you all objects on your PDB.
    • In X$ structures, there’s a CON_ID column, but if you are connected to a PDB, you will see only the data related to this PDB.
To summarize, static fixed tables contain data for the actual container (CDB$ROOT or PDB), but X$ structures contain data for all containers if you are connected to the CDB$ROOT container, and contain data related to the PDB you are connected to, in case of a PDB.
If you disassemble static views like CDB_TABLES, you will see a new function used to have data for all containers. This is CDB$VIEW function :
SQL> show con_id

CON_ID
------------------------------
1
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> select count(*) from obj$;

  COUNT(*)
----------
     91005

SQL> select con_id,count(*) from CDB$VIEW("SYS"."OBJ$") group by con_id order by 1;

    CON_ID   COUNT(*)
---------- ----------
         1      91005
         2      90708
         3      90960
         4      90948
 Ok, now we have the information for all our containers. But if we want to join an X$ Structure and a CDB$VIEW transformed object, time for execute this is too long:
select x.con_id,o.name,count(*)
from x$bh x, (select name,dataobj#,con_id from CDB$VIEW("SYS"."OBJ$")) o
where x.con_id=o.con_id
and o.dataobj#=x.obj
and o.name like 'T_PDB%'
group by x.con_id,o.name
/
... never ends :(
To execute it in a better time, I used query factorization with a WITH block, and forced materialization of it:
SQL> with o as (select /*+ MATERIALIZE */ name,dataobj#,con_id from CDB$VIEW("SYS"."OBJ$"))
  2  select x.con_id,o.name,count(*)
  3  from x$bh x,o
  4  where x.con_id=o.con_id
  5  and o.dataobj#=x.obj
  6  and o.name like 'T_PDB%'
  7  group by x.con_id,o.name
  8  /

    CON_ID NAME                   COUNT(*)
---------- -------------------- ----------
         4 T_PDB2                        9
         3 T_PDB1                        9

Elapsed: 00:00:02.40