import oracle9i to 8i

[kt_ananth]

Hi,



I want to know is it possible to import a dump file( which is created from oracle 9i ) into an oracle 8i database.

I'm not sure,but Sowewhere i read that it is possible by the use catexp.sql.

Could u pls. give me an answer .

[robertbalmer]

I have tried it in the past and it doesnt work and somehow it makes sense that it should work...easily

The best you can do is make a "small" test imp on one user schema.

[engiri]

We too tried but in vain,,So tired..

http://www.dbasupport.com/forums/sho...threadid=41437

[DaPi]

I am not surprised it doesn't work. (Speaking naively) 9i export can produce stuff from new facilities that 8i can't understand because they didn't exist when 8i was born.

The rule is, when going from version X to version Y:
- export with the lower of X and Y, from X
- import with Y, into Y
(see Tom Kyte 1-on-1 p.353)

So:
- from 9i to 8i, both export and import need to be 8i
- from 8i to 9i, export with 8i, import with 9i

[jzk]

I think, it does work, but you need small workaround.
first, you need use for export from 9i database 8i client.
second in the 9i database you have to replace the original view SYS.EXU81RLS by this one modified :
CREATE OR REPLACE VIEW exu81rls (
objown,
objnam,
policy,
polown,
polsch,
polfun,
stmts,
chkopt,
enabled,
spolicy )
AS
select u.name, o.name, r.pname, r.pfschma, r.ppname, r.pfname,
decode(bitand(r.stmt_type,1), 0,'', 'SELECT,')
|| decode(bitand(r.stmt_type,2), 0,'', 'INSERT,')
|| decode(bitand(r.stmt_type,4), 0,'', 'UPDATE,')
|| decode(bitand(r.stmt_type,8), 0,'', 'DELETE,'),
r.check_opt, r.enable_flag,
DECODE(BITAND(r.stmt_type, 16), 0, 0, 1)
from user$ u, obj$ o, rls$ r
where u.user# = o.owner#
and r.obj# = o.obj#
and (uid = 0 or
uid = o.owner# or
exists ( select * from session_roles where role='SELECT_CATALOG_ROLE')
)
/

After this you can export from 9i database with 8i client and this dump you can import into 8i database.

Regards
JZK

[davey23uk]

that wasnt the question - the question was to import a 9i dmp file into an 8i db which generally isnt possible

[jzk]

hmmmm, the question doesn't specify which version of client could by used, of course dump made by 9i client is not usable (I think).

[slimdave]

Wouldn't it be amazing if Oracle documented this stuff? In the Utilities Guide, for example.

They might call it "Restrictions When Using Different Releases and Versions of Export and Import" and even provide "Examples of Using Different Releases of Export and Import". Maybe they'dgiveabrief overview of "Possible Errors When Using Different Releases and Versions".

Oh wait ...

[mrchrispy]

ROFL Slim

you be evil