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

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 12 Oct 2011 06:14:42 -0700 (PDT)
Message-ID: <271b03c4-55d4-432c-bbd6-86e8a95e7ae8_at_x6g2000prn.googlegroups.com>

On Oct 12, 4: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

Jeremy, I did a quick search of Oracle Support and did not find any notes that would seem to apply. I am not sure on 11.2 if the default is still direct=n but if so have you tried direct=y and I would add the buffer=1048576 (conventional) and feedback=100 parameters to see if feedback shows any progress for the table.

Also check the target directory and make sure it is not near full.

HTH -- Mark D Powell -- Received on Wed Oct 12 2011 - 08:14:42 CDT