Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to migrate and process the table space of the Oracle partition table". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to migrate and process the table space of the Oracle partition table".

1. Due to the need of work, the table under CAMS_CORE user needs to be migrated once. The migration command script is generated as follows:

Select 'alter table CAMS_CORE.' | | TABLE_NAME | |' move tablespace cams_core_tab;' from dba_tables where owner='CAMS_CORE'

two。 Migrate the generated statements, where 2 tables are Interval Partition partitioned tables, and encountered a problem during the migration:

Alter table CAMS_CORE.BP_VOUCHER_RECENT move tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move tablespace cams_core_tab

Prompt error

ORA-14511: cannot perform operation on a partitioned object

3. Use oerr to view error messages

[oracle@XLJ181 dump] $oerr ORA 1451114511, 00000, "cannot perform operation on a partitioned object" / / * Cause: An attempt was made to perform an operation that is not allowed// on partitioned tables or indexes.// * Action: Retry the command with correct syntax.

4. From the error prompt, the migration of partitioned tables should not be based on table migration, but need to be migrated based on partitions. To improve the operation, first check the fields of the dba_tab_ partitions table.

SYS@cams > desc dba_tab_partitions Name Null? Type-TABLE_OWNER VARCHAR2 (30) TABLE_NAME VARCHAR2 (30) COMPOSITE VARCHAR2 (3) PARTITION_NAME VARCHAR2 ( 30) SUBPARTITION_COUNT NUMBER HIGH_VALUE LONG HIGH_VALUE_LENGTH NUMBER PARTITION_POSITION NUMBER TABLESPACE_NAME VARCHAR2 (30) PCT_FREE NUMBER PCT_USED NUMBER INI_TRANS NUMBER MAX_TRANS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENT NUMBER MAX_EXTENT NUMBER MAX_SIZE NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER LOGGING VARCHAR2 (7) COMPRESSION VARCHAR2 (8) COMPRESS_FOR VARCHAR2 (12) NUM_ROWS NUMBER BLOCKS NUMBER EMPTY_BLOCKS NUMBER AVG_SPACE NUMBER CHAIN_CNT NUMBER AVG_ROW_LEN NUMBER SAMPLE_SIZE NUMBER LAST_ANALYZED DATE BUFFER_POOL VARCHAR2 (7) FLASH_CACHE VARCHAR2 (7) CELL_FLASH_CACHE VARCHAR2 (7) GLOBAL_STATS VARCHAR2 (3) USER_STATS VARCHAR2 (3) IS_NESTED VARCHAR2 (3) PARENT_TABLE_PARTITION VARCHAR2 (30) INTERVAL VARCHAR2 (3) SEGMENT_CREATED VARCHAR2 (4)

5. Spelling automatically generates sql for migration statements

SYS@cams > set pages 1000SYS@cams > set lines 200SYS@cams > select 'alter table' | | table_owner | |'. | | table_name | | 'move partition' | | partition_name | | 'tablespace cams_core_tab;' as move_sql from dba_tab_partitions where table_owner='CAMS_CORE' and table_name in (' BP_VOUCHER_RECENT','BP_VOUCHER_HISTORY') | MOVE_SQL- -- alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition P0 tablespace cams_core_tab Alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition P1 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition P2 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P118 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P119 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P120 tablespace cams_core_tab Alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P121 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P122 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P123 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P124 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P125 tablespace cams_core_tab Alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P126 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P127 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P128 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P129 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P130 tablespace cams_core_tab Alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P131 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P132 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P133 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P134 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_RECENT move partition P0 tablespace cams_core_tab Alter table CAMS_CORE.BP_VOUCHER_RECENT move partition P1 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_RECENT move partition P2 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_RECENT move partition P3 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_RECENT move partition P4 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_RECENT move partition P5 tablespace cams_core_tab; 26 rows selected.

6. The generated sql is re-executed, all prompts are successful, and the tablespace migration is completed successfully.

Thank you for your reading, the above is the content of "how to migrate and deal with the table space of the Oracle partition table". After the study of this article, I believe you have a deeper understanding of how to migrate the table space of the Oracle partition table and deal with this problem, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.