Re: import to different user with the new correct grant doesn't work

From: Bryce <brice.dutheil_at_gmail.com>
Date: 21 Dec 2006 07:48:17 -0800
Message-ID: <1166716097.107260.14990@42g2000cwt.googlegroups.com>

Thank you so much!

It is exactly what I was looking for! However I got some problems when importing huge XMLType data stored as LOB.

I got something like that:
ORA-31693: Table data object "TESTADMINUSER"."XMLDATATABLE" failed to load/unload and is being skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
ORA-26065: check constraint cannot reference column, SYS_NC00003$, in

direct path load

I will investigate this error. At first it seems the way the file was loaded in the table was using a file in a directory on the DB server, and impdp cannot probably figure it out to import the data in the new schema. :S
then FK to this table cannot be

Anyway for the information, It is really usefull!

Andy Hassall a écrit :

> On 15 Dec 2006 06:06:38 -0800, "Bryce" <brice.dutheil_at_gmail.com> wrote:
>
> >I have a problem with the import util from oracle (Import: Release
> >10.2.0.1.0 - Production on Fri Dec 15 14:45:49 2006).
> >
> >I would like to import from 2 schemas to 2 different schema, and the
> >import tool works fine exept for the grantas that it sets to the
> >original user.
> >
> >adminuser (grants select, insert, etc to realuser)
> >realuser (got grants from adminuser)
> >
> >imp userid=sys/sys fromuser=adminuser,realuser
> >touser=testadminuser,testuser
> >
> >All the schemas are on the same database server.
> >
> >And once my import is done, testuser didn't get anygrant to
> >TESTADMINUSER objects while REALUSER now have grants from ADMINUSER and
> >from TESTADMINUSER)
> >
> >Is there a way to import related schemas into the same schemas named
> >differently.
>
> As you're on 10g it is well worth looking into Data Pump, the replacement for
> imp/exp. Basically Data Pump is what imp/exp should have been in the first
> place, and it will quite easily handle this case with the REMAP_SCHEMA option.
>
> e.g. after having done:
>
> $ expdp test/test dumpfile=cdos.dmp schemas=adminuser,realuser
> [output snipped]
>
> $ impdp test/test dumpfile=cdos.dmp remap_schema=adminuser:testadminuser
> remap_schema=realuser:testuser
>
> Import: Release 10.2.0.1.0 - Production on Monday, 18 December, 2006 17:25:38
>
> Copyright (c) 2003, 2005, Oracle. All rights reserved.
>
> Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 -
> Production
> Master table "TEST"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
> Starting "TEST"."SYS_IMPORT_FULL_02": test/******** dumpfile=cdos.dmp
> remap_schema=adminuser:testadminuser remap_schema=realuser:testuser
> Processing object type SCHEMA_EXPORT/USER
> Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
> Processing object type SCHEMA_EXPORT/ROLE_GRANT
> Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
> Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
> Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
> Processing object type SCHEMA_EXPORT/TABLE/TABLE
> Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
> . . imported "TESTADMINUSER"."T" 0 KB 0 rows
> Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
> Job "TEST"."SYS_IMPORT_FULL_02" successfully completed at 17:25:45
>
> $ sqlplus testuser/testuser
>
> SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 18 17:27:56 2006
>
> Copyright (c) 1982, 2005, Oracle. All rights reserved.
>
>
> Connected to:
> Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
>
> SQL> select table_name from all_tab_privs where table_schema = 'ADMINUSER';
>
> no rows selected
>
> SQL> select table_name from all_tab_privs where table_schema = 'TESTADMINUSER';
>
> TABLE_NAME
> ------------------------------
> T
>
>
> Data Pump is also exposed through PL/SQL APIs so you could also do this from
> within the database rather than from the commandline.
>
> --
> Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
> http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Received on Thu Dec 21 2006 - 09:48:17 CST