On Wednesday, August 28, 2013 9:15:32 AM UTC-6, Akshay T wrote:
>
> Suppose we've old_table in MS/SQL Server which is getting migrated to
> new_table in Oracle.To ensure the consistency & integrity of data we shall
> carry out following data checks :-
>
> 1) Check count of every column both at old & new migrated table to ensure
> all records have been migrated.
> 2) Check sum of lengths of all columns which are being migrated from
> source to target.
>
> But, let us assume that the column_1 in old table has value "Akshay"
> migrated to column_2 in new table as "Skshay".Accordingly the results for
> both the steps would confirm that the data has been migrated properly.
>
> If there is no DB link provided between MS SQL Server &
> Oracle(therefore Minus query also won't work) then how to ensure accurate
> data migration ?
>
>
> *Thanks & Regards,*
> Akshay S. Tanksale
>
Let's presume the release of Oracle is 10.2 or later (since you failed to
report which release you are using) -- you would dump the table data
from SQL Server into a text file and use that as the source for an external
table in Oracle. Then, in Oracle you would do a MINUS query. Here is an
example using MERGE:
SQL> -- create source table
SQL>
SQL> CREATE TABLE admin_employees
2 (test_id NUMBER(4),
3 test_name VARCHAR2(25)
4 );
Table created.
SQL>
SQL> insert into admin_employees
2 values (20, '"Test 3"');
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> -- create the external table
SQL> CREATE TABLE admin_ext_employees
2 (test_id NUMBER(4),
3 test_name VARCHAR2(25)
4 )
5 ORGANIZATION EXTERNAL
6 (
7 TYPE ORACLE_LOADER
8 DEFAULT DIRECTORY admin_dat_dir
9 ACCESS PARAMETERS
10 (
11 records delimited by newline
12 badfile admin_bad_dir:'loader_tst%a_%p.bad'
13 logfile admin_log_dir:'loader_tst%a_%p.log'
14 fields terminated by ','
15 missing field values are null
16 ( test_id, test_name
17 )
18 )
19 LOCATION ('loader_tst1.dat', 'loader_tst2.dat')
20 -- LOCATION ('loader_tst1.dat')
21 )
22 PARALLEL
23 REJECT LIMIT UNLIMITED;
Table created.
SQL>
SQL> select * from admin_ext_employees;
TEST_ID TEST_NAME
---------- -------------------------
1 "Test 1"
2 "Test 1"
3 "Test 1"
4 "Test 1"
5 "Test 1"
6 "Test 1"
7 "Test 1"
8 "Test 1"
9 "Test 1"
10 "Test 1"
11 "Test 1"
12 "Test 1"
13 "Test 1"
TEST_ID TEST_NAME
---------- -------------------------
14 "Test 1"
15 "Test 1"
16 "Test 1"
17 "Test 1"
18 "Test 1"
19 "Test 1"
21 "Test 2"
22 "Test 2"
23 "Test 2"
24 "Test 2"
25 "Test 2"
26 "Test 2"
27 "Test 2"
TEST_ID TEST_NAME
---------- -------------------------
28 "Test 2"
29 "Test 2"
30 "Test 2"
31 "Test 2"
32 "Test 2"
33 "Test 2"
34 "Test 2"
35 "Test 2"
36 "Test 2"
37 "Test 2"
38 "Test 2"
39 "Test 2"
38 rows selected.
SQL>
SQL> select * from admin_employees;
TEST_ID TEST_NAME
---------- -------------------------
20 "Test 3"
SQL>
SQL> set autotrace on linesize 150
SQL>
SQL> merge into admin_employees a
2 using admin_ext_employees e on (e.test_id = a.test_id)
3 when matched then
4 update
5 set test_name = a.test_name
6 when not matched then
7 insert
8 (test_id, test_name)
9 values (e.test_id, e.test_name);
38 rows merged.
Execution Plan
----------------------------------------------------------
Plan hash value: 1420256803
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 8168 |
542K| 33 (4)| 00:00:01 |
| 1 | MERGE | ADMIN_EMPLOYEES | |
| | |
| 2 | VIEW | | |
| | |
|* 3 | HASH JOIN RIGHT OUTER | | 8168 |
526K| 33 (4)| 00:00:01 |
| 4 | TABLE ACCESS FULL | ADMIN_EMPLOYEES | 39 | 1521
| 3 (0)| 00:00:01 |
| 5 | EXTERNAL TABLE ACCESS FULL| ADMIN_EXT_EMPLOYEES | 8168 |
215K| 29 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."TEST_ID"="A"."TEST_ID"(+))
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel
threshold
Statistics
----------------------------------------------------------
87 recursive calls
5 db block gets
761 consistent gets
0 physical reads
0 redo size
833 bytes sent via SQL*Net to client
977 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
38 rows processed
SQL>
SQL> select * from admin_employees
2 order by 1;
TEST_ID TEST_NAME
---------- -------------------------
1 "Test 1"
2 "Test 1"
3 "Test 1"
4 "Test 1"
5 "Test 1"
6 "Test 1"
7 "Test 1"
8 "Test 1"
9 "Test 1"
10 "Test 1"
11 "Test 1"
12 "Test 1"
13 "Test 1"
TEST_ID TEST_NAME
---------- -------------------------
14 "Test 1"
15 "Test 1"
16 "Test 1"
17 "Test 1"
18 "Test 1"
19 "Test 1"
20 "Test 3"
21 "Test 2"
22 "Test 2"
23 "Test 2"
24 "Test 2"
25 "Test 2"
26 "Test 2"
TEST_ID TEST_NAME
---------- -------------------------
27 "Test 2"
28 "Test 2"
29 "Test 2"
30 "Test 2"
31 "Test 2"
32 "Test 2"
33 "Test 2"
34 "Test 2"
35 "Test 2"
36 "Test 2"
37 "Test 2"
38 "Test 2"
39 "Test 2"
39 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2437798676
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 1053 | 4 (25)|
00:00:01 |
| 1 | SORT ORDER BY | | 39 | 1053 | 4 (25)|
00:00:01 |
| 2 | TABLE ACCESS FULL| ADMIN_EMPLOYEES | 39 | 1053 | 3 (0)|
00:00:01 |
--------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel
threshold
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
1348 bytes sent via SQL*Net to client
542 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
39 rows processed
SQL>
You can use MINUS as well since the external table is considered a table by
Oracle.
David Fitzjarrell
--
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
---
You received this message because you are subscribed to the Google Groups
"Oracle PL/SQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to oracle-plsql+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
