Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Example Analysis of Exchange Partition

2025-01-14 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

Today, I will talk to you about the example analysis of Exchange Partition. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

The conclusion of Exchange Partition experiment results:

Intermediate table (non-partitioned, empty table)

1. All the partition data of the source partition table has been transferred to the intermediate table, and the partition data of the source partition cannot be found.

2. Because both the source partition table and the target partition table generate partition names automatically by INTERVAL, there will be no partition names when there is no new data in the target partition table, so insert a piece of data into the target partition table and add a new partition.

3. After the new partition Exchange of the intermediate table and the target partition table, the new partition data of the intermediate table and the target partition table are exchanged.

4. When both the source partition table and the destination partition table are INTERVAL, there is no guarantee that the partition names exported by the original table are the same as those imported by the target table.

T3: source partition table

CREATE TABLE T3 (hid number,hdate date)

PARTITION BY RANGE (hid) INTERVAL (5)

(PARTITION P01 VALUES LESS THAN (10)

PARTITION p0q2 VALUES LESS THAN (14))

T2: intermediate table

CREATE TABLE T2 (hid number,hdate date)

T4: target partition table

CREATE TABLE T4 (hid number,hdate date)

PARTITION BY RANGE (hid) INTERVAL (5)

(PARTITION P01 VALUES LESS THAN (10)

PARTITION p0q2 VALUES LESS THAN (14))

Make T3 table have data

Insert into T3 values (1 SysdateMur1)

Insert into T3 values (10 ~ (th) SysdateMur1)

Insert into T3 values (20th SysdateMurray 1)

Insert into T3 values (30th SysdateMurray 1)

Commit

Select partition_name, partition_position from user_tab_partitions where table_name='T3'

PARTITION_NAME PARTITION_POSITION

P01 1

P0Q2 2

SYS_P66 3

SYS_P67 4

SQL > select * from T3

HID HDATE

--

1 04-AUG-17

10 04-AUG-17

20 04-AUG-17

30 04-AUG-17

The first step

Transfer partition 3 SYS_P66 of T3 to T2

ALTER TABLE T3 EXCHANGE PARTITION SYS_P66 WITH TABLE T2

Step two

To view the results, the SYS_P66 structure of partition 3 of T3 is still there, but the partition data is sent to the T2 table.

SQL > select partition_name, partition_position from user_tab_partitions where table_name='T3'

PARTITION_NAME PARTITION_POSITION

P01 1

P0Q2 2

SYS_P66 3

SYS_P67 4

SQL > select * from T3

HID HDATE

--

1 04-AUG-17

10 04-AUG-17

30 04-AUG-17

SQL > select * from T2

HID HDATE

--

20 04-AUG-17

Step three

Then transfer T2 to T4.

Because T4 is INTERVAL, if there is no new data, there will be no new partition, but Exchange can only use the existing partition name and the partition can insert this value, so we must first create a partition for the T4 table that can be used for Exchange.

SQL > ALTER TABLE T4 EXCHANGE PARTITION WITH TABLE T2;-T4 without any partition name, error reported

ALTER TABLE T4 EXCHANGE PARTITION WITH TABLE T2

*

ERROR at line 1:

ORA-14006: invalid partition name

SQL > ALTER TABLE T4 EXCHANGE PARTITION SYS_P66 WITH TABLE T2;-- T4 with a non-existent partition name, reported an error

ALTER TABLE T4 EXCHANGE PARTITION SYS_P66 WITH TABLE T2

*

ERROR at line 1:

ORA-02149: Specified partition does not exist

SQL > ALTER TABLE T4 EXCHANGE PARTITION p01 WITH TABLE T2;-- T4 has a partition name, but this partition must be less than 10, an error was reported

ALTER TABLE T4 EXCHANGE PARTITION p01 WITH TABLE T2

*

ERROR at line 1:

ORA-14099: all rows in table do not qualify for specified partition

Create a partition for the T4 table that can be used for Exchange, and SYS_P68 is automatically generated in the case of INTERVAL.

SQL > insert into T4 values (1951 sysdatemur1)

1 row created.

SQL > commit

SQL > select partition_name, partition_position from user_tab_partitions where table_name='T4'

PARTITION_NAME PARTITION_POSITION

P01 1

P0Q2 2

SYS_P68 3

Continue Exchange and find that the data of T2 and T4 have been exchanged.

SQL > ALTER TABLE T4 EXCHANGE PARTITION SYS_P68 WITH TABLE T2

Table altered.

SQL > select * from T2

HID HDATE

--

19 04-AUG-17

SQL > select * from T4

HID HDATE

--

20 04-AUG-17

After reading the above, do you have any further understanding of the example analysis of Exchange Partition? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report