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

How to speed up statistics gathering on a large table - inter object parallelism

When you use parallelism to analyze a specific object, Oracle refer to this as "Inter Object Parallelism".
In short, the Degree Of Parallelism (DOP) is decided based on the object's DEGREE setting, OR the DEGREE Directive in the dbms_stats.gather_table_stats procedure.

In my example, the partitioned table HR.SALES was created with DEGREE=1, which is the default.

I then collected statistics on it, like this:

BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
     OwnName        => 'HR'
    ,TabName        => 'SALES'
    ,Degree            => DBMS_STATS.DEFAULT_DEGREE 
);
END;
/

When dbms_stats.gather_table_stats was executed with DEGREE=DBMS_STATS.DEFAULT_DEGREE, Oracle used a DOP of 8, which is calculated using the formula

PARALLEL_THREADS_PER_CPU (2) * CPU_COUNT (4)

If you look in v$sql, this Call to dbms_stats triggers a parallel SQL:

/* SQL Analyze(0) */
select /*+ full(t) parallel(t,8) parallel_index(t,8) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */

I found that for a table sized 100G, the statistics gathering dropped from 3 hours to 47 minutes using the DEGREE directive.

Note that Oracle will still collect statistics one partition at a time:

“You should note that setting the DEGREE for a partitioned table means that multiple parallel sever processes will be used to gather statistics on each partition but the statistics will not be gathered concurrently on the different partitions. Statistics will be gathered on each partition one after the other”.

Sources: Whitepaper "Best Practices for Gathering Optimizer Statistics with Oracle Database 12c"
Oracles Documentation about dbms_stats

Oracle 12c feature: Online statistics gathering

Oracle 12c introduced the concept of "online statitistics gathering".

The white paper "Best Practices for Gathering Optimizer Statistics with Oracle Database 12c" released in June 2013, explains:


"In Oracle Database 12c, online statistics gathering "piggybacks" statistics gather as part of a direct-path data loading operation"

and

Gathering statistics as part of the data loading operation, means no additional full data scan is required to have statistics available immediately after the data is loaded.”

and

"online statistics gathering was designed to have a minimal impact on the performance of a direct path load operation it can only occur when data is being loaded into an empty object"

This can be a huge time saver in database where regular heap-organized tables are loaded and truncated as a part of their ETL Processing.

Note that some restrictions apply:

1. Histograms or index statistics are not gathered as a part of online statistics gathering, since they require additional data scans

2. Online statistics gathering will only follow a Direct-path load:

   * CTAS - Create Table As Select
   * IAS - Insert As Select
   * INSERT INTO /* +APPEND */

3. The base tables which are loaded must be empty

Any negative consequence of the first restriction can be mitigated by collecting historgram data on the table *after* the online statistics have been collected. Oracle does this without re-gathering the base column statistics:

exec dbms_stats.gather_table_stats(OwnName => 'SCOTT',TabName => 'DEPT', options=>'GATHER AUTO');

The easiest way to check if the statistics in a column has been gathered via the Online statisitcs feature, is to check the "histogram" and the "notes" column in the user|dba|all _tab_col_statistics view:

set lines 200
col column_name format a30
col notes format a30
select column_name,num_distinct,num_nulls,histogram,notes
from dba_tab_col_statistics
where table_name='DEPT';

If output in the histogram column says "NONE" and the notes columns says "STATS_ON_LOAD", Online statistics gathering has indeed been utilized.

If the output of the same columns says "HYBRID" and "HISTOGRAM_ONLY", it indicates that histograms were gathered without regathering basic column statistics.

How to solve ORA-14323: cannot add partition when DEFAULT partition exists

You have a partitioned table with an overflow partition, and you try to add another partition to it:

ALTER TABLE RECEIVED_DOCUMENTS 
ADD PARTITION EES_COUNTRIES VALUES('NORWAY','ICELAND')

Result:

ERROR at line 1:
ORA-14323: cannot add partition when DEFAULT partition exists

Solution: split the overflow partition:

ALTER TABLE RECEIVED_DOCUMENTS 
SPLIT PARTITION "OTHERS" 
VALUES ('NORWAY','ICELAND')
INTO ( PARTITION EES_COUNTRIES,
       PARTITION OTHERS
       )
UPDATE INDEXES;

How to split an overflow partition using ALTER TABLE .... SPLIT PARTITION

For a range partitioned table:

ALTER TABLE SCOTT.EMPLOYEES
SPLIT PARTITION START_DATE_MAX AT (TO_DATE('2014-03-01', 'YYYY-MM-DD')) 
INTO (
      PARTITION START_DATE_FEB_2014 TABLESPACE ACTIVE_DATA,
      PARTITION START_DATE_MAX TABLESPACE TOOLS
     );

For a list-partitioned table:

ALTER TABLE received_documents
SPLIT PARTITION OTHERS
VALUES ('EAST','WEST','NORTH','SOUTH')
INTO (PARTITION NON_EU_CITIZEN,
      PARTITION OTHERS
) UPDATE INDEXES PARALLEL 8;

See this post for an example of splitting a default subpartition.

How to create a global temporary table

An example of a Global Temporary Table (GTT) would be:

CREATE GLOBAL TEMPORARY TABLE INCOMING_DATA (
  ROW_ID                      NUMBER(10) NOT NULL,
  SSN                         NUMBER(11),
  ENTRY_DATE                  DATE NOT NULL,
  HANDLED_BY                  VARCHAR2(30 BYTE) NOT NULL,
  CONTENT_CODE                VARCHAR2(4 BYTE),
  MESSAGE                     CLOB
 )
ON COMMIT DELETE ROWS
;

ON COMMIT DELETE ROWS indicates that Oracle will purge the rows after an ended transaction (after a COMMIT, ROLLBACK or an implicit COMMIT).
Instead of ON COMMIT DELETE ROWS you could also use ON DELETE PRESERVE ROWS, which will remove the rows at the end of the user's session.

You can add indexes, triggers and views on a GTT. You can even truncate a temporary table, and it will only affect the curren session's rows, leaving other users' rows intact.


With Oracle 12c came the ability for temporary tables to store its UNDO segments in temporary tablespaces, rather than conventional tablespaces, thus removing the need to generate REDO.

To enable this for your session, use:

ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;

before creating the GTT.

Sources:

Oracle-Base: article about GTT and another one explaining the 12c new feature "temporary undo"

Oracle Documentation: Overview of GTT
Check my note on private temporary tables, available from Oracle 18c.

Create a globally partitioned hash index

Partitioned table layout:

CREATE TABLE SCOTT.ARCHIVED_NOTES
(
  NOTE_ID                     NUMBER(10)        NOT NULL,
  PPT_ID                      NUMBER(10),
  REGISTRED_DATE              DATE              NOT NULL,
  EDITED_DATE                 DATE,
  ALTERED_BY                  VARCHAR2(30 BYTE) NOT NULL,
  ALTERED_DATE                DATE              NOT NULL,
  CONTENT_CODE                VARCHAR2(4 BYTE),
  NOTEFORMAT                  VARCHAR2(1 BYTE)
)
TABLESPACE USERS
PARTITION BY RANGE (NOTE_ID)
(  
  PARTITION ARCHIVED20 VALUES LESS THAN (21000000),  
  PARTITION ARCHIVED21 VALUES LESS THAN (22000000),
  PARTITION ARCHIVED22 VALUES LESS THAN (23000000)
)
;

Create a global hash-partitioned index. Notice the GLOBAL keyword:

CREATE INDEX ARCHIVED_NOTES_IDX2 ON ARCHIVED_NOTES (REGISTRED_DATE)
  GLOBAL PARTITION BY HASH (REGISTRED_DATE)
  PARTITIONS 3;

Verify its existence:

SELECT INDEX_NAME,PARTITION_NAME 
FROM USER_IND_PARTITIONS 
WHERE INDEX_NAME = (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='ARCHIVED_NOTES'); 

INDEX_NAME	PARTITION_NAME
ARCHIVED_NOTES_IDX2	SYS_P441
ARCHIVED_NOTES_IDX2	SYS_P442
ARCHIVED_NOTES_IDX2	SYS_P443

Let's take a deeper look at the index properties:

SELECT C.TABLE_NAME,I.INDEX_NAME,I.PARTITIONING_TYPE, I.SUBPARTITIONING_TYPE, I.LOCALITY,I.ALIGNMENT,C.COLUMN_NAME,C.COLUMN_POSITION
FROM USER_PART_INDEXES I JOIN USER_IND_COLUMNS C
ON (I.INDEX_NAME = C.INDEX_NAME)
WHERE I.TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES WHERE PARTITIONED='YES')
ORDER BY 1,2,7;

TABLE_NAME	INDEX_NAME	PARTITIONING_TYPE	SUBPARTITIONING_TYPE	LOCALITY	ALIGNMENT	COLUMN_NAME	COLUMN_POSITION
ARCHIVED_NOTES	ARCHIVED_NOTES_IDX2	HASH	NONE	GLOBAL	PREFIXED	REGISTRED_DATE	1

For global partitioned indexes, the "alignment" column will always show the index as "prefixed". This is the only supported index type for global partitioned indexes.

Create a globally partitioned range index

CREATE TABLE SCOTT.ARCHIVED_NOTES
(
  NOTE_ID                     NUMBER(10)        NOT NULL,
  PPT_ID                      NUMBER(10),
  REGISTRED_DATE              DATE              NOT NULL,
  EDITED_DATE                 DATE,
  ALTERED_BY                  VARCHAR2(30 BYTE) NOT NULL,
  ALTERED_DATE                DATE              NOT NULL,
  CONTENT_CODE                VARCHAR2(4 BYTE),
  NOTEFORMAT                  VARCHAR2(1 BYTE)
)
TABLESPACE USERS
PARTITION BY RANGE (NOTE_ID)
(  
  PARTITION ARCHIVED20 VALUES LESS THAN (21000000),  
  PARTITION ARCHIVED21 VALUES LESS THAN (22000000),
  PARTITION ARCHIVED22 VALUES LESS THAN (23000000)
)
;

Create a global range partitioned index. Notice the GLOBAL keyword:

CREATE INDEX SCOTT.ARCHIVED_NOTES_IDX1 
ON SCOTT.ARCHIVED_NOTES(NOTE_ID)
GLOBAL PARTITION BY RANGE(NOTE_ID)(  
  PARTITION ARCHIVED20 VALUES LESS THAN (21000000),  
  PARTITION ARCHIVED21 VALUES LESS THAN (22000000),  
  PARTITION ARCHIVED22 VALUES LESS THAN (23000000),
  PARTITION ARCHIVED_OTHERS VALUES LESS THAN (MAXVALUE)
);

Also note that Oracle required the MAXVALUES clause as the last partition in the index, to ensure that all rows in the table will be represented in the index. Without MAXVALUES, Oracle will throw the error:

ORA-14021: MAXVALUE must be specified for all columns

Verify its existence:

SELECT INDEX_NAME,PARTITION_NAME 
FROM USER_IND_PARTITIONS 
WHERE INDEX_NAME = (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='ARCHIVED_NOTES'); 

INDEX_NAME	PARTITION_NAME
ARCHIVED_NOTES_IDX1	ARCHIVED20
ARCHIVED_NOTES_IDX1	ARCHIVED21
ARCHIVED_NOTES_IDX1	ARCHIVED22
ARCHIVED_NOTES_IDX1	ARCHIVED_OTHERS

You could also make the index UNIQUE:

CREATE UNIQUE INDEX SCOTT.ARCHIVED_NOTES_IDX1 ... 

A unique index like this could support a primary key constraint on the table, if desirable:

ALTER TABLE ARCHIVED_NOTES ADD CONSTRAINT ARCHIVED_NOTES_PK
PRIMARY KEY (NOTE_ID)
USING INDEX ARCHIVED_NOTES_IDX1;

Let's take a deeper look at the index Properties:

SELECT C.TABLE_NAME,I.INDEX_NAME,I.PARTITIONING_TYPE, I.SUBPARTITIONING_TYPE, I.LOCALITY,I.ALIGNMENT,C.COLUMN_NAME,C.COLUMN_POSITION
FROM USER_PART_INDEXES I JOIN USER_IND_COLUMNS C
ON (I.INDEX_NAME = C.INDEX_NAME)
WHERE I.TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES WHERE PARTITIONED='YES')
ORDER BY 1,2,7;

TABLE_NAME	INDEX_NAME	PARTITIONING_TYPE	SUBPARTITIONING_TYPE	LOCALITY	ALIGNMENT	COLUMN_NAME	COLUMN_POSITION
ARCHIVED_NOTES	ARCHIVED_NOTES_IDX1	RANGE	NONE	GLOBAL	PREFIXED	NOTE_ID	1

For globally partitioned indexes, the "alignment" column will always show the index as "prefixed". This is the only supported index type for global partitioned indexes.

An important point regarding globally partitioned indexes is pointed out in the Oracle Documentation:

"Normally, a global index is not equipartitioned with the underlying table."

and

"There is nothing to prevent an index from being equipartitioned with the underlying table, but Oracle does not take advantage of the equipartitioning when generating query plans or executing partition maintenance operations. So an index that is equipartitioned with the underlying table should be created as LOCAL."

In other words, while the example above would work, it may not be a good idea. It would make better sense for the global partitioned index to be created on another column:

CREATE INDEX SCOTT.ARCHIVED_NOTES_IDX1 
ON SCOTT.ARCHIVED_NOTES(REGISTRED_DATE)
GLOBAL PARTITION BY RANGE(REGISTRED_DATE)(  
  PARTITION NOTES_2012 VALUES LESS THAN (to_date('01.01.2013', 'dd.mm.yyyy')),  
  PARTITION NOTES_2013 VALUES LESS THAN (to_date('01.01.2014', 'dd.mm.yyyy')),  
  PARTITION NOTES_2014 VALUES LESS THAN (to_date('01.01.2015', 'dd.mm.yyyy'))
  ,PARTITION ARCHIVED_OTHERS VALUES LESS THAN (MAXVALUE)
);

Create a local non-prefixed partitioned index

The table below is partitioned by range, using the column NOTE_ID as the partition key:

CREATE TABLE SCOTT.ARCHIVED_NOTES
(
  NOTE_ID                     NUMBER(10)        NOT NULL,
  PPT_ID                      NUMBER(10),
  REGISTRED_DATE              DATE              NOT NULL,
  EDITED_DATE                 DATE,
  ALTERED_BY                  VARCHAR2(30 BYTE) NOT NULL,
  ALTERED_DATE                DATE              NOT NULL,
  CONTENT_CODE                VARCHAR2(4 BYTE),
  NOTEFORMAT                  VARCHAR2(1 BYTE)
)
TABLESPACE USERS
PARTITION BY RANGE (NOTE_ID)
(  
  PARTITION ARCHIVED20 VALUES LESS THAN (21000000),  
  PARTITION ARCHIVED21 VALUES LESS THAN (22000000),
  PARTITION ARCHIVED22 VALUES LESS THAN (23000000)
)
;

Create a global partitioned index:

CREATE INDEX SCOTT.REGISTRED_DATE_SK1 ON SCOTT.ARCHIVED_NOTES
(REGISTRED_DATE)
LOCAL (  
  PARTITION ARCHIVED20,  
  PARTITION ARCHIVED21,  
  PARTITION ARCHIVED22
)

Confirm its existence:

SELECT INDEX_NAME,PARTITIONED
FROM USER_INDEXES
WHERE TABLE_NAME='ARCHIVED_NOTES';

INDEX_NAME	PARTITIONED
REGISTRED_DATE_SK1	YES

Confirm that the expected index partitions have been created:

SELECT INDEX_NAME,PARTITION_NAME 
FROM USER_IND_PARTITIONS 
WHERE INDEX_NAME = (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='ARCHIVED_NOTES'); 

INDEX_NAME	PARTITION_NAME
REGISTRED_DATE_SK1	ARCHIVED20
REGISTRED_DATE_SK1	ARCHIVED21
REGISTRED_DATE_SK1	ARCHIVED22

Add a new partition:

ALTER TABLE SCOTT.ARCHIVED_NOTES
 ADD 
  PARTITION ARCHIVED23 VALUES LESS THAN (24000000);

If you confirm the index partitions again, you'll see that a new index partition has been added:

INDEX_NAME	PARTITION_NAME
REGISTRED_DATE_SK1	ARCHIVED23

Let's take a deeper look at the properties of this index:

SELECT C.TABLE_NAME,I.INDEX_NAME,I.PARTITIONING_TYPE, I.SUBPARTITIONING_TYPE, I.LOCALITY,I.ALIGNMENT,C.COLUMN_NAME,C.COLUMN_POSITION
FROM USER_PART_INDEXES I JOIN USER_IND_COLUMNS C
ON (I.INDEX_NAME = C.INDEX_NAME)
WHERE I.TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES WHERE PARTITIONED='YES')
ORDER BY 1,2,7;

TABLE_NAME	INDEX_NAME	PARTITIONING_TYPE	SUBPARTITIONING_TYPE	LOCALITY	ALIGNMENT	COLUMN_NAME	COLUMN_POSITION
ARCHIVED_NOTES	REGISTRED_DATE_SK1	RANGE	NONE	LOCAL	NON_PREFIXED	REGISTRED_DATE	1

The "alignment" column of the above output reveals that the index is of type "non_prefixed", which means that the partitioning key is not the first column in the index.

Create a local prefixed partitioned index

The table below is partitioned by range, using the column NOTE_ID as the partition key:

CREATE TABLE SCOTT.ARCHIVED_NOTES
(
  NOTE_ID                     NUMBER(10)        NOT NULL,
  PPT_ID                      NUMBER(10),
  REGISTRED_DATE              DATE              NOT NULL,
  EDITED_DATE                 DATE,
  ALTERED_BY                  VARCHAR2(30 BYTE) NOT NULL,
  ALTERED_DATE                DATE              NOT NULL,
  CONTENT_CODE                VARCHAR2(4 BYTE),
  NOTEFORMAT                  VARCHAR2(1 BYTE)
)
TABLESPACE USERS
PARTITION BY RANGE (NOTE_ID)
(  
  PARTITION ARCHIVED20 VALUES LESS THAN (21000000),  
  PARTITION ARCHIVED21 VALUES LESS THAN (22000000),
  PARTITION ARCHIVED22 VALUES LESS THAN (23000000)
)
;

Create a local index:

CREATE UNIQUE INDEX SCOTT.ARCHIVED_NOTES_PK ON SCOTT.ARCHIVED_NOTES
(NOTE_ID)
LOCAL (  
  PARTITION ARCHIVED20,  
  PARTITION ARCHIVED21,  
  PARTITION ARCHIVED22
);

Confirm its existence:

INDEX_NAME	PARTITIONED
ARCHIVED_NOTES_PK	YES

The index we just created can be used to support a unique or a primary key constraint:

ALTER TABLE ARCHIVED_NOTES ADD CONSTRAINT ARCHIVED_NOTES_PK
PRIMARY KEY (NOTE_ID)
USING INDEX ARCHIVED_NOTES_PK;

Confirm that the expected index partitions have been created:

SELECT INDEX_NAME,PARTITION_NAME 
FROM USER_IND_PARTITIONS 
WHERE INDEX_NAME = (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='ARCHIVED_NOTES'); 

INDEX_NAME	PARTITION_NAME
ARCHIVED_NOTES_PK	ARCHIVED20
ARCHIVED_NOTES_PK	ARCHIVED21
ARCHIVED_NOTES_PK	ARCHIVED22

Add a new partition:

ALTER TABLE SCOTT.ARCHIVED_NOTES
 ADD 
  PARTITION ARCHIVED23 VALUES LESS THAN (23000000);

If you confirm the index partitions again, you'll see that a new index partition has been added:

INDEX_NAME	PARTITION_NAME
ARCHIVED_NOTES_PK	ARCHIVED23

Let's take a deeper look at the properties of this index:

SELECT C.TABLE_NAME,I.INDEX_NAME,I.PARTITIONING_TYPE, I.SUBPARTITIONING_TYPE, I.LOCALITY,I.ALIGNMENT,C.COLUMN_NAME,C.COLUMN_POSITION
FROM USER_PART_INDEXES I JOIN USER_IND_COLUMNS C
ON (I.INDEX_NAME = C.INDEX_NAME)
WHERE I.TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES WHERE PARTITIONED='YES')
ORDER BY 1,2,7;

TABLE_NAME	INDEX_NAME	PARTITIONING_TYPE	SUBPARTITIONING_TYPE	LOCALITY	ALIGNMENT	COLUMN_NAME	COLUMN_POSITION
ARCHIVED_NOTES	ARCHIVED_NOTES_PK	RANGE	NONE	LOCAL	PREFIXED	NOTE_ID	1

The "alignment" column of the above output reveals that the index is of type "prefixed", which means that the partitioning key is the first column in the index.

How to lock table statstics in Oracle

Use dbms_stats.lock_table_stats

conn scott
exec dbms_stats.lock_table_stats('SCOTT','DEPT');

Verify the setting:

select table_name,object_type,stattype_locked 
from user_tab_statistics;

TABLE_NAME                     OBJECT_TYPE                          STATTYPE_LOCKED
------------------------------ ------------------------------------ ---------------
DEPT                           TABLE                                ALL
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
RECEIVED_DOCUMENTS             TABLE

Lock stats on a specific partition:

exec dbms_stats.lock_partition_stats('SCOTT','RECEIVED_DOCUMENTS','SETTELM');

Verify the setting:

select table_name,partition_name,subpartition_name,object_type,stattype_locked 
from user_tab_statistics 
where table_name='RECEIVED_DOCUMENTS';

TABLE_NAME                     PARTITION_NAME  SUBPARTITION_NAME OBJECT_TYPE          STATTYPE_LOCKED
------------------------------ --------------- ----------------- -------------------- ---------------
RECEIVED_DOCUMENTS                                               TABLE
RECEIVED_DOCUMENTS             APPLICANT                         PARTITION
RECEIVED_DOCUMENTS             APPLICATIONS                      PARTITION
RECEIVED_DOCUMENTS             SETTELM                           PARTITION            ALL

To reverse the process:

exec dbms_stats.unlock_table_stats('SCOTT','DEPT);
exec dbms_stats.unlock_partition_stats('SCOTT','RECEIVED_DOCUMENTS','SETTELM');

How to check if the "gather_database_stats" automatic maintenance jobs has succseeded

select id,operation,to_char(start_time,'dd.mm.yyyy hh24:mi:ss') "starttime",
                        to_char(end_Time,'dd.mm.yyyy hh24:mi:ss') "endtime",
                        to_dsinterval(to_char(end_time-start_Time,'dd.mm.yyyy hh24:mi:ss')) "elapsed",
                        status,job_name
from DBA_OPTSTAT_OPERATIONS
where operation = 'gather_database_stats (auto)'
order by start_time desc
fetch first 5 rows only;

Gave the following result:

ID	OPERATION	starttime	endtime	elapsed	STATUS	JOB_NAME
58778	gather_database_stats (auto)	07.02.2017 22:00:02	08.02.2017 02:00:15	+00 04:00:13.178197	TIMED OUT	ORA$AT_OS_OPT_SY_1965
58585	gather_database_stats (auto)	06.02.2017 22:00:06	07.02.2017 02:00:24	+00 04:00:18.089479	TIMED OUT	ORA$AT_OS_OPT_SY_1963
58273	gather_database_stats (auto)	05.02.2017 06:00:04	06.02.2017 02:00:26	+00 20:00:21.968028	TIMED OUT	ORA$AT_OS_OPT_SY_1950
58088	gather_database_stats (auto)	04.02.2017 06:00:02	05.02.2017 02:00:32	+00 20:00:29.696085	TIMED OUT	ORA$AT_OS_OPT_SY_1945
58038	gather_database_stats (auto)	03.02.2017 22:00:02	04.02.2017 02:00:17	+00 04:00:15.314408	TIMED OUT	ORA$AT_OS_OPT_SY_1943

The output indicates that the Automatic maintenance job never get to finish before the maintenance Windows Closes.

You can examine another view, dba_opstat_operation_tasks, to see the details on what the job didn't have time to do before the window closed:

select  opid, target,target_type, 
        to_char(start_Time,'dd.mm.yyyy hh24:mi:ss') "starttime",
        to_char(end_time,'dd.mm.yyyy hh24:mi:ss') "endtime"
        ,status
from dba_optstat_operation_tasks 
where opid=58778
order by start_Time desc
;

Sample output:

OPID	TARGET	TARGET_TYPE	starttime	endtime	STATUS
58778	SCOTT.TABLE1.SYS_SUBP25275	TABLE	08.02.2017 02:00:11	08.02.2017 02:00:11	TIMED OUT
58778	SCOTT.TABLE1.SYS_SUBP25267	TABLE	08.02.2017 02:00:11	08.02.2017 02:00:11	TIMED OUT

Notice how the starttime and endtime is identical; this is because the window Closes, and the job aborts. Oracle then records this timestamp as the start time and end time for the Objects it never had time to analyze.

The Predefined Maintenance Windows in Oracle 12c

Oracle documentation states:

"By default there are seven predefined maintenance windows, each one representing a day of the week. The weekend maintenance windows, SATURDAY_WINDOW and SUNDAY_WINDOW, are longer in duration than the weekday maintenance windows. The window group MAINTENANCE_WINDOW_GROUP consists of these seven windows."

Verify by executing:

SELECT WINDOW_NAME,REPEAT_INTERVAL,DURATION,to_char(LAST_START_DATE,'dd.mm.yyyy hh24:mi') "laststart",
    to_char(NEXT_START_DATE,'dd.mm.yyyy hh24:mi') "nextstart",ENABLED "window enabled?",ACTIVE "window open?"
FROM DBA_SCHEDULER_WINDOWS 
WHERE RESOURCE_PLAN='DEFAULT_MAINTENANCE_PLAN';

WINDOW_NAME	REPEAT_INTERVAL	DURATION	laststart	nextstart	window enabled?	window open?
SUNDAY_WINDOW	freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0	+00 20:00:00.000000	05.02.2017 06:00	12.02.2017 06:00	TRUE	FALSE
SATURDAY_WINDOW	freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0	+00 20:00:00.000000	04.02.2017 06:00	11.02.2017 06:00	TRUE	FALSE
FRIDAY_WINDOW	freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0	+00 04:00:00.000000	03.02.2017 22:00	10.02.2017 22:00	TRUE	FALSE
THURSDAY_WINDOW	freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0	+00 04:00:00.000000	02.02.2017 22:00	09.02.2017 22:00	TRUE	FALSE
WEDNESDAY_WINDOW	freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0	+00 04:00:00.000000	01.02.2017 22:00	08.02.2017 22:00	TRUE	FALSE
TUESDAY_WINDOW	freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0	+00 04:00:00.000000	07.02.2017 22:00	14.02.2017 22:00	TRUE	FALSE
MONDAY_WINDOW	freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0	+00 04:00:00.000000	06.02.2017 22:00	13.02.2017 22:00	TRUE	FALSE

The weekend Windows starts at 6 in the morning and run for 20 hours.
The weekday Windows starts at 22:00 and runs until 02:00 in the morning.

An overview of some important data Dictionary views can be found here

How to display a SYSTIMESTAMP data type as a TIMESTAMP WITH TZ

To display a value of datatype SYSTIMESTAMP as TIMESTAMP WITH TIME ZONE, use TO_CHAR first, then convert to TIMESTAMP WITH TIMEZONE.

The last expression adds one hour, which can be done after the conversion between the datatypes is complete:

select  TO_CHAR(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),
        TO_TIMESTAMP_TZ(to_char(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),
        TO_TIMESTAMP_TZ(to_char(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')+1/24
from dual;

systimestamp	TIMESTAMP_TZ	TIMESTAMP_TZ + 1hr
2017/02/01 10:36:28.158921 +01:00	01/02/2017 10:36:28,158921000 +01:00	01.02.2017 11:36:28

How to use gzip and tar together

To compress and tar a directory in one command, use the following syntax:

# tar -czvf mytarfile.tar.gz mydir

which will tar and compress the directory "mydir" with its contents.

To extract and untar a .tar.gz file in one command, use the following syntax:

# tar -zxvf mytarfile.tar.gz