Data … as usual

All things about data by Laurent Leturgez

Category Archives: SQL

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.

data_block_dump,data header at 0x7f59e1a66a5c
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x7f59e1a66a5c
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1f94
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 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
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1f94
0x14:pri[1]     offs=0x1f88
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 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#, object_name, col_name
  2  from obj$ o, col$ c
  3  where o.obj#=c.obj#
  4  and'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
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


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

  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

SQL> show con_name

SQL> select count(*) from obj$;


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,,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 like 'T_PDB%'
group by x.con_id,
... 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,,count(*)
  3  from x$bh x,o
  4  where x.con_id=o.con_id
  5  and o.dataobj#=x.obj
  6  and like 'T_PDB%'
  7  group by x.con_id,
  8  /

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

Elapsed: 00:00:02.40

Oracle Database 12c released … some features

If you are an Oracle DBA and you don’t live on earth, you have not heard about Oracle Database 12c … which is finally released today.

For the moment, you can download it on otn ( and on edelivery ( Documentation is available here:

As a beta tester, I can now speak about some features of this release. Of course, I could write about multitenant database or Information Lifecycle Management … I prefer to write about “smaller” features … but very useful.

  • online datafile move

Before 12c, if you wanted to move a datafile (or rename it), you had to offline it and move it on the OS Side, and then modify the control file data by renaming file, and then online the datafile. Now in 12c … it becomes a little bit easier:

SQL> select file_name from dba_data_files;


SQL> alter database move datafile '/u02/oradata/orcl/users01.dbf' to '/u02/oradata/newdest/users01.dbf';

Database altered.

SQL> !ls /u02/oradata/newdest

That’s it 😉

  • write sql statement directly in RMAN
[oracle@oel ~]$ rman target /

Recovery Manager: Release - Production on Tue Jun 25 21:57:09 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1335655385)

RMAN> select dbid,name from v$database;

using target database control file instead of recovery catalog
---------- ---------
1335655385 ORCL
  • impdp in nologging mode

impdp utility have now many transformation functions that offer the opportunity to modify your data directly during the import process. Among these functions, you have a nologging option to reduce the redo information generated during impdp process (which was impossible with previous versions)


That’s it for today ! 🙂