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

It seems it is a the bug No. 4550853 in 10.2.0. Great, I will have to wait until version 11, usefull tool for later anyway, but it probalbly wont work on older bases :/

I'll stick with my workaround :/

Bryce a écrit :

> 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 - 10:24:45 CST