none
SQL Server Migration Assistant for Oracle -- oracle connection issue -- ORA-00942 RRS feed

  • Question

  • Hi,

    i'm trying to migrate from Oracle 12.c to Sql Server 2017.

    It is failing to get metadata from the Oracle database.

    There are some other threads out there saying this is a permissions issue. I have all the correct permissions and the exception details are different to those threads.

    Any help is much appreciated.

    In the output window I get:

    [Mandatory: Generic] [9716/3] [2019-06-12 12:38:56]: SQL Server Migration Assistant for Oracle v8.2.0
    [Mandatory: Generic] [9716/3] [2019-06-12 12:38:56]: Starting assembly: SSMAforOracle, Version=8.2.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
    Created: 05/28/2019 22:02:48
    Last Modified: 05/28/2019 22:02:48
    OS Version: Microsoft Windows NT 6.2.9200.0
    OS architecture: x64
    Processor architecture: AMD64
    Current culture: it-IT
    UI culture: it-IT
    [Mandatory: Collector] [9716/7] [2019-06-12 12:39:33]: Oracle connection provider: oracleclient.
    [Mandatory: Collector] [9716/7] [2019-06-12 12:39:33]: Oracle connection mode: Standard.
    [Mandatory: Collector] [9716/7] [2019-06-12 12:39:35]: Oracle server version: '12.2.0.1.0'.
    [Error: Generic] [9716/7] [2019-06-12 12:41:29]: Failed to collect metabase data.
    [Error: Generic] [9716/7] [2019-06-12 12:41:29]: Exception: Error occurred while collecting data.
     site: Microsoft.SSMA.Framework.Generic.Collectors.XMetabase CreateMetabaseAndCollectData(System.Data.IDbConnection, System.String, Microsoft.SSMA.Framework.Generic.Collectors.XMetabase, Microsoft.SSMA.Framework.Generic.XTree.XNode, Microsoft.SSMA.Framework.Generic.UserInterface.IUIProgressBarProvider, Microsoft.SSMA.Framework.Generic.UserInterface.IUIStopOperationProvider, Microsoft.SSMA.Framework.Generic.Collectors.ICollectionContext)
     source: Microsoft.SSMA.Framework.Oracle.Generic
       in Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.CreateMetabaseAndCollectData(IDbConnection connection, String xmlConnectionParameters, XMetabase existingMetabase, XNode xFilter, IUIProgressBarProvider progressBarProvider, IUIStopOperationProvider stopOperationProvider, ICollectionContext context)
       in Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.CollectData(String connectionParameters, SecureString password, XMetabase existingMetabase, ICollectionContext context, IDbConnection implicitConnection, XNode filter)
       in Microsoft.SSMA.Framework.Generic.Collectors.Default.DefaultObjectCollector.CollectData(String connectionParameters, SecureString password, ICollectionContext context)
       in Microsoft.SSMA.Framework.Generic.SSMAWorkspace.MetabaseSsmaWorkspaceItem.Connect(String connectionParameters, SecureString password)
    [Error: Generic] [9716/7] [2019-06-12 12:41:29]: Inner Exception #1: ORA-00942: tabella o vista inesistente
     site: Int32 VerifyExecution(Int32 ByRef, Boolean, OracleInternal.Common.SqlStatementType, Int32, Oracle.ManagedDataAccess.Client.OracleException ByRef, Boolean ByRef, Boolean)
     source: Oracle Data Provider for .NET, Managed Driver
     error code: -2147467259
       in OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
       in OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
       in Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
       in Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
       in Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.Execute[T](ExecutionDelegate`1 executionMethod)
       in Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.ExecuteReader()
       in Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.LoadObjects(IDbConnection connection, String objectType, String sql, ILazyLoadProvider lazyLoadProvider, Dictionary`2 schemaID2XCategory, XNode xSchemas, HashSet`1 excludedObjects, IUIProgressBarProvider progressBarProvider, IUIStopOperationProvider stopOperationProvider)
       in Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.CollectDataIntoMetabase(XMetabase xMetabase, IDbConnection connection, OracleCollectionQueryAdapter queryAdapter, XMetabase existingMetabase, IUIProgressBarProvider progressBarProvider, IUIStopOperationProvider stopOperationProvider, ICollectionContext context)
       in Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.CreateMetabaseAndCollectData(IDbConnection connection, String xmlConnectionParameters, XMetabase existingMetabase, XNode xFilter, IUIProgressBarProvider progressBarProvider, IUIStopOperationProvider stopOperationProvider, ICollectionContext context)
    [Error: Gui] [9716/7] [2019-06-12 12:41:30]: LowLevelAdapter.ReadSource : Read Source error: Microsoft.SSMA.Framework.Generic.Collectors.CollectorException: Error occurred while collecting data. ---> Oracle.ManagedDataAccess.Client.OracleException: ORA-00942: tabella o vista inesistente
       in OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
       in OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
       in Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
       in Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
       in Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.Execute[T](ExecutionDelegate`1 executionMethod)
       in Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbCommand.ExecuteReader()
       in Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.LoadObjects(IDbConnection connection, String objectType, String sql, ILazyLoadProvider lazyLoadProvider, Dictionary`2 schemaID2XCategory, XNode xSchemas, HashSet`1 excludedObjects, IUIProgressBarProvider progressBarProvider, IUIStopOperationProvider stopOperationProvider)
       in Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.CollectDataIntoMetabase(XMetabase xMetabase, IDbConnection connection, OracleCollectionQueryAdapter queryAdapter, XMetabase existingMetabase, IUIProgressBarProvider progressBarProvider, IUIStopOperationProvider stopOperationProvider, ICollectionContext context)
       in Microsoft.SSMA.Framework.Oracle.Collector.OracleObjectCollector.CreateMetabaseAndCollectData(IDbConnection connection, String xmlConnectionParameters, XMetabase existingMetabase, XNode xFilter, IUIProgressBarProvider progressBarProvider, IUIStopOperationProvider stopOperationProvider, ICollectionContext context)
       --- Fine della traccia dello stack dell'eccezione interna ---

    Wednesday, June 12, 2019 2:40 PM

All replies

  • Hi,

    Error translates to something similar to "Table or view does not exists".

    Can you please go to Tools -> Global settings -> Logging and switch all logs to Debug? Then connect to Oracle again and check the log file. It should have all queries that were executed, so you can see which one failed. That should help us understand which table/view is causing it to fail and we can act accordingly - request you to get extra permissions and/or revisit our query to see, if we can avoid querying that table/view.

    Thanks,
    Alex.

    Monday, June 17, 2019 4:56 PM