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

This article mainly shows you the "oracle partition table global unique index failure caused by unable to add, delete and change what to do", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "oracle partition table global unique index failure caused by unable to add, delete and change how to do" this article.

# create a partition table

CREATE TABLE T_NEW (ID number, TIME date) PARTITION BY RANGE (TIME)

(PARTITION P1 VALUES LESS THAN (TO_DATE ('2004-7-1),' YYYY-MM-DD'))

PARTITION P2 VALUES LESS THAN (TO_DATE ('2005-1-1,' YYYY-MM-DD'))

PARTITION P3 VALUES LESS THAN (TO_DATE ('2005-7-1,' YYYY-MM-DD'))

PARTITION P4 VALUES LESS THAN (MAXVALUE))

# # inserting data

Insert into T_NEW values (1 minute totem date ('2003-7-1 minute recording yyy Meim Meidd`))

Insert into T_NEW values (1 minute topless date ('2004-10-1 minute recording Yyyymi Meidd`))

Insert into T_NEW values (1 minute totem date ('2005-6-1 won won yyyym Meidd`))

Commit

# # viewing data

Select * from T_NEW

Select * from T_NEW partition (p1)

Select * from T_NEW partition (p2)

Select * from T_NEW partition (p3)

Select * from T_NEW partition (p4)

# # Building a global unique index

Create unique index i on T_NEW (TIME)

# # deleting partitions without updating the global index

Alter table t_new drop partition p1

# # insert Datagram error

Insert into T_NEW values (1 recording topless date ('2011-6-1 recording recording Yyyymi Meidd`); # wrong report

# # online parallel Reconstruction of Global unique Index

ALTER INDEX I REBUILD ONLINE PARALLEL 8

Alter index i noparallel

# # inserting data is normal

Insert into T_NEW values (1 recall date ('2011-6-1 won't report errors); # do not report an error

Commit

# # creating a global non-unique index

Drop index i

Create index i on T_NEW (TIME)

# # deleting partitions without updating the global index

Alter table t_new drop partition p2

# # do not report errors when inserting data

Insert into T_NEW values (1 recall date ('2012-6-1 won't report errors); # do not report an error

Commit

# # creating a globally unique Index

Drop index i

Create unique index i on T_NEW (TIME)

# # deleting Partition and updating Global Index

Alter table t_new drop partition p3 update global indexes

# # do not report errors when inserting data

Insert into T_NEW values (1 recall date ('2013-6-1 won't report errors); # do not report an error

Commit

Conclusion:

1. If there is a [global] [unique] index on the partition table, if the drop partition does not have [update global indexes], the index will become invalid, and all additions, deletions and modifications (which can be changed to non-index keys) will report an error.

2. After parallel indexing, you need to disable parallelism: alter index i noparallel

3. Sql:ALTER INDEX I REBUILD ONLINE PARALLEL 8 is recommended for indexing or re-indexing.

The above is all the contents of the article "what to do if the global unique index of the oracle partition table fails," thank you for your reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.