Re: sqlplus & exp "hanging" on a particular table

From: joel garry <joel-garry_at_home.com>
Date: Wed, 12 Oct 2011 11:17:17 -0700 (PDT)
Message-ID: <2c93ffaf-5a67-430c-a4d8-6ec755289375_at_v18g2000pri.googlegroups.com>

On Oct 12, 1:26 am, Jeremy <jeremy0..._at_gmail.com> wrote:
> Hi this is weird.
>
> DB11 is 11.2.0.2 (Oracle Linux)
> DB10 is 10.2.0.3 (RHEL)
>
> We want to export a (tiny) schema from DB11 and import into DB10
>
> Using the exp utility on DB10:
>
> $ exp u/p file=/tmp/exp.dmp
>
> Export: Release 10.2.0.4.0 - Production on Tue Oct 11 18:18:02 2011
>
> Copyright (c) 1982, 2007, Oracle.  All rights reserved.
>
> Connected to: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
> With the Automatic Storage Management option
> Export done in US7ASCII character set and UTF8 NCHAR character set
> server uses WE8ISO8859P1 character set (possible charset conversion)
> . exporting pre-schema procedural objects and actions
> . exporting foreign function library names for user WD_EXP
> . exporting PUBLIC type synonyms
> . exporting private type synonyms
> . exporting object type definitions for user WD_EXP
> About to export WD_EXP's objects ...
> . exporting database links
> . exporting sequence numbers
> . exporting cluster definitions
> . about to export WD_EXP's tables via Conventional Path ...
> . . exporting table                            IDS          0 rows
> exported
> . . exporting table               X_IC_BO_BRANDING          0 rows
> exported
> . . exporting table           X_IC_PRES_MENU_ITEMS         81 rows
> exported
> . . exporting table             X_IC_SCREEN_FIELDS         60 rows
> exported
> . . exporting table             X_IC_SCREEN_LAYERS          7 rows
> exported
> .... etc
>
> then it reaches one particular table (6 columns, maybe 300 rows) and
> just stops dead. No further messages. Lets refer to this as
> PROBLEM_TABLE.
>
> I then executed a couple of specific tests:
>
> $ exp u/p file=/tmp/exp.dmp tables=X_IC_BO_BRANDING
>
> - worked fine
>
> $ exp u/p file=/tmp/exp.dmp tables=PROBLEM_TABLE
>
> - hung as before
>
> "Very strange" thought I.
>
> What about a DB link? Created from the DB10 database to connect to DB11
>
> SQL> create database link db11 connect to .... identified by .... using
> '....';
>
> OK lets try:
>
> SQL> create X_IC_BO_BRANDING as select * from  X_IC_BO_BRANDING_at_db11;
>
> Table created.
>
> SQL> create PROBLEM_TABLE as select * from  PROBLEM_TABLE_at_db11;
> [NOTHING!]
>
> -----
>
> So, quite frustrated and obviously seeing an issue with this table we
> logged into the corresponding account on DB11 and performed:
>
> SQL> create PROBLEM_TABLE_COPY as select * from  PROBLEM_TABLE;
> Table created.
>
> So PROBLEM_TABLE is acessible and usable in the 11g account - no issues.
>
> Anyone seen anything like this?
>
> PROBLEM_TABLE looks like this:
>
>  ORG_VS_VAL_ID_USAGE_ID                    NOT NULL NUMBER
>  ORG_ID                                             NUMBER
>  VS_VAL_ID                                          NUMBER
>  ORG_ENABLED_FLAG                                   VARCHAR2(1)
>  DATE_CREATED                                       DATE
>  USER_ID_CREATED                                    NUMBER
>  DATE_UPDATED                                       DATE
>  USER_ID_UPDATED                                    NUMBER
>  SEQ                                                NUMBER
>
> --
> jeremy

Have you tried the 11g original export? Does the copied table export? How about rows=n?

Wild guess is there is something associated with the table in the data dictionary that is 11g only that the 10g chokes on. Can't imagine what. Reaching way into outer space, maybe something having to do with deferred segment creation, so somehow 10g access thinks there are no segments yet.

jg

--
_at_home.com is bogus.
http://www.thinq.co.uk/2011/10/12/oracles-virtualbox-kernel-mod-labelled-crap/

Received on Wed Oct 12 2011 - 13:17:17 CDT