sqlplus & exp "hanging" on a particular table
Date: Wed, 12 Oct 2011 09:26:20 +0100
Message-ID: <MPG.28ff5f89f8a1dd79989983_at_News.Individual.NET>
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
-- jeremyReceived on Wed Oct 12 2011 - 03:26:20 CDT