advertisements
_____________________________________________________________________________________________________________________
Method#1: Partition Exchange
What is Partition Exchange?
ALTER TABLE EXCHANGE PARTITION can convert a partition into a non-partitioned table and vice versa
a. Create duplicate table of non-partitioned table with required partitions
b. Alter table EXCHANGE partition <partition_name>
with <non_partition_table_name>;
with <non_partition_table_name>;
Scenario: The table example to be partitioned. We need to create interim tables to complete this activity. Please go through the example to understand this method.
1. The structure of EXAMPLE table is as below.
set pagesize 200
set long 999999
set linesize 150
select dbms_metadata.get_ddl('TABLE','EXAMPLE_PART') from dual;
CREATE TABLE "EXAMPLE"
( "ID" number(10) NOT NULL ENABLE,
"UID" VARCHAR2(40),
"PIX" VARCHAR2(40),
"FNAME" VARCHAR2(100),
"MNAME" VARCHAR2(100),
"LNAME" VARCHAR2(100),
"SFIX" VARCHAR2(40),
"JTITLE" VARCHAR2(100),
"COMNAME" VARCHAR2(40),
"ADD1" VARCHAR2(50),
"ADD2" VARCHAR2(50),
"ADD3" VARCHAR2(50),
"CTY" VARCHAR2(30),
"STAT" VARCHAR2(20),
"POSTCODE" VARCHAR2(10),
"COUNTY" VARCHAR2(40),
"COUNTRY" VARCHAR2(40),
"PH_NUM" VARCHAR2(20),
"FX_NUM" VARCHAR2(15)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
2. A unique is added to the table example.
alter table example add constraint unique_example unique(id);
Table altered.
3. Create a table with same structure of the table EXAMPLE with required partitions. Here I created 2 partitions.
CREATE TABLE "EXAMPLE_PARTITION"
( "ID" number(10) NOT NULL ENABLE,
"UID" VARCHAR2(40),
"PIX" VARCHAR2(40),
"FNAME" VARCHAR2(100),
"MNAME" VARCHAR2(100),
"LNAME" VARCHAR2(100),
"SFIX" VARCHAR2(40),
"JTITLE" VARCHAR2(100),
"COMNAME" VARCHAR2(40),
"ADD1" VARCHAR2(50),
"ADD2" VARCHAR2(50),
"ADD3" VARCHAR2(50),
"CTY" VARCHAR2(30),
"STAT" VARCHAR2(20),
"POSTCODE" VARCHAR2(10),
"COUNTY" VARCHAR2(40),
"COUNTRY" VARCHAR2(40),
"PH_NUM" VARCHAR2(20),
"FX_NUM" VARCHAR2(15)
) PARTITION BY RANGE(ID)
(PARTITION EXAMPLE_P1 VALUES LESS THAN (50000),
PARTITION EXAMPLE_P2 VALUES LESS THAN (100000))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
4. Verify the example_partition table partition details from user_tab_partitions
SQL> select table_name, partition_name, high_value from user_tab_partitions
where table_name ='EXAMPLE_PARTITION'
SQL> /
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ------------EXAMPLE_PARTITION EXAMPLE_P1 50000
EXAMPLE_PARTITION EXAMPLE_P2 100000
5. Create temporary tables to filter the data for the partitions. Here I have 2 partitions and I created two interim tables (Part1 & Part2) for partition exchange. You need to create interim tables as many partitions which you required.
SQL> Create table part1 as select * from example where id<50000;
Table created.
SQL> Create table part2 as select * from example where id between 50001 and 99999;
Table created.
6. Exchange the interim tables with table partitions(part1 to example_p1 and part2 to example_p2)
SQL> Alter table example_partition exchange partition example_p1 with table part1;
Table altered.
SQL> Alter table example_partition exchange partition example_p2 with table part2;
Table altered.
7. Next Drop the example table
8. Rename the example_partition to example.
9. Now constraints needs to be added as in the table example.
SQL> alter table example_partition add constraint unique_example unique(id);
Table altered.
Method#2: Split Partition with Partition Exchange
Scenario: Partition the table EXAMPLE and the table definition is as below. For this example I created this table and inserted the sample data with following scripts.
1. Create table EXAMPLE
SQL> CREATE TABLE "EXAMPLE"
2 ( "ID" number(10) NOT NULL ENABLE,
3 "UID" VARCHAR2(40),
4 "PIX" VARCHAR2(40),
5 "FNAME" VARCHAR2(100),
6 "MNAME" VARCHAR2(100),
7 "LNAME" VARCHAR2(100),
8 "SFIX" VARCHAR2(40),
9 "JTITLE" VARCHAR2(100),
10 "COMNAME" VARCHAR2(40),
11 "ADD1" VARCHAR2(50),
12 "ADD2" VARCHAR2(50),
13 "ADD3" VARCHAR2(50),
14 "CTY" VARCHAR2(30),
15 "STAT" VARCHAR2(20),
16 "POSTCODE" VARCHAR2(10),
17 "COUNTY" VARCHAR2(40),
18 "COUNTRY" VARCHAR2(40),
19 "PH_NUM" VARCHAR2(20),
20 "FX_NUM" VARCHAR2(15)
21 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
22 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
23 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
24 TABLESPACE "USERS"
25 ;
Table created.
alter table example add constraint unique_example unique(id);
Table altered.
SQL> insert into example (id) select rownum from dba_objects where rownum<100000;
95114 rows created.
SQL> commit;
Commit complete.
2. Create interim table EXAMPLE_PARTITION with required one partition using clause PARTITION max_value values less than (maxvalue)
SQL> CREATE TABLE "EXAMPLE_PARTITION"
( "ID" number(10) NOT NULL ENABLE,
"UID" VARCHAR2(40),
"PIX" VARCHAR2(40),
"FNAME" VARCHAR2(100),
"MNAME" VARCHAR2(100),
"LNAME" VARCHAR2(100),
"SFIX" VARCHAR2(40),
"JTITLE" VARCHAR2(100),
"COMNAME" VARCHAR2(40),
"ADD1" VARCHAR2(50),
"ADD2" VARCHAR2(50),
"ADD3" VARCHAR2(50),
"CTY" VARCHAR2(30),
"STAT" VARCHAR2(20),
"POSTCODE" VARCHAR2(10),
"COUNTY" VARCHAR2(40),
"COUNTRY" VARCHAR2(40),
"PH_NUM" VARCHAR2(20),
"FX_NUM" VARCHAR2(15)
) PARTITION BY RANGE(ID)
(PARTITION max_value values less than (maxvalue))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS";
Table created.
3. Verify the interim table with following commands
SQL> select table_name, partition_name, high_value from user_tab_partitions
where table_name ='EXAMPLE_PARTITION'
SQL> /
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ----------------
EXAMPLE_PARTITION MAX_VALUE MAXVALUE
4. Do a partition exchange with partition(max_value) and the table EXAMPLE. After the partition exchange the main table (EXAMPLE) will be empty. See the commands below
SQL> Alter table example_partition exchange partition max_value with table example;
Table altered.
SQL> select count(*) from example;
COUNT(*)
----------
0
SQL> select count(*) from example_partition;
COUNT(*)
----------
95114
5. Create as many partitions needed using Split_partition cluase as like below
SQL>Alter table example_partition
Split partition max_value at (50000) into (partition example_p1, partition max_value);
Table altered.
SQL> Alter table example_partition
Split partition max_value at (100000) into (partition example_p2, partition max_value);
Table altered.
SQL>
SQL> select table_name, partition_name, high_value from user_tab_partitions where table_name ='EXAMPLE_PARTITION'
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ----------
EXAMPLE_PARTITION EXAMPLE_P1 50000
EXAMPLE_PARTITION EXAMPLE_P2 100000
EXAMPLE_PARTITION MAX_VALUE MAXVALUE
Now drop table example
Rename table example_partition to example
Add constraints to example
alter table example add constraint unique_example unique(id);
Table altered.
Method#3: Table redefinition using dbms_redefinition package
Scenario: Partition the table EXAMPLE and the table definition is as below.
SQL> CREATE TABLE "EXAMPLE"
2 ( "ID" number(10) NOT NULL ENABLE,
3 "UID" VARCHAR2(40),
4 "PIX" VARCHAR2(40),
5 "FNAME" VARCHAR2(100),
6 "MNAME" VARCHAR2(100),
7 "LNAME" VARCHAR2(100),
8 "SFIX" VARCHAR2(40),
9 "JTITLE" VARCHAR2(100),
10 "COMNAME" VARCHAR2(40),
11 "ADD1" VARCHAR2(50),
12 "ADD2" VARCHAR2(50),
13 "ADD3" VARCHAR2(50),
14 "CTY" VARCHAR2(30),
15 "STAT" VARCHAR2(20),
16 "POSTCODE" VARCHAR2(10),
17 "COUNTY" VARCHAR2(40),
18 "COUNTRY" VARCHAR2(40),
19 "PH_NUM" VARCHAR2(20),
20 "FX_NUM" VARCHAR2(15)
21 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
22 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
23 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
24 TABLESPACE "USERS"
25 ;
Table created.
SQL> insert into example (id) select rownum from dba_objects where rownum<100000;
95120 rows created.
SQL> COMMIT;
Commit complete.
1. Check whether the table can be redefined online. Table must need a primary key for this method.otherwise you will get following error message. Below mentioned package will check whether redefinition is possible or not.
SQL> exec dbms_redefinition.can_redef_table('scott', 'example');
BEGIN dbms_redefinition.can_redef_table('scott', 'example'); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "SCOTT"."EXAMPLE" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 139
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1770
ORA-06512: at line 1
SQL> alter table example add primary key (id);
Table altered.
SQL> exec dbms_redefinition.can_redef_table('scott', 'example');
PL/SQL procedure successfully completed.
2. Create a table with required partitions and this table would be the partitioned structure. The EXAMPLE table would be redefined with this structure.
CREATE TABLE "EXAMPLE_PARTITION"
( "ID" number(10) NOT NULL ENABLE,
"UID" VARCHAR2(40),
"PIX" VARCHAR2(40),
"FNAME" VARCHAR2(100),
"MNAME" VARCHAR2(100),
"LNAME" VARCHAR2(100),
"SFIX" VARCHAR2(40),
"JTITLE" VARCHAR2(100),
"COMNAME" VARCHAR2(40),
"ADD1" VARCHAR2(50),
"ADD2" VARCHAR2(50),
"ADD3" VARCHAR2(50),
"CTY" VARCHAR2(30),
"STAT" VARCHAR2(20),
"POSTCODE" VARCHAR2(10),
"COUNTY" VARCHAR2(40),
"COUNTRY" VARCHAR2(40),
"PH_NUM" VARCHAR2(20),
"FX_NUM" VARCHAR2(15)
) PARTITION BY RANGE(ID)
(PARTITION EXAMPLE_P1 VALUES LESS THAN (50000),
PARTITION EXAMPLE_P2 VALUES LESS THAN (100000))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS";
Table created.
3. Start the redefinition process using start_redef_table procedure.
SQL> exec dbms_redefinition.start_redef_table('scott', 'EXAMPLE', 'EXAMPLE_PARTITION');
PL/SQL procedure successfully completed.
declare
error_count pls_integer := 0;
BEGIN
dbms_redefinition.copy_table_dependents('SCOTT', 'EXAMPLE', 'EXAMPLE_PARTITION',1, true, true, true, false,error_count);
dbms_output.put_line('errors := ' || to_char(error_count));
END;
/
error_count pls_integer := 0;
BEGIN
dbms_redefinition.copy_table_dependents('SCOTT', 'EXAMPLE', 'EXAMPLE_PARTITION',1, true, true, true, false,error_count);
dbms_output.put_line('errors := ' || to_char(error_count));
END;
/
PL/SQL procedure successfully completed.
4. Finish the redefinition process using finish_redef_table procedure. Now EXAMPLE table is ready with partitions.
SQL> exec dbms_redefinition.finish_redef_table('SCOTT', 'EXAMPLE', 'EXAMPLE_PARTITION');
PL/SQL procedure successfully completed.
5. Check the partition details using below mentioned sql.
SQL> select table_name, partition_name, high_value from user_tab_partitions
2 where table_name ='EXAMPLE';
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ------------
EXAMPLE EXAMPLE_P1 50000
EXAMPLE EXAMPLE_P2 100000
Method#4: Using Exp/imp utility
1. Take export of non partitioned table
2. Drop the non partitioned table
3. Recreate the table with partitions
4. Import the table with ignore=y. ignore=y option skips the table creation error and continue with import
Method#5: Duplicate table
1. Create a duplicate of non-partitioned(with different name and same structure) table with partitions
2. Insert the data into partitioned table from non-partitioned table
3. Drop non-partitioned table
4. Rename the duplicate table to original table name
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment