不胜人生一场醉

--CREATE THE CREATE_TABLE SCRIPT, THOUGH WE CAN USE

--LIKE select dbms_metadata.get_ddl('TABLE','TABLENAME','USERNAME') from dual; GET THE SQL SCRIPT

--BUT IT IS NOT HELPFUL TO COMPARE THE DIFFERENT VERSION

SELECT SQLTEXT FROM
(
SELECT 'CREATE TABLE '||TABLE_NAME AS SQLTEXT,-1 AS COLUMN_ID,TABLE_NAME FROM USER_TABLES@REMOTEKGK
UNION
SELECT '(' AS SQLTEXT,0 AS COLUMN_ID,TABLE_NAME FROM USER_TABLES@REMOTEKGK
UNION
SELECT ');' AS SQLTEXT,100 AS COLUMN_ID,TABLE_NAME FROM USER_TABLES@REMOTEKGK
UNION
SELECT
CASE WHEN DATA_TYPE='NUMBER'
THEN
CASE WHEN DATA_PRECISION IS NULL
THEN COLUMN_NAME||' INTEGER,'
ELSE COLUMN_NAME||' '||DATA_TYPE||'('||TO_CHAR(DATA_PRECISION)||','||TO_CHAR(DATA_SCALE)||')'||DECODE(COLUMN_ID,(SELECT MAX

(B.COLUMN_ID) FROM USER_TAB_COLUMNS@REMOTEKGK B WHERE A.TABLE_NAME=B.TABLE_NAME),' ',',')
END
WHEN DATA_TYPE='NVARCHAR2'
THEN COLUMN_NAME||' '||DATA_TYPE||'('||TO_CHAR(DATA_LENGTH/2)||')'||DECODE(COLUMN_ID,(SELECT MAX(B.COLUMN_ID) FROM

USER_TAB_COLUMNS@REMOTEKGK B WHERE A.TABLE_NAME=B.TABLE_NAME),' ',',')
WHEN DATA_TYPE IN ('CHAR','VARCHAR2')
THEN COLUMN_NAME||' '||DATA_TYPE||'('||TO_CHAR(DATA_LENGTH)||')'||DECODE(COLUMN_ID,(SELECT MAX(B.COLUMN_ID) FROM

USER_TAB_COLUMNS@REMOTEKGK B WHERE A.TABLE_NAME=B.TABLE_NAME),' ',',')
WHEN DATA_TYPE IN ('DATE','BLOB','CLOB','LONG','NCLOB')
THEN COLUMN_NAME||' '||DATA_TYPE||DECODE(COLUMN_ID,(SELECT MAX(B.COLUMN_ID) FROM USER_TAB_COLUMNS@REMOTEKGK B WHERE

A.TABLE_NAME=B.TABLE_NAME),' ',',')
END AS SQLTEXT,
COLUMN_ID,
A.TABLE_NAME
FROM USER_TAB_COLUMNS@REMOTEKGK A
Where A.table_name Like 'T%'
) d
Where d.table_name Not In
(
Select c.table_name From USER_TABLES c
Where c.table_name Like 'T%'
)
ORDER BY TABLE_NAME,COLUMN_ID,SQLTEXT

--ADD NEW COLUMN ACCORDING THE LATEST TABLE NAME

--BASED THE COLUMN_NAME AND COLUMN_ID,NEED TO MODIFY THE COLUMN NAME

--BUT IN FACT MOST SITUATION IT RESULT IN NEW COLUMN,SO IT IS NO USEFUL

....