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

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

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

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;

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>

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>

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

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');

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;

create index food_i on food(id);
create index inverted_i on inverted(colname,colvalue);

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>

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>