DBA Hub

📋Steps in this guide1/5

Partitioning New features in Oracle 12c DBACLASS

Partitioning New features in Oracle 12c . Features like adding, dropping, truncating,merging multiple partitions and splitting single partition to multiple .

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

Below are the partitioning enhancements that has been introduced in Oracle 12c version. 1. Online partition move: From oracle 12c, we can move partitions online without blocking the DML statements during that time. — To maintain global indexes

Code/Command (click line numbers to comment):

1
ALTER TABLE DBACLASS MOVE PARTITION DBA_JAN2014 ONLINE;
2

Section 2

— Moving to another tablespace: 2. Adding multiple partitions with One command:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
ALTER TABLE DBACLASS MOVE PARTITION DBA_JAN2014 ONLINE UPDATE GLOBAL INDEXES;

ALTER TABLE DBACLASS MOVE PARTITION DBACLASS_JAN2014 ONLINE TABLESPACE TESTTS UPDATE INDEXES;

ALTER TABLE CMADMIN.DBACLASS ADD
PARTITION DBACLASS_JAN VALUES LESS THAN (TO_DATE('01-FEB-2016','DD-MON-YYYY')) TABLESPACE USERS,
PARTITION DBACLASS_FEB VALUES LESS THAN (TO_DATE('01-MAR-2016','DD-MON-YYYY')) TABLESPACE USERS,
PARTITION DBACLASS_MAR VALUES LESS THAN (TO_DATE('01-APR-2016','DD-MON-YYYY')) TABLESPACE USERS,
UPDATE GLOBAL INDEXES;
3

Section 3

3. Drop/Truncate multiple partitions with one command: —For Drop — For truncate

Code/Command (click line numbers to comment):

1
2
3
ALTER TABLE CMADMIN.DBACLASS DROP PARTITIONS DBACLASS_JAN, DBACLASS_FEB, DBACLASS_MAR UPDATE GLOBAL INDEXES;

ALTER TABLE CMADMIN.DBACLASS DROP PARTITIONS DBACLASS_JAN, DBACLASS_FEB, DBACLASS_MAR UPDATE GLOBAL INDEXES;
4

Section 4

4. Merging multiple partitions to single partition — NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present 5. Splitting single partition to multiple partitions:

Code/Command (click line numbers to comment):

1
2
3
ALTER TABLE CMADMIN.DBACLASS MERGE PARTITIONS 
DBACLASS_JAN, DBACLASS_FEB, DBACLASS_MAR 
INTO partition DBACLASS_Q1;
5

Section 5

- How to install oracle 12c on Linux - Oracle 12c Multitenent Architecture - How to create a multi tenant database in oracle 12c - Upgrade database from 11g to 12c manually - Upgrade database from 11g to 12C using DBUA - Upgrade database using OEM 12C cloud control - How to rename a pluggable database ( PDB) - How to clone a pluggable database for existing PDB - How to create a pluggable database in oracle 12c - How to unplug and plug in pluggable database in oracle 12c - Convert non CDB database to PDB database in oracle 12c - How to open the PDBS automatically when CDB restarts - Cross platform migration using transportable tablespace 12c with zero downtime - Rolling forward standby database when archives missing in primary in 12c - Restore missing datafile in standby database oracle 12c - GRANT READ PRIVILEGE – Oracle 12c new feature - How to apply JVM patch in oracle 12c database - Tablespace monitoring in oracle 12c multitenant database. - Rman backup in multitenant database oracle 12c - ENABLE_DDL_LOGGING in oracle 12c - truncate table with cascade feature in oracle 12c - Invisible column in oracle 12c - LOGTIME=ALL parameter in datapump of oracle 12c - views_as_tables parameter in datapump of oracle 12c - How to Import data with nologgin option in oracle 12c - How to archive table rows in oracle 12c - how to use DBMS_PRIVILEGE_CAPTURE to capture privs in oracle 12c - RMAN active cloning in Oracle 12c - Database Proactive Bundle Patching

Code/Command (click line numbers to comment):

1
2
3
4
ALTER TABLE CMADMIN.DBACLASS SPlIT PARTITIONS DBACLASS_Q1 INTO
(PARTITION DBACLASS_JAN values less than (TO_DATE('01-FEB-2016','dd-MON-yyyy')),
PARTITION DBACLASS_FEB values less than (TO_DATE('01-MAR-2016','dd-MON-yyyy')),
PARTITION DBACLASS_MAR);

Comments (0)

Please to add comments

No comments yet. Be the first to comment!