sqlplus & exp "hanging" on a particular table

#1

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@db11;

Table created.

SQL> create PROBLEM_TABLE as select * from PROBLEM_TABLE@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

#2

On Oct 12, 4:26*am, Jeremy <jeremy0... (AT) gmail (DOT) com> wrote:

Quote:

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@db11;

Table created.

SQL> create PROBLEM_TABLE as select * from *PROBLEM_TABLE@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 NULLNUMBER
*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 --

#3

On Wed, 12 Oct 2011 09:26:20 +0100, Jeremy wrote:

Quote:

SQL> create PROBLEM_TABLE as select * from PROBLEM_TABLE@db11;
[NOTHING!]

Did you check for wait events? What does "hanging" means? What events do
you see in V$SESSION? Is it waiting? Doing something? Tracing the
background process by turning on 10046 would also be a good idea.

--
http://mgogala.byethost5.com

#4

On Oct 12, 1:26*am, Jeremy <jeremy0... (AT) gmail (DOT) com> wrote:

Quote:

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@db11;

Table created.

SQL> create PROBLEM_TABLE as select * from *PROBLEM_TABLE@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 NULLNUMBER
*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
--
@home.com is bogus.
http://www.thinq.co.uk/2011/10/12/or...labelled-crap/

#5

On Oct 12, 7:26*pm, Jeremy <jeremy0... (AT) gmail (DOT) com> wrote:

Quote:

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

Weird...
Can you run a dbms_metadata.get_ddl on that table and post the
results?

#6

On Oct 12, 2:17*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:

Quote:

On Oct 12, 1:26*am, Jeremy <jeremy0... (AT) gmail (DOT) 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@db11;

Table created.

SQL> create PROBLEM_TABLE as select * from *PROBLEM_TABLE@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
--
@home.com is bogus.http://www.thinq.co.uk/2011/10/12/or...-mod-label...- Hide quoted text -

- Show quoted text -

JG brought up an interesting point. I am at home and do not have a
system to check against but in the old days some newer releases of
Oracle came with scripts located in $ORACLE_HOME/rdbms/admin that had
to be ran to create the export/import views for older versions of the
utility to be ran against the database. (Showing my age here) It
might be worth looking if the table is not defined using any new 11g
only features that the 10g export would not know how to handle.

HTH -- Mark D Powell --

#7

On Wed, 12 Oct 2011 17:33:27 -0700, Mark D Powell wrote:

Quote:

G brought up an interesting point. I am at home and do not have a
system to check against but in the old days some newer releases of
Oracle came with scripts located in $ORACLE_HOME/rdbms/admin that had to
be ran to create the export/import views for older versions of the
utility to be ran against the database. (Showing my age here) It might
be worth looking if the table is not defined using any new 11g only
features that the 10g export would not know how to handle.

Why not use expdp with the proper version parameter?

--
http://mgogala.byethost5.com

#8

In article <271b03c4-55d4-432c-bbd6-86e8a95e7ae8
@x6g2000prn.googlegroups.com>, Mark.Powell2 (AT) hp (DOT) com says...

Quote:

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.

OK I used the options you suggested:

...
...
...
...
.. . exporting table X_WD_VS_VALS
......
EXP-00008: ORACLE error 12592 encountered
ORA-12592: TNS:bad packet
EXP-00056: ORACLE error 1403 encountered
ORA-01403: no data found
EXP-00000: Export terminated unsuccessfully

This is NOT what I referred to as PROBLEM_TABLE.

I think we have a network/firewall issue here...

PROBLEM_TABLE now exports successfully using the direct=yes but removing
that option results in "hanging" again - i.e. just doesn't complete.

Very very odd. Talking with the service providers about their networks.

--
jeremy

#9

On Thu, 13 Oct 2011 21:24:29 +0100, Jeremy wrote:

Quote:

PROBLEM_TABLE now exports successfully using the direct=yes but removing
that option results in "hanging" again - i.e. just doesn't complete.

What event is the session waiting on? Can you tell?

--
I don't always test software, but when I do, I prefer doing it in
production.
(The most interesting DBA in the world)

#10

On Oct 12, 10:26 am, Jeremy <jeremy0505 (AT) gmail (DOT) com> wrote:

Quote:

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@db11;

Table created.

SQL> create PROBLEM_TABLE as select * from PROBLEM_TABLE@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