Tuesday, June 29, 2010

Error when using 11g export client on a 10g database

The only reason to read this post is if you have googled for the error "ORA-00904: "POLTYP": invalid identifier". This error occurs if you try the old export command from an 11g client against a database on version 10g or lower. The export command runs a query against a table called EXU9RLS in the SYS schema. On 11g this table was expanded with the column POLTYP and the export command (exp) expects to find this column. This should not be much of a problem since Data Pump export can be used.

5 comments :

Anonymous said...

Data Pump cannot help:
Export: Release 11.2.0.1.0 - Production on Wed Jun 30 14:44:57 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
UDE-00018: Data Pump client is incompatible with database version 10.2.0.3.0

Oyvind Isene said...

Right, Data Pump export against a lower version won't work (Note 553337.1), you'll have to use the Data Pump client installed on the server with the source database. Since you'll have to fetch the export file from the same server it does not make much difference. Or you can run a Data Pump import over database link.

Anonymous said...

I found that if I ran the EXP command from an 11.2 client against an 11.1 server, I got the same error.

If DESC SYS.EXU9RLS on this database, it is missing that column too.

Anonymous said...

there is a patch: 7568350
execute
CREATE OR REPLACE VIEW exu9rls (
objown, objnam, polgrp, policy, polown, polsch, polfun, stmt,
chkopt, enabled, spolicy, poltyp) AS
SELECT u.name, o.name, r.gname, 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),
case bitand(r.stmt_type,16)+
bitand(r.stmt_type,64)+
bitand(r.stmt_type,128)+
bitand(r.stmt_type,256)+
bitand(r.stmt_type,8192)+
bitand(r.stmt_type,16384)+
bitand(r.stmt_type,32768)
when 16 then 'DBMS_RLS.STATIC'
when 64 then 'DBMS_RLS.SHARED_STATIC'
when 128 then 'DBMS_RLS.CONTEXT_SENSITIVE'
when 256 then 'DBMS_RLS.SHARED_CONTEXT_SENSITIVE'
when 8192 then 'DBMS_RLS.XDS1'
when 16384 then 'DBMS_RLS.XDS2'
when 32768 then 'DBMS_RLS.XDS3'
else 'DBMS_RLS.DYNAMIC'
end
FROM sys.user$ u, sys.obj$ o, sys.rls$ r
WHERE u.user# = o.owner# AND
r.obj# = o.obj# AND
(UID IN (o.owner#, 0) OR
EXISTS (
SELECT role
FROM sys.session_roles
WHERE role = 'SELECT_CATALOG_ROLE'))
/
GRANT SELECT ON sys.exu9rls TO PUBLIC;

-- this did the trick for me

Oyvind Isene said...

Thanks for stopping by, I'll keep this patch in mind next time.