133 lines
5.4 KiB
SQL
133 lines
5.4 KiB
SQL
-- -----------------------------------------------------------------------------------
|
|
-- File Name : https://oracle-base.com/dba/miscellaneous/table_differences.sql
|
|
-- Author : Tim Hall
|
|
-- Description : Checks column differences between a specified table or ALL tables.
|
|
-- : The comparison is done both ways so datatype/size mismatches will
|
|
-- : be listed twice per column.
|
|
-- : Log into the first schema-owner. Make sure a DB Link is set up to
|
|
-- : the second schema owner. Use this DB Link in the definition of
|
|
-- : the c_table2 cursor and amend v_owner1 and v_owner2 accordingly
|
|
-- : to make output messages sensible.
|
|
-- : The result is spooled to the Tab_Diffs.txt file in the working directory.
|
|
-- Call Syntax : @table_differences (table-name or all)
|
|
-- Last Modified: 15/07/2000
|
|
-- -----------------------------------------------------------------------------------
|
|
SET SERVEROUTPUT ON
|
|
SET LINESIZE 500
|
|
SET VERIFY OFF
|
|
SET FEEDBACK OFF
|
|
PROMPT
|
|
|
|
SPOOL Tab_Diffs.txt
|
|
|
|
DECLARE
|
|
|
|
CURSOR c_tables IS
|
|
SELECT a.table_name
|
|
FROM user_tables a
|
|
WHERE a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'));
|
|
|
|
CURSOR c_table1 (p_table_name IN VARCHAR2,
|
|
p_column_name IN VARCHAR2) IS
|
|
SELECT a.column_name,
|
|
a.data_type,
|
|
a.data_length,
|
|
a.data_precision,
|
|
a.data_scale,
|
|
a.nullable
|
|
FROM user_tab_columns a
|
|
WHERE a.table_name = p_table_name
|
|
AND a.column_name = NVL(p_column_name,a.column_name);
|
|
|
|
CURSOR c_table2 (p_table_name IN VARCHAR2,
|
|
p_column_name IN VARCHAR2) IS
|
|
SELECT a.column_name,
|
|
a.data_type,
|
|
a.data_length,
|
|
a.data_precision,
|
|
a.data_scale,
|
|
a.nullable
|
|
FROM user_tab_columns@pdds a
|
|
WHERE a.table_name = p_table_name
|
|
AND a.column_name = NVL(p_column_name,a.column_name);
|
|
|
|
v_owner1 VARCHAR2(10) := 'DDDS2';
|
|
v_owner2 VARCHAR2(10) := 'PDDS';
|
|
v_data c_table1%ROWTYPE;
|
|
v_work BOOLEAN := FALSE;
|
|
|
|
BEGIN
|
|
|
|
Dbms_Output.Disable;
|
|
Dbms_Output.Enable(1000000);
|
|
|
|
FOR cur_tab IN c_tables LOOP
|
|
v_work := FALSE;
|
|
FOR cur_rec IN c_table1 (cur_tab.table_name, NULL) LOOP
|
|
v_work := TRUE;
|
|
|
|
OPEN c_table2 (cur_tab.table_name, cur_rec.column_name);
|
|
FETCH c_table2
|
|
INTO v_data;
|
|
IF c_table2%NOTFOUND THEN
|
|
Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : Present in ' || v_owner1 || ' but not in ' || v_owner2);
|
|
ELSE
|
|
IF cur_rec.data_type != v_data.data_type THEN
|
|
Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : DATA_TYPE differs between ' || v_owner1 || ' and ' || v_owner2);
|
|
END IF;
|
|
IF cur_rec.data_length != v_data.data_length THEN
|
|
Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : DATA_LENGTH differs between ' || v_owner1 || ' and ' || v_owner2);
|
|
END IF;
|
|
IF cur_rec.data_precision != v_data.data_precision THEN
|
|
Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : DATA_PRECISION differs between ' || v_owner1 || ' and ' || v_owner2);
|
|
END IF;
|
|
IF cur_rec.data_scale != v_data.data_scale THEN
|
|
Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : DATA_SCALE differs between ' || v_owner1 || ' and ' || v_owner2);
|
|
END IF;
|
|
IF cur_rec.nullable != v_data.nullable THEN
|
|
Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : NULLABLE differs between ' || v_owner1 || ' and ' || v_owner2);
|
|
END IF;
|
|
END IF;
|
|
CLOSE c_table2;
|
|
END LOOP;
|
|
|
|
FOR cur_rec IN c_table2 (cur_tab.table_name, NULL) LOOP
|
|
v_work := TRUE;
|
|
|
|
OPEN c_table1 (cur_tab.table_name, cur_rec.column_name);
|
|
FETCH c_table1
|
|
INTO v_data;
|
|
IF c_table1%NOTFOUND THEN
|
|
Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : Present in ' || v_owner2 || ' but not in ' || v_owner1);
|
|
ELSE
|
|
IF cur_rec.data_type != v_data.data_type THEN
|
|
Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : DATA_TYPE differs between ' || v_owner2 || ' and ' || v_owner1);
|
|
END IF;
|
|
IF cur_rec.data_length != v_data.data_length THEN
|
|
Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : DATA_LENGTH differs between ' || v_owner2 || ' and ' || v_owner1);
|
|
END IF;
|
|
IF cur_rec.data_precision != v_data.data_precision THEN
|
|
Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : DATA_PRECISION differs between ' || v_owner2 || ' and ' || v_owner1);
|
|
END IF;
|
|
IF cur_rec.data_scale != v_data.data_scale THEN
|
|
Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : DATA_SCALE differs between ' || v_owner2 || ' and ' || v_owner1);
|
|
END IF;
|
|
IF cur_rec.nullable != v_data.nullable THEN
|
|
Dbms_Output.Put_Line(cur_tab.table_name || '.' || cur_rec.column_name || ' : NULLABLE differs between ' || v_owner2 || ' and ' || v_owner1);
|
|
END IF;
|
|
END IF;
|
|
CLOSE c_table1;
|
|
END LOOP;
|
|
|
|
IF v_work = FALSE THEN
|
|
Dbms_Output.Put_Line(cur_tab.table_name || ' does not exist!');
|
|
END IF;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
SPOOL OFF
|
|
|
|
PROMPT
|
|
SET FEEDBACK ON
|