DBA Hub

📋Steps in this guide1/1

Split partition online oracle 12.2 DBACLASS

Split partition online oracle 12.2. This is a new feature of oracle 12.2. We can split partitions online, without impacting the DML statements.

oracle clusteringintermediate
by OracleDba
11 views
1

Overview

In Oracle 12.2, We can split partitions or subpartitions online without impacting the DML statements.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
SQL> select partition_name,read_only,high_value from dba_tab_partitions where table_name='ORDER_TAB';

PARTITION_NAME                   READ HIGH_VALUE
-------------------------------- ---- --------------------------------------------------------------------------------
CREATED_2105_P10                 NO   TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P11                 NO   TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P12                 NO   TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P8                  NO   TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P9                  NO   TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_MX                       NO   MAXVALUE

6 rows selected.

SQL> alter table order_tab split partition CREATED_MX  into
(partition CREATED_2106_P2 VALUES LESS THAN (TO_DATE('01/03/2016', 'DD/MM/YYYY')),PARTITION CREATED_MX) ONLINE;  2

Table altered.

SQL>  select partition_name,read_only,high_value from dba_tab_partitions where table_name='ORDER_TAB';

PARTITION_NAME                   READ HIGH_VALUE
-------------------------------- ---- --------------------------------------------------------------------------------
CREATED_2105_P10                 NO   TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P11                 NO   TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P12                 NO   TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P8                  NO   TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2105_P9                  NO   TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_2106_P2                  NO   TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
CREATED_MX                       NO   MAXVALUE

8 rows selected.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!