How to add a check constraint to a table using ALTER TABLE

The following statement adds a check constraint to the table and allows Oracle to give it a system generated name:

ALTER TABLE MYTABLE ADD 
 CHECK (COL1 IS NOT NULL)
 ENABLE
 VALIDATE;

Note that when you execute the following DDL, Oracle will throw ORA-02438: Column check constraint cannot reference other columns:

ALTER TABLE MYTABLE ADD
  CONSTRAINT CHECK (COL1 IS NOT NULL)
  ENABLE VALIDATE;

The above error only happens when you let Oracle give the constraint a system generated name.


If you are explisitly naming your constraint, the use of the CONSTRAINT keyword will work:

ALTER TABLE MYTABLE ADD
  CONSTRAINT mytable_cc2 CHECK (COL1 IS NOT NULL)
  ENABLE VALIDATE;

How to find wait information about a session

Yesterday, I had started a script with two long-running queries before I my shift ended, and it was finished when I got back, as expected. The session was still connected to the database and waiting for some time. I was curious how long time the SQL took, and I forgot to set timing on in sqlplus.

SELECT 
    S.SID,
    S.SERIAL#,
    S.STATUS "status",
    S.LOGON_TIME "logon time",
    S.PREV_EXEC_START "last op started",
    Q.LAST_ACTIVE_TIME "last op ended", 
    S.STATE "state",
    S.EVENT "event",
    ROUND((S.WAIT_TIME_MICRO/1000000)/60/60,1) "total wait hrs",
    DECODE(S.TIME_REMAINING_MICRO,'-1', 'indefinite', '0','Wait timed out',NULL,'session not waiting') "remaining wait time", 
    DECODE(S.TIME_SINCE_LAST_WAIT_MICRO, '0','Still waiting') "current status"
FROM V$SESSION S  JOIN V$SQL Q 
ON (S.SQL_ID = Q.SQL_ID)
WHERE SID=1758;

The output below shows that my session was established at 14:33, and the last operation ended at 19:20. In other words, it took approximately 5 hours for the two statements in my script to finish:

SID	SERIAL#	status	logon time	last op started	last op ended	state	event	total wait hrs	remaining wait time	current status
1758	35360	INACTIVE	14.03.2017 14:33:42	14.03.2017 19:10:31	14.03.2017 19:20:52	WAITING	SQL*Net message from client	13,2	indefinite	Still waiting

Note that v$session column "seconds_in_wait" has been deprecated. You should use "wait_time_micro" instead.

How to use the Oracle 12c Row-limiting clauses

One of the most useful features of Oracle 12c is the ability to limit the number of rows from a query using top-N queries. Amongst others, they provide a great way to populate test tables.

It can be used in several ways, by specifying an offset of rows to skip, the number of rows to return or the percentage of rows to return.

A couple of simple examples:

Fetch the first 5 rows from the result set:

select * 
from scott.emp
fetch first 5 rows only;

Fetch the next 10 rows after the first five rows have been skipped:

select *  
from Scott.emp 
offset 5 rows fetch next 10 rows only;

Load the first few rows of data from a specific partition, into a new table:

insert into test_table
SELECT  *
FROM       original_table
subpartition (other_sp_default)
fetch first 10 rows only;

Sources:

http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BABHFGAA

and

http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BABEAACC

How to tar and compress a directory

You want to compress the folder "setup", located in /u01/admin/scripts, with all its contents including subfolders:

cd /u01/admin/scripts
ls -latr 
drwxr-xr-x  8 oracle dba  8192 Mar 13 09:17 .
drwxrwxr-x  2 oracle dba  4096 Mar 13 09:40 setup

tar -cvzf setup.tar.gz setup

Errors ORA-39083 and ORA-28365 during import

During an import you get:

ORA-39083: Object type TABLE:"SCOTT"."EMP" failed to create with error:
ORA-28365: wallet is not open

Action:
Open the wallet:

SQL> administer key management set keystore open identified by "mysecretpassword";

keystore altered.

Check status of the wallet:

select status from  v$encryption_Wallet;

STATUS
------
OPEN

You can now perform your import as you normally would.

After import you can Close the wallet:

administer key management set keystore close identified by "mysecretpassword";

How to partition a table using a virtual column and online redefinition

Consider the following table:

desc DOCUMENT_SUMMARY
Navn                                      Null?    Type
----------------------------------------- -------- ----------------------------
DOCUMENTNAME                              NOT NULL VARCHAR2(100)
SUMMARY                                            VARCHAR2(32)

A SELECT * FROM DOCUMENT_SUMMARY reveals contents of the following type (excerpt only):

DOCUMENT1:2015-08:9200060665151:9000010707472:9000044000014	9b37f5bcee4ce643058ee633359b3253
DOCUMENT2:2016-01:9200060665151:8837773777371:9000088000009	17bf7170217960f303755bdd4c648676

The table is quite big, and expected to grow.
The customer would like to get it partitioned.

The column DOCUMENTNAME already contains a string that is partly generated from a date, as revealed by the query below:

SELECT SUBSTR(DOCUMENTNAME,32,7) "datepart" 
FROM DOCUMENT_SUMMARY
FETCH FIRST 2 ROWS ONLY;

datepart
2015-01
2015-01

We could most likely use a substring of the value to create a new, virtual column and use it as a partition key.

So let's do that:

CREATE TABLE DOCUMENT_SUMMARY_INTERIM
(
  DOCUMENTNAME  VARCHAR2(100 BYTE),
  SUMMARY       VARCHAR2(32 BYTE),
  PERIOD AS (
     CAST(
        TO_DATE(SUBSTR(documentname,32,7),'YYYY-MM') 
        AS DATE)
  ) VIRTUAL
)
PARTITION BY RANGE(PERIOD)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH') )
(
PARTITION P_INIT VALUES LESS THAN (TO_DATE('2013-01','YYYY-MM') )
)
TABLESPACE USERS
;

Run through a normal online redefinition, and the tables should be switched seamlessly.

Selecting from the new table shows that the date is being generated as a virtual column for every row:

SELECT * 
FROM DOCUMENT_SUMMARY
FETCH FIRST 2 ROWS ONLY;

DOKUMENTNAME	SUMMARY	PERIOD
DOCUMENT1:2015-08:9200060665151:9000010707472:9000044000014	f1081c88eb379b736954641589d5715b	01.08.2015
DOCUMENT2:2016-01:9200060665151:8837773777371:9000088000009	051722d803b2fbaca4192a047b7689a6	01.01.2016

You can verify your partition key attributes with this SQL:

SELECT K.COLUMN_NAME, C.DATA_TYPE,C.VIRTUAL_COLUMN ,PT.INTERVAL
FROM DBA_PART_KEY_COLUMNS K JOIN DBA_TAB_COLS C
    ON (K.COLUMN_NAME = C.COLUMN_NAME) 
        JOIN DBA_PART_TABLES PT ON PT.TABLE_NAME = C.TABLE_NAME
WHERE  K.NAME='DOCUMENT_SUMMARY' ;

COLUMN_NAME	DATA_TYPE	VIRTUAL_COLUMN	INTERVAL
PERIODE	DATE	YES	NUMTOYMINTERVAL(1,'MONTH')

What is the INHERIT PRIVILEGES granted to PUBLIC in 12c?

I was checking which object privileges my user had made, and I found the following:

select * from user_tab_privs_made;

GRANTEE	TABLE_NAME	GRANTOR	PRIVILEGE	GRANTABLE	HIERARCHY	COMMON	TYPE
PUBLIC	MYUSER	MYUSER	INHERIT PRIVILEGES	NO	NO	NO	USER

I couldn't understand when this privilege had been made, and why the type was set to USER.

Searching the internet, I soon found that this is a new privilege in Oracle 12c. By default the privilege INHERIT PRIVILEGES is granted to PUBLIC, and it secures a weakness where a malicious user may accidently get access to a privilege that user shouldn't have.

For backward compability INHERIT PRIVILEGES is granted to PUBLIC in Oracle 12c, but it can and probably should be revoked.

Tim Hall has setup a simple example on how this new privilege can secure your database. Instead of trying to mimic his work, I will simply refer you to his site.

You can find his article at oracle-base.com

See also the release changes section for Oracle 12c