DBA Hub

📋Steps in this guide1/4

Online Move of a Table in Oracle Database 12c Release 2 (12.2)

In Oracle Database 12c Release 2 (12.2) you can now perform an online move of a table, as well as partitions and sub-partitions.

oracle 12cconfigurationintermediate
by OracleDba
11 views
1

Setup

We create and populate a test table with a primary key and secondary index. This will be used for the example move operations.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
DROP TABLE t1 PURGE;

-- Create table.
CREATE TABLE t1 AS
SELECT level AS id,
       'Description for ' || level AS description,
       SYSDATE AS created_date
FROM   dual
CONNECT BY level <= 1000;
COMMIT;

ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id);
CREATE INDEX t1_created_date_idx ON t1(created_date);
2

MOVE (offline)

In previous releases moving a table was an offline operation, and by default would a mark indexes as unusuable. We can manually rebuild the indexes. Alternatively we could include the clause to manage the indexes for us.

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
-- Offline: Basic move.
ALTER TABLE t1 MOVE TABLESPACE users;

-- Check indexes.
SELECT index_name, status FROM user_indexes ORDER BY 1;

INDEX_NAME                     STATUS
------------------------------ --------
T1_CREATED_DATE_IDX            UNUSABLE
T1_PK                          UNUSABLE

2 rows selected.

SQL>

-- Manually rebuild indexes.
ALTER INDEX t1_pk REBUILD ONLINE;
ALTER INDEX t1_created_date_idx REBUILD ONLINE;

-- Check indexes.
SELECT index_name, status FROM user_indexes ORDER BY 1;

INDEX_NAME                     STATUS
------------------------------ --------
T1_CREATED_DATE_IDX            VALID
T1_PK                          VALID

2 rows selected.

SQL>

-- Offline: Include UPDATE INDEXES to manage the indexes.
ALTER TABLE t1 MOVE TABLESPACE users UPDATE INDEXES;

-- Check indexes.
SELECT index_name, status FROM user_indexes ORDER BY 1;

INDEX_NAME                     STATUS
------------------------------ --------
T1_CREATED_DATE_IDX            VALID
T1_PK                          VALID

2 rows selected.

SQL>
3

MOVE ONLINE

From Oracle 12.2 onward we can move the table as an online operation using the keyword. In addition to moving the table, the online move automatically maintains the indexes. We can also use this feature to change table compression and storage parameters in an online operation.

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
-- Online: Basic move.
ALTER TABLE t1 MOVE
ONLINE
TABLESPACE users;

-- Check indexes.
SELECT index_name, status FROM user_indexes ORDER BY 1;

INDEX_NAME                     STATUS
------------------------------ --------
T1_CREATED_DATE_IDX            VALID
T1_PK                          VALID

2 rows selected.

SQL>

-- Online: Change table compression.
ALTER TABLE t1 MOVE ONLINE TABLESPACE users COMPRESS;
ALTER TABLE t1 MOVE ONLINE TABLESPACE users NOCOMPRESS;

-- Online: Change storage parameters.
ALTER TABLE t1 MOVE ONLINE STORAGE (PCTINCREASE 0);
4

Restrictions

There are some restrictions associated with online moves of tables described here . - It can't be combined with any other clause. - It can't be used on a partitioned index-organized table or index-organized tables that have a column defined as a LOB, VARRAY, Oracle-supplied type, or user-defined object type. - It can't be used if there is a domain index on the table. - Parallel DML and direct path inserts are not supported against an object with an ongoing online move. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!