Methods for comparing two table structure differences and data differences in Oracle

there is a need to do this in your job: compare the table structure of two tables to see if they are the same, and find out the difference. Compare the data in two tables and find out the difference data?

Analysis: Due to the more fields in the table structure, the manual comparison is a waste of time, and there is no guarantee that the error will not be guaranteed. For the data in the table there can be a lot more, not to be compared in this way.

In order to think simple and convenient test, first set up two test tables, and insert some test data, SQL is as follows:

CREATE TABLE t_a (  ID   VARCHAR2 () not NULL,  name VARCHAR2 (+), age  number  ,  sex  VARCHAR2 (2)); INSERT into t_a (ID, name, age, Sex) VALUES (' 1 ', ' 1 ', 1, ' 1 '); INSERT into t_a (ID, name, age, Sex) VALUES (' 2 ' , ' 2 ', 2, ' 2 '); Commit;create table t_b (  ID    VARCHAR2 () not NULL,  name  VARCHAR2 (+),  age Number   ,  clazz VARCHAR2), insert into T_b (ID, name, age, Clazz) VALUES (' 1 ', ' 1 ', 1, ' 1 '); INSERT into T_b (ID , name, age, Clazz) VALUES (' 2 ', ' 2 ', 1, ' 3 '); Insert to T_b (ID, name, age, Clazz) VALUES (' 3 ', ' 3 ', 3, ' 3 '); commit;

resolution process: Just beginning to consider the use of stored procedures, in a circular comparison of processing, first need to find a table structure of SQL, access to the data, in Oracle all table structure information is stored in User_tab_columns, then query the table structure information of a single table is simple:
Select column_name from user_tab_columns where table_name = ' t_a ';
After running the discovery can not find the results, why? After removing the query criteria to query the results, check found that the original content stored in the user_tab_columns is uppercase, so, SQL changed to the following can be queried the results:
Select column_name from user_tab_columns where table_name = ' t_a ';
Writing such a stored procedure is still a little complicated, find out on the internet there is no ready-made, their own write a will find very complex. Found a minus keyword on the internet. Popular Science: In Oracle, Union and set intersect intersection minus difference set; Can I use a difference set to achieve that demand?
Soon the SQL was written:

/*1. Compare table structure */(SELECT column_name from          user_tab_columns         where table_name = ' t_a '        minus        Select Column_ Name from          user_tab_columns         where table_name = ' t_b ') union (select column_name from         user_tab_columns        WHERE table_name = ' t_b '       minus       select column_name from         user_tab_columns        where table_name = ' t_a '); c12/>/* 2. Compare table Data */(SELECT * from          t_a        minus        select * from t_b) union (SELECT * from         t_b       minus
   select * from T_a)

Let's look at how SQL works:

Table T_A structure and data:

Table T_b structure and data:

Table Structure Differences:

Data differences:

Rethinking: Why did I not think about using the difference set before?
1. Mathematics is not well-learned, there is no mathematical thinking. The concept of intersection and difference set is long studied in middle school, but mathematical thinking is not established, so .... Got to fill up the math.
2.oracle function not ripe, it seems I need to find an Oracle function manual, when it's okay to flip.

Methods for comparing two table structure differences and data differences in Oracle