11g 後設資料匯入 19c 分割槽表建立不成功

解決方案 1: 用使用者導

解決方案2:去掉 TRANSITION ("PART_01") ，重新匯入

解決方案 3: 匯入全資料

In this Document

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.4 to 12.2.0.1 [Release 11.2 to 12.2]

Oracle Database Cloud Schema Service - Version N/A and later

Oracle Database Backup Service - Version N/A and later

Oracle Database Cloud Exadata Service - Version N/A and later

Oracle Database Exadata Express Cloud Service - Version N/A and later

Information in this document applies to any platform.

SYMPTOMS

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or built-in schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

The following example describes the issue:

- Export the partitioned table with:

> expdp testcase/<PASSWORD> directory=test_dp dumpfile=test2.dmp tables=interval_tab

Export: Release 12.2.0.1.0 - Production on Thu Mar 15 06:24:34 2018

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Starting "TESTCASE"."SYS_EXPORT_TABLE_01": testcase/******** directory=test_dp dumpfile=test2.dmp tables=interval_tab

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "TESTCASE"."INTERVAL_TAB":"SYS_P364" 6.375 KB 2 rows

. . exported "TESTCASE"."INTERVAL_TAB":"PART_01" 6.367 KB 2 rows

Master table "TESTCASE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

- Import the dumpfile with parameter SQLFILE as below:

> impdp testcase/<PASSWORD> directory=test_dp dumpfile=test2.dmp sqlfile=test.sql

Import: Release 12.2.0.1.0 - Production on Thu Mar 15 06:26:11 2018

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Master table "TESTCASE"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "TESTCASE"."SYS_SQL_FILE_FULL_01": testcase/******** directory=test_dp dumpfile=test2.dmp sqlfile=test.sql

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "TESTCASE"."SYS_SQL_FILE_FULL_01" successfully completed at Thu Mar 15 06:26:15 2018 elapsed 0 00:00:03

- Execute the SQLFILE from the import above:

SQL> @test.sql

Session altered

.....

Session altered.

PARTITION BY RANGE ("CREATED_DATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) TRANSITION ("PART_01")

*

ERROR at line 10:

ORA-00922: missing or invalid option

- Reviewing the generated sqlfile, a transition clause TRANSITION ("PART_01") is used in the DDL statement:

SQL> ! cat test.sql

-- CONNECT TESTCASE

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

....

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: TABLE_EXPORT/TABLE/TABLE

CREATE TABLE "TESTCASE"."INTERVAL_TAB"

( "ID" NUMBER,

"CODE" VARCHAR2(10 BYTE),

"DESCRIPTION" VARCHAR2(50 BYTE),

"CREATED_DATE" DATE

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS"

PARTITION BY RANGE ("CREATED_DATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) TRANSITION ("PART_01")

(PARTITION "PART_01" VALUES LESS THAN (TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 8388608 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" READ WRITE ,

PARTITION "SYS_P364" VALUES LESS THAN (TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE

...

TABLESPACE "USERS" READ WRITE ) ;

- As it can be seen from dbms_metadata.get_ddl output, the transaction clause doesn't exist in the source database:

SQL> Set long 10000000

SQL> Select dbms_metadata.get_ddl('TABLE','INTERVAL_TAB','TESTCASE') FROM DUAL ;

DBMS_METADATA.GET_DDL('TABLE','INTERVAL_TAB','TESTCASE')

--------------------------------------------------------------------------------

CREATE TABLE "TESTCASE"."INTERVAL_TAB"

( "ID" NUMBER,

"CODE" VARCHAR2(10),

"DESCRIPTION" VARCHAR2(50),

"CREATED_DATE" DATE

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS"

PARTITION BY RANGE ("CREATED_DATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

(PARTITION "PART_01" VALUES LESS THAN (TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE

....

TABLESPACE "USERS" )

- Tracing the error with:

SQL> alter system set events '922 trace name ERRORSTACK level 3';

the generated trace file shows:

--- Error Stack Dump -----

ORA-00922: missing or invalid option

----- SQL Statement (None) -----

Current SQL information unavailable - no cursor.

prspo_partition prspo_one_opt prspo_proc_opts prsctc prscmd prsdrv kksQuickParse kksLoadParentOnLock kglLoadOnLock kgllkal kglLock kglget kkspsc0 kksParseCursor opiosq0 kpooprx

CAUSE

When you add data into the table beyond the range of partition, a new partition is created. The transition keyword specifies the range above which partition intervals will be created so as to enable DataPump to be able to regenerate the DDL.

The issue is under investigation in unpublished Bug 27470663 - ORA-00922 WHILE CREATING INTERVAL PARTITION WITH TRANSITION KEYWORD.

SOLUTION

To avoid the error until the unpublished BUG 27470663 is fixed, use one of below options:

1/ Edit the sqlfile, remove TRANSITION ("PART_01") and run the sqlfile without the transition clause.

- OR -

2/ Use DataPump import utility to load the dumpfile instead of running the create table statement with transition clause manually.

Example: impdp testcase/<PASSWORD> directory=test_dp dumpfile=test2.dmp

Import: Release 12.2.0.1.0 - Production on Thu Mar 15 07:00:01 2018

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Master table "TESTCASE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "TESTCASE"."SYS_IMPORT_FULL_01": testcase/******** directory=test_dp dumpfile=test2.dmp

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "TESTCASE"."INTERVAL_TAB":"SYS_P364" 6.375 KB 2 rows

. . imported "TESTCASE"."INTERVAL_TAB":"PART_01" 6.367 KB 2 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "TESTCASE"."SYS_IMPORT_FULL_01" successfully completed at Thu Mar 15 07:00:17 2018 elapsed 0 00:00:16

REFERENCES

11g後設資料匯入19c分割槽表建立不成功

相關文章