出錯原因： 往b用戶imp表時，要創建type，使用的OID和用戶a的一樣，同一個實例的OID不能重復。 解決辦法： 在system用戶下定義 type MAIN_SZ_ZGY_TYPE，授權 grant all on MAIN_SZ_ZGY_TYPE to public; 用戶a建表用system.MAIN_SZ_ZGY_TYPE 類型，這樣導出用戶表時就不會到type了，在用戶b中就不會建type。 詳細內容如下： Introduction: ============= If you are importing using the FROMUSER/TOUSER clause to duplicate a schema within an instance, you may experience the following errors: ??imp system/manager fromuser=a touser=b file=demo.dmp log=import.log ??IMP-00017: following statement failed with ORACLE error 2304: ??IMP-00003: ORACLE error 2304 encountered ??ORA-02304: invalid object identifier literal ??IMP-00063: Warning: Skipping table "x"."x" because object ? ?? ?? ?? ?? ?type "x"."x" cannot be created or has different identifier These errors will occur if the schema has a user defined object type(s) (CREATE TYPE) and a relational table column of a user defined datatype. The IMP-00017 error is of particular interest since it indicates te source of the error: ??IMP-00017: following statement failed with ORACLE error 2304: ??"CREATE TYPE "xxxx" TIMESTAMP '1999-01-01:12:00:00' OID '####' as object ..." In brief, if the FROMUSER's object types already exist on the target instance, errors occur because the object identifiers (OIDs) of the TOUSER's object types already exist. Within a single database instance, object identifiers (OIDs) must be unique. As a result, the error causes Import will skip the creation of relational tables with columns of the pre-existing user defined type. So what are the options available to us for completing this import? Possible Solution Scenarios: ============================ A.) Use the IGNORE=Y clause on the import ? ? This WILL NOT succeed since CREATE TYPE errors are only ignored if ? ? importing into the originating schema, not into a separate "to" ? ? schema! B.) Pre-create the relational table in the TOUSER's schema ? ? This WILL NOT succeed since the CREATE TYPE statement is present in ? ? the export file. C.) Drop the TABLE and TYPE in the FROMUSER schema prior to performing ? ? the import. ? ? This WILL succeed. Note that we cannot simply drop ? ? the type since this will result in an ORA-02303 error as follows: ? ? ORA-02303: cannot drop or replace a type with type or table dependents ? ? We must first drop all tables containing the target TYPE, then the TYPE ? ? itself as follows: ? ? SQL> drop table mytypetable; ? ? SQL> drop table mytypetable2; ? ? SQL> drop type mytype;? ??? D.) From import.log note down the object id (OID) for the erroring type. ? ? I.e., the OID '####' of the error.?? ? ? Then run the following statement as dba: ? ? SQL> select OWNER, TYPE_NAME from dba_types where TYPE_OID='####'; ? ? This statement would give you the owner and the typename for this OID. ? ? If not needed, drop this type as below: ? ? SQL>drop type XXX; ? ? Run the import again. E.) Perform a cascading drop of the FROMUSER prior to performing the import. ? ? This WILL succeed since it is essentially the same as option C, only ? ? far less selective. The syntax is quite simple: ? ? SQL> drop user myfromuser cascade; F.) Recreate the TYPE in an independent schema, grant all on the TYPE to PUBLIC, ? ? create a copy of the TABLE in the FROMUSER schema using this public TYPE, ? ? copy all the old TABLE into the new TABLE using PL/SQL, and redo the ? ? export. Subsequently, perform the TOUSER import. ? ? This WILL succeed since the owner of the TYPE is not involved in the ? ? export or import operations. As such, the CREATE TYPE statement is ? ? not issued as a part of the import operation. ? ? The trick part of this option is recreating the object in question using ? ? the public TYPE. This can accomplished by following this guide: ? ? -- create the public type ? ? SQL> connect system/manager@local ? ? SQL> create or replace type mytype as object (m1 number, m2 varchar2(20)); ? ? SQL> grant all on mytype to public; ? ? -- rename the user-type table ? ? SQL> connect myuser/mypassword@local ? ? SQL> rename mytypetable to mytypetemp; ? ? -- create the new public-type table to be corrected ? ? SQL> create table mytypetable (id number primary key, person system.mytype); ? ? -- copy the data from the user-type table to the public-type table ? ? SQL> declare ? ?? ?? ???v_col1??number; ? ?? ?? ???v_col2??mytype; ? ?? ?? ???cursor c1 is ? ?? ?? ?? ? select * from mytypetemp; ? ?? ?? ?begin ? ?? ?? ???open c1; ? ?? ?? ???loop ? ?? ?? ?? ? fetch c1 into v_col1, v_col2; ? ?? ?? ?? ? exit when c1%notfound; ? ?? ?? ?? ? insert into mytypetable ? ?? ?? ?? ?? ?values (v_col1, system.mytype(v_col2.m1, v_col2.m2)); ? ?? ?? ?? ? commit; ? ?? ?? ???end loop; ? ?? ?? ???close c1; ? ?? ?? ?end; ? ?? ?? ?/ ? ? -- drop the user-type and user-type table ? ? SQL> drop table mytypetable; ? ? SQL> drop type mytype;? ??? Summmary: ========= In summary, if FROMUSER/TOUSER import is used to duplicate a schema in an instance then object types should be isolated in a schema designated only for object types. This is a design and maintenance issue that requires serious consideration.??IGNORE=Y only ignores CREATE TYPE import errors if the import schema is the export schema.?? Note: A table level export/import works exactly the same as a schema level in ? ?? ?regards to object types since the object type is a component of the table ? ?? ?scope.

總結

以上是生活随笔為你收集整理的oracle中怎么导外表,ORACLE 自定义类型该如何导入？？？？的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。