sqlplus & exp "hanging" on a particular table

From: Jeremy <jeremy0505_at_gmail.com>
Date: Wed, 12 Oct 2011 09:26:20 +0100
Message-ID: <MPG.28ff5f89f8a1dd79989983_at_News.Individual.NET>

Hi this is weird.

DB11 is (Oracle Linux)
DB10 is (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 - Production on Tue Oct 11 18:18:02 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Release - 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
. . exporting table X_IC_BO_BRANDING 0 rows
. . exporting table X_IC_PRES_MENU_ITEMS 81 rows
. . exporting table X_IC_SCREEN_FIELDS 60 rows
. . exporting table X_IC_SCREEN_LAYERS 7 rows
.... 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_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

Received on Wed Oct 12 2011 - 03:26:20 CDT

Original text of this message