Oracle reverse engineering

To reverse engineere the oracle database, you can use the Oracle data dictionary like follows.

-- Get table information from oracle DB
select T.* from USER_TABLES T;

-- Get column information from oracle DB
select C.* from USER_TAB_COLUMNS C;

-- Get constraint information from oracle DB
SELECT cons.constraint_type, cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_name = cols.constraint_name
ORDER BY cols.table_name, cols.position;

-- oracle constraint types
--C (check constraint on a table)
--P (primary key)
--U (unique key)
--R (referential integrity)
--V (with check option, on a view)
--O (with read only, on a view)

-- Get all referential constraints from oracle DB including referencing table
select UCC.TABLE_NAME, UCC.CONSTRAINT_NAME, UCC.COLUMN_NAME, UC.CONSTRAINT_TYPE, UC.SEARCH_CONDITION, UC2.TABLE_NAME as REFERENCES_TABLE
from USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC, USER_CONSTRAINTS UC2
where UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
and UC.R_CONSTRAINT_NAME = UC2.CONSTRAINT_NAME(+) and UC.CONSTRAINT_TYPE = 'R'
order by UCC.CONSTRAINT_NAME;

-- Get ddl of a table from oracle by name
SELECT DBMS_METADATA.GET_DDL('TABLE','MY_TABLE_NAME') FROM DUAL;

-- Get all referential constraints from oracle DB including referencing table and column     
SELECT CONS.CONSTRAINT_NAME, CONS.TABLE_NAME, COLS.COLUMN_NAME,
    CONS.R_CONSTRAINT_NAME, CONS_R.TABLE_NAME R_TABLE_NAME, COLS_R.COLUMN_NAME R_COLUMN_NAME
FROM USER_CONSTRAINTS CONS
    LEFT JOIN USER_CONS_COLUMNS COLS ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
    LEFT JOIN USER_CONSTRAINTS CONS_R ON CONS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
    LEFT JOIN USER_CONS_COLUMNS COLS_R ON COLS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
-- returns only foreign key constraints
WHERE CONS.CONSTRAINT_TYPE = 'R' -- AND CONS.TABLE_NAME = 'MY_TABLE_NAME'
ORDER BY CONS.TABLE_NAME, COLS.COLUMN_NAME;