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

How to alter a subpartition template

Use the SET SUBPARTITION TEMPLATE clause, like this:

ALTER TABLE SCOTT.MYTABLE
SET SUBPARTITION TEMPLATE
  (SUBPARTITION SP_2014 VALUES ('2014') TABLESPACE USERS,
   SUBPARTITION SP_2015 VALUES ('2015') TABLESPACE USERS,
   SUBPARTITION SP_2016 VALUES ('2016') TABLESPACE USERS,
   SUBPARTITION SP_2017 VALUES ('2017') TABLESPACE USERS,
   SUBPARTITION SP_2018 VALUES ('2018') TABLESPACE USERS,
   SUBPARTITION SP_2019 VALUES ('2019') TABLESPACE USERS,
   SUBPARTITION SP_2020 VALUES ('2020') TABLESPACE USERS,
   SUBPARTITION SP_DEFAULT VALUES (default) TABLESPACE USERS
  );

Source: Oracle Documentation

Insert As Select statements

The syntax for an Insert As Select Statement would be:

INSERT INTO MY_INTERIM_TABLE (E_ID, ENAME,CREATED_DT, REVISION, FISCALYEAR, FPERIOD, PHONENBR)
                       SELECT E_ID, ENAME,CREATED_DT, REVISION, FISCALYEAR, TO_DATE(FPERIOD,'YYYY-MM'), PHONENBR
                       FROM   MY_ORIGINAL_TABLE
                       FETCH FIRST 5 ROWS ONLY;

The FETCH FIRST num ROWS ONLY is a convenient way to limit the number of rows when you are setting up tables for test purposes.