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.

How to create a range partitioned table

Using interval partitioning:

A simple example of an Interval Range Partitioned table:

CREATE TABLE DOCUMENTS
(
  DOCNAME          VARCHAR2(100 BYTE),
  TRACKING#        VARCHAR2(32 BYTE),
  ESTABLISHED_DT   DATE
)
PARTITION BY RANGE (ESTABLISHED_DT)
INTERVAL
(
   NUMTOYMINTERVAL(1,'MONTH')
)
(
PARTITION P_INIT VALUES LESS THAN (TO_DATE('2014-03','YYYY-MM') )
)
TABLESPACE USERS;

If you use INTERVAL partitioning, you are not allowed to specify an overflow partition.

Without internval partitioning:

Before Oracle 11g, this was the only way to create a RANGE partitioned table:
You had to manually define your partitions, and remember to manually add new ones if you expected new partition key values to be inserted.

Here I am using a column of type NUMBER to implement range partitioning.
I am defining an overflow partition to catch values outside the range of expected values:

CREATE TABLE mytable
(
  ID                      NUMBER(18)           NOT NULL,
  f_name                  NUMBER(18)           NOT NULL,
  l_name                  NUMBER(18)           NOT NULL,
  created_year            NUMBER(4)            NOT NULL
   ....
)
PARTITION BY RANGE (created_year)
(
PARTITION P_2003 values less than (2004) TABLESPACE 2003_DATA,
PARTITION P_2004 values less than (2005) TABLESPACE 2004_DATA,
PARTITION P_2005 values less than (2006) TABLESPACE 2005_DATA,
PARTITION P_DEFAULT values less than (MAXVALUE)
);