PURGE DBA_RECYCLEBIN System Privilege in Oracle 12c

Oracle January 24, 2014

PURGE DBA_RECYCLEBIN  command empties the recyclebin at  database level i.e. purges the recyclebin for all users in the database.  Until 11g , you need to have powerful  SYSDBA system privilege to execute this command.  To some extent it made sense as this option was mainly used for upgrades or migration or to apply DST patch. One of focus areas of Oracle12c is separation of duties resulting in  new roles like SYSKM for wallet management, SYSBACKUP for backups and restore, SYSDG  for administration of Data Guard.

Along the same lines, We have new privilege  PURGE DBA_RECYCLEBIN that helps with implementing separation of duty. Users granted the PURGE DBA_RECYCLEBIN system privilege can purge the recyclebin for all users in the database using the PURGE DBA_RECYCLEBIN command.

Unified Auditing (Part-I)

Oracle January 22, 2014

Increased scrutiny of Organizations adherence to  regulatory guidance like SOX, Hippa & PCI DSS has made audit of   critical and privileged actions in the database more likely to be mandatory than optional. Therefore forcing database or software vendors to implement auditing with piecemeal approach. Oracle is no different with various  ways to audit  & various  locations  to store them in various formats.  For example some of the auditing options available with Oracle 11g are mandatory auditing, standard database auditing , Data Vault auditing & FGA and so on ….

Staying complaint is expensive , time consuming and complex. Some of the complexities involved with auditing has been simplified with unified auditing available with Oracle12c. In this blog , we will discuss about Unified auditing in 4 part series. Here is the 1st part.

So what is unified auditing ?
Unified Auditing is selective and effective auditing  of database actions to a single unified audit trail  inside of the database using policies and conditions.  Most of unified auditing features are implemented using DBMS_AUDIT_MGMT or Oracle Grid Control. DBMS_AUDIT_MGMT is not a new to some/most of us as it is available with Oracle11g. It is enhanced in Oracle12c to support unified auditing.  Unified auditing supports auditing of following components.

  1. FGA
  2. Real Application Security
  3. RMAN
  4. Database Vault
  5. Oracle Label Security
  6. Data Mining
  7. Data Pump
  8. SQL*Loader

My next question why do we need a unified auditing? What is wrong with existing auditing?  Why couldn’t we audit everything in the database and get done with it and make our auditors happy. What actually stopped us from auditing everything in the database? The main reasons were performance impact caused by auditing followed by security.  Now we have the burden on us to prove that all our audit is safe, correct and not tampered with. And finally where do I store all the audit data ; Getting additional storage for database itself is cumbersome…. Now you need space to store all of audit data.

Let us look at how unified auditing measures  itself against the 3 metrics: Performance, Security & Storage

Performance

As per Oracle, Unified auditing  results in  negligible overhead. Why ? Because it  is implemented in SGA using named Queues. Dequeing is continuous with each audit client (Example: RMAN, Oracle Label Security) having two separate queues so that the client can continue to write to the second queue while the first queue is being dequeued to database table by background process GEN0. if needed audit Data can also be manually flushed to the database using procedure FLUSH_UNIFIED_AUDIT_TRAIL.

Example: EXECUTE DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

As usual we have the choice of synchronous and asynchronous dequeuing. In  unified audit terms, they are called as Immediate write mode and queued write mode.

Immediate-Write mode

Audit records are immediately written to the trail and there is a performance overhead associated with this approach

Example: EXECUTE DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY (DBM_MS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,  DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_MODE);

Queued-Write mode(Default)

The named queue in the SGA is  dequeued periodically. In this mode , data could be lost after an instance crash and therefore its a trade-in  for data loss for performance. How cruel …. never can have everything

Example: EXECUTE DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY (DBM_MS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_MODE);

Summary: Performance is better mainly because of  queued-write mode architecture; Using immediate-write mode will cause performance impact but not as significant pre12c auditing.

 

 

Let us look at other metrics in Part-II of this series. I will post the link once  I am ready.

Collecting stats during bulk load

Oracle August 26, 2013

Starting with Oracle 12c, the default behavior of database is to gathers statistics during bulk loads like CTAS or INSERT SELECT.  However for some reason , if you want to go back to pre-12c behavior , you can do so with a new hint NO_GATHER_OPTIMIZER_STATISTICS hint.

See illustration of examples comparing Oracle11g and Oracle12c

Oracle 11g

SQL> SELECT * FROM V$VERSION;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
PL/SQL Release 11.2.0.2.0 – Production
CORE    11.2.0.2.0      Production
TNS for Linux Version 11.2.0.2.0 – Production
NLSRTL Version 11.2.0.2.0 – Production

SQL> CREATE TABLE MY_OBJECTS_NOSTATS AS SELECT * FROM MY_OBJECTS;

Table created.

SQL> CREATE TABLE MY_OBJECTS_STATS AS SELECT /*+ GATHER_OPTIMIZER_STATISTICS */  * FROM MY_OBJECTS; ==> Database is 11g, Hint is ignored

Table created.

SQL>  CREATE TABLE MY_OBJECTS_NOSTATS_HINT  AS SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */  * FROM MY_OBJECTS; ==> Database is 11g, Hint is ignored

Table created.

SQL> SELECT TABLE_NAME, LAST_ANALYZED , NUM_ROWS, BLOCKS FROM DBA_TABLES WHERE TABLE_NAME LIKE ‘MY_OBJECTS%’; ==> Database is 11g, Stats are not gathered for all tables

TABLE_NAME                     LAST_ANAL   NUM_ROWS     BLOCKS
—————————— ——— ———- ———-
MY_OBJECTS
MY_OBJECTS_STATS
MY_OBJECTS_NOSTATS
MY_OBJECTS_NOSTATS_HINT

Oracle12c

SQL> SELECT BANNER FROM V$VERSION;

BANNER
——————————————————————————–
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
PL/SQL Release 12.1.0.1.0 – Production
CORE    12.1.0.1.0      Production
TNS for Linux: Version 12.1.0.1.0 – Production
NLSRTL Version 12.1.0.1.0 – Production

SQL> CREATE TABLE MY_OBJECTS_NOSTATS AS SELECT * FROM MY_OBJECTS;

Table created.

SQL> CREATE TABLE MY_OBJECTS_STATS AS SELECT /*+ GATHER_OPTIMIZER_STATISTICS */  * FROM MY_OBJECTS; ==> Default behavior ;hint is of no help here.

Table created.

SQL> CREATE TABLE MY_OBJECTS_NOSTATS_HINT  AS SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */  * FROM MY_OBJECTS; ==> Hint instructing Oracle not to collect Stats

Table created.

SQL> SET LINESIZE 200
SQL> COLUMN TABLE_NAME FOR A40
SQL> SELECT TABLE_NAME, LAST_ANALYZED , NUM_ROWS, BLOCKS FROM DBA_TABLES WHERE TABLE_NAME LIKE ‘MY_OBJECTS%’; ==> Stats are gathered for all tables except table MY_OBJECTS_NOSTATS_HINT which was created with NO_GATHER_OPTIMIZER_STATISTICS hint

TABLE_NAME                               LAST_ANAL   NUM_ROWS     BLOCKS
—————————————————————————————————————
MY_OBJECTS                                26-AUG-13      90908       1561
MY_OBJECTS_NOSTATS                26-AUG-13      90908       1561
MY_OBJECTS_STATS                    26-AUG-13      90908       1561
MY_OBJECTS_NOSTATS_HINT

4 rows selected.

SQL>

TABLE ACCESS BY INDEX ROWID BATCHED

Oracle August 24, 2013

TABLE ACCESS BY INDEX ROWID BATCHED is new execution plan operation that helps improve performance.  It is generally used for range ( > or < ) queries.  For this new operation, Oracle selects few  ROWIDs from the index and then try to access the rows in blocks. This significantly reduces the number of times Oracle must access the blocks thereby improving performance.

Sample Execution plan

Execution Plan
———————————————————-
Plan hash value: 525114061

—————————————————————————————————————————-
| Id  | Operation                                                   | Name     | Rows   | Bytes   | Cost (%CPU)| Time
—————————————————————————————————————————–
|   0 | SELECT STATEMENT                                   |                 |    10  |  3020  |     3   (0)| 00:00:01
|*  1 |  COUNT STOPKEY                                      |                 |          |           |              |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | EMP          | 28012 |  8261K|     3   (0)| 00:00:01
|*  3 |    INDEX RANGE SCAN                                | EMP_SAL_I |          |           |     2   (0)| 00:00:01
—————————————————————————————————————————–

Oracle12c:Temporal Validity (Part-1)

Oracle August 23, 2013 1 Comment

You will really appreciate this feature if you have called some of the cable companies to terminate your cable connection.   This is the response you generally get to hear ” Please call us on so and so date; I cannot update the system now”. Hopefully some of those companies can use these feature now.

Temporal Validity  associates one or more valid time dimensions to your data making it visible or invisible  depending on the time as set by the user. If adopted , may be with this feature, you can change your address or phone number months in advance, don’t have to wait for the last minute.

So how do you implement it?

PERIOD FOR clause of  CREATE/ALTER table command is used to implement temporal validity.

The syntax for PERIOD FOR clause is as below.

PERIOD FOR dimension_name [( start_time_column , end_time_column)]

You can explicitly  specify  columns “start_time_column”  & “end_time_column”  and when specified  they must be existing columns in the table.

If  “start_time_column”  & “end_time_column”   is skipped , Oracle will create  two invisible columns  dimension_name_start & dimension_name_end for time dimensions. The invisible columns cane be used in INSERT  & UPDATE statement like like any defined table columns.

 

start_time_column —–end_time_column ——-Result
NULL NULL Valid for all Times
NULL Value Valid for all times before  end_time_column values
Value NULL Valid for all times after start_time_column values
Value Value Valid for all times between start_time_column and end_time_column values

See examples below for creating tables

In the 1st example below, I am creating a time dimension using columns of the table itself. Two columns SERVICE_START and SERVICE_END  specifies the time dimension for the row.

SQL> CREATE TABLE CUSTOMER_1
2  (
3  CUST_NO    NUMBER(12) PRIMARY KEY,
4  CUST_NAME  VARCHAR2(20) NOT NULL,
5  CUST_ADDR  VARCHAR2(40),
6  SERVICE_START      TIMESTAMP,
7  SERVICE_END        TIMESTAMP,
8  PERIOD FOR CUST_HIST_TIME(SERVICE_START, SERVICE_END)
9  );

Table created.

In the 2nd example below, I am creating the  table  without specifying the time dimensions for the data. In such case Oracle will create 2 invisible columns CUST_HIST_TIME_START & CUST_HIST_TIME_END that can be used  almost like any other regular table column.

Not: CUST_HIST_TIME is user defined value for time dimension specified with PERIOD FOR clause.

SQL> CREATE TABLE CUSTOMER_2
2  (
3  CUST_NO    NUMBER(12) PRIMARY KEY,
4  CUST_NAME  VARCHAR2(20) NOT NULL,
5  CUST_ADDR  VARCHAR2(40),
6  PERIOD FOR CUST_HIST_TIME ==> Invisible start and end columns are created
7  );

Table created.

Inserting data into temporal tables with invisible columns

SQL> INSERT INTO CUSTOMER_2 (CUST_NO, CUST_NAME, CUST_ADDR, CUST_HIST_TIME_START , CUST_HIST_TIME_END)
2  VALUES (1,’SHAN’,’1235 RIVER RD VA 20155′,SYSDATE, SYSDATE+90);

1 row created.

SQL> INSERT INTO CUSTOMER_2 (CUST_NO, CUST_NAME, CUST_ADDR, CUST_HIST_TIME_START , CUST_HIST_TIME_END)
2   VALUES (2,’JOE’,’78628 LAKE ST IL 60148′,SYSDATE , SYSDATE+120);

1 row created.

Click here for Part-2 : Work-in-progress

Oracle12c: Session Data Unit(SDU)

Oracle August 20, 2013

Session Data Unit(SDU) is the session layer responsible for  sending/receiving data from transport layer.  See table below for OSI diagram. Generally increasing the value of SDU can result in improved performance, efficient network utilization when transmitting large amounts of data.  However until Oracle 11g  the SDU size was limited  to 64K. Starting with Oracle12c, support for  SDU  is increased to 2M thereby allowing efficient use of  high bandwidth network.

SDU can be set at  database level by configuring DEFAULT_SDU_SIZE parameter in sqlnet.ora or SDU listener.ora parameter. On the client size SDU can set be by configuring DEFAULT_SDU_SIZE parameter in client sqlnet.ora or SDU tnsnames.ora parameter.

Note: SDU size  actually used is the smaller of  the client and server value.

OSI Model

Layer# Name Description
7 Application Standard communication services and applications that everyone can use
6 Presentation Layer that takes data from application and presents it  in a format that is recognized by other layers; Functions like character set conversion is done by this layer
5 Session Establishes, maintain & end connections
4 Transport Delivers data without errors in sequence and no data loss or duplications. Large messages can be delivered but is constrained by message size limits imposed by the network layer.
3 Network Controls the way data will be sent to recipient ; Factors like the physical path the data should take based  etc are performed by this layer. This layer translates logical addresses, or names, into physical addresses, performs routing
2 Data Link Handles the transfer of data across the network ; It  establishes and terminates the logical link between two nodes.
1 Physical The lowest layer of the OSI  is generally concerned with the transfer of data medium like optical or electrical medium etc.

Temporary Undo in Oracle12c

Oracle August 16, 2013 1 Comment

Prior to Oracle12c, there was lot of confusion about Global temporary tables and redo generation.  Some of us initially misinterpreted documentation and concluded that no redo is generated for DMLs against global temporary tables.  However in Oracle12c, the documentation is very clear about undo being generated for  DMLs against global temporary tables  because undo by default is stored in undo tablespace. This off course results in generation of redo because of the undo being generated in undo tablespace. Kind of chain effect.

Depending on  the type of DML, this can be significant.  For example,  the undo for insert statement is delete (based on rowid) and so undo will be minimal and therefore the redo too.  Please note the redo is generated for undo.

I have the  illustrated an example below where the redo for global temporary table  is 601200(600k) vs 12513404 (12M) from  permanent temporary table.

Therefore  redo is generated for DMLs into Global temporary table.

SQL> create  global temporary table dba_objects_gt as select * from dba_objects where 1=0;

Table created.

SQL>
SQL> set autotrace on

SQL> insert into dba_objects_gt select * from dba_objects;

90858 rows created.

Statistics
———————————————————-
70  recursive calls
8458  db block gets
6230  consistent gets
0  physical reads
601200  redo size
857  bytes sent via SQL*Net to client
852  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
3  sorts (memory)
0  sorts (disk)
90858  rows processed

SQL> create  table dba_objects_pm as select * from dba_objects where 1=0
2  ;

Table created.

SQL> insert into dba_objects_pm select * from dba_objects;

90859 rows created.

Statistics
———————————————————-
61  recursive calls
8781  db block gets
7746  consistent gets
0  physical reads
  12513404  redo size
859  bytes sent via SQL*Net to client
852  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
90859  rows processed

As we are aware  that undo records for temporary tables are stored in the undo tablespace and therefore logged in the redo. This is same way undo is managed for permanent tables too. With Oracle12c, this default bahavoiour can be changed with  initialization parameter TEMP_UNDO_ENABLED that separated undo for temporary tables from undo for persistent tables.  Temporary undo can be enabled at session level or system level.  By default ,Temporary undo is also enabled for physical standby databases (Active Dataguard)

Other than reduced redo generation, enabling temporary undo also results in less data stored in undo which in turn can help predict more accurate undo retentions.

I enabled temporary undo and ran the insert statement once again. What a difference … From 12M to 600K  to 228 bytes.

SQL> ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;

System altered.

SQL>
SQL>  create  global temporary table dba_objects_gt as select * from dba_objects where 1=0;

Table created.

SQL>
SQL> insert into dba_objects_gt select * from dba_objects;

90860 rows created.

Statistics
———————————————————-
13  recursive calls
8456  db block gets
6153  consistent gets
0  physical reads
   228  redo size
860  bytes sent via SQL*Net to client
852  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
90860  rows processed

Oracle12c: Muliple indexes on Same columns

Oracle August 13, 2013 Comments: 2

Starting with 12c, Oracle supports creating different type of indexes on same column(s). MySQL supported this kind of indexing mechanism and glad that Oracle also supports it. Generally indexing the same column is considered  a performance bottleneck because it slows down inserts and updates to maintain multiple indexes but some times it becomes a necessity.

Few reasons why you would need different type of indexes on same of columns

  1. B*TREE for OLTP sessions v.s BITMAP for nightly reports
  2. Local vs Global Indexes
  3. Testing  performance of different type of index

SQL> SELECT * FROM V$VERSION
2  ;

BANNER
——————————————————————————
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
PL/SQL Release 11.2.0.2.0 – Production
CORE    11.2.0.2.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 – Production
NLSRTL Version 11.2.0.2.0 – Production

SQL> CREATE TABLE EMP
2  (
3  EMPNO      NUMBER(9)       PRIMARY KEY ,
4  EMPNAME    VARCHAR2(15)    NOT NULL,
5  DEPTNO             NUMBER(4)       ,
6  PHONENO    NUMBER(10)      ,
7  HIREDATE   DATE            ,
8  SALARY     NUMBER(9, 2)
9  )
10  /

Table created.

SQL> CREATE INDEX EMP_DEPT_BTREE_IDX ON EMP(DEPTNO);

Index created.

SQL> CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO) INVISIBLE  ;
CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO) INVISIBLE
*
ERROR at line 1:
ORA-01408: such column list already indexed

Lets us repeat the same steps in Oracle12c

SQL> SELECT * FROM V$VERSION;

BANNER                                                                               CON_ID
——————————————————————————– ———-
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production              0
PL/SQL Release 12.1.0.1.0 – Production                                                    0
CORE    12.1.0.1.0      Production                                                                0
TNS for Linux: Version 12.1.0.1.0 – Production                                            0
NLSRTL Version 12.1.0.1.0 – Production                                                    0
SQL> CREATE TABLE EMP
2  (
3  EMPNO      NUMBER(9)       PRIMARY KEY ,
4  EMPNAME    VARCHAR2(15)    NOT NULL,
5  DEPTNO             NUMBER(4)       ,
6  PHONENO    NUMBER(10)      ,
7  HIREDATE   DATE            ,
8  SALARY     NUMBER(9, 2)
9  )
10  TABLESPACE USERS
11  ;

Table created.

SQL>
SQL>
SQL> CREATE INDEX EMP_DEPT_BTREE_IDX ON EMP(DEPTNO);

Index created.

SQL> CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO) INVISIBLE  ;

Index created.

.

IDENTITY Columns: Now with Oracle12c

Oracle August 13, 2013 Comments: 2

Identity columns generate  unique numeric value for each row in a table. This option was available on DB2 since version 7 and is now available with Oracle 12c too.  Hooray !!!!

You can define identify columns with ALWAYS or DEFAULT option of CREATE/ALTER TABLE command.

ALWAYS: Oracle Database always uses the sequence generator to assign a value to the column and does not allow the user  to update or insert the identity column value.

BY DEFAULT: Similar to ALWAYS but  permits the user to explicitly assign a value using insert and update.

Using “BY DEFAULT” as opposed to “ALWAYS” does not guarantee uniqueness as the values can be updated by user.  The “ALWAYS”  option pretty much does what a primary key or unique key or unique index does with out having to create them explicitly. Use this to create uniqueness at your own peril.

How is identify clause different from Sequences?

An identity column has a  1-1 relationship  with tables whereas sequences are not tied to any single table. Therefore with sequences, the same value can be used across several tables. Also most of the sequence options like start value, max values etc can be defined with identity column option.

Example to illustrate 1-1 relationship between identity columns and tables

In  this example, I am creating 2 tables with similar structure and inserting data in both the table

SQL> CREATE TABLE EMP1
2  (
3  EMPNO      NUMBER(9)       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
4  EMPNAME    VARCHAR2(15)    NOT NULL,
5  DEPT               NUMBER(4)       ,
6  PHONENO    NUMBER(10)      ,
7  HIREDATE   DATE            ,
8  SALARY     NUMBER(9, 2)
9  )
10  TABLESPACE USERS;

Table created.

SQL> SQL> CREATE TABLE EMP2
2  (
3  EMPNO      NUMBER(9)       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
4  EMPNAME    VARCHAR2(15)    NOT NULL,
5  DEPT               NUMBER(4)       ,
6  PHONENO    NUMBER(10)      ,
7  HIREDATE   DATE            ,
8  SALARY     NUMBER(9, 2)
9  )
10  TABLESPACE USERS;

Table created.

SQL>
SQL>
SQL> INSERT INTO EMP1 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> INSERT INTO EMP2 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> INSERT INTO EMP1 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL>  INSERT INTO EMP2 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> INSERT INTO EMP1 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL>  INSERT INTO EMP2 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> SELECT * FROM EMP1;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY
———- ————— ———- ———- ——— ———-
1 TIM                     10 6309351100 13-AUG-13      50000
2 TIM                     10 6309351100 13-AUG-13      50000
3 TIM                     10 6309351100 13-AUG-13      50000

SQL> SELECT * FROM EMP2;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY
———- ————— ———- ———- ——— ———-
1 TIM                     10 6309351100 13-AUG-13      50000
2 TIM                     10 6309351100 13-AUG-13      50000
3 TIM                     10 6309351100 13-AUG-13      50000

Example to illustrate DEFAULT option not preserving the uniqueness

In  this example, I am creating a  table with identify column (DEFAULT option) and therefore allows updates to  identity column  leading to duplicate values

SQL>
SQL> CREATE TABLE EMP4
2  (
3  EMPNO      NUMBER(9)       GENERATED BY DEFAULT AS IDENTITY ,
4  EMPNAME    VARCHAR2(15)    NOT NULL,
5  DEPT               NUMBER(4)       ,
6  PHONENO    NUMBER(10)      ,
7  HIREDATE   DATE            ,
8  SALARY     NUMBER(9, 2)
9  )
10  TABLESPACE USERS;

Table created.

SQL>
SQL> INSERT INTO EMP4 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> INSERT INTO EMP4 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> INSERT INTO EMP4 (EMPNAME,DEPT,PHONENO,HIREDATE,SALARY) VALUES (‘TIM’,10,6309351100,SYSDATE,50000);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> SELECT * FROM EMP4;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY
———- ————— ———- ———- ——— ———-
1 TIM                     10 6309351100 13-AUG-13      50000
2 TIM                     10 6309351100 13-AUG-13      50000
3 TIM                     10 6309351100 13-AUG-13      50000

SQL> UPDATE EMP4 SET EMPNO=2 WHERE EMPNO=1;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL>  SELECT * FROM EMP4;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY
———- ————— ———- ———- ——— ———-
2 TIM                     10 6309351100 13-AUG-13      50000
2 TIM                     10 6309351100 13-AUG-13      50000
3 TIM                     10 6309351100 13-AUG-13      50000

Example to illustrate adding new identity columns  to existing tables.

When you add a new identity column to an existing  table all existing rows are updated using
the sequence generator. The order in which a value is assigned to each existing row cannot be determined.

Also you can change an existing column to identity column

CREATE TABLE EMP3
(
EMPNO         NUMBER(9)        PRIMARY KEY,
EMPNAME      VARCHAR2(15)    NOT NULL,
DEPT         NUMBER(4)       ,
PHONENO       NUMBER(10)      ,
HIREDATE      DATE            ,
SALARY        NUMBER(9, 2)
)
TABLESPACE USERS;

SQL>
SQL> SELECT * FROM EMP3;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY
———- ————— ———- ———- ——— ———-
101 TIM                     10 6309351100 13-AUG-13      50000
102 TIM                     10 6309351100 13-AUG-13      50000
103 TIM                     10 6309351100 13-AUG-13      50000

SQL>
SQL>

SQL>  ALTER TABLE EMP3 ADD (NEW_EMPNO NUMBER(9) GENERATED ALWAYS AS IDENTITY);

Table altered.

SQL>  SELECT * FROM EMP3;

EMPNO EMPNAME               DEPT    PHONENO HIREDATE      SALARY  NEW_EMPNO
———- ————— ———- ———- ——— ———- ———-
101 TIM                     10 6309351100 13-AUG-13      50000          1
102 TIM                     10 6309351100 13-AUG-13      50000          2
103 TIM                     10 6309351100 13-AUG-13      50000          3

Modifying existing column to identify column is not supported

SQL> ALTER TABLE EMP3 MODIFY  (EMPNO GENERATED ALWAYS AS IDENTITY);
ALTER TABLE EMP3 MODIFY  (EMPNO GENERATED ALWAYS AS IDENTITY);
*
ERROR at line 1:
ORA-30673: column to be modified is not an identity column

SQL>
SQL> ALTER TABLE EMP3 MODIFY  (EMPNO GENERATED BY DEFAULT AS IDENTITY);
ALTER TABLE EMP3 MODIFY  (EMPNO GENERATED BY DEFAULT AS IDENTITY)
*
ERROR at line 1:
ORA-30673: column to be modified is not an identity column

Upgrading to Oracle 12c: How different is it from previous versions?

upgrading to Oracle 12c August 11, 2013 1 Comment

1. Preupgrade Utility

Preupgrade utility “utlu121s.sql”  is replaced with “preupgrd.sql”. The new utility provides fixup scripts “preupgrade_fixups.sql” and “postupggrade_fixups.sql” to address issues that might be present both before and after the upgrade.These fixup scripts can be executed interactively.

2. Upgrade Utility

The catupgrd.sql Upgrade utility is replaced with the catctl.pl utility. The new utility allows you to take advantage of CPUs by running the upgrade process in parallel thereby reducing the upgrade time.  This new script is used more like a wrapper on the existing one. Also by default “catuppst.sql” is run when “catctl.pl” is executed. The new script has lot of options , few of them are explained below.

Option “-n” specifies the degree of parallelism, the default is 4 and maximum is 8.
Option “-p” supports rerun of the upgrade by skipping successful steps.

Example: $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -p $ORACLE_HOME/rdbms/admin/catupgrd.sql

3. XML DB

Oracle XML DB is no longer an optional component and therefore cannot be uninstalled. It is automatically installed into SYSAUX tablespace when you create a new 12c database or if you upgrade from prior versions of Oracle.

4. Upgrade Status

A successful upgrade sets the status of database status to UPGRADED. This is different from previous versions where the status is set to VALID.  The database status is set to VALID only if ultrp.sql is executed.