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

This article mainly introduces "how to understand the table space of database mobile partition table and partition index". In daily operation, I believe many people have doubts about how to understand the table space of database mobile partition table and partition index. Xiaobian consulted all kinds of data and sorted out simple and useful operation methods. I hope it will be helpful for you to answer the question of "how to understand the table space of database mobile partition tables and partition indexes"! Next, please follow the editor to study!

Move the tablespace of a partitioned table

1) uncombined partition table

Alter table RPT_F_STATION_DP move PARTITION SYS_P3742 tablespace reportdata

2) combined partition table:

(1) move the subpartition to another tablespace first:

Alter table RPT_F_CABECF move SUBPARTITION SYS_SUBP8842 tablespace reportdata

(2) subpartition has move gone, leaving only the father of subpartition, so there is no need for move, just change the properties of the partition.

ALTER TABLE RPT_F_CABECF MODIFY DEFAULT ATTRIBUTES FOR PARTITION PARTITION_660 TABLESPACE reportdata

Note:

If a direct move contains subpartitions, the following error will occur

ORA-14257: cannot move partition other than a Range or Hash partition

Move the table space of the index on the partition table

1) non-partitioned index

ALTER INDEX PK_RPT_F_NOFINISH_WORKSHEET REBUILD TABLESPACE REPORTDATA

2) index without subpartition

ALTER INDEX IDX_RPT_F_STATION_DP_1 REBUILD PARTITION SYS_P3721 TABLESPACE REPORTDATA

3) Subpartition index

ALTER INDEX IDX_RPT_F_CABECF_1 REBUILD SUBPARTITION SYS_SUBP8842 TABLESPACE REPORTDATA

Automatically generate the required scripts

SELECT 'alter table'

| | table_name |

| | 'move PARTITION' |

| | partition_name |

| | 'tablespace reportdata;' |

FROM USER_TAB_PARTITIONS

WHERE SUBPARTITION_COUNT = 0 AND tablespace_name = 'RMGZ';-- uncombined partition table

SELECT 'alter table'

| | table_name |

| | 'move subPARTITION' |

| | SUBPARTITION_NAME |

| | 'tablespace reportdata;' |

FROM USER_TAB_SUBPARTITIONS

WHERE SUBPARTITION_COUNT > 0 AND tablespace_name = 'RMGZ';-- move subpartition

SELECT 'ALTER TABLE'

| | table_name |

| | 'MODIFY DEFAULT ATTRIBUTES FOR PARTITION' |

| | partition_name |

| | 'TABLESPACE reportdata;' |

FROM USER_TAB_PARTITIONS

WHERE SUBPARTITION_COUNT > 0 AND tablespace_name = 'RMGZ';-- modify parent partition properties

SELECT 'ALTER INDEX' | | INDEX_NAME | | 'REBUILD TABLESPACE REPORTDATA;'

FROM USER_INDEXES

WHERE tablespace_name='RMGZ'

SELECT 'ALTER INDEX'

| | index_name |

| | 'REBUILD PARTITION' |

| | PARTITION_NAME |

| | 'TABLESPACE REPORTDATA;' |

FROM USER_IND_PARTITIONS

Where SUBPARTITION_COUNT = 0

And tablespace_name='RMGZ';-index without subpartition

SELECT 'ALTER INDEX'

| | index_name |

| | 'REBUILD SUBPARTITION' |

| | SUBPARTITION_NAME |

| | 'TABLESPACE REPORTDATA;' |

FROM USER_IND_SUBPARTITIONS

Where tablespace_name='RMGZ';-subpartition index

At this point, the study on "how to understand the tablespaces of database mobile partition tables and partition indexes" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.