is there any way to display two different table structure (compare two table), table from two differ-CodePudding

--this is way to compare inside single database tables how can i compare over different database tables?

CREATE OR REPLACE PROCEDURE compareTwoTables is
BEGIN
  FOR i in (SELECT column_name
              FROM all_tab_columns
             WHERE table_name = 'table2'
            MINUS
            SELECT column_name
              FROM all_tab_columns
             WHERE table_name = 'table1') LOOP
    dbms_output.put_line(i.column_name);
  END LOOP;
END;

CodePudding user response：

You need to create a DB link to the remote database. After that you can use a script similar to:

DECLARE 
  l_table VARCHAR2(100) := &tbl;
  l_res   NUMBER;
BEGIN
  FOR col IN (SELECT column_name, ownder, data_type, data_length, data_precision, data_scale, nullable, column_id
                FROM all_tab_columns@<remote_server> --asuming you have created DB link
               WHERE table_name = l_table)
  LOOP
    
    BEGIN
      SELECT 1
        INTO l_res
        FROM all_tab_columns
       WHERE NVL(column_name   ,'x')       = NVL(col.column_name   ,'x')
             NVL(ownder        ,'x')       = NVL(col.ownder        ,'x')
             NVL(data_type     ,'x')       = NVL(col.data_type     ,'x')
             NVL(data_length   ,'x')       = NVL(col.data_length   ,'x')
             NVL(data_precision,'x')       = NVL(col.data_precision,'x')
             NVL(data_scale    ,'x')       = NVL(col.data_scale    ,'x')
             NVL(nullable      ,'x')       = NVL(col.nullable      ,'x')
             NVL(column_id     ,'x')       = NVL(col.column_id;    ,'x');
       
    EXCEPTION WHEN no_data_found THEN
      dbms_output.put_line(col.column_name);
    END; 
  END LOOP;
END;