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

From: PaulS <stefan.kapitza_at_googlemail.com>
Date: Fri, 14 Oct 2011 05:32:22 -0700 (PDT)
Message-ID: <a45a1d61-3f0c-4ee4-b2a9-e65c9e714069_at_p25g2000yqh.googlegroups.com>



On Oct 12, 10:26 am, Jeremy <jeremy0505_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

Had this as our security guys implemented an Application Gateway (Juniper)
between our Servers, dropping Connections. Do you have such boxes in place ?

regards

Stefan Kapitza Received on Fri Oct 14 2011 - 07:32:22 CDT

Original text of this message