An Online Move data file operation is not compatible when:
• The data file is an OFFLINE data file
• A concurrent flashback database operation is executing
• A media recovery is completing
• A file shrink operation or tablespace offline/drop operation involving the same file is performing
But it is compatible with:
• Block media recovery
• ALTER TABLESPACE READ ONLY or READ WRITE operations
• Data file extension operation
• Tablespace/database online backup mode involving the same file
If a flashback database is executed to a time when the file was not yet moved, the flashback database operation will not change the file name to the original name, although it will bring back the old contents of the file.
12c Online Partition maintenance enhancements provide the capability to move table partitions or subpartitions online without preventing concurrent DML operations.
This can be used to:
• Move partitions and subpartitions from one kind of storage to another
• Move time-based partitions and subpartitions to low cost storage once they become infrequently accessed (for example, according to ADO policies configured)
• Compress time-based partitions and subpartitions according to ADO policies configured
Online Move Partition: Benefits
• DML allowed, not DDL
• Move, split, or merge partitions ONLINE to low cost storage
• Global and local indexes maintained
•• Online operation on IOTs is not supported.
Online Move Partition: Compress
MOVE is also used for compression.
• ROW STORE COMPRESS [BASIC]
SQL> ALTER TABLE ORDERS MOVE PARTITION ORD_P1 ROW STORE COMPRESS UPDATE INDEXES ONLINE;
• ROW STORE COMPRESS ADVANCED
SQL> ALTER TABLE ORDERS MOVE PARTITION ORD_P1 ROW STORE COMPRESS ADVANCED UPDATE INDEXES ONLINE;
• COLUMN STORE COMPRESS
SQL> ALTER TABLE ORDERS MOVE PARTITION ORD_P1 COLUMN STORE COMPRESS FOR QUERY HIGH UPDATE INDEXES ONLINE; SQL> ALTER TABLE ORDERS MOVE PARTITION ORD_P1 COLUMN STORE COMPRESS FOR ARCHIVE HIGH UPDATE INDEXES ONLINE;