locked
SSMA for Oracle 6.0 Cannot connect to Oracle DB reporting "table or view does not exist" RRS feed

  • Question

  • SSMA For Oracle v6.0.0
    MS SQL Server Enterprise 64bits v11.0.3000.0
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

    Symptom:

    when connecting Oracle, it reports error "Common Requirement: User does not have required permissions". Then it reports error: "Error occurred while collecting data."

    The following is log:

    [Generic: Mandatory] [1500/1] [2015-03-03 04:19:29]: SQL Server Migration Assistant for Oracle v6.0.0
    [Generic: Mandatory] [1500/1] [2015-03-03 04:19:29]: Starting assembly: SSMAforOracle, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
    Created: 7/22/2014 4:42:22 PM
    Last Modified: 7/22/2014 4:42:22 PM
    OS Version: Microsoft Windows NT 6.1.7601 Service Pack 1
    OS architecture: x64
    Processor architecture: AMD64
    Current culture: en-US
    UI culture: en-US
    [Collector: Mandatory] [1500/4] [2015-03-03 04:20:04]: Oracle connection provider: OracleClient.
    [Collector: Mandatory] [1500/4] [2015-03-03 04:20:04]: Oracle connection mode: Standard.
    [Collector: Mandatory] [1500/4] [2015-03-03 04:20:06]: Oracle server version: '11.2.0.3.0'.
    [Gui: Error] [1500/4] [2015-03-03 04:20:12]: 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.ExecuteScalarInternal(Boolean needCLStype, Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
       at System.Data.OracleClient.OracleCommand.ExecuteScalar()
       at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.ExecuteScalar()
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.ExecuteScalarToInt(IDbConnection connection, String text)
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.GetCountForExcludedSchemas(IDbConnection connection, IEnumerable`1 excludedSchemas, Int64 version)
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.CountObjects(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] [1500/4] [2015-03-03 04:20:12]: 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] [1500/4] [2015-03-03 04:20:12]: 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.ExecuteScalarInternal(Boolean needCLStype, Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
       at System.Data.OracleClient.OracleCommand.ExecuteScalar()
       at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.ExecuteScalar()
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.ExecuteScalarToInt(IDbConnection connection, String text)
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.GetCountForExcludedSchemas(IDbConnection connection, IEnumerable`1 excludedSchemas, Int64 version)
       at Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.CountObjects(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)


    Tuesday, March 3, 2015 10:00 AM

Answers

  • Hi Airings,

    'ORA-00942: table or view does not exist'

    According to the error message, it seems that the migrating table or view does not exist in the database, or SSMA does not have access to it. To troubleshoot the issue, please check the following things.

     1. Verify that if the spelling of the table or view name is correct.

     2. If the table or view exists but is in a different schema from the current schema where the SQL is executing (in other word, the table doesn’t own by you, but owned by other user), the ORA-00942 error will return too. Resolve this by explicitly reference the table or view by specifying the schema name (schema_name.table_name).

    3. SSMA queries some additional catalog tables that you may not have permission to, please make sure that you grant the account permission to  read sys.mlog$. For more details, please review this similar thread: Bug in SSMA For Oracle 6.0 for non-dba Oracle user.

    Reference:
    ORA-00942 Table or View Does Not Exist Oracle Error


    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Thursday, March 5, 2015 6:07 AM

All replies

  • when connecting Oracle, it reports error "Common Requirement: User does not have required permissions".

    Hello,

    See Connecting to Oracle Database (OracleToSQL) => Required Oracle Permissions; do you have all these permissions?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, March 3, 2015 10:07 AM
  • I think the user have all the permissions. The connecting user I used owns the schema and there exists tables under this user/schema.

    Checked this thread as well: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b68c4da6-a8dd-4566-a53f-d36b35bb77fe/ssma-for-oracle-v52?forum=sqlservermigration

    I can run the following sql when directly connecting to Oracle:

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

    Tuesday, March 3, 2015 10:46 AM
  • Hi Airings,

    'ORA-00942: table or view does not exist'

    According to the error message, it seems that the migrating table or view does not exist in the database, or SSMA does not have access to it. To troubleshoot the issue, please check the following things.

     1. Verify that if the spelling of the table or view name is correct.

     2. If the table or view exists but is in a different schema from the current schema where the SQL is executing (in other word, the table doesn’t own by you, but owned by other user), the ORA-00942 error will return too. Resolve this by explicitly reference the table or view by specifying the schema name (schema_name.table_name).

    3. SSMA queries some additional catalog tables that you may not have permission to, please make sure that you grant the account permission to  read sys.mlog$. For more details, please review this similar thread: Bug in SSMA For Oracle 6.0 for non-dba Oracle user.

    Reference:
    ORA-00942 Table or View Does Not Exist Oracle Error


    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Thursday, March 5, 2015 6:07 AM
  • Hello,

    This is an old thread but I got around the ORA-942 by using a different Oracle Provider, try OraOLEDB.Oracle

    Regards,

    Frank

    Wednesday, March 22, 2017 7:34 PM