oracle中怎么导外表,ORACLE 自定义类型该如何导入？？？？

出错原因： 往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.