Re: OUTLN schema import oddity

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <kevin.lidh@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 10 Feb 2006 18:33:24 -0000


There are various post-import tasks that belong to certain schema. OUTLN has one, and one of its jobs is to delete outlines that are not self-
consistent. It certainly used to be the case in 8i
that you could manually "adjust" an outline in a
way that would make it do what you wanted, but which would not survive an exp/imp.


Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 2nd Feb 2006

----- Original Message ----- From: "Kevin Lidh" <kevin.lidh@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, February 10, 2006 5:50 PM
Subject: OUTLN schema import oddity



We have a production system in which we created 12 stored outlines. So if you log in as outln you can say: select count(*) from ol$ and get 12. When I do an export, I see this:
exp outln file=test.dmp rows=y

Export: Release 9.2.0.4.0 - Production on Fri Feb 10 09:26:32 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining
options
JServer Release 9.2.0.4.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user OUTLN
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user OUTLN
About to export OUTLN's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export OUTLN's tables via Conventional Path ...
. . exporting table                            OL$         12 rows exported
. . exporting table                       OL$HINTS        904 rows exported
. . exporting table                       OL$NODES         11 rows exported

....< ~~ truncated for e-mail brevity

Export terminated successfully without warnings.

Then when I import, I see this:
imp outln file=test.dmp full=y ignore=y

Import: Release 9.2.0.4.0 - Production on Fri Feb 10 10:12:24 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
export client uses WE8MSWIN1252 character set (possible charset conversion)
. importing OUTLN's objects into OUTLN
. . importing table                          "OL$"         12 rows imported
. . importing table                     "OL$HINTS"        904 rows imported
. . importing table                     "OL$NODES"         11 rows imported
Import terminated successfully without warnings.

But when I do a select count(*) on our test system, I get this:
select count(*) from ol$;

COUNT(*) ---------- 9 And there are only 678 hints as well. I've tried moving the table to a tablespace outside of SYSTEM (which is only 4% full), granting "dba" to OUTLN, setting quota to unlimited on SYSTEM to OUTLN, used consistent=y on the export and it always comes out the same. No errors on import but only 9 records. To make things more confusing for me, I imported into a different schema and got all 12 records. Has anyone ever seen this? I'm stumped. I'm not seeing any errors in the alert log either (or trace files generated).


-- //www.freelists.org/webpage/oracle-l


Other related posts: