ORA-00904: "SPOLICY": invalid identifier

[mike2000]

I want to export the tables on 9i using 8i client exp utilities and I thought that should work fine but I have problem, below is the errors:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8ISO8859P1 character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...
EXP-00008: ORACLE error 904 encountered
ORA-00904: "SPOLICY": invalid identifier
EXP-00000: Export terminated unsuccessfully


Can someone please tell me what happen???

[pando]

search the forum, there are many threads about this, search metalink as well

[rohitsn]

Hi,
The 8i export is reported to fail on 9i because of a problem with on of the export views (has to do with subtypes).
OK, I've the workaround suggested after I've opened a TAR and now it works. The workaround was to create the following view as sys on the 9.2.0 DB:

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')
)
/
grant select on sys.exu81rls to public;
/

and then try to export it.

Regards,
Rohit Nirkhe,Oracle/APPS DBA,OCP 8i,OCA 9i
oracle-support@indiatimes.com