Re: hanged on import on undo segment

It's v$session_wait for sid = 11 that we need to see, not v$session. However, going back to your earlier post, session 11 is doing this:

       11 34903 SQL*Net message from dblink driver id 675562835 28444553
#bytes 1 00000001

  0 00 0 17097 WAITING Which means waiting for a reply from a remote database for the last 4.7 hours.

Is it possible that your import is loading data into a pre-existing table which has a trigger to a remote database.

Having said that, I would have expected your session to fail with 'remote db gone away' rather than just wait; so I suspect you've suffered a software error and the remote doesn't know that this database is waiting for a message.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 4th Nov 2005

• Original Message ----- From: "Juan Carlos Reyes Pacheco" <juancarlosreyesp_at_gmail.com> To: <jonathan_at_jlcomp.demon.co.uk> Cc: <oracle-l_at_freelists.org> Sent: Wednesday, November 23, 2005 8:09 PM Subject: Re: hanged on import on undo segment

Thanks Jonathan

select SID from v$session where program='IMP.EXE'-- where username is not null;
11

SQL> EXEC PRINT_TABLE('select * from v$session where SID=11');

SADDR                         : **
UNPRINTABLE **
SID                           : 11
SERIAL#                       : 16
AUDSID                        : 51
PADDR                         : **
UNPRINTABLE **
USER#                         : 5
USERNAME                      : SYSTEM
COMMAND                       : 0
OWNERID                       : 2147483644
TADDR                         : 14AAD41C
LOCKWAIT                      :
STATUS                        : ACTIVE
SERVER                        : DEDICATED
SCHEMA#                       : 54
SCHEMANAME                    : CTB
OSUSER                        : Administrador
PROCESS                       : 1316:1404
MACHINE                       : DAZASOFTWARE\SRVDAZSC
TERMINAL                      : SRVDAZSC
PROGRAM                       : IMP.EXE
TYPE                          : USER
SQL_ADDRESS                   : **
UNPRINTABLE **
SQL_HASH_VALUE                : 1141231982
PREV_SQL_ADDR                 : **
UNPRINTABLE **
PREV_HASH_VALUE               : 1141231982
MODULE                        :
MODULE_HASH                   : 0
ACTION                        :
ACTION_HASH                   : 0
CLIENT_INFO                   :
FIXED_TABLE_SEQUENCE          : 323
ROW_WAIT_OBJ#                 : -1
ROW_WAIT_FILE#                : 0
ROW_WAIT_BLOCK#               : 0
ROW_WAIT_ROW#                 : 0
LOGON_TIME                    : 23 Nov 2005
09:47:31
LAST_CALL_ET                  : 19543
PDML_ENABLED                  : NO
FAILOVER_TYPE                 : NONE
FAILOVER_METHOD               : NONE
FAILED_OVER                   : NO
RESOURCE_CONSUMER_GROUP       : SYS_GROUP
PDML_STATUS                   : DISABLED
PDDL_STATUS                   : ENABLED
PQ_STATUS                     : ENABLED
CURRENT_QUEUE_DURATION        : 0
CLIENT_IDENTIFIER             :

How do you know that you are "hanging" - if you've done an import that needs to create a lot of indexes and it's doing it on parallel-enabled tables, maybe the display has stopped but the machine is busy.

firstI checked I didn't clicked in the cmd windows :), Second my database is abuot 4 G, and the import of the biggest tables already was done, it was in the last stage "importing objects" and there are few object to take as much time.

--
http://www.freelists.org/webpage/oracle-l