Moving Oracle Database Objects to Another Tablespace in 19c (Table, Index, LOB Column)

A Tale of Terabytes: Moving Thousands of Database Objects

Recently, we encountered a block corruption issue that, fortunately, only affected an index. We resolved it by dropping and recreating the index. However, this incident sparked the idea of reorganizing the storage for all database objects in a legacy database, which had been successfully upgraded to 19c and still handles high transaction rates.

The absence of consistent storage rules had led to various types of database objects and applications sharing the same tablespaces for years, resulting in a significant organizational mess. While moving objects across schemas is typically straightforward, we faced the huge challenge of reorganizing thousands of objects – nearly 50,000 in total – spread across different schemas and encompassing a wide variety of types, such as tables, indexes (including spatial and text indexes), index-organized tables, LOBs and more.

Although newer database versions allow most types of objects to be moved online, the scale of the task made me a bit anxious about encountering potential bugs. However, if you don’t try, you’ll never know.

I generally write my posts with the primary goal of sharing knowledge with the community. For this post, though, my main motivation is to create a cheatsheet for myself. Beware that everything changes constantly. This blog post is written for Oracle 19c RU24, and every database environment is unique. Keep in mind how much trust to place in advice you get from articles about Oracle published on the internet.

There is a document on Oracle Support titled How to Move Different Database Objects to Another Tablespace (Doc ID 1915040.1) that you may find helpful. I am confident that I can provide you with additional information as well.

Old Logic (No logic actually): Store objects wherever desired, without any defined rules. Database objects are stored without consideration for their types or the applications they serve.

New Logic: We decided to follow a hybrid approach of grouping by types and applications. For some applications serving the same domain or organizational units, objects like LOBs and indexes are separated based on their types. For small, isolated applications, all objects belonging to the application are grouped into a dedicated tablespace.

Targeted Outcome :

The gains of this hybrid approach are simplified management, better resource allocation, easier backup and recovery, reduced fragmentation, improved performance (lower segment size and lower cost for FTS) and better scalability. Grouping database objects according to their types, such as separating LOBs and indexes, helps optimize space usage and improves I/O performance. For small, isolated applications, grouping objects by application into a dedicated tablespace simplifies management, making it easier to monitor and manage storage needs independently, reducing the risk of impacting other applications. This approach also allows for more efficient resource allocation, as storage parameters can be tailored to the specific needs of the application or object type. Backup and recovery operations become more efficient, enabling targeted backup/recovery without affecting the entire database. Additionally, organizing objects logically minimizes fragmentation within tablespaces, leading to more efficient storage utilization and faster data access. As the database grows, this approach supports scalability by allowing each application or object type to be managed and scaled independently based on its specific needs.

The new approach also includes removing the unlimited tablespace grant from database users. By implementing this change, users will be granted access only to predefined, designated tablespaces. This restriction enhances security and ensures better control over database storage usage. It prevents users from inadvertently or intentionally consuming excessive space in any tablespace, thus maintaining a more organized and efficient storage structure. This change helps align user permissions with the new organizational strategy, ensuring that users’ access to database resources is both restricted and properly managed.

Prerequisites:

The user should have the required quota or unlimited tablespace privilege on the new tablespace before moving the object to it.

SQL> ALTER USER APP_USER QUOTA UNLIMITED ON USERS_NEWTBS;

Moving Tables To a New Tablespace:

For a standard (heap-organized) table, the MOVE command is quite simple.

Oracle Database 12c Release 1 (12.1) introduced the ability to move table partitions and sub-partitions online. In Oracle Database 12c Release 2 (12.2), the capability was expanded to allow online moves of entire tables, in addition to individual partitions and sub-partitions.

You need to specify the new tablespace, the ONLINE option (if you want to move the table while keeping it accessible to users), and PARALLEL (if you want to speed up the operation and have available CPU resources). For an online move operation, an index rebuild operation is not needed afterward.

Here is an example of the MOVE command for a heap-organized table:

SQL>  ALTER TABLE APP_USER.APP_TABLE MOVE TABLESPACE USERS_NEWTBS ONLINE PARALLEL 16;

If you move a table offline, you need to rebuild its indexes; otherwise, they will become unusable.

You can also rebuild these indexes with the ONLINE and PARALLEL options for improved performance. However, remember to reset the degree of parallelism to NOPARALLEL afterward. If not, subsequent index accesses may remain parallel, potentially consuming excessive resources in a busy environment.

SQL> select 
  'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD PARALLEL 16 ONLINE ;
ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' NOPARALLEL;
' 
from 
  dba_indexes 
where table_name = 'APP_TABLE' and owner='APP_USER'
  status = 'UNUSABLE'

Here are the some key considerations you should know about moving database objects.

Tables with LONG data type columns cannot be moved.

Attempting to do so will result in ORA-00997 error. Oracle deprecated the LONG data type starting with version 8i, and such columns should be converted to CLOBs. This can be achieved using online redefinition or by adding a new column, updating its value with the data from the LONG column, and then dropping the LONG column.

Currently, there is no supported method to move FDA history tables to a different tablespace directly.

You cannot move FDA (Flashback Data Archive) history tables to a different tablespace, nor can you move the FDA itself using the ALTER FLASHBACK ARCHIVE command. These history tables reside in the Flashback Data Archive’s designated tablespace. Additionally, Flashback Archive-enabled tables do not support certain DDL operations like MOVE. To move such a table, you must first DISASSOCIATE it from the archive, perform the MOVE operation, and then REASSOCIATE it with the archive. However, this process alters the rowids of the table’s rows, rendering the old Flashback Archive data meaningless, as the referenced rowids change. Although the move operation itself completes, subsequent flashback queries may return incorrect results.

The only supported way to migrate Flashback Archive table history is by using the DBMS_FLASHBACK_ARCHIVE_MIGRATE package, introduced in Oracle Database 21ai. However, this feature has limitations and ongoing issues, especially in multitenant architectures when the table resides in CDB$ROOT or a PDB. Please refer to Migrating Flashback Data Archive (FDA) tables for more information.

The recommended approach is to disable the Flashback Archive for the affected tables, perform the move operation, and then re-enable the Flashback Archive afterward. If you have an Advanced Compression license, consider using the OPTIMIZE DATA clause when creating a Flashback Data Archive. Specifying OPTIMIZE DATA enables optimization for the storage of history tables. This includes features like Advanced Row Compression, Advanced LOB Compression, Advanced LOB Deduplication, segment-level compression tiering, and row-level compression tiering. Note that using this clause requires a valid Advanced Compression license.

SQL>  ALTER TABLE APP_USER.APP_TABLE_FDA_ENABLED NOFLASHBACK ARCHIVE;
SQL>  CREATE FLASHBACK ARCHIVE DEFAULT FDA_2YEAR TABLESPACE USERS_FTT_NEWTBS  RETENTION 730 DAY OPTIMIZE DATA;
SQL>  ALTER TABLE APP_USER.APP_TABLE_FDA_ENABLED MOVE TABLESPACE USERS_NEWTBS ONLINE PARALLEL 16;
SQL>  ALTER TABLE APP_USER.APP_TABLE_FDA_ENABLED FLASHBACK ARCHIVE FDA_2YEAR;

Index Organized Tables (IOT) can not be moved online if there is an OVERFLOW tablespace related with them, but you may offline.

Index-Organized Tables (IOTs), which function like B-Tree indexes, can become fragmented over time or may need rebuilding for various reasons. If an IOT does not have an overflow segment, it can be rebuilt either online or offline. However, if an overflow segment is present, rebuilding can only be done offline. By rebuilding, I mean performing a move operation, as they are tables.

--online move without OVERFLOW TABLESPACE
SQL> ALTER TABLE APP_USER.APP_IOT_TABLE MOVE TABLESPACE USERS_NEWTBS ONLINE PARALLEL 16;

--offline move with OVERFLOW TABLESPACE
SQL> ALTER TABLE APP_USER.APP_IOT_TABLE MOVE TABLESPACE USERS_NEWTBS  OVERFLOW TABLESPACE USERS_NEWOVERTBS;

A nested table can be moved to a different tablespace only by moving its containing table to the target tablespace.

Movement of AQ$ tables requires special handling due to their unique structure.

For Oracle Database Releases 10.2 to 12.1, moving Advanced Queueing (AQ) tables can be moved using the move_qt_pkg package functions, as described in How to Perform an Online Move of Advanced Queueing Tables using DBMS_REDEFINITION (Doc ID 1410195.1).A .plb file corresponding to your database version must be downloaded and used.

For Oracle Database 19c and later (including 23ai), the DBMS_AQADM.MOVE_QUEUE_TABLE function is used, as detailed in How to Perform a Movement of Non-SYS Owned Advanced Queueing Tables Across Tablespaces using DBMS_AQADM.MOVE_QUEUE_TABLE on 19c and Above (Doc ID 2867445.1). This function supports both local redefinition (within the same tablespace) and non-local redefinition (to a different tablespace). The DBMS_AQADM.MOVE_QUEUE_TABLE function is included from Oracle 23ai. In order to use  in older versions you need to have the Patch 37223181 applied for your specific Database Release Update Version.

SQL> BEGIN
dbms_aqadm.move_queue_table(
queue_table => 'APP_USER.APP_AQ_TAB',
to_tablespace => 'USERS_NEWTBS',
flags => DBMS_AQADM.MOVEQT_ONLINE 
);
END;
/

Moving partitioned/subpartitioned heap tables is not significantly different from moving standard heap tables. However, it’s important to include the ONLINE UPDATE INDEXES clause to ensure that both local and global indexes are updated during the move operation.

Starting with Oracle Database 12c Release 2, many partition operations, along with various other maintenance tasks, can be performed online by simply adding the ONLINE clause to the operation without adding UPDATE INDEXES clauses explicitly.

The partition DDL statement takes longer to execute because indexes, which would otherwise be marked as UNUSABLE, are updated immediately. However, this increase in execution time should be compared to the time required to execute the DDL without updating the indexes and subsequently rebuilding them. As a general rule of thumb, updating the indexes is faster if the size of the partition is less than 5% of the total table size. Each partition move operation updates its local indexes as well as all global indexes. If you can tolerate indexes becoming unusable during the movement process, you might consider omitting this clause for individual partition moves and instead adding it only for the final partition move to reduce overhead.

In Oracle Database 12c, global index maintenance for DROP and TRUNCATE table partition operations is enhanced through Asynchronous Global Index Maintenance. This feature decouples global index maintenance from the partition operations. Until the maintenance is completed, invalid entries in the global index are ignored by queries, DDL, or DML operations on the partitioned table.

A significant improvement is the ability of global indexes to track a subset of the table’s partitions, eliminating the need to mark the entire global index as unusable during partition maintenance. The ORPHANED_ENTRIES column, added to DBA_INDEXES and USER_INDEXES, is used to identify these indexes. For a detailed demonstration, refer to Connor McDonald’s video titled “Primary Key Options for Partitioned Tables”. Deferred global index maintenance is automatically managed during daily maintenance operations by a scheduler job (PMO_DEFERRED_GIDX_MAINT_JOB), which includes the description: “Oracle Defined automatic index cleanup for partition maintenance operations with deferred global index maintenance.”

SQL> select 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME || ' MOVE PARTITION ' || PARTITION_NAME || ' TABLESPACE USERS_NEWTBS PARALLEL 16 ONLINE UPDATE INDEXES; ', a.* from dba_tab_partitions a where table_name='APP_PARTITIONED_TAB' and table_owner='APP_USER'

-- Examples

SQL> ALTER TABLE APP_USER.APP_PARTITIONED_TAB MOVE PARTITION SYS_P1321923 TABLESPACE USERS_NEWTBS PARALLEL 16 ONLINE UPDATE INDEXES;


SQL> select 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME || ' MOVE SUBPARTITION' || SUBPARTITION_NAME|| ' TABLESPACE USERS_NEWTBS PARALLEL 16 ONLINE UPDATE INDEXES; ', a.* from dba_tab_subpartitions a where table_name='APP_SUBPARTITIONED_TAB' and table_owner='APP_USER'


SQL> ALTER TABLE APP_USER.APP_SUBPARTITIONED_TAB MOVE SUBPARTITION SYS_SUBP1321919 TABLESPACE USERS_NEWTBS PARALLEL 16 ONLINE UPDATE INDEXES;

Do not forget to modify the default attributes of the partitioned table to ensure that future partitions are created in the desired tablespace.

Use the following syntax. For subpartitioned tables, ensure you also modify the default attributes of the parent partitioned table to ensure consistency:

SQL> ALTER TABLE APP_USER.APP_PARTITIONED_TAB MODIFY DEFAULT ATTRIBUTES TABLESPACE USERS_NEWTBS ;

SQL> SELECT 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME ||
	'MODIFY DEFAULT ATTRIBUTES FOR 
	  PARTITION ' ||PARTITION_NAME || ' TABLESPACE USERS_NEWTBS; ' from dba_tab_partitions 
   where table_name='APP_SUBPARTITIONED_TAB' and table_owner='APP_USER'

SQL> ALTER TABLE APP_USER.APP_SUBPARTITIONED_TAB
  MODIFY DEFAULT ATTRIBUTES FOR PARTITION SYS_P1321920 
  TABLESPACE USERS_NEWTBS ;

Tables with DOMAIN INDEXES (like Spatial Index and Oracle Text Index) cannot be moved online, but they can be moved without the online clause.

Attempting to move tables with domain indexes with an Oracle Text Index or Spatial Index online will result in an ORA-14427 error. These tables should be moved offline. After the offline move operation related indexes should be rebuild.

Moving Indexes To a New Tablespace:

Although I briefly mentioned this in the previous section, moving an index to another tablespace is done by rebuilding it in the new tablespace. This operation can be performed online and with parallelism for improved performance. However, be sure to reset the degree of parallelism to NOPARALLEL afterward. Failing to do so may result in subsequent index accesses remaining parallel, which could consume excessive resources in a busy environment.

SQL> ALTER INDEX APP_USER.APP_INDEX REBUILD ONLINE TABLESPACE USERS_NEWINDEX_TBS PARALLEL 32;
SQL> ALTER INDEX APP_USER.APP_INDEX NOPARALLEL;

Moving partitioned indexes is no different from moving regular indexes.

SQL> SELECT 'ALTER INDEX ' ||  INDEX_OWNER || '.' || INDEX_NAME || ' REBUILD PARTITION ' || PARTITION_NAME || '  PARALLEL 16 TABLESPACE USERS_NEWINDEX_TBS ONLINE ; ', a.* from dba_ind_partitions a where index_name='APP_PARTITONED_INDEX' and index_owner='APP_USER';

SQL> ALTER INDEX APP_USER.APP_PARTITONED_INDEX NOPARALLEL;

Do not forget to modify the default attributes of the partitioned index to ensure that future partitions are created in the desired tablespace.

SQL> ALTER INDEX APP_USER.APP_PARTITONED_INDEX
MODIFY DEFAULT ATTRIBUTES
  TABLESPACE USERS_NEWINDEX_TBS ;

SPATIAL INDEXES :

For a spatial index, you can control where it will be created using the appropriate syntax. You may drop and recreate the index on the new tablespace if needed. Additionally, I recommend ensuring that spatial indexes are created with the SPATIAL_INDEX_V2 type for optimal performance when using spatial operators. For further details, you can refer to my blog post, “spatial_vector_acceleration” parameter improves Oracle spatial vector query performance”. If no PARAMETERS or TABLESPACE clause is provided, the related SDO index metadata tables (starting with MDRT) will be created in the user’s default tablespace.

Here’s an example command to create a spatial index in the desired tablespace (USERS_NEWINDEX_TBS):

SQL> CREATE INDEX APP_USER.APP_SPATIAL_INDEX ON APP_USER.APP_GEO_TABLE (GEOMETRY_COL) 
INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2
PARAMETERS ('tablespace=USERS_NEWINDEX_TBS')  PARALLEL 16;

SQL> ALTER INDEX APP_USER.APP_SPATIAL_INDEX NOPARALLEL ;

Here’s an example command to rebuild a spatial index online in the desired tablespace (USERS_NEWINDEX_TBS):

SQL> ALTER INDEX APP_USER.APP_SPATIAL_INDEX REBUILD ONLINE
PARAMETERS ('tablespace=USERS_NEWINDEX_TBS') PARALLEL 16;

SQL> ALTER INDEX APP_USER.APP_SPATIAL_INDEX NOPARALLEL ;

After an offline move operation , you may check for all unusable spatial indexes and rebuild them in the desired tablespace.

SQL> SELECT    'ALTER INDEX '
       || owner
       || '."'
       || index_name
       || '" REBUILD ONLINE PARAMETERS (''tablespace=USERS_NEWINDEX_TBS'') PARALLEL 16;
														   '
       || 'ALTER INDEX '
       || owner
       || '."'
       || index_name
       || '" NOPARALLEL ;
														   '    AS command,
       d.*
  FROM dba_indexes d, dba_users a
 WHERE     a.ORACLE_MAINTAINED = 'N'
       AND d.OWNER = a.USERNAME
       AND index_type = 'DOMAIN'
       AND ityp_name LIKE 'SPATIAL%'
       AND (   status = 'UNUSABLE'
            OR domidx_status = 'FAILED'
            OR domidx_opstatus = 'FAILED')

ORACLE TEXT INDEXES :

Oracle Text indexes use internal tables to manage and store the metadata required for full-text search functionality. These internal tables hold tokenized data, index metadata, and supporting information for efficient text retrieval. By default, BASIC_STORAGE attributes are not set. In such cases, the Text index tables are created in the index owner’s default tablespace. However, by creating a preference you can specify a different tablespace for these internal tables, providing better control over storage management and performance.

More information is available on Oracle Text Reference 19 – Storage Types . You may also refer to Doc Id 2847479.1 (Setting STORAGE parameters for Oracle Text Index $KD and $KR tables) and Doc Id 117068.1 (Setting STORAGE parameters for Oracle Text Index)

Internal Table	Purpose of Table
I table	The I table is the index data table.
K table	The K table is the keymap table.
N table	The N table is the negative list table.
R table	The R table is the rowid table.
$u table	The $u table keeps track of concurrent updates.

An example is provided below.

SQL> BEGIN

CTX_DDL.CREATE_PREFERENCE('CTXSYS.TXT_IDX_STORE','BASIC_STORAGE');

/* The I table is the index data table. Parameter clause for dr$indexname$I table creation. */
CTX_DDL.set_attribute('CTXSYS.TXT_IDX_STORE','i_table_clause','tablespace USERS_NEWINDEX_TBS storage(initial 64K)');

/* The K table is the keymap table. Parameter clause for dr$indexname$K table creation. */
CTX_DDL.set_attribute('CTXSYS.TXT_IDX_STORE','K_TABLE_CLAUSE','tablespace USERS_NEWINDEX_TBS storage(initial 64K)');

/* The N table is the negative list table. Parameter clause for dr$indexname$N table creation. */
CTX_DDL.set_attribute('CTXSYS.TXT_IDX_STORE','N_TABLE_CLAUSE','tablespace USERS_NEWINDEX_TBS storage(initial 64K)');

/* The R table is the rowid table. Parameter clause for dr$indexname$R table creation. */
CTX_DDL.set_attribute('CTXSYS.TXT_IDX_STORE','R_TABLE_CLAUSE','tablespace USERS_NEWINDEX_TBS storage(initial 64K)');

/* The $u table keeps track of concurrent updates. Parameter clause for dr$indexname$U table creation. */
CTX_DDL.set_attribute('CTXSYS.TXT_IDX_STORE','U_TABLE_CLAUSE','tablespace USERS_NEWINDEX_TBS storage(initial 64K)');

/*Parameter clause for dr$indexname$X index creation. */
CTX_DDL.set_attribute('CTXSYS.TXT_IDX_STORE','i_index_clause','tablespace USERS_NEWINDEX_TBS storage(initial 64K)');

/*Parameter clause for dr$indexname$KD index creation. */
CTX_DDL.set_attribute('CTXSYS.TXT_IDX_STORE','KD_INDEX_CLAUSE','tablespace USERS_NEWINDEX_TBS storage(initial 64K)');

/*Parameter clause for dr$indexname$KR index creation. */
CTX_DDL.set_attribute('CTXSYS.TXT_IDX_STORE','KR_INDEX_CLAUSE','tablespace USERS_NEWINDEX_TBS storage(initial 64K)');

/* The P table is the prefix list table. Parameter clause for dr$indexname$P table creation. */
CTX_DDL.set_attribute('CTXSYS.TXT_IDX_STORE','P_TABLE_CLAUSE','tablespace USERS_NEWINDEX_TBS storage(initial 64K)');

END;

After creating the preference, you can specify the new storage parameters for the index during the creation of a domain index or by using the rebuild command, as shown below.

-- Create command
SQL>CREATE INDEX APP_USER.IC_APP_DESCRIBE ON APP_USER.APP_TABLE_WITH_TEXT 
(DESCRIPTION)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('STORAGE CTXSYS.TXT_IDX_STORE') PARALLEL 32;

SQL> ALTER INDEX APP_USER.IC_APP_DESCRIBE  NOPARALLEL;

-- Rebuild command
SQL> GRANT EXECUTE ON CTX_DDL TO APP_USER;

SQL> BEGIN 
CTX_DDL.RECREATE_INDEX_ONLINE('APP_USER.IC_APP_DESCRIBE');  
END;

The tablespace for the LOBINDEX cannot be explicitly specified; it is automatically created and moved along with the LOBSEGMENT.

Moving Lobs To a New Tablespace:

That has been quite a lengthy post, but I’ve finally reached the topic of Large Objects (LOBs). For storing LOBs, you should use SecureFiles. SecureFile LOBs are available for use in any edition of Oracle. Set the DB_SECUREFILE parameter to ALWAYS and migrate all users large objects to SecureFiles without delay. There are numerous blog posts and documents available to guide you through the process. You may use the command provided below to identify LOBs that could benefit from migration.

SQL> SELECT *
  FROM dba_lobs dl, dba_users du
 WHERE     dl.securefile = 'NO'
       AND du.USERNAME = dl.owner
       AND du.ORACLE_MAINTAINED = 'N';

-- Example
SQL> ALTER TABLE APP_USER.APP_TAB_WITH_LOBS
MOVE LOB (CONTENT) STORE AS SECUREFILE
(TABLESPACE USERS_NEWLOB_TBS) PARALLEL 16 ONLINE;

If you have an Oracle Advanced Compression license, you should also consider utilizing deduplication (especially for duplicated data such as XML, JSON values, etc.) and high compression options for SecureFiles, as they can significantly optimize storage.

-- Example if you have Oracle Advanced Compression license
SQL> ALTER TABLE APP_USER.APP_TAB_WITH_LOBS
MOVE LOB ( CONTENT) STORE AS SECUREFILE
(TABLESPACE USERS_NEWLOB_TBS ENABLE STORAGE IN ROW 
DEDUPLICATE COMPRESS HIGH) PARALLEL 16;

You can move multiple LOB segments in a single command.

-- Move more than one lob segment
SQL> ALTER TABLE APP_USER.APP_TAB_WITH_MULTILOBS
MOVE LOB (CONTENT_1) STORE AS SECUREFILE
(TABLESPACE USERS_NEWLOB_TBS)
LOB (CONTENT_2) STORE AS SECUREFILE
(TABLESPACE USERS_NEWLOB_TBS)
PARALLEL 16 ONLINE;

You can even move a table along with all its LOB segments in a single command.

-- Move table and lob segments
SQL> ALTER TABLE MOVE APP_USER.APP_TAB_WITH_MULTILOBS TABLESPACE USERS_NEWTBS 
LOB (CONTENT_1) STORE AS SECUREFILE
(TABLESPACE USERS_NEWLOB_TBS)
LOB (CONTENT_2) STORE AS SECUREFILE
(TABLESPACE USERS_NEWLOB_TBS)
PARALLEL 16 ONLINE;

Moving VARRAYs is similar to moving LOB segments.

Here is an example of moving VARRAYs of an SDO_GEOMETRY column to a new tablespace. This command moves the VARRAY storage of the SDO_GEOMETRY column’s SDO_ELEM_INFO array and SDO_ORDINATES array to the specified new_tablespace (USERS_NEWLOB_TBS). If the sdo_geometry column does not have a spatial index, you can perform an online move. However, as mentioned earlier, if a spatial index (domain index) is present, the move must be performed offline.

-- Move table and varray
SQL> ALTER TABLE MOVE APP_USER.APP_TAB_WITH_VARRAYS TABLESPACE USERS_NEWTBS 
VARRAY "GEOMETRY_COL"."SDO_ELEM_INFO" STORE AS SECUREFILE LOB
(TABLESPACE USERS_NEWLOB_TBS)
VARRAY "GEOMETRY_COL"."SDO_ORDINATES" STORE AS SECUREFILE LOB
(TABLESPACE USERS_NEWLOB_TBS)
PARALLEL 16 ;

There is nothing different moving lob segments of the partitioned tables.

Remember to modify the default attributes of a LOB column in a partitioned table to ensure that future LOB segments for new partitions are created in the desired tablespace.

SQL> ALTER TABLE APP_USER.APP_PARTITIONED_TAB_WITH_LOBS
    MODIFY DEFAULT ATTRIBUTES 
        TABLESPACE USERS_NEWTBS 
        LOB (CONTENT_1) STORE AS SECUREFILE (TABLESPACE USERS_NEWLOB_TBS)
        LOB (CONTENT_2) STORE AS SECUREFILE (TABLESPACE USERS_NEWLOB_TBS);

Last but not least, even if you have an Advanced Compression license, be cautious when deciding to use the deduplication feature. Parallel DML/DDL operations are not yet supported (Internal Bug Id: 16698639) on these columns, which can lead to “enq: TX – row lock contention” waits. Performing maintenance operations on large tables with a single core may also lead to significant challenges in the future.

Conclusion

Moving different types of database objects – such as partitions, indexes, LOBs, and other segments – presents significant challenges in Oracle environments, especially when handling large volumes like 50,000 objects, as each requires different syntax. With Oracle 12c Release 2, more online move operations are supported, but some objects still require special handling, such as domain indexes (e.g., Oracle Text and spatial indexes), Advanced Queue Tables, partitions, IOT tables, flashback archive-enabled tables etc. Additionally, there are many key considerations to be aware of before undertaking such a massive challenge. The good news is that I’ve shared many of them here, so you can be well-prepared. I’ve also highlighted the useful feature of deferred global index maintenance on partitioned tables, among other valuable tips. The main challenge, however, lies in balancing system resources, ensuring minimal downtime, and maintaining data integrity, especially when managing such a large number of objects. Strategic planning, careful execution, and efficient use of Oracle’s features are crucial to successfully completing these operations.

Hope it helps.