Summary of work over the past week-Oracle partition migration

This week was very calm and there was no customer disturbance.

A while ago I wrote something about data table compression. Now I feel it is a good way to compress the data and migrate it directly.

The following table is available:

 Create   Table  Table_1 (month_id  Varchar2 ( 10  ), Area_id  Varchar2 ( 10  ), Seri  Number  ) Partition By  Range (month_id) subpartition  By  List (area_id) subpartition template (subpartition subpart_1  Values ( '  1  '  ), Subpartition subpart_2  Values ( '  2  '  ), Subpartition subpart_3  Values ( '  3  '  ), Subpartition subpart_4  Values ( '  4  '  ), Subpartition subpart_5  Values ( '  5  '  ), Subpartition subpart_6  Values ('  6  '  ), Subpartition subpart_7  Values ( '  7  '  ), Subpartition subpart_8  Values ( '  8  '  ), Subpartition Subpart _  9  Values ('  9  '  ), Subpartition subpart_10  Values ( '  10  '  ), Subpartition subpart_default  Values ( Default  ) (Partition part_1  Values Less ( '  201210 '  ), Partition part_2  Values Less ( '  201211  '  ), Partition part_3  Values Less ( '  201212  '  )) 

Partition storage is like this:

Now we can migrate the month_id of this table as "201209" to the users tablespace:

Alter TableTable_1 move partition part_1 tablespace users;
 

An error occurred. After checking the information on the Internet, you should first remove the subpartition.

Alter TableTable_1 move subpartition part_partition subpart_1 tablespace users;Alter TableTable_1 move subpartition part_1_subpart_2 tablespace users;Alter TableTable_1 move subpartition part_1_subpart_3 tablespace users;Alter TableTable_1 move subpartition part_1_subpart_4 tablespace users;...Alter TableTable_1 move subpartition part_partition subpart_10 tablespace users;
 

Then, execute the following:

 
Alter TableTable_1 modifyDefaultAttributesForPartition part_1tablespace users;
 

This is the result:

Now the partition is transferred to users.

What will happen if there are no subpartitions? Re-create this table. Do not subpartition this time, just partition by month_id, and then execute the transfer statement:

 
Alter TableTable_1 move partition part_1 tablespace users;
 

Success. This shows that when transferring a partitioned table with sub-partitions, the table should be transferred from the lowest level of sub-partitions and then to the upper level of partitions.

Then, compress part_1 from the table above. The statement is as follows:

Alter TableTable_1 move partition part_1 compress;
 

The result is as follows:

Create a table with sub-partitions and execute the preceding statement:

The same problem occurs again. That is to say, we have to compress the sub-partitions first. But today I see a more useful statement:

 
Alter TableTable_1 modify partition part_1 compress;
 

Result After execution:

That's all.

I forgot to write the statement when creating a table partition today. This is not correct. Here we record it:

CreateTablespace data_warehouse datafile'D: \ app \ User \ oradata \ pC1 \ storage2.dbf'Size 50 m autoextendOn Next50 m maxsize 2048 m;
 

Change the user's default tablespace to the created tablespace:

 
Alter UserWingsDefaultTablespace data_warehouse;
 

Welcome Oracle fans to join our discussion group: 120244471.