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.

Let’s first configure our environment before performing the move …

Setting up the environment

It is connected to the Root container:

Check out existing Pluggable Databases:

I create a new PDB called SOEPDB:

I elevate my session to the newly created PDB:

I open the PDB in question:

I create the user FRANKY already with the Role DBA:

I create two tablespaces to perform the tests:

I connect to the PDB:

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:

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

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

Verify that the T_MOVE1 table of the owner FRANKY can be redefined:

I made a mistake in checking because this table does not have a primary key. We can only use this method for tables that have PK.

I create the PK constraint in the table:

Again check if the table can be redefined:

I create the T_MOVE2 table in the USERS2 tablespace based on T_MOVE1, but without its data:I create the T_MOVE2 table in the USERS2 tablespace based on T_MOVE1, but without its data:

Start the reset process by specifying the owner, the source table, and the target table (which must be empty):
I consult the data from both tables to see if there are the same number of records:

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:

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:

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

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:

I’m going to insert a new record in the T_MOVE1 table:

And now I compare the source table with the target table:

Note that we have a different amount of records, so we concluded that our application could continue working on the source table during this move process.

To match the tables we need to execute the synchronism. For this we use the procedure SYNC_INTERIM_TABLE passing again the owner, the source table and the target table:

I now refer to the T_MOVE2 table to see if it has been synchronized with T_MOVE1:

Again I consult the segments created after this synchronization process:

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:

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

You must do this manually:

Now the tables are synchronized:

I consult again the segments to verify that no other was created:

Now to finish the process I execute the FINISH_REDEF_TABLE procedure. This procedure will sync final and remove the Materialized View Log:

What really happened was a rename of the tables involved. The redefinition process does this transparently.

Now I remove the T_MOVE2 table:

 I consult the table index T_MOVE1 to confirm that its state is valid after the reset:

Again I consult the segments:

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

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:

I confirm that the index is valid:

Move the T_MOVE1 table to the USERS tablespace:

I verify that the index has become unusable (unusable):

I check the segments and note that the index object exists, but not its segment:

I also rebuilt the index in the USERS tablespace:

Now the index has become valid:

The index segment was created after its rebuild:

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:

I move the T_MOVE1 table to the USERS2 tablespace using MOVE ONLINE:

I check the state of the table index and note that it remains valid:

Here as process bonuses I rebuild the index online in the tablespace USERS2:

I check the segments again:

Table and index moved to the USERS2 tablespace without generating unavailability for the application using the new MOVE ONLINE feature of Oracle Database 12cR2.

Moving tables in Oracle has other methods not covered in this article, but you can also move them using DataPump.

I hope this article is useful for many people. If you liked, subscribe to the blog as a contribution and still know every time you leave a new article.