Managing and Compare two tables data
------------------------------------
Toad -> Compare Data (Data Source(LE)/Data Source(LE2-use DB link) -> All Differences


This can be useful if 2 similar tables need to be compared across different schemas (or different databases, in which case a local view will need to be created using database link), or to compare a table with itself at different points in time, for example, before and after an update through Applications to track how a table is affected. The script indicates whether a record is present in one table but not in the other, or if the same key is present in both tables but non-key columns have different values.
Example tables and data to test

CREATE TABLE A (
    C1    NUMBER NOT NULL,
    C2    VARCHAR2(2000),
    C3    CHAR(255)
    );
   
CREATE UNIQUE INDEX A_INDEX ON A (C1);

CREATE TABLE B (
    C1    NUMBER NOT NULL,
    C2    VARCHAR2(2000),
    C3    CHAR(255)
    );
   
CREATE UNIQUE INDEX B_INDEX ON B (C1);

INSERT INTO A(C1, C2, C3) VALUES(1, 'A_ROW_001', 'TRUE');
INSERT INTO A(C1, C2, C3) VALUES(2, 'A_ROW_002', 'FALSE');
INSERT INTO A(C1, C2, C3) VALUES(3, 'A_ROW_003', 'TRUE');
INSERT INTO A(C1, C2, C3) VALUES(4, 'A_ROW_004', 'FALSE');

INSERT INTO B(C1, C2, C3) VALUES(1, 'B_ROW_001', 'TRUE');
INSERT INTO B(C1, C2, C3) VALUES(2, 'A_ROW_002', 'FALSE');
INSERT INTO B(C1, C2, C3) VALUES(3, 'B_ROW_003', 'TRUE');
INSERT INTO B(C1, C2, C3) VALUES(4, 'A_ROW_004', 'FALSE');

COMMIT;

(SELECT 'TAB1',C1,C2,C3
FROM SYS.A
MINUS SELECT 'TAB1',C1,C2,C3
FROM SYS.B)
UNION(
SELECT 'TAB2',C1,C2,C3
FROM SYS.B MINUS
SELECT 'TAB2',C1,C2,C3
FROM SYS.A)
ORDER BY C1;




SELECT id,title
FROM (
SELECT id, title FROM t1
UNION ALL
SELECT id,title FROM t2
) tbl
GROUP BY id, title
HAVING count(*) = 1
ORDER BY id;



select
       ADDRESS_TITLE, ADD_LINE1, ADD_LINE2, ADD_LINE3, PHONE_NO
       , MOBILE_NO, FAX_NO, E_MAIL_ADDRESS, CONTACT_PERSON, CITY_ID
       , STATE_ID, COUNTRY_ID, PARTY_CODE, ZONE_ID, DEFAULT_ADD
       , CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE
from   KMLG.PARTY_ADDRESS_LINES Tbl1
minus
select
       ADDRESS_TITLE, ADD_LINE1, ADD_LINE2, ADD_LINE3, PHONE_NO
       , MOBILE_NO, FAX_NO, E_MAIL_ADDRESS, CONTACT_PERSON, CITY_ID
       , STATE_ID, COUNTRY_ID, PARTY_CODE, ZONE_ID, DEFAULT_ADD
       , CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE
from   KMLG.PARTY_ADDRESS_LINES@"LINKDB_LE_LE2" Tbl2





Example
--------
--Find the difference in two tables:

select * from kmlg.CITY_MASTER
minus
select * from kmlg.CITY_MASTER@LINKDB_LE_LE2



insert into kmlg.CITY_MASTER@LINKDB_LE_LE2(col1,col2..)
select * from kmlg.CITY_MASTER
minus
select * from kmlg.CITY_MASTER@LINKDB_LE_LE2


--Truncate/delete test table

truncate table kmlg.CITY_MASTER@LINKDB_LE_LE2


--if child records exists then run the following:

--Very very important
--Find all child tables and enable|disable constraint:

SELECT a.owner CHILD_OWNER,
  a.constraint_name CHILD_CONSTRAINT,
  c.COLUMN_NAME CHILD_COLUMN,
  a.table_name CHILD_TABLE,
  a.r_owner PARENT_OWNER,
  a.r_constraint_name PARENT_REF_NAME,
  d.column_name PARENT_COLUMN,
  b.table_name AS PARENT_TABLE,
  a.delete_rule,
  'ALTER TABLE '||a.owner||'.'||a.table_name||' DISABLE CONSTRAINT '||a.constraint_name||';',
  'ALTER TABLE '||a.owner||'.'||a.table_name||' DISABLE CONSTRAINT '||a.constraint_name||';'
FROM dba_constraints a,
  dba_constraints b,
  dba_cons_columns c,
  dba_cons_columns d
WHERE
a.constraint_type='R'
AND a.r_constraint_name=b.constraint_name
AND a.constraint_name  =c.constraint_name
AND b.constraint_name  =d.constraint_name
and a.owner='KMLG'
and b.table_name='CITY_MASTER'
ORDER BY PARENT_TABLE;



--Run the DISABLE CONSTRAINT query, again truncate table then run insert again.





Managing and Determine the differences between two tables
------------------------------------------------------------
create table test (
  id number primary key,
  Mname varchar2(50),
  NName varchar2(50)
)

--Suppose this is the table containing new data for Insert or Updates,
--even duplicates that will be discarded
create table test_new (
  id number primary key,
  Mname varchar2(50),
  NName varchar2(50)
)

begin
insert into test values(1, 'pippo', 'pluto');
insert into test values(2, 'cacco', 'gnacco');
insert into test_new values(3, 'pappo', 'babbo');
insert into test_new values(2, 'caccone', 'gnacco');
insert into test_new values(1, 'pippo', 'pluto');
end;

commit;

--this is the data that need to be Insert or Updates
--It will show Non-Duplicate data
select * from (
  select p.*,
  k.mname old_mname, k.nname old_nname,
  CASE WHEN k.id IS NOT NULL THEN 'U' ELSE 'I' END AS delta_type
  from test_new p
  left outer join test k on
    --join condition based on pk
    k.id = p.id
) where delta_type = 'I'
--no pk columns where to detect changes
--any already present row will be discarded 
or old_nname != nname
or old_mname != mname





Find m to n rows from table
----------------------------
If you want to find a specific range or rows from a table, lets say all records from 35 to 65 from table YOUR_QUERY

CREATE TABLE YOUR_QUERY (ID NUMBER(3), col1 VARCHAR2(30));

BEGIN
    FOR i IN 1..100 LOOP
      INSERT INTO YOUR_QUERY VALUES (i, 'ROW' || i);
    END LOOP;
    COMMIT;
  END;

execute the following:

SELECT *
  FROM (SELECT A.*, ROWNUM rnum
          FROM (SELECT * FROM your_query) A
         WHERE ROWNUM <= 65 )
 WHERE rnum >= 35;






Remove bad 'child' records to enable or create a foreign key
-------------------------------------------------------------
If you get "Parent Keys Not Found" error when you try to enable/create relation from a child table to parent table, then you must identify and remove bad 'child' records.
The records can be identified and removed using the query:

        DELETE FROM childtablename ct
        WHERE NOT EXISTS (SELECT 'x' FROM parenttablename pt
        WHERE ct.keycolumn1 = pt.keycolumn1 AND ct.keycolumn2 = pt.keycolumn2...);




Finding Child constraints of table
----------------------------------
select 'alter table '||a.owner||'.'||a.table_name||
            ' disable constraint '||a.constraint_name||';'
            from all_constraints a, all_constraints b
            where a.constraint_type = 'R'
            and a.r_constraint_name = b.constraint_name
            and a.r_owner  = b.owner
            and b.table_name = 'NE_TRANSACTIONS';

References:
http://www.dba-oracle.com/t_convert_set_to_join_sql_parameter.htm