Non-partitioned table conversion composition Area table

1.partition a non-partitioned table in one of four ways:
A) Export/import method
1, exp usr/pswd tables=numbers file=exp.dmp
2, drop table numbers;
3,
CREATE TABLE Numbers (Qty Number (3), name VARCHAR2 (15))
Partition by Range (qty)
(partition P1 values less than (501),
Partition P2 values less than (MaxValue));
4,imp usr/pswd file=exp.dmp Ignore=y
The ignore=y causes the "Import to skip" table creation and continues to load all rows.

B) Insert with a subquery method

C) Partition Exchange method
ALTER TABLE EXCHANGE PARTITION can used to convert a PARTITION (or subpartition) into a non-partitioned TABLE and a non -partitioned table into a
Partition (or subpartition) of a partitioned table by exchanging their data and index segments.
1.create Table P_emp
(Sal number (7,2))
Partition by range (SAL)
(partition EMP_P1 values less than (2000),
Partition EMP_P2 values less than (4000));

2.create table dummy_y as select RowNum sal from dual connect by rownum > 1000 and RowNum < 2000;
Eate table Dummy_z as SELECT * FROM select RowNum sal to dual connect by RowNum < 4000) where Sal > 2000

3.qn@rac> ALTER TABLE P_EMP Exchange partition EMP_P1 with table dummy_y;
ALTER TABLE P_EMP Exchange partition EMP_P1 with table dummy_y
*
ERROR at line 1:
Ora-14097:column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Qn@rac> ALTER TABLE dummy_y modify SAL number (7,2);
ALTER TABLE dummy_y Modify SAL number (7,2)
*
ERROR at line 1:
Ora-01440:column to is modified must be empty to decrease precision or scale

qn@rac> truncate TABLE dummy_y;
qn@rac> INSERT INTO dummy_y SELECT * FROM (select RowNum sal to dual connect by rownum <) where Sal > 1 000
Qn@rac> commit;
qn@rac> ALTER TABLE P_EMP Exchange partition EMP_P1 with table dummy_y;


D) dbms_redefinition
Example 1:
/* Non-partitioned table is customized as a partitioned table, while adding fields * *
This example illustrates online redefinition of the previously created table Hr.admin_emp NS Columns:empno, ename, Job, Deptno.
The table isredefined as follows:
1.Online Table redefinition Examples
Qn.admin_emp
CREATE TABLE Admin_emp (
Empno Number (5) PRIMARY KEY
, ename VARCHAR2 not NULL
, Job VARCHAR2 (10)
, Deptno number (3) is not NULL
)

1.Verify that the "table is a" candidate for online redefinition. In the case for you specify
That's the redefinition is to being done using primary keys or pseudo-primary keys

/* must have permission, otherwise dbms_redefinition run error * *
Grant execute on dbms_redefinition to QN;
Grant Execute_catalog_role to QN; --execute privileges on the Dbms_redefinition package are granted to Execute_catalog_role.

Grant CREATE TABLE to QN;
Grant CREATE materialized VIEW to QN; /* Description of the synchronization process requires materialized views * *
Grant CREATE INDEX to QN;

The CREATE TRIGGER privilege is also required to execute the copy_table_dependents procedure.

BEGIN
Dbms_redefinition. Can_redef_table (' QN ', ' admin_emp ', dbms_redefinition. CONS_USE_PK);
End;

2.Create an interim table hr.int_admin_emp
Create tablespace admin_tbs datafile size 200m;
Create tablespace admin_tb2 datafile size 200m;

CREATE TABLE Int_admin_emp
(empno number (5) PRIMARY KEY,
Ename VARCHAR2 () not NULL,
Job VARCHAR2 (10),
Mgr Number (5),
HireDate DATE DEFAULT (sysdate),
Sal Number (7,2),
Deptno Number (3) is not NULL,
Bonus number (7,2) DEFAULT (1000))
PARTITION by RANGE (empno)
(PARTITION emp1000 VALUES less THAN (1000) tablespace Admin_tbs,
PARTITION emp2000 VALUES less THAN (Watts) tablespace ADMIN_TB2);


3.Start the redefinition process
/* Corresponding fields to synchronize data, otherwise only part of the data can be synchronized
/* DBMS_REDEFINITION.CONS_USE_PK and Cons_use_rowid
/* Custom method: CONS_USE_PK According to primary key
Cons_use_rowid according to ROWID
*/

BEGIN
Dbms_redefinition. Start_redef_table (' qn ', ' admin_emp ', ' int_admin_emp ', ' empno empno, ename ename, Job Job, Deptno+10 deptno, 0 bonus ', Dbms_ REDEFINITION.CONS_USE_PK);
End;


4.Copy dependent objects. (Automatically create any triggers, indexes, materialized view logs, grants, and constraints on hr.int_admin_emp.)
/* Automatic replication triggers, indexes, materialized view logs, grants, constraints * *
DECLARE
Num_errors Pls_integer;
BEGIN
Dbms_redefinition. Copy_table_dependents (' QN ', ' admin_emp ', ' int_admin_emp ', dbms_redefinition. Cons_orig_params, True, True, true, true, num_errors);
End;
/

The Ignore_errors argument is set to TRUE for this call. The reason is this interim table was created with a primary key constraint,
And when Copy_table_dependents attempts to COPY the primary KEY constraint and index from the original TABLE, errors S. Can ignore these errors,
But you must run the query shown into the next step into the if there are other errors.

5.Query The dba_redefinition_errors view to check for ERRORS.
/* To query the error, not and all errors are required to implement the * *
Sys@rac> Select object_name, Base_table_name, ddl_txt from Dba_redefinition_errors;

object_name Base_table_name Ddl_txt
------------------------------ ------------------------------ --------------------------------------------------- -----------------------------
sys_c0011092 admin_emp CREATE UNIQUE INDEX "QN". tmp$$_sys_c00110920 "on" QN "." Int_admin_emp "(" EMPNO ")
PCTFREE Initrans 2 Maxtrans 255 COMPUTE STATISTICS
STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 maxextents 2147483645
Pctincrease 0 freelists 1 freelist GROUPS 1
Buffer_pool default Flash_cache default Cell_flash_cache default)
Tablespace "USERS"

sys_c0011090 admin_emp ALTER TABLE "QN". Int_admin_emp "MODIFY (" ename "CONSTRAINT" tmp$$_sys_c00110900 "not NULL ENABLE novalidate)

sys_c0011091 admin_emp ALTER TABLE "QN". Int_admin_emp "MODIFY (" DEPTNO "CONSTRAINT" tmp$$_sys_c00110910 "not NULL ENABLE novalidate)

sys_c0011092 admin_emp ALTER TABLE "QN". Int_admin_emp "ADD CONSTRAINT" tmp$$_sys_c00110920 "PRIMARY KEY (" EMPNO ")
USING INDEX PCTFREE Initrans 2 Maxtrans 255 COMPUTE STATISTICS
STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 maxextents 2147483645
Pctincrease 0 freelists 1 freelist GROUPS 1
Buffer_pool default Flash_cache default Cell_flash_cache default)
Tablespace "USERS" ENABLE novalidate


6.Optionally, synchronize the interim table hr.int_admin_emp.
/* This step will refresh the data from admin_emp to the middle table int_admin_emp, without changing the table name * *
BEGIN
Dbms_redefinition. Sync_interim_table (' QN ', ' admin_emp ', ' int_admin_emp ');
End;




7.Complete the redefinition
/* This step will reload the data from the Admin_emp refresh to the middle table int_admin_emp, exclusive mode, INT_ADMIN_EMP changed to Admin_emp,admin_emp to Int_admin_emp * *
BEGIN
Dbms_redefinition. Finish_redef_table (' qn ', ' admin_emp ', ' int_admin_emp ');
End;



8. Wait for any long-running queries against the interim table to complete, and then
Drop the interim table.


Select Dbms_metadata.get_ddl (' TABLE ', ' admin_emp ') from dual;