Move operations for Oracle partitioned tables

The move operation of the partitioned table is still a question that deserves to be delved into. If the partition table contains LOB fields, the difficulty is increased.

sql> ALTER TABLE charge move tablespace large_data;
ALTER TABLE charge move tablespace large_data
             *
ERROR at line 1:
ora-14511:cannot perform operation on a Partitioned Object

At this point, you can use the following SQL statement to generate dynamic SQL to do partition level move operations.
For example, the following 3 large tables, such as the following SQL can generate hundreds of statements to complete the partition level of move operations.

Select ' ALTER TABLE ' | | table_name| | ' Move Partition ' | | partition_name| | ' tablespace large_data; ' from User_tab_partitions where table_name in (' CHARGE ', ' MEMO ', ' Charge_rel ')

If the table contains LOB fields, you may encounter the following issues

ALTER TABLE memo move partition xxxx tablespace large_data;

*
ERROR at line 1:
Ora-01658:unable to create INITIAL extent for segment in Tablespace DATAS01

  encounter this problem, generally have the following reasons, a need to see quota, whether in the corresponding table space contains enough quota
one is to see the table space storage, there is enough space.
If space is limited and some partitions have no data, you can turn on lazy loading. Enable Deferred_segment

Expdp/impdp
The use of data pump is a good choice, can be very convenient data structure dump everywhere, and then in the import of the time to do remap_tablespace, this function is not exp/imp.
It is recommended to use IMPDP's remap function.

Perl/shell
Of course, you can use the Exp+imp+shell/perl method to parse the dump file.
You can refer to the following blog post.
http://blog.itpub.net/23718752/viewspace-1160404/
The details of parsing the dump file are discussed in detail.

Dbms_metadata.get_ddl
If the source Shema can be accessed at any time, the corresponding creation statements can be generated directly through the dbms_metadata when the conditions permit, and the table space can be re-mapping manually on this basis.
The process seems simple, but there are a lot of details to consider. Partition too many partitions, it is easy to exceed the maximum character length, you need to grasp the control.


Exchange partition
This approach may be a bit risky, but in some cases it is also a scenario where you can do a remap of the tablespace in the form of exchange partition.
Use the normal table to make an exchange with the partition.

However, the above methods are not suitable for high availability, but downtime time is also to be considered, the need to minimize the operating time, after all, the use of partitioned tables in the environment, the data is very large, this also needs to be weighed. Table space Change is the purpose, but the reorganization of data is involved in the data export, import and other work, Avenue to Jane, but too much detail, or need to pay more attention.

from:http://blog.itpub.net/23718752/viewspace-1266689/

This article from the "90SirDB" blog, reproduced please contact the author!

Move action for an Oracle partitioned table