0

I migrated database from oracle to Sql server using sqlmigration(SSMA) tool. After migration, report say 100% successfully migrated but the row counts are more than oracle in Sql Server. I cant figure out why its showing more row counts than oracle. Please Help

king87501
  • 13
  • 2
  • How are you cross checking row counts in the source (oracle) and target (sql server) table(s)? Can you share row count SQLs? is this mismatch in one table or many? – pahariayogi Oct 19 '16 at 18:19
  • I am not using any query to match the row counts. I am using properties. its more than one tables. In oracle i am using Pl /sql developer. I right click on table and properties shows Num_Rows in oracle . In sql server when i right click on table properties---> Storage --- Row count. When i match num_rows and row counts they are not matching. Sql Server showing more row counts than oracle num_rows. – king87501 Oct 19 '16 at 18:29
  • Rownum stats may be stale. perform like -> -- Source select count(1) from table_source; -- Target select count(1) from table_target; – pahariayogi Oct 19 '16 at 18:32
  • But this is static database. I mean no insert or any update. This is only one server we have in oracle and we are trying to migrate in sql server – king87501 Oct 19 '16 at 18:37
  • Thanks! when i use select count(1) from table_souce. its giving me correct row counts. – king87501 Oct 19 '16 at 18:46
  • Does it hurt to run SQLs and verify the count b/w source and target? As I said, the way you are checking is not the right approach. The rownum information does not get refreshed after a large DML operation. I am typically an Oracle DBA and have not worked in SQL server recently but the concept is same. You need to refresh it to get real time rownum or other optimiser statistics. – pahariayogi Oct 19 '16 at 18:50
  • Good! Hope to see the question marked 'answered' ... – pahariayogi Oct 19 '16 at 18:53

2 Answers2

0

You can check row counts of all the tables in sql server using below query

SELECT
    t.name, i.rows
FROM sys.tables t
INNER JOIN sysindexes i
    ON t.object_id = i.id
WHERE i.indid < 2

Similarly for oracle you can use this query

Get counts of all tables in a schema

and you can compare

Community
  • 1
  • 1
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38
-1

perform row count like ->

-- Source 

select count(1) from table_source;

-- Target 

select count(1) from table_target;
pahariayogi
  • 1,073
  • 1
  • 7
  • 18