locked
SSMA For Oracle v5.2 RRS feed

  • Question

  • Hi,

    My configuration:-

    SSMA For Oracle v5.2.1259
    MS SQL Server Enterprise 64bits v11.0.3000.0
    Windows Server 2012 Standard 64bits
    Oracle9i Enterprise Edition Release 9.2.0.6.0

    When i connect to Oracle DB, i got this error:-

    Connection to Oracle established successfully.
    Connection string: Data Source="TCMSPROD";Unicode="True";User ID="MMA_DATA";
    Error occurred while collecting data. 

    Below is the SSMA.LOG content:-

    [Generic: Mandatory] [6140/1] [2014-01-08 15:11:26]: SQL Server Migration Assistant for Oracle v5.2.1259
    [Collector: Mandatory] [6140/4] [2014-01-08 15:11:44]: Oracle connection provider: OracleClient.
    [Collector: Mandatory] [6140/4] [2014-01-08 15:11:44]: Oracle connection mode: ServiceName.
    [Collector: Mandatory] [6140/4] [2014-01-08 15:11:44]: Oracle server version: '9.2.0.6.0'.
    [Gui: Error] [6140/4] [2014-01-08 15:12:57]: LowLevelAdapter.ReadSource : Read Source error: Microsoft.SSMA.Framework.Generic.Collectors.CollectorException: Error occurred while collecting data. ---> System.Data.OracleClient.OracleException: ORA-00942: table or view does not exist

       at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
       at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
       at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
       at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
       at Microsoft.SSMA.Framework.Generic.Utilities.DbUtilities.DbCommandWrapper.ExecuteReader()
       at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.Execute[T](ExecutionDelegate`1 executionMethod)
       at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.ExecuteReader()
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.LoadSchemas(XNode xSchemas, IDbConnection connection, Int64 serverVersion, OracleCollectionFilter filter, Set`1 excludedSchemas)
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.CollectDataIntoMetabase(XMetabase xMetabase, IDbConnection connection, Int64 serverVersion, OracleCollectionFilter filter, IUIProgressBarProvider progressBarProvider, IUIStopOperationProvider stopOperationProvider, ICollectionContext context)
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.CreateMetabaseAndCollectData(IDbConnection connection, String xmlConnectionParameters, XNode xFilter, IUIProgressBarProvider progressBarProvider, IUIStopOperationProvider stopOperationProvider, ICollectionContext context)
       --- End of inner exception stack trace ---
       at Microsoft.SSMA.Framework.Generic.Workspaces.Default.MetabaseWorkspaceItem.Connect(String connectionParameters, SecureString password)
       at Microsoft.SSMA.GUI.Product.OracleToMsSql.LowLevel.AppConnectToSourceOracle.ConnectToSourceStart(Object guiContext)
    [Gui: Error] [6140/4] [2014-01-08 15:12:57]: Exception: Error occurred while collecting data.
     site: Void Connect(System.String, System.Security.SecureString)
     source: Microsoft.SSMA.Framework.Generic.SSMAEnvironment
       at Microsoft.SSMA.Framework.Generic.Workspaces.Default.MetabaseWorkspaceItem.Connect(String connectionParameters, SecureString password)
       at Microsoft.SSMA.GUI.Product.OracleToMsSql.LowLevel.AppConnectToSourceOracle.ConnectToSourceStart(Object guiContext)
    [Gui: Error] [6140/4] [2014-01-08 15:12:57]: Inner Exception #1: ORA-00942: table or view does not exist

     site: Void CheckError(System.Data.OracleClient.OciErrorHandle, Int32)
     source: System.Data.OracleClient
     error code: -2146232008
       at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
       at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
       at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
       at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
       at Microsoft.SSMA.Framework.Generic.Utilities.DbUtilities.DbCommandWrapper.ExecuteReader()
       at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.Execute[T](ExecutionDelegate`1 executionMethod)
       at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.ExecuteReader()
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.LoadSchemas(XNode xSchemas, IDbConnection connection, Int64 serverVersion, OracleCollectionFilter filter, Set`1 excludedSchemas)
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.CollectDataIntoMetabase(XMetabase xMetabase, IDbConnection connection, Int64 serverVersion, OracleCollectionFilter filter, IUIProgressBarProvider progressBarProvider, IUIStopOperationProvider stopOperationProvider, ICollectionContext context)
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.CreateMetabaseAndCollectData(IDbConnection connection, String xmlConnectionParameters, XNode xFilter, IUIProgressBarProvider progressBarProvider, IUIStopOperationProvider stopOperationProvider, ICollectionContext context)

    Thanks for helping.

    Wednesday, January 8, 2014 7:51 AM

Answers

  • David,

    Based on the SQL given, NO, not successful.

    TQ

    Then you have a permissions problem.  That is the query SSMA sends in the method

    Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.LoadSchemas

    which is failing.  Either get an Oracle DBA to help you with the permissions so that that query succeeds, or use EXPDP to move a copy of the target schema to a server you control.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by ITMan2000my Thursday, January 16, 2014 3:05 AM
    Wednesday, January 15, 2014 1:25 PM

All replies

  • Hello,

    "ORA-00942: table or view does not exist."

    Based on the error message, it seems that tried table or view does not exist in the database, or SSMS does not have access to it.

    If the issue is not caused by premission, please verify that the table or view name exists and the spelling of the table or view name is correct.

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Thursday, January 9, 2014 1:21 PM
  • This is during

    Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.LoadSchemas()

    So it's likely to be a permissions issue reading the Oracle catalog.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, January 9, 2014 3:52 PM

  • List of privileges required for SSMA:

    • CREATE ANY PROCEDURE
    • EXECUTE ANY PROCEDURE
    • SELECT ANY TABLE
    • SELECT ANY SEQUENCE
    • CREATE ANY TYPE
    • CREATE ANY TRIGGER

    Its a permission issue. Try to grant above mentioned privilege to oracle id.

    -Prashanth

    Thursday, January 9, 2014 4:02 PM
  • Base on SSMA pre-requisite which was prompted by SSMA at the start of the program start-up, the oracle user requires the below permissions:-

    • CREATE ANY PROCEDURE
    • SELECT ANY SEQUENCE
    • CREATE ANY TYPE
    • CREATE ANY TRIGGER

    The above was enabled earlier on. This is NEW, EXECUTE ANY PROCEDURE is not listed in the SSMA at program start up.

    HOWEVER, using TOAD to access the same DB using the same user id, TOAD is able to view and extract table information from the schema.

    Hope my explanation is helpful.

    I will get the DBA to enable EXECUTE ANY PROCEDURE to see this will solve the problem.

    Thanks.

    Johnny

    Friday, January 10, 2014 3:17 AM
  • Dear All,

    As advice by all, I'm still getting the SAME error as per the log below:-

    [Generic: Mandatory] [3244/1] [2014-01-13 11:22:19]: SQL Server Migration Assistant for Oracle v5.2.1259
    [Collector: Mandatory] [3244/4] [2014-01-13 11:24:16]: Oracle connection provider: OracleClient.
    [Collector: Mandatory] [3244/4] [2014-01-13 11:24:16]: Oracle connection mode: ServiceName.
    [Collector: Mandatory] [3244/4] [2014-01-13 11:24:21]: Oracle server version: '9.2.0.6.0'.
    [Gui: Error] [3244/4] [2014-01-13 11:24:31]: LowLevelAdapter.ReadSource : Read Source error: Microsoft.SSMA.Framework.Generic.Collectors.CollectorException: Error occurred while collecting data. ---> System.Data.OracleClient.OracleException: ORA-00942: table or view does not exist

       at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
       at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
       at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
       at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
       at Microsoft.SSMA.Framework.Generic.Utilities.DbUtilities.DbCommandWrapper.ExecuteReader()
       at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.Execute[T](ExecutionDelegate`1 executionMethod)
       at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.ExecuteReader()
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.LoadSchemas(XNode xSchemas, IDbConnection connection, Int64 serverVersion, OracleCollectionFilter filter, Set`1 excludedSchemas)
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.CollectDataIntoMetabase(XMetabase xMetabase, IDbConnection connection, Int64 serverVersion, OracleCollectionFilter filter, IUIProgressBarProvider progressBarProvider, IUIStopOperationProvider stopOperationProvider, ICollectionContext context)
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.CreateMetabaseAndCollectData(IDbConnection connection, String xmlConnectionParameters, XNode xFilter, IUIProgressBarProvider progressBarProvider, IUIStopOperationProvider stopOperationProvider, ICollectionContext context)
       --- End of inner exception stack trace ---
       at Microsoft.SSMA.Framework.Generic.Workspaces.Default.MetabaseWorkspaceItem.Connect(String connectionParameters, SecureString password)
       at Microsoft.SSMA.GUI.Product.OracleToMsSql.LowLevel.AppConnectToSourceOracle.ConnectToSourceStart(Object guiContext)
    [Gui: Error] [3244/4] [2014-01-13 11:24:31]: Exception: Error occurred while collecting data.
     site: Void Connect(System.String, System.Security.SecureString)
     source: Microsoft.SSMA.Framework.Generic.SSMAEnvironment
       at Microsoft.SSMA.Framework.Generic.Workspaces.Default.MetabaseWorkspaceItem.Connect(String connectionParameters, SecureString password)
       at Microsoft.SSMA.GUI.Product.OracleToMsSql.LowLevel.AppConnectToSourceOracle.ConnectToSourceStart(Object guiContext)
    [Gui: Error] [3244/4] [2014-01-13 11:24:31]: Inner Exception #1: ORA-00942: table or view does not exist

     site: Void CheckError(System.Data.OracleClient.OciErrorHandle, Int32)
     source: System.Data.OracleClient
     error code: -2146232008
       at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
       at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
       at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
       at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
       at Microsoft.SSMA.Framework.Generic.Utilities.DbUtilities.DbCommandWrapper.ExecuteReader()
       at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.Execute[T](ExecutionDelegate`1 executionMethod)
       at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.ExecuteReader()
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.LoadSchemas(XNode xSchemas, IDbConnection connection, Int64 serverVersion, OracleCollectionFilter filter, Set`1 excludedSchemas)
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.CollectDataIntoMetabase(XMetabase xMetabase, IDbConnection connection, Int64 serverVersion, OracleCollectionFilter filter, IUIProgressBarProvider progressBarProvider, IUIStopOperationProvider stopOperationProvider, ICollectionContext context)
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.CreateMetabaseAndCollectData(IDbConnection connection, String xmlConnectionParameters, XNode xFilter, IUIProgressBarProvider progressBarProvider, IUIStopOperationProvider stopOperationProvider, ICollectionContext context)

    TQ

    Monday, January 13, 2014 3:26 AM
  • Can you connect directly to Oracle using that user and run:

     SELECT username, user_id, created FROM sys.all_users ORDER BY 1

    Sucessfully?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, January 13, 2014 1:13 PM
  • Please verify the rights assigned to the user see:

    http://support.microsoft.com/kb/2020711

    Monday, January 13, 2014 2:55 PM
  • David,

    Based on the SQL given, NO, not successful.

    TQ

    Wednesday, January 15, 2014 12:42 AM
  • Tom,

    All required privileges are enabled.

    TQ

    Wednesday, January 15, 2014 12:47 AM
  • David,

    Based on the SQL given, NO, not successful.

    TQ

    Then you have a permissions problem.  That is the query SSMA sends in the method

    Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.LoadSchemas

    which is failing.  Either get an Oracle DBA to help you with the permissions so that that query succeeds, or use EXPDP to move a copy of the target schema to a server you control.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by ITMan2000my Thursday, January 16, 2014 3:05 AM
    Wednesday, January 15, 2014 1:25 PM
  • David,

    Thanks for the reply.

    If I read yr statement correctly, I hv to get the DBA to allow my ID to be able to select sys.all_users? Once the permission is given, SSMA should be able to read the Oracle DB?

    TQ

    Johnny

    Thursday, January 16, 2014 12:44 AM
  • David,

    Got the DBA to grant access to sys.all_users.

    SSMA can view the schemas now.

    Thanks for the help.

    Johnny

    Thursday, January 16, 2014 3:05 AM