OraFAQ Articles

Subscribe to OraFAQ Articles feed
Home page of The Oracle Frequently Asked Questions Website. A valuable resource for all Oracle professionals! You may not get the Oracle company line, but you will surely get an answer to just about any question concerning the software giant and its products.
Updated: 20 min 30 sec ago

Recursive WITH, part III: IS_LEAF

Mon, 2016-06-13 14:25

The CONNECT BY syntax provides a useful pseudocolumn, CONNECT_BY_ISLEAF, which identifies leaf nodes in the data: it’s 1 when a row has no further children, 0 otherwise. In this post, I’ll look at emulating this pseudocolumn using recursive WITH.

read more

Recursive WITH, part II: Hierarchical queries

Fri, 2016-06-03 04:38

In my last post, I looked at using recursive WITH to implement simple recursive algorithms in SQL. One very common use of recursion is to traverse hierarchical data. I recently wrote a series of posts on hierarchical data, using Oracle’s CONNECT BY syntax and a fun example. In this post, I’ll be revisiting the same data using recursive WITH.

read more

Recursion with recursive WITH

Tue, 2016-05-24 04:03

I recently had the opportunity to talk with Tom Kyte (!), and in the course of our conversation, he really made me face up to the fact that the SQL syntax I use every day is frozen in time: I’m not making much use of the analytic functions and other syntax that Oracle has introduced since 8i.

read more

SQL*Plus error logging – New feature release 11.1

Fri, 2014-05-02 09:39

One of the most important things that a developer does apart from just code development is, debugging. Isn’t it? Yes, debugging the code to fix the errors that are raised. But, in order to actually debug, we need to first capture them somewhere. As of now, any application has it’s own user defined error logging table(s).

Imagine, if the tool is rich enough to automatically capture the errors. It is very much possible now with the new SQL*PLus release 11.1

A lot of times developers complain that they do not have privilege to create tables and thus they cannot log the errors in a user defined error logging table. In such cases, it’s a really helpful feature, at least during the unit testing of the code.

I made a small demonstration in SCOTT schema using the default error log table SPERRORLOG, hope this step by step demo helps to understand easily :

NOTE : SQL*Plus error logging is set OFF by default. So, you need to “set errorlogging on” to use the SPERRORLOG table.

SP2 Error

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc sperrorlog;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 USERNAME                                           VARCHAR2(256)
 TIMESTAMP                                          TIMESTAMP(6)
 SCRIPT                                             VARCHAR2(1024)
 IDENTIFIER                                         VARCHAR2(256)
 MESSAGE                                            CLOB
 STATEMENT                                          CLOB

SQL> truncate table sperrorlog;

Table truncated.

SQL> set errorlogging on;
SQL> selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
SQL> select timestamp, username, script, statement, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.27.29.000000 AM
SCOTT


TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.

ORA Error

SQL> truncate table sperrorlog;

Table truncated.

SQL> select * from dula;
select * from dula
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select timestamp, username, script, statement, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.36.08.000000 AM
SCOTT


TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

select * from dula
ORA-00942: table or view does not exist

Like shown above, you can capture PLS errors too.

If you want to execute it through scripts, you can do it like this, and later spool the errors into a file. I kept these three lines in the sperrorlog_test.sql file -

truncate table sperrorlog;
selct * from dual;
select * from dula;

SQL> @D:\sperrorlog_test.sql;

Table truncated.

SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
select * from dula
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select TIMESTAMP, SCRIPT, STATEMENT, MESSAGE from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.50.17.000000 AM

D:\sperrorlog_test.sql;
SP2-0734: unknown command beginning "D:\sperror..." - rest of line ignored.


TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.50.27.000000 AM
D:\sperrorlog_test.sql
selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.


TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

11-SEP-13 01.50.27.000000 AM
D:\sperrorlog_test.sql
select * from dula
ORA-00942: table or view does not exist

SQL>

Check Oracle documentation on SPERRORLOG.

In addition to above, if you want to be particularly specific about each session’s error to be spooled into a file you could do this -

SQL> set errorlogging on identifier my_session_identifier

Above mentioned IDENTIFIER keyword becomes a column in SPERRORLOG table. It would get populated with the string value “my_session_identifier”. Now you just need to do this -
SQL> select timestamp, username, script, statement, message
2 from sperrorlog
3 where identifier = 'my_session_identifier';

To spool the session specific errors into a file, just do this -

SQL> spool error.log
SQL> select timestamp, username, script, statement, message
2 from sperrorlog
3 where identifier = 'my_session_identifier';
SQL> spool off

articles: 

Finding gaps with analytic functions

Sun, 2014-01-12 06:20

Finding gaps is classic problem in PL/SQL. The basic concept is that you have some sort of numbers (like these: 1, 2, 3, 5, 6, 8, 9, 10, 15, 20, 21, 22, 23, 25, 26), where there’s supposed to be a fixed interval between the entries, but some entries could be missing. The gaps problem involves identifying the ranges of missing values in the sequence. For these numbers, the solution will be as follows:
START_GAP END_GAP
4 4
7 7
11 14
16 19
24 24

First, run the following code, to create tab1 table:


CREATE TABLE tab1
(
col1 INTEGER
);

Then, insert a few rows:


INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (2);
INSERT INTO tab1 VALUES (3);
INSERT INTO tab1 VALUES (5);
INSERT INTO tab1 VALUES (6);
INSERT INTO tab1 VALUES (8);
INSERT INTO tab1 VALUES (9);
INSERT INTO tab1 VALUES (10);
INSERT INTO tab1 VALUES (15);
INSERT INTO tab1 VALUES (20);
INSERT INTO tab1 VALUES (21);
INSERT INTO tab1 VALUES (22);
INSERT INTO tab1 VALUES (23);
INSERT INTO tab1 VALUES (25);
INSERT INTO tab1 VALUES (26);

COMMIT;

With data, you can take care of solving the gaps problem…

One of the most efficient solutions to the gaps problem involves using analytic functions (also known as window functions)


WITH aa AS
(SELECT col1 AS cur_value, LEAD (col1) OVER (ORDER BY col1) AS next_value
FROM tab1)
SELECT cur_value + 1 AS start_gap, next_value - 1 AS end_gap
FROM aa
WHERE next_value - cur_value > 1
ORDER BY start_gap

Using the LEAD function, you can return for each current col1 value (call it cur_value) the next value in the sequence (call it next_value). Then you can filter only pairs where the difference between the two is greater than the one.

articles: 

Inverted tables: an alternative to relational structures

Sun, 2013-09-08 03:52

The inverted table format can deliver fast and flexible query capabilities, but is not widely used. ADABAS is probably the most successful implementation, but how often do you see that nowadays? Following is a description of how to implement inverted structures within a relational database. All code run on Oracle Database 12c, release 12.1.0.1.

Consider this table and a few rows, that describe the contents of my larder:

create table food(id number,capacity varchar2(10),container varchar2(10),item varchar2(10));
insert into food values(1,'large','bag','potatoes');
insert into food values(2,'small','box','carrots');
insert into food values(3,'medium','tin','peas');
insert into food values(4,'large','box','potatoes');
insert into food values(5,'small','tin','carrots');
insert into food values(6,'medium','bag','peas');
insert into food values(7,'large','tin','potatoes');
insert into food values(8,'small','bag','carrots');
insert into food values(9,'medium','box','peas');

The queries I run against the table might be "how many large boxes have I?" or "give me all the potatoes, I don't care about how they are packed". The idea is that I do not know in advance what columns I will be using in my predicate: it could be any combination. This is a common issue in a data warehouse.
So how do I index the table to satisfy any possible query? Two obvious possibilities:
First, build an index on each column, and the optimizer can perform an index_combine operation on whatever columns happen to be listed in the predicate. But that means indexing every column - and the table might have hundreds of columns. No way can I do that.
Second, build a concatenated index across all the columns: in effect, use an IOT. That will give me range scan access if any of the predicated columns are in the leading edge of the index key followed by filtering on the rest of the predicate. Or if the predicate does not include the leading column(s), I can get skip scan access and filter. But this is pretty useless, too: there will be wildly divergent performance depending on the predicate.
The answer is to invert the table:
create table inverted(colname varchar2(10),colvalue varchar2(10),id number);
insert into inverted select 'capacity',capacity,id from food;
insert into inverted select 'container',container,id from food;
insert into inverted select 'item',item,id from food;

Now just one index on each table can satisfy all queries:
create index food_i on food(id);
create index inverted_i on inverted(colname,colvalue);

To retrieve all the large boxes:
orclz> set autotrace on explain
orclz> select * from food where id in
  2  (select id from inverted where colname='capacity' and colvalue='large'
  3  intersect
  4  select id from inverted where colname='container' and colvalue='box');

        ID CAPACITY   CONTAINER  ITEM
---------- ---------- ---------- ----------
         4 large      box        potatoes


Execution Plan
----------------------------------------------------------
Plan hash value: 1945359172

---------------------------------------------------------------------------------
| Id  | Operation                                | Name       | Rows  | Bytes | C
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |            |     3 |   141 |
|   1 |  MERGE JOIN                              |            |     3 |   141 |
|   2 |   TABLE ACCESS BY INDEX ROWID            | FOOD       |     9 |   306 |
|   3 |    INDEX FULL SCAN                       | FOOD_I     |     9 |       |
|*  4 |   SORT JOIN                              |            |     3 |    39 |
|   5 |    VIEW                                  | VW_NSO_1   |     3 |    39 |
|   6 |     INTERSECTION                         |            |       |       |
|   7 |      SORT UNIQUE                         |            |     3 |    81 |
|   8 |       TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED   |     3 |    81 |
|*  9 |        INDEX RANGE SCAN                  | INVERTED_I |     3 |       |
|  10 |      SORT UNIQUE                         |            |     3 |    81 |
|  11 |       TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED   |     3 |    81 |
|* 12 |        INDEX RANGE SCAN                  | INVERTED_I |     3 |       |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ID"="ID")
       filter("ID"="ID")
   9 - access("COLNAME"='capacity' AND "COLVALUE"='large')
  12 - access("COLNAME"='container' AND "COLVALUE"='box')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

orclz>

Or all the potatoes:
orclz> select * from food where id in
  2  (select id from inverted where colname='item' and colvalue='potatoes');

        ID CAPACITY   CONTAINER  ITEM
---------- ---------- ---------- ----------
         1 large      bag        potatoes
         4 large      box        potatoes
         7 large      tin        potatoes


Execution Plan
----------------------------------------------------------
Plan hash value: 762525239

---------------------------------------------------------------------------------
| Id  | Operation                              | Name       | Rows  | Bytes | Cos
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |            |     3 |   183 |
|   1 |  NESTED LOOPS                          |            |       |       |
|   2 |   NESTED LOOPS                         |            |     3 |   183 |
|   3 |    SORT UNIQUE                         |            |     3 |    81 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED   |     3 |    81 |
|*  5 |      INDEX RANGE SCAN                  | INVERTED_I |     3 |       |
|*  6 |    INDEX RANGE SCAN                    | FOOD_I     |     1 |       |
|   7 |   TABLE ACCESS BY INDEX ROWID          | FOOD       |     1 |    34 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("COLNAME"='item' AND "COLVALUE"='potatoes')
   6 - access("ID"="ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan

orclz>

Of course, consideration needs to be given to handling more complex boolean expressions; maintaining the inversion is going to take resources; and a query generator has to construct the inversion code and re-write the queries. But In principle, this structure can deliver indexed access for unpredictable predicates of any number of any columns, with no separate filtering operation. Can you do that with a normalized star schema? I don't think so.
I hope this little thought experiment has stimulated the little grey cells, and made the point that relational structures are not always optimal for all problems.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com

articles: 

Three impossibilities with partitioned indexes

Sun, 2013-09-01 11:22

There are three restrictions on indexing and partitioning: a unique index cannot be local non-prefixed; a global non-prefixed index is not possible; a bitmap index cannot be global. Why these limitations? I suspect that they are there to prevent us from doing something idiotic.

This is the table used for all examples that follow:

CREATE TABLE EMP
      (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
       ENAME VARCHAR2(10),
       JOB VARCHAR2(9),
       MGR NUMBER(4),
       HIREDATE DATE,
       SAL NUMBER(7,2),
       COMM NUMBER(7,2),
       DEPTNO NUMBER(2) )
PARTITION BY HASH (EMPNO) PARTITIONS 4;

the usual EMP table, with a partitioning clause appended. It is of course a contrived example. Perhaps I am recruiting so many employees concurrently that a non-partitioned table has problems with buffer contention that can be solved only with hash partitioning.

Why can't I have a local non-prefixed unique index?
A local non-unique index is no problem, but unique is not possible:

orclz> create index enamei on emp(ename) local;

Index created.

orclz> drop index enamei;

Index dropped.

orclz> create unique index enamei on emp(ename) local;
create unique index enamei on emp(ename) local
                              *
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

You cannot get a around the problem by separating the index from the constraint (which is always good practice):

orclz> create index enamei on emp(ename) local;

Index created.

orclz> alter table emp add constraint euk unique (ename);
alter table emp add constraint euk unique (ename)
*
ERROR at line 1:
ORA-01408: such column list already indexed


orclz>

So what is the issue? Clearly it is not a technical limitation. But if it were possible, consder the implications for performance. When inserting a row, a unique index (or a non-unique index enforcing a unique constraint) must be searched to see if the key value already exists. For my little four partition table, that would mean four index searches: one of each local index partition. Well, OK. But what if the table were range partitioned into a thousand partitions? Then every insert would have to make a thousand index lookups. This would be unbelievably slow. By restricting unique indexes to global or local prefixed, Uncle Oracle is ensuring that we cannot create such an awful situation.

Why can't I have a global non-prefixed index?
Well, why would you want one? In my example, perhaps you want a global index on deptno, partitioned by mgr. But you can't do it:

orclz> create index deptnoi on emp(deptno) global partition by hash(mgr) partitions 4;
create index deptnoi on emp(deptno) global partition by hash(mgr) partitions 4
                                                                *
ERROR at line 1:
ORA-14038: GLOBAL partitioned index must be prefixed


orclz>
This index, if it were possible, might assist a query with an equality predicate on mgr and a range predicate on deptno: prune off all the non-relevant mgr partitions, then a range scan. But exactly the same effect would be achieved by using global nonpartitioned concatenated index on mgr and deptno. If the query had only deptno in the predicate, it woud have to search each partition of the putative global partitioned index, a process which would be just about identical to a skip scan of the nonpartitioned index. And of course the concatenated index could be globally partitioned - on mgr. So there you have it: a global non-prefixed index would give you nothing that is not available in other ways.

Why can't I have a global partitioned bitmap index?
This came up on the Oracle forums recently, https://forums.oracle.com/thread/2575623
Global indexes must be prefixed. Bearing that in mind, the question needs to be re-phrased: why would anyone ever want a prefixed partitioned bitmap index? Something like this:

orclz>
orclz> create bitmap index bmi on emp(deptno) global partition by hash(deptno) partitions 4;
create bitmap index bmi on emp(deptno) global partition by hash(deptno) partitions 4
                                       *
ERROR at line 1:
ORA-25113: GLOBAL may not be used with a bitmap index

orclz>

If this were possible, what would it give you? Nothing. You would not get the usual benefit of reducing contention for concurrent inserts, because of the need to lock entire blocks of a bitmap index (and therefore ranges of rows) when doing DML. Range partitioning a bitmap index would be ludicrous, because of the need to use equality predicates to get real value from bitmaps. Even with hash partitions, you would not get any benefit from partition pruning, because using equality predicates on a bitmap index in effect prunes the index already: that is what a bitmap index is for. So it seems to me that a globally partitioned bitmap index would deliver no benefit, while adding complexity and problems of index maintenance. So I suspect that, once again, Uncle Oracle is protecting us from ourselves.

Is there a technology limitation?
I am of course open to correction, but I cannot see a technology limitation that enforces any of these three impossibilities. I'm sure they are all technically possible. But Oracle has decided that, for our own good, they will never be implemented.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com

articles: 

Are older releases of the database really unsupported?

Sun, 2013-07-28 05:19

I see posts on Oracle related forums about various releases (anything that isn't 11.x or 12.x) being "unsupported". This is wrong. Of course you should upgrade any 9i or 10g databases, but you don't have to.

Oracle Corporation's lifetime support policy is documented here,
Lifetime Support Policy
take a look, and you'll see that release 10.2 was in premier support until end July 2010 when it went into extended support. At end July 2013, it goes into sustaining support. Sustaining support will continue indefinitely. Even release 8.1.7 will have sustaining support indefinitely.
So what is sustaining support? That is documented here,
Lifetime support benefits
To summarize, extended support gives you everything you are likely to need. What you do not get is certification against new Oracle products or new third party products (principally, operating systems). But does that matter? I don't think so. For example, release 11.2.0.3 (still in premier support) is not certified against Windows 8, but it works fine.
Sustaining support has a more significant problem: no more patches. Not even patches for security holes, or changes in regulatory requirements. The security patch issue may of course be serious, but regulatory issues are unlikely to matter (this is a database, not a tax management system.) Think about it: 10g has been around for many years. It is pretty well de-bugged by now. If you hit a problem with no work around, you are pretty unlucky. Sustaining support gives you access to technical support, available patches, software, and documentation. That is all most sites will ever need.
Right now, I am working on a 9.2.0.8 database. It cannot be upgraded because the application software is written by a company that does not permit a database upgrade. Why not? Well, the reason may be commercial: they have a replacement product that is supported on newer databases. But that is nothing to do with me. The database works, the software works. Making it work better is a challenge - but that is what a DBA is paid to do. Don't just write it off as "unsupported".
Of course I am not suggesting that users should not upgrade to current releases - but upgrades are a huge project, and can have major implications. Running out dated software is silly, unless you have an irrefutable reason for so doing. The lack of security patches make you vulnerable to data loss. The lack of regulatory patches may make it illegal. The lack of newer facilities will be restricting the utility of the system. You may be losing money by not taking of advantage of changes of newer technology that can better exploit your hardware.
If anyone is looking for consulting support to upgrade their database - my boss will be happy to give you a quote. But I won't refuse to support you in the meantime.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com

articles: 

12C: IN DATABASE ARCHIVING

Sun, 2013-07-28 03:42

In this post, I will demonstrate a new feature introduced in 12c : In database archiving. It enables you to archive rows within a table by marking them as invisible. This is accomplshed  by means of a hidden column ORA_ARCHIVE_STATE. These invisible rows are not visible to the queries but if needed, can be viewed , by setting a session parameter ROW ARCHIVAL VISIBILITY.

Overview:

-- Create test user uilm, tablespace ilmtbs
-- Connect as user uilm
-- create and populate test table (5 rows) ilmtab with row archival clause
-- Note that the table has an additional column ORA_ARCHIVE_STATE automatically created   and has the default value of 0 (indicates that row is active)
-- Note that this column is not visible when we describe the table or simply issue select * from ...
-- We need to access data dictionary to view the column
-- Make two  rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value.
-- Check that inactive rows are not visible to query
-- Set the parameter ROW ARCHIVAL VISIBILITY  = all to see inactive rows also
-- Set the parameter ROW ARCHIVAL VISIBILITY  = active to hide inactive rows
-- Issue an insert into ... select * and check that only 3 visible rows are inserted
-- Set the parameter ROW ARCHIVAL VISIBILITY  = all to see inactive rows also
-- Issue an insert into ... select * and check that all the rows are inserted but ORA_ARCHIVE_STATE    is not propagated in inserted rows
-- Disable row archiving in the table and check that column ORA_ARCHIVE_STATE is automatically dropped
-- drop tablespace ilmtbs and user uilm

Implementation :

-- Create test user, tablespace and test table
SQL> conn sys/oracle@em12c:1523/pdb1 as sysdba
sho con_name

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

SQL> set sqlprompt PDB1>

PDB1>create tablespace ilmtbs datafile '/u02/app/oracle/oradata/cdb1/pdb1/ilmtbs01.dbf' size 1m;
grant connect, resource, dba  to uilm identified by oracle;
alter user uilm default tablespace ilmtbs;

conn uilm/oracle@em12c:1523/pdb1
sho con_name

CON_NAME
------------------------------
PDB1
-- create table with "row archival clause"
PDB1>drop table ilmtab purge;
create table ilmtab (id number, txt char(15)) row archival;
insert into ilmtab values (1, 'one');
insert into ilmtab values (2, 'two');
insert into ilmtab values (3, 'three');
insert into ilmtab values (4, 'four');
insert into ilmtab values (5, 'five');
commit;
-- Note that the table has an additional column ORA_ARCHIVE_STATE automatically created    and has the default value of 0 (indicates that row is active)
PDB1>col ora_archive_state for a20
select id, txt, ora_archive_state from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             0
2 two             0
3 three           0
4 four            0
5 five            0
-- Note that this column is not visible when we describe the table or simply issue select * from ...
PDB1>desc ilmtab
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
TXT                                                CHAR(15)

PDB1>select * from ilmtab;

ID TXT
---------- ---------------
1 one
2 two
3 three
4 four
5 five
-- Since the column is invisible, let me try and make it visible
-- Note that Since the column is maintained by oracle itself, user can't modify its attributes
PDB1>alter table ilmtab modify (ora_archive_state visible);
alter table ilmtab modify (ora_archive_state visible)
*
ERROR at line 1:
ORA-38398: DDL not allowed on the system ILM column
-- We need to access data dictionary to view the column
-- Note that this column is shown as hidden and has not been generated by user
PDB1>col hidden for a7
col USER_GENERATED for 20
col USER_GENERATED for a20

select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
from user_tab_cols where table_name='ILMTAB';

TABLE_NAME  COLUMN_NAME          HID USER_GENERATED
----------- -------------------- --- --------------------
ILMTAB      ORA_ARCHIVE_STATE    YES NO
ILMTAB      ID                   NO  YES
ILMTAB      TXT                  NO  YES
-- We can make selected rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value.
This can be accomplished using update table... set ORA_ACRHIVE_STATE =
. <non-zero value>
. dbms_ilm.archivestatename(1)

-- Let's update row with id =1 with ORA_ARCHIVE_STATE=2
     and update row with id =2 with dbms_ilm.archivestatename(2)
PDB1>update ilmtab set ora_archive_state=2 where id=1;

update ilmtab set ora_archive_state= dbms_ilm.archivestatename(2) where id=2;
-- Let's check whether updates have been successful and hidden rows are not visible
PDB1>select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three           0
4 four            0
5 five            0
-- The updated rows are not visible!!
-- Quite logical since we have made the rows active and by default only active rows are visible

-- To see inactive rows also, we need to set the parameter ROW ARCHIVAL VISIBILITY  = all at session level
-- Note that the column ORA_ARCHIVE_STATE has been set to 1 for id =2 although we had set it to 2 using
dbms_ilm.archivestatename(2)
PDB1>alter session set ROW ARCHIVAL VISIBILITY  = all;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             2
2 two             1
3 three           0
4 four            0
5 five            0
-- Note that the column ORA_ARCHIVE_STATE has been set to 1 for id =2 although we had set it to 2 using    dbms_ilm.archivestatename(2)

-- Let's find out why
-- Note that The function dbms_ilm.archivestatename(n) returns only two values    0 for n=0 and 1 for  n <> 0
PDB1>col state0 for a8
col state1 for a8
col state2 for a8
col state3 for a8

select dbms_ilm.archivestatename(0) state0 ,dbms_ilm.archivestatename(1) state1,
dbms_ilm.archivestatename(2) state2,dbms_ilm.archivestatename(3) state3  from dual;

STATE0   STATE1   STATE2   STATE3
-------- -------- -------- --------
0        1        1        1
-- In order to make the inactive rows (id=1,2) hidden again, we need to set the parameter ROW ARCHIVAL VISIBILITY  = Active
PDB1>alter session set row archival visibility = active;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three           0
4 four            0
5 five            0
-- Let's issue an insert into ... select *
-- Note that only 3 new rows are visible
PDB1>insert into ilmtab select * from ilmtab;

select id, txt, ora_archive_state from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three           0
4 four            0
5 five            0
3 three           0
4 four            0
5 five            0

6 rows selected.
-- I want to check if hidden rows were also inserted
-- Let's check by making  hidden rows visible again
-- Note that only visible rows(id=3,4,5) were inserted
PDB1>alter session set row archival visibility=all;
select id, txt, ora_archive_state from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             2
2 two             1
3 three           0
4 four            0
5 five            0
3 three           0
4 four            0
5 five            0

8 rows selected.
-- Let's set row archival visibility = all and then again insert rows from ilmtab
-- Note that all the 8 rows are inserted but ORA_ARCHIVE_STATE ha not been copied    ORA_ARCHIVE_STATE <> 0 in only 2 records (id = 1,2) even now.
PDB1>alter session set row archival visibility=all;
insert into ilmtab select * from ilmtab;
select id, txt, ora_archive_state from ilmtab order by id;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             0
1 one             2
2 two             0
2 two             1
3 three           0
3 three           0
3 three           0
3 three           0
4 four            0
4 four            0
4 four            0
4 four            0
5 five            0
5 five            0
5 five            0
5 five            0

16 rows selected.
-- Disable row level archiving for the table
-- Note that as soon as row archiving is disabled, pseudo column ora_archive_state is dropped automatically
PDB1>alter table ilmtab no row archival;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ERROR at line 1:
ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier

PDB1>col hidden for a7
col USER_GENERATED for 20
col USER_GENERATED for a20

select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
from user_tab_cols where table_name='ILMTAB';

TABLE_NAME  COLUMN_NAME          HID USER_GENERATED
----------- -------------------- --- --------------------
ILMTAB      ID                   NO  YES
ILMTAB      TXT                  NO  YES
Note : Had we created this table using sys, we could not have disabled row archiving .

-- cleanup --
PDB1>conn sys/oracle@em12c:1523/pdb1 as sysdba
drop tablespace ilmtbs including contents and datafiles;
drop user uilm cascade;
References:

http://docs.oracle.com/cd/E16655_01/server.121/e17613/part_lifecycle.htm#VLDBG14154

----------------------------------------------------------------------------------------------------

Oracle 12c Index

----------------------------------------------------------------------------------------------

 

12c: CONNECTING TO PDB'S WITH SAME NAME

Sun, 2013-07-28 03:37

When you create a PDB, the database automatically creates and starts a service inside the CDB.The service has the same name as the PDB. It is possible that the name of the service will collide with an existing service name which is registered with the same listener. For example if two or more CDBs on the same computer system use the same listener, and the newly established PDB has the same service name as another PDB in these CDBs, then a collision occurs.

You must not attempt to operate a PDB that causes a collision with an existing service name.

I will demonstrate that a connection that specifies the default service name of a PDB can connect randomly to any of the PDBs with the same service name. To avoid incorrect connections, we should configure a separate listener for each CDB on a computer system.

Current scenario:

I have two CDB's (CDB1 and CDB2) on the same computer system.
Pluggable database PDB1 exists in both the CDB's CDB1 and CDB2

There are two listeners running in database home
listener1 on port 1523
listener2 on port 1524

Overview:

-- Register both the CDB's (and hence PDB's) with listener1 running on port 1523.
-- Verify that if we repeatedly connect to service PDB1, we are randomly connected to different pdb's (PDB1@CDB1 and PDB1@CDB2).
-- Register PDB1@CDB2 with listener2 on port 1524.
-- Verify that now we can connect to the right pdb

Implementation:

-- Register both the CDB's with listener1 running on port 1523

CDB1>alter system set local_listener='em12c.oracle.com:1523';

sho parameter local_listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string em12c.oracle.com:1523

CDB2>alter system set local_listener='em12c.oracle.com:1523';

sho parameter local_listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string em12c.oracle.com:1523
-- check that both the CDB's and PDB1 in both the CDB's are registered with listener1 (port 1523)

[oracle@em12c ~]$ lsnrctl stat listener1
(output trimmed)
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/em12c/listener1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1523)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "cdb1" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb2" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb2XDB" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 2 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Instance "cdb2", status READY, has 1 handler(s) for this service...
The command completed successfully
It can be seen that PDB PDB1 has same name in both the CDB's (CDB1 and CDB2) and default service for both the PDB's (PDB1@CDB1 and PDB1@CDB2) are registered with the listener on the same port (1523).

-- Verify that if we repeatedly connect to service PDB1, we are randomly connected to different pdb's (PDB1@CDB1 and PDB1@CDB2)

CDB1>conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb2

CDB1>conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb1

CDB1>conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb2

CDB1>conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb1
Hence, to connect to the right PDB, we should register different PDB's with listeners running on different ports.

-- Let's register PDB1@CDB2 with listener2 on port 1524

CDB2>alter system set local_listener='em12c.oracle.com:1524';

sho parameter local_listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string em12c.oracle.com:1524
-- check that CDB1 and pdb1@CDB1 are registered with listener1 (port 1523)

[oracle@em12c ~]$ lsnrctl stat listener1

(output trimmed)

Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/em12c/listener1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1523)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "cdb1" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
The command completed successfully
-- check that CDB2 and PDB1@CDB2 are registered with listener2 (port 1524)

[oracle@em12c ~]$ lsnrctl stat listener2

(output trimmed)

Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/em12c/listener2/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1524)))
Services Summary...
Service "cdb2" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb2XDB" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
The command completed successfully
-- Verify that now we can connect to the right pdb

-- connect to PDB1@CDB1 (listener1, port 1523)

SQL> conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb1

SQL> conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb1

-- connect to PDB1@CDB2 (listener2, port 1524)

SQL> conn system/oracle@em12c:1524/pdb1
sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb2

SQL> conn system/oracle@em12c:1524/pdb1
sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb2
Hence, it can be concluded that to avoid incorrect connections, we should configure a separate listener for each CDB on a computer system.
References:

http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_6009.htm
http://docs.oracle.com/cd/E16655_01/server.121/e17633/cdblogic.htm#CIHDEDCC
-------------------------------------------------------------------------------------