How to move a partition online from Oracle 12.1 and onwards

From Oracle version 12.1 and ownwards, moving partitions and subpartitions becomes non-blocking DDL operations. DML against the table can continue while the subobject is being moved. Oracle guarantees that global indexes are maintained during the move operation, so you no longer have to specify the "UPDATE INDEXES ONLINE"-clause.

Remember that skipping this clause will trigger Asynchronous Global Index Maintenance during the nightly maintenance window.

To move a subpartition to a new tablespace:

ALTER TABLE MYUSER.MYTABLE 
MOVE SUBPARTITION P_201312 ONLINE
TABLESPACE MYTABSPC;

To compress the subpartition data at the same time, use

ALTER TABLE MYUSER.MYTABLE 
MOVE SUBPARTITION P_201312_P_OPLSUM ONLINE 
TABLESPACE MYTABSPC 
ROW STORE COMPRESS ADVANCED;

To move a partition to a new tablespace using a DOP of 4 while updating any global indexes defined on the table:

ALTER TABLE MYUSER.MYTABLE
MOVE PARTITION P_2018_05 ONLINE
TABLESPACE NEWTABLESPACE
PARALLEL 4;

To compress the partition data at the same time, use

ALTER TABLE MYUSER.MYTABLE
MOVE PARTITION P_2018_06 ONLINE
TABLESPACE NEWTABLESPACE
ROW STORE COMPRESS ADVANCED
PARALLEL 4;

Remember to change the properties for the partition and the table, too:

ALTER TABLE MYUSER.MYTABLE
MODIFY DEFAULT ATTRIBUTES FOR PARTITION P201312
TABLESPACE MYTABSPC;

ALTER TABLE MYUSER.MYTABLE 
MODIFY DEFAULT ATTRIBUTES 
TABLESPACE MYTABSPC;

A simple query to find how much space the partitions are using would be:

select partition_name,tablespace_name, round(sum(bytes)/1024/1024/1024,1) "GB"
from dba_segments
where segment_name='MYTABLE'
group by partition_name,tablespace_name
order by 1;

Example output:

PARTITION_NAME	TABLESPACE_NAME	GB
SYS_SUBP102302	DATA2	8,9
SYS_SUBP102303	DATA2	2
SYS_SUBP102304	DATA2	0
SYS_SUBP120071	DATA2	7,9
SYS_SUBP120072	DATA	1,8
SYS_SUBP120073	DATA2	0
SYS_SUBP12401	DATA	129,5

Source: Oracle Documentation, New Features list, Version 12.1.0.1. See section "2.2.3.4 ONLINE Move Partition"

What exactly is a LOB index object?

This question was answered by Tom Kyte in an old post from 2001.


A LOB is simply a pointer. It points to an index. The index points to the chunks that make up the LOB.
Hence when you create a LOB, you will always get a lob index created (to find the chunks for the LOB fast) and a segment that holds the LOB data (chunks).

and

You control the placement of the LOB segment. You have NO control over the LOB index, it is an internal structure and goes where it goes. The LOB index is considered "part of the LOB" itself.

How to split a default subpartition in a table using a subpartition template

Here is an example on how to split a subpartition utilizing a "subpartition template".

Before the change, the table looked as follows:

CREATE TABLE RECEIVED_DOCUMENTS
(
  DOCID                       VARCHAR2(160 BYTE)     NULL,
  FISCAL_YEAR                 NUMBER(4)             NULL,
  DOCUMENT_TYPE               VARCHAR2(100 CHAR)     NULL,
  DOCUMENT_NAME               VARCHAR2(1000 CHAR)     NULL,
  VALID                       CHAR(1 BYTE)          NULL,
  CREATED_TIMESTAMP           NUMBER(20)            NULL,
  VERSION                     NUMBER(20)            NULL,
  DOC_XML                     CLOB                  NULL,
  PERIOD                      VARCHAR2(1000 CHAR)     NULL,
  TRANSACTION_ID              VARCHAR2(1000 BYTE)     NULL
)
LOB (DOC_XML) STORE AS SECUREFILE RECEIVED_DOCUMENT_XML_LOB (TABLESPACE  LOB_DATA)
TABLESPACE USERS
PARTITION BY LIST (DOCUMENT_TYPE)
SUBPARTITION BY LIST (PERIOD)
SUBPARTITION TEMPLATE
  (SUBPARTITION SP_2014 VALUES ('2014') TABLESPACE DIV_2014,
   SUBPARTITION SP_2015 VALUES ('2015') TABLESPACE DIV_2015,
   SUBPARTITION SP_2016 VALUES ('2016') TABLESPACE DIV_2016,
   SUBPARTITION SP_DEFAULT VALUES (default) TABLESPACE DIV_DEFAULT
   )
  (  
  PARTITION CUSTOMS_DEC VALUES ('EU_CUST', 'NONEU_CUST'),
  PARTITION VAT_REF VALUES ('EU_VAT_REF', 'EES_VAT_REF','NONEU_VAT_REF'),
  PARTITION DIV_DOCUMENTS VALUES (default)
  )
;

First, alter the subpartition template. I also take the opportunity to create template for subpartitions in the years to come, up until 2020:

ALTER TABLE RECEIVED_DOCUMENTS
SET SUBPARTITION TEMPLATE
  (SUBPARTITION SP_2014 VALUES ('2014') TABLESPACE DIV_2014,
   SUBPARTITION SP_2015 VALUES ('2015') TABLESPACE DIV_2015,
   SUBPARTITION SP_2016 VALUES ('2016') TABLESPACE DIV_2016,
   SUBPARTITION SP_2017 VALUES ('2017') TABLESPACE DIV_2017,
   SUBPARTITION SP_2018 VALUES ('2018') TABLESPACE DIV_2018,
   SUBPARTITION SP_2019 VALUES ('2019') TABLESPACE DIV_2019,
   SUBPARTITION SP_2020 VALUES ('2020') TABLESPACE DIV_2020,
   SUBPARTITION SP_DEFAULT VALUES (default) TABLESPACE DIV_DEFAULT
  );

Then, split the resulting default subpartition DIV_DOCUMENTS_SP_DEFAULT, into multiple other subpartitions. In this example, I am splitting out rows in the DIV_DOCUMENTS_SP_DEFAULT subpartition which have their PERIOD value set to the string '2017':

ALTER TABLE RECEIVED_DOCUMENTS SPLIT SUBPARTITION DIV_DOCUMENTS_SP_DEFAULT INTO
     (  SUBPARTITION DIV_SP_2017 VALUES ('2017') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_01 VALUES ('2017-01') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_02 VALUES ('2017-02') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_03 VALUES ('2017-03') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_04 VALUES ('2017-04') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_05 VALUES ('2017-05') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_06 VALUES ('2017-06') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_07 VALUES ('2017-07') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_08 VALUES ('2017-08') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_09 VALUES ('2017-09') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_10 VALUES ('2017-10') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_11 VALUES ('2017-11') TABLESPACE DIV_2017
      , SUBPARTITION DIV_SP_2017_12 VALUES ('2017-12') TABLESPACE DIV_2017
      , SUBPARTITION DIV_DOCUMENTS_SP_DEFAULT) UPDATE INDEXES PARALLEL 8;

Notice in the above code that I also consider the other commonly used strings to indicate the year 2017. Of course, a row with a value of 'January 2017' in the PERIOD column will not find an appropriate partition according to the template, and thus end up in the default subpartition DIV_DOCUMENTS_SP_DEFAULT.

Let's add more partitions, this time to be prepared for the year 2018:

ALTER TABLE RECEIVED_DOCUMENTS SPLIT SUBPARTITION DIV_DOCUMENTS_SP_DEFAULT INTO
     (  SUBPARTITION DIV_SP_2018 VALUES ('2017') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_01 VALUES ('2017-01') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_02 VALUES ('2017-02') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_03 VALUES ('2017-03') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_04 VALUES ('2017-04') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_05 VALUES ('2017-05') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_06 VALUES ('2017-06') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_07 VALUES ('2017-07') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_08 VALUES ('2017-08') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_09 VALUES ('2017-09') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_10 VALUES ('2017-10') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_11 VALUES ('2017-11') TABLESPACE DIV_2018
      , SUBPARTITION DIV_SP_2018_12 VALUES ('2017-12') TABLESPACE DIV_2018
      , SUBPARTITION DIV_DOCUMENTS_SP_DEFAULT) UPDATE INDEXES PARALLEL 8;

This time, the split was considerably faster, since the resulting partitions for 2018 ended up with zero rows in them. Oracle can therefore perform a "fast split".

Read more in the 12.2 documentation

Some important tuning parameters in Oracle 12c

Here are some parameters that you will have to deal with when tuning an Oracle 12c database. Execute the following SQL to view their current setting:

col name format a40
col value format a20
col description format a80
set lines 200

SELECT NAME,VALUE,DESCRIPTION 
FROM   V$SYSTEM_PARAMETER
WHERE NAME IN ( 'optimizer_adaptive_features',
                'optimizer_adaptive_reporting_only',
                'optimizer_features_enable',
                'optimizer_use_sql_plan_baselines',
                'optimizer_capture_sql_plan_baselines',
                'optimizer_dynamic_sampling',
                'optimizer_adaptive_plans',
                'optimizer_adaptive_statistics');

OPTIMIZER_ADAPTIVE_FEATURES enables or disables all of the adaptive optimizer features, including

* adaptive plan (adaptive join methods and bitmap pruning)
* automatic re-optimization
* SQL plan directives
* adaptive distribution Methods

OPTIMIZER_DYNAMIC_SAMPLING controls both when the database gathers dynamic statistics, and the size of the sample that the optimizer uses to gather the statistics.

Dynamic statistics were called dynamic sampling in versions prior to 12c.

Range of values: 0 to 11. Default depends on the value of the parameter optimizer_features_enable:

• If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2 
• If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1 
• If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0

At level 2, dynamic statistics are used "if at least one table in the statement has no statistics".

At level 4, Oracle is less restrictive and will use dynamic statistics when "at least one table in the statement has no statistics, the statement has one or more expressions used in the WHERE clause predicates... or the statement uses complex predicates".

When this parameter is set to 11, the optimizer will use dynamic statistics to verify cardinality estimates for all SQL operators, and it will determine an internal time limit to spend verifying the estimates.

Notice that Oracle says the following about level 11:

There are cases where the optimizer will automatically decide to use 11, for example:

•The query will run in parallel.
•The query was executed before and its history is available (from the cursor cache, Automatic Workload Repository, or the SQL Management Base).


From personal experience, this parameter is important! If it's turned off, I would recommend setting it to the default value. I have seen many cases where queries will actually perform better only by setting this parameter from 0 (off) to the default value.

Read more about the different Levels:
Oracle 12cR1
Oracle 12cR2

The two parameters appearing last in the list, optimizer_adaptive_plans and optimizer_adaptive_statistics, were added in 12.2, and replace the parameter optimizer_adaptive_features.

What does 1048575 in the Pstop column in the explain plan indicate?

The answer lies in the dictionary view ALL_PART_TABLES (or DBA_PART_TABLES).
The column partition_count is the total number of partitions, but for interval partitioned tables, this number is always 1048575.


When you explain a SQL statement that is accessing a partitioned table using interval partitioning, you may notice how the number 1048575 always shows up in the Pstop column of your EXPLAIN PLAN output.

My table and the local index is partitoned as follows:

CREATE TABLE MYTABLE
(
  SEKNUM           NUMBER(19)            DEFAULT "MYTAB_MYSEQ"."NEXTVAL" NOT NULL,
  COL1             VARCHAR2(200 CHAR)    NOT NULL,
  COL2             VARCHAR2(100 CHAR)    NOT NULL,
PARTITION BY RANGE (SEKNUM)
INTERVAL( 100000)
);

CREATE UNIQUE INDEX UK_SEK ON HENDELSER_PART_ST1_S.HENDELSER
(SEKVENSNUMMER)
  LOCAL;

This gives me a table with about 200 partitions.

When executing a query using the > operator, like below:

set lines 200
set autot on explain

SELECT count(*)
FROM   mytable
WHERE  seknum > 430000;

It would generate the following plan

--------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |            |     1 |     6 | 41342   (4)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE           |            |     1 |     6 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|            |    20M|   115M| 41342   (4)| 00:00:02 |     5 |1048575|
|*  3 |    INDEX RANGE SCAN       | UK_SEK     |    20M|   115M| 41342   (4)| 00:00:02 |     5 |1048575|
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("SEKNUM">430000)

The Pstop value can be confusing. Obviously, you don't have that many partitions.
To verify that partition pruning is indeed working, change the operator from > ("greater than") to < ("less than"), and rerun the query:

--------------------------------------------------------------------------------------------------------

| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |            |     1 |     6 |   887   (4)| 00:00:01 |       |       |

|   1 |  SORT AGGREGATE           |            |     1 |     6 |            |          |       |       |

|   2 |   PARTITION RANGE ITERATOR|            |   431K|  2525K|   887   (4)| 00:00:01 |     1 |     5 |

|*  3 |    INDEX RANGE SCAN       | UK_SEK     |   431K|  2525K|   887   (4)| 00:00:01 |     1 |     5 |

--------------------------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



3 - access("SEKNUM"<430000)

You now see that the Pstop column is 5, so Oracle search partitions 1 through 4 for the rows.

These are:

set lines 200
col partition_name format a30
col high_value format a20

select partition_name,partition_position,high_value
from dba_tab_partitions 
where table_NAME='MYTABLE'
and table_owner='SCOTT'
and partition_position between 1 and 4;

PARTITION_NAME                 PARTITION_POSITION HIGH_VALUE          
------------------------------ ------------------ --------------------
P1_INIT                                         1 100001              
SYS_P9545                                       2 200001              
SYS_P9548                                       3 300001              
SYS_P9551                                       4 400001              

4 rows selected.

How to use the 12c Privilege Analysis feature

One of the many New features in Oracle 12c is the ability to perform analysis of privileges that are assigned to a user.

Privilege Analysis sorts under Oracles "Database Vault" option, but can be used without enabling Database Vault.
Note that you need a valid License for Database Vault option to use the privilege analysis package.

To check if it's allready in use:

SELECT
   NAME             ,
   DETECTED_USAGES  ,
   FIRST_USAGE_DATE ,
   LAST_USAGE_DATE,
   LAST_SAMPLE_DATE,
   CURRENTLY_USED,
   DESCRIPTION   
 FROM
   DBA_FEATURE_USAGE_STATISTICS
 WHERE
   FIRST_USAGE_DATE IS NOT NULL
   AND NAME LIKE 'Privilege%';

From my example database, this is the output:

NAME	DETECTED_USAGES	FIRST_USAGE_DATE	LAST_USAGE_DATE	LAST_SAMPLE_DATE	CURRENTLY_USED	DESCRIPTION
Privilege Capture	11	27.05.2017 03:41:35	05.08.2017 03:46:46	05.08.2017 03:46:46	TRUE	Privilege Capture is being used

Here's how to set it up:

1. Create the capture:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.create_capture(
    name        => 'my_policy',
    type        => DBMS_PRIVILEGE_CAPTURE.g_context,
    condition   => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SCOTT'''
  );
END;
/

2. Check that the capture was created:

COLUMN name FORMAT A15
COLUMN roles FORMAT A20
COLUMN context FORMAT A30
SET LINESIZE 100

SELECT name,
       type,
       enabled,
       roles,
       context
FROM   dba_priv_captures
ORDER BY name;

3. Enable the capture

BEGIN
  DBMS_PRIVILEGE_CAPTURE.enable_capture('my_policy');
END;
/

When a representative time has passed, you can disable the capture and generate results:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.disable_capture(
    name        => 'saga2_felles_policy'
  );

  DBMS_PRIVILEGE_CAPTURE.generate_result(
    name        => 'saga2_felles_policy'
  );
END;
/

You can check the results of the capture by using these queries:

SET LINESIZE 200
COLUMN username FORMAT A20
COLUMN username FORMAT A20
COLUMN used_role FORMAT A30
COLUMN path FORMAT A50
COLUMN sys_priv FORMAT A30
COLUMN obj_priv FORMAT A8
COLUMN object_owner FORMAT A15
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A11

prompt ================================
prompt Which privileges have been used?
prompt Look in DBA_USED_SYSPRIVS
prompt ================================
SELECT username, sys_priv
FROM   dba_used_sysprivs
WHERE  capture = 'my_policy'
ORDER BY username, sys_priv;

prompt ================================
prompt How were the privileges granted
prompt to the user?
prompt Look in DBA_USED_SYSPRIVS_PATH
prompt ================================
SELECT username, sys_priv, used_role, path
FROM   dba_used_sysprivs_path
WHERE  capture='my_policy'
order by username,sys_priv;

prompt ================================
prompt What object privileges were necessary?
prompt Look in DBA_USED_OBJPRIVS
prompt ================================
SELECT username, obj_priv, object_owner, object_name, object_type
FROM   dba_used_objprivs
WHERE  capture = 'my_policy';

prompt ================================
prompt How were the object privileges granted to the user?
prompt Look in DBA_USED_OBJPRIVS_PATH
prompt ================================

SELECT username, obj_priv, object_owner, object_name, used_role, path
FROM   dba_used_objprivs_path
WHERE  capture = 'my_policy';

exit

To drop the usage of Privilage Capture all together:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.drop_capture(
    name        => 'my_policy'
  );
END;
/

How to rebuild an index subpartition

To limit a rebuild to a specific index subpartition only, use the following syntax:

ALTER INDEX SCOTT.SALES_IDX3
  REBUILD SUBPARTITION SALES_IDX3_SP_2017_06 ONLINE
  TABLESPACE DATA2;

Notice the use of the ONLINE keyward, which will allow DML against the table (and thus update the index).

To generate rebuild-statements, one index at a time, I have used the script below.

It takes as arguments
1. The index owner
2. The index name
3. The new tablespace where you want to place your subpartitions
4. The old tablespace from which you want to move out

set termout off
select 'alter session set nls_language=''american'';' from dual;
set termout on
accept index_owner prompt 'Index owner: '
accept index_name prompt 'Index name: '
accept new_tabspc prompt 'New tablespace: '
accept old_tabspc prompt 'Old tablespace: '

set lines 300
set heading off
set feedback off
set verify off
set echo off
set pages 0
set trimspool on
spool exec_move_subpart_&&index_owner..&&index_name..sql
select 'alter session set nls_language=''american'';' from dual;

select 'alter session force parallel ddl;' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool exec_rebuild_subpart_&&index_owner..&&index_name..log' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>''Move'', action_name=>''Rebuild_supbart_&&index_name'');' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>''Rebuild_supbart_t_&&index_name'');' from dual;
select 'exec DBMS_SESSION.SET_IDENTIFIER (client_id=>''Rebuild_supbart_&&index_name''); ' from dual;

select 'prompt moving the index subpartitions from &&old_tabspc to &&new_tabspc,' from dual;
select 'prompt and setting default attributes for the involved partitions. ' from dual;
SELECT 'ALTER INDEX ' ||  IDXSUBPART.INDEX_OWNER || '.' || IDXSUBPART.INDEX_NAME || ' rebuild subpartition ' || IDXSUBPART.SUBPARTITION_NAME || ' tablespace &&new_tabspc ONLINE;'
FROM   DBA_IND_SUBPARTITIONS IDXSUBPART JOIN DBA_SEGMENTS S
ON     (IDXSUBPART.SUBPARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXSUBPART.INDEX_NAME='&&index_name'
AND    IDXSUBPART.TABLESPACE_NAME='&&old_tabspc'
AND    S.SEGMENT_TYPE='INDEX SUBPARTITION'
union
select distinct 'alter index ' ||   IDXSUBPART.INDEX_OWNER || '.' || IDXSUBPART.INDEX_NAME || ' modify default attributes for partition ' || idxsubpart.partition_name || ' tablespace &&new_tabspc;'
FROM   DBA_IND_SUBPARTITIONS IDXSUBPART JOIN DBA_SEGMENTS S
ON     (IDXSUBPART.SUBPARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXSUBPART.INDEX_NAME='&&index_name'
AND    IDXSUBPART.TABLESPACE_NAME='&&old_tabspc'
AND    S.SEGMENT_TYPE='INDEX SUBPARTITION';

select 'exit' from dual;
exit

Run it as a user with access to the data dictionary:

sqlplus / as sysdba @gen_move_idx_subpart.sql

It will generate a new script called exec_move_subpart_<username>.<index_name>.sql which will actually perform the rebuild:

sqlplus / as sysdba @exec_move_subpart_<username>.<index_name>.sql

I also added som calls to dbms_application_info so that the session can be easily found in v$session