DBA Hub

📋Steps in this guide1/4

Online Move of Partitions and Sub-Partitions in Oracle 12c Release 1

In Oracle 12c it is now possible to move table partitions and sub-partitions as online operations.

oracle 12cconfigurationintermediate
by OracleDba
14 views
1

MOVE PARTITION ... ONLINE

The following example shows how to online move a table partition. We can now move a partition using the keyword.

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- Create partitioned table.
DROP TABLE t1 PURGE;

CREATE TABLE t1
(id            NUMBER,
 description   VARCHAR2(50),
 created_date  DATE)
PARTITION BY RANGE (created_date)
(PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users);


-- Populate it.
INSERT INTO t1
SELECT level,
       'Description for ' || level,
       CASE
         WHEN MOD(level,2) = 0 THEN TO_DATE('01/07/2014', 'DD/MM/YYYY')
         ELSE TO_DATE('01/07/2015', 'DD/MM/YYYY')
       END
FROM   dual
CONNECT BY level <= 1000;
COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 't1');


-- Check partitions.
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20

SELECT table_name,
       partition_name,
       num_rows
FROM   user_tab_partitions
ORDER BY 1,2;

TABLE_NAME           PARTITION_NAME         NUM_ROWS
-------------------- -------------------- ----------
T1                   PART_2014                   500
T1                   PART_2015                   500

SQL>

ALTER TABLE t1 MOVE PARTITION part_2015 ONLINE TABLESPACE users UPDATE INDEXES;

Table altered.

SQL>
2

MOVE SUBPARTITION ... ONLINE

The following example shows how to online move a table sub-partition. We can now move a sub-partition using the keyword.

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- Create sub-partitioned table.
DROP TABLE t1 PURGE;

CREATE TABLE t1
(id            NUMBER,
 description   VARCHAR2(50),
 created_date  DATE)
PARTITION BY RANGE (created_date)
SUBPARTITION BY HASH (id)
SUBPARTITIONS 4
(PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users);


-- Populate it.
INSERT INTO t1
SELECT level,
       'Description for ' || level,
       CASE
         WHEN MOD(level,2) = 0 THEN TO_DATE('01/07/2014', 'DD/MM/YYYY')
         ELSE TO_DATE('01/07/2015', 'DD/MM/YYYY')
       END
FROM   dual
CONNECT BY level <= 1000;
COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 't1', granularity => 'SUBPARTITION');


-- Check sub-partitions.
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20

SELECT table_name,
       partition_name,
       subpartition_name,
       num_rows
FROM   user_tab_subpartitions
ORDER BY 1,2,3;

TABLE_NAME           PARTITION_NAME       SUBPARTITION_NAME      NUM_ROWS
-------------------- -------------------- -------------------- ----------
T1                   PART_2014            SYS_SUBP786                 214
T1                   PART_2014            SYS_SUBP787                 272
T1                   PART_2014            SYS_SUBP788                 242
T1                   PART_2014            SYS_SUBP789                 272
T1                   PART_2015            SYS_SUBP790                 254
T1                   PART_2015            SYS_SUBP791                 216
T1                   PART_2015            SYS_SUBP792                 280
T1                   PART_2015            SYS_SUBP793                 250

SQL>

ALTER TABLE t1 MOVE SUBPARTITION SYS_SUBP793 ONLINE TABLESPACE users UPDATE INDEXES;

Table altered.

SQL>
3

Restrictions

There are some restrictions associated with the clause. - It cannot be used for tables owned by SYS, IOTs, tables with object types, or tables with bitmap join or domain indexes. - It cannot be used in 12.1.0.1 when database-level supplemental logging is enabled. From 12.1.0.2 this restriction is lifted. - Parallel DML and direct path inserts are not supported against an object with an ongoing online move. - This functionality is not available for materialized views.
4

MOVE ONLINE (12.2)

Oracle Database 12c Release 2 (12.2) allows regular tables to be moved online, as described here . For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!