Move tables (online / offline) in Oracle (9i-12cR2)

I must thank my fellow DBA Franky Weber Faust for his publication in his blog. The goal here is to share the procedures for performing the move of tables in Oracle Database from version 9i to version 12cR2.

Here in this article I am using 12cR2 (12.2.0.1) to also be able to demonstrate the features of this version.So by taking the part of the Multitenant architecture, the procedures also work for non-CBD databases.

Move Online from tables using DBMS_REDEFINITION package

The purpose here is to move the T_MOVE1 table to the USERS2 tablespace without generating unavailability for the application using the DBMS_REDEFINITION package.This procedure works from Oracle Database 10gR1, although the DBMS_REDEFINITION package exists from Oracle Database 9i.

I create the table in question in the USERS tablespace from the ALL_OBJECTS view:

1

2

3

4

5

SQL>create table t_move1 tablespace users asselect *from all_objects;

Table created.

Elapsed:00:00:05.55

I create an index for this table also in the USERS tablespace:

1

2

3

4

5

SQL>create index i_move1 on t_move1(object_id)tablespace users;

Index created.

Elapsed:00:00:00.16

I check the created segments and also the tablespace where they are stored:

I consult the data from both tables to see if there are the same number of records:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SQL>select count(*)from t_move1;

COUNT(*)

----------

68108

Elapsed:00:00:00.10

SQL>select count(*)from t_move2;

COUNT(*)

----------

68108

Elapsed:00:00:00.05

Given that both are the same I will use the anonymous block below to copy the dependencies of the source table to the target table:

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

SQL>SET SERVEROUTPUT ON

SQL>DECLARE

num_errors PLS_INTEGER;

BEGIN

dbms_redefinition.copy_table_dependents(

'FRANKY',

'T_MOVE1',

'T_MOVE2',

copy_indexes=>dbms_redefinition.cons_orig_params,

num_errors=>num_errors

);

dbms_output.put_line(num_errors);

END;

/

DECLARE

*

ERROR at line1:

ORA-01442:column tobe modified toNOTNULLisalready NOTNULL

ORA-06512:at"SYS.DBMS_REDEFINITION",line2074

ORA-06512:at"SYS.DBMS_REDEFINITION",line2074

ORA-06512:at"SYS.DBMS_REDEFINITION",line1608

ORA-06512:at"SYS.DBMS_REDEFINITION",line1487

ORA-06512:at"SYS.DBMS_REDEFINITION",line408

ORA-06512:at"SYS.DBMS_REDEFINITION",line1480

ORA-06512:at"SYS.DBMS_REDEFINITION",line2056

ORA-06512:at"SYS.DBMS_REDEFINITION",line3115

ORA-06512:at"SYS.DBMS_REDEFINITION",line5578

ORA-06512:at line4

Elapsed:00:00:09.52

An error was returned stating that the source NULL columns are already with NOT NULL on the target.

We can ignore these errors by reporting the ignore_errors parameter with the value TRUE in the anonymous block below:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

SQL>DECLARE

num_errors PLS_INTEGER;

BEGIN

dbms_redefinition.copy_table_dependents(

'FRANKY',

'T_MOVE1',

'T_MOVE2',

copy_indexes=>dbms_redefinition.cons_orig_params,

num_errors=>num_errors,

ignore_errors=>true

);

dbms_output.put_line(num_errors);

END;

/

PL/SQL procedure successfully completed.

Elapsed:00:00:12.55

Another option is to use this anonymous block (by oracle-base.com) informing in the parameter copy_constraints the value FALSE:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

--Copy table dependents

SET SERVEROUTPUT ON

DECLARE

l_num_errors PLS_INTEGER;

BEGIN

DBMS_REDEFINITION.copy_table_dependents(

uname=>'FRANKY',

orig_table=>'T_MOVE1',

int_table=>'T_MOVE2',

copy_indexes=>DBMS_REDEFINITION.cons_orig_params,--Non-Default

copy_triggers=>TRUE,--Default

copy_constraints=>FALSE,--Non Default

copy_privileges=>TRUE,--Default

ignore_errors=>FALSE,--Default

num_errors=>l_num_errors);

DBMS_OUTPUT.put_line('l_num_errors='||l_num_errors);

END;

/

It is important to observe the other objects dependent on a table during the procedure, because in some versions of Oracle Database PL / SQL objects may be invalid, mainly triggers, because they are directly related to the tables.

After copying the dependencies I check the existing segments.Notice that we have the table T_MOVE1 and its index I_MOVE1 in the tablespace USERS and the table T_MOVE2 in the tablespace USERS2 and its index TMP $$ _ I_MOVE10 in the tablespace USERS:

Note that two more segments have been created: the MLOG table $ _T_MOVE1 and the I_MLOG index $ _T_MOVE1.Actually here we have a Materialized View Log and the T_MOVE2 table is like our Materialized View.We conclude that this process is done through a Snapshot method.

I’ll insert another record to see if sync happens automatically:

1

2

3

4

5

6

7

8

9

10

11

SQL>insert into t_move1(OWNER,OBJECT_NAME,OBJECT_ID,CREATED,LAST_DDL_TIME,NAMESPACE)values('C##FRANKY','ASDF',9999998,SYSDATE,SYSDATE,999);

1row created.

Elapsed:00:00:00.07

SQL>commit;

Commit complete.

Elapsed:00:00:00.03

When looking at the tables we can see that the synchronism did not occur:

As I am with recyclebin enabled the segments remain with the prefix BIN $ … so it is necessary to clean the recyclebin:

1

2

3

4

5

SQL>purge recyclebin;

Recyclebin purged.

Elapsed:00:00:00.39

The move from table T_MOVE1 to tablespace USERS2 was completed successfully online.

Move from tables using MOVE (offline)

The purpose here is to move the T_MOVE1 table to the USERS tablespace using the MOVE clause of the ALTER TABLE command.This method generates unavailability for application and also invalidates table indexes.This procedure works from Oracle Database 9i.

I’ll look at the segments and check in which tablespace they are stored:

Move from tables using MOVE ONLINE (online)

The purpose here is to move the T_MOVE1 table to the USERS2 tablespace using the MOVE ONLINE clause of the ALTER TABLE command.This method does not generate unavailability for application and does not invalidate the table indexes.This procedure works from Oracle Database 12cR2.

I’ll look at the segments and check in which tablespace they are stored: