Question:
How to make a non partitioned large table to a partioned one without losing its data ( droping it)?
Answer:
Partition Exchange, Allows the transfer of non-partitioned tables into partitions as well as the reverse. That is the transfer of partitions into non-partitioned tables. This option is particularly useful in migrating V7 partitioned views into table partitions. Consider the following partitioned view based on four tables: less50, less100, less150, less200.
CREATE VIEW test_view AS
SELECT * FROM less50
UNION ALL
SELECT * FROM less100
UNION ALL
SELECT * FROM less150
UNION ALL
SELECT * FROM less200;
An empty partitioned table needs to be created, on the same schema as
the underlying tables of the partitioned views:
CREATE TABLE new_test(
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
sal NUMBER(7,2))
PARTITION BY RANGE(EMPNO)
(
partition emp_p1 VALUES LESS THAN (50),
partition emp_p2 VALUES LESS THAN (100),
partition emp_p3 VALUES LESS THAN (150),
partition emp_p4 VALUES LESS THAN (200));
Then you need to transfer each underlying table of the view in the
corresponding partition of the partitioned table:
ALTER TABLE new_test EXCHANGE PARTITION
emp_p1WITH TABLE less50 WITH VALIDATION;
This takes a very short time as the updates only take place in the
Oracle dictionary. There is no physical movement of the segments.
The structure of the tables to swap partitioned as well as non-
partitioned must be identical in terms of types, columns, and sizes,
as well as the number of columns.
submitted
by:
Mr Hamid Belallia
