locked
Connection to Oracle freezes RRS feed

  • Question

  • I've installed SSMA for Oracle on the server where the Oracle instance resides.

    When I "Connect to Oracle", I fill in all the necessary connection details.  It gives me some warning about some permissions I'm missing:

    but otherwise seems to proceed OK.  Then it starts "loading objects"... and progresses slowly from 0% to 54%... and then it locks up at 54%.  I left it running overnight, and this morning it's still stuck at 54%.  The process in Task Manager is showing zero activity.

    What is wrong?  How do I fix this?


    • Edited by ShaulB Wednesday, April 3, 2013 6:56 AM
    Wednesday, April 3, 2013 6:42 AM

Answers

  • Note for those who come after me: I did find a solution.

    The solution was to ditch SSMA, and use SQLines.

    SSMA totally stinks as a product, and the cost of purchasing SQLines is totally worth the grief and frustration you'll save yourself.

    • Marked as answer by ShaulB Sunday, April 7, 2013 12:48 PM
    Sunday, April 7, 2013 12:48 PM

All replies

  • Maybe it waits a resource in Oracle? Connect to Oracle in other session, and execute:

    select sid, program, username, status, event
    from v$session 
    where username is not null;

    Find the SSMA connection. What value is set in event column?

    Dmitry

    --

    http://www.sqlines.com - Database migration tools for SQL Server


    Wednesday, April 3, 2013 8:42 AM
  • Hi Dmitry,

    Thanks for replying!

    I do not have an Oracle SQL client.  The DB is a 3rd party product that I am importing to SQL for our product that will be replacing the 3rd party product.  I have already wasted several hours trying to work out how to install an oracle client, and I am fast coming to the conclusion that the developers at Oracle were selected for their hatred of the human race.  Did they never learn about usability, user-friendliness, etc., that in order to get anything running you practically have to edit the source code in your OS kernel?  I worked out how to install the ODBC drivers, not from the Oracle web site (heaven forfend they should give any easy to follow instructions there), but from some programming blog.

    Unless you can refer me to an easy, step-by-step guide how to install an Oracle SQL client so I can run the query you suggest?

    Thanks!

    Wednesday, April 3, 2013 8:53 AM
  • Hey!  I worked out (more or less) how to use SQL*Plus!  After SSMS, it feels like going back to rubbing two sticks together to get fire.  But I did manage to work out how to run your query and output the results to a file, albeit horribly formatted, so it's very difficult to tell what data is from what column:

           SID PROGRAM                                                              
    ---------- ----------------------------------------------------------------     
    USERNAME                       STATUS                                           
    ------------------------------ --------                                         
    EVENT                                                                           
    ----------------------------------------------------------------                
    SQL*Net message from client                                                     
                                                                                    
           881 JDBC Thin Client                                                     
    PS                             INACTIVE                                         
    SQL*Net message from client                                                     
                                                                                    
           882 SSMAforOracle.exe  

    Are those two separate rows?  Which one is "Inactive"?  What does this mean?

    Wednesday, April 3, 2013 9:22 AM
  • Shaul,

    There are 2 rows. Looks like you did not post the full output for the second row. What are USERNAME, STATUS and EVENT output for the row starting with:

    882 SSMAforOracle.exe

    Dmitry

    http://www.sqlines.com - Database migration tools for SQL Server


    Wednesday, April 3, 2013 11:00 AM
  • No, I didn't leave anything out; the event was blank

    Just ran it again now, and it looks like it's "Active"; what do you think?

          SID PROGRAM                                                              
    ---------- ----------------------------------------------------------------     
    USERNAME                       STATUS                                           
    ------------------------------ --------                                         
    EVENT                                                                           
    ----------------------------------------------------------------                
           881 JDBC Thin Client                                                     
    PS                             INACTIVE                                         
    SQL*Net message from client                                                     
                                                                                    
           882 SSMAforOracle.exe                                                    
    PS                             ACTIVE                                           
    asynch descriptor resize     

    Wednesday, April 3, 2013 11:03 AM
  • Microsoft always provides great IDEs :) Look at Oracle SQL Developer tool, a Java-based tool for DBAs and developers, it is quite good. 

    Since you already connected to Oracle with SSMA (I assume it is Oracle Instant Client with minimal set of tools), you have enough to access Oracle using SQL Developer. 

    Dmitry

    http://www.sqlines.com - Database migration tools for SQL Server

    Wednesday, April 3, 2013 11:05 AM
  • "SQL*Net message from client" means that the application does not work with Oracle at this time, and Oracle waits a SQL request from the application (like an idle connection). 

    So if you have this value for SSMA connection, looks like something wrong inside the tool. Try to connect with a user having DBA role.

    Dmitry

    http://www.sqlines.com - Database migration tools for SQL Server

    Wednesday, April 3, 2013 11:12 AM
  • Doesn't it say, "asynch descriptor resize"?  Or am I reading it wrong?
    Wednesday, April 3, 2013 11:16 AM
  • Yes, and it can be a tricky issue. Can you just restart Oracle instance and SSMA, and try again? What happens? SSMA usually needs up to a few minutes to read the objects, no need to wait hours. 

    Dmitry

    Wednesday, April 3, 2013 12:10 PM
  • Yes, and it can be a tricky issue. Can you just restart Oracle instance and SSMA, and try again? What happens? SSMA usually needs up to a few minutes to read the objects, no need to wait hours. 

    Dmitry

    Yep, been there, done that.  Same result, more or less.  Just freezes at a different % complete.  Now it's stuck at 11%.

    I noticed in your signature sqlines.com.  Is that a different product, that does more or less the same thing as SSMA?  Perhaps... more reliably...?

    Wednesday, April 3, 2013 12:20 PM
  • Note for those who come after me: I did find a solution.

    The solution was to ditch SSMA, and use SQLines.

    SSMA totally stinks as a product, and the cost of purchasing SQLines is totally worth the grief and frustration you'll save yourself.

    • Marked as answer by ShaulB Sunday, April 7, 2013 12:48 PM
    Sunday, April 7, 2013 12:48 PM