Shulou(Shulou.com)06/01 Report--

There are several common methods:

1 、 expdp/impdp

2. Ctas + parallel + nologin

The second method should note that the primary key is not created in the new table.

NOT NULL constraints that were implicitly created by Oracle Database on columns of the selected table (for example, for primary keys) are not carried over to the new table.

Http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm

3 、 exchange partition

The following tests are conducted for the third method:

The creation of the big_table script comes from the Art of Oracle Database 9i10g11g programming and goes deep into the database architecture (2nd edition). The conversion mode is: ordinary table A.A-> partition table A.Anodic temp-> ordinary table B.B.

1. Create a test table:

Info@PROD > create table big_table 2 as 3 select rownum id, a.OWNER, a.OBJECT_NAME, a.SUBOBJECT_NAME, a.O 3 select rownum id, a.OWNER, a.OBJECT_NAME, a.SUBOBJECT_NAME, a.OBJECT_ID, a.DATA_OBJECT_ID 4 from all_objects a 5 where 1 O 6 / Table created.Elapsed: 00:00:00.09info@PROD > alter table big_table nologging;Table altered.Elapsed: 00:00:00.01info@PROD > declare 2 l_cnt number 3 l_rows number: = & 1; 4 begin 5 insert / * + append * / 6 into big_table 7 select rownum, a.OWNER, a.OBJECT_NAME, a.SUBOBJECT_NAME, a.OBJECT_ID, a.DATA_OBJECT_ID 8 from all_objects a 9 where rownum exec dbms_stats.gather_table_stats (user, 'BIG_TABLE', estimate_percent= > 1); PL/SQL procedure successfully completed.

Create an intermediate table:

Info@PROD > CREATE TABLE big_table_temp 2 PARTITION BY RANGE (id) 3 (PARTITION id_1 VALUES LESS THAN (MAXVALUE)) 4 AS 5 SELECT * 6 FROM big_table 7 WHERE ROWNUM alter table big_table_temp add constraint pk_big_table_temp_id primary key (id)

Authorize pinfo users:

Info@PROD > grant ALL on big_table to "PINFO"; info@PROD > grant ALL on big_table_temp to "PINFO"

Log in to pinfo and create a table with the same name as info:

Info@PROD > conn pinfo/adminConnected.pinfo@PROD > CREATE TABLE pinfo.big_table 2 AS 3 SELECT * 4 FROM info.big_table 5 WHERE ROWNUM conn info/admininfo@PROD > ALTER TABLE big_table_temp EXCHANGE PARTITION id_1 WITH TABLE big_table EXCLUDING INDEXES WITHOUT VALIDATION Table altered.Elapsed: Table altered.Elapsed: 0000VIE00.0The excludeing option is used here, otherwise ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION will be reported. You can manually create the index info@PROD > select count (*) from big_table; COUNT (*)-0info@PROD > select count (*) from info.big_table_temp; COUNT (*)-8000000 after the exchange is completed.

Log in to pinfo and exchange big_table_temp to big_table:

Pinfo@PROD > ALTER TABLE info.big_table_temp EXCHANGE PARTITION id_1 WITH TABLE pinfo.big_table EXCLUDING INDEXES WITHOUT VALIDATION;Table altered.Elapsed: 00:00:00.01pinfo@PROD > select count (*) from big_table; COUNT (*)-8000000Elapsed: 00:00:02.91pinfo@PROD > select count (*) from info.big_table_temp; COUNT (*)-0

The exchange is completed in almost milliseconds.

It can also be swapped back:

Pinfo@PROD > ALTER TABLE info.big_table_temp EXCHANGE PARTITION id_1 WITH TABLE pinfo.big_table EXCLUDING INDEXES WITHOUT VALIDATION;info@PROD > conn info/admininfo@PROD > ALTER TABLE big_table_temp EXCHANGE PARTITION id_1 WITH TABLE big_table exCLUDING INDEXES WITHOUT VALIDATION

The following content is from asktom, transformed by ordinary table A.A-> partition table B.B.

Reference: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:752030266230

To quickly move big tables between schemas use EXCHANGE PARTITION feature of Oracle 8i.for example:SQL > connect as user "A" SQL > create table large_table (a number, b char, c date)-just for this example only. SQL > grant ALL on large_table to "B"; SQL > connect as user "B" SQL > create table large_table (a number, b char, c date) partition by range (a) (partition dummy values less than (maxvalue)) Then you can use the following command to quickly move "A.large_table" to "B.large_table" SQL > connect as user "B" SQL > alter table large_table exchange partition dummy with table A.largetables and return it back to schema A:SQL > alter table large_table exchange partition dummy with table A.largetables Tablewitz-of course, it is the same SQL command

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.