Reverse Engineering Oracle Database Schema

Admin — Mon, 23 Feb 2009 15:01:42 GMT

Have you ever wanted to reverse engineer a database schema but didn't have a GUI tool such as TOAD or DBArtisian?
We all know how tricky this was and very time consuming prior to version 9i.

Here are some helpful tips in getting you the results with some of the newer features that allow you to do this.

Hope it helps and do leave some feedback and your experiences regarding this.

Thanks,

Use can use GET_DDL and granted_ddl to retrieve the dll of objects in the database.
Here is some examples:

dbms_metadata.get_ddl

The general syntax of GET_DDL is
GET_DDL(object_type, name, schema, version, model, transform);

select DBMS_METADATA.GET_DDL('TABLE',table_name) from user_tables;

select DBMS_METADATA.GET_DDL('INDEX','DEPT_IDX','USER_NAME') from dual;

select DBMS_METADATA.GET_DDL('USER','USER_NAME') from dual;

select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','USER_NAME') from dual;

Remember when running these commands you will need to adjust your pagesize and linesize:

set linesize 110
set pagesize 100
set long 1000
set echo off
set heading off

You can adjust these based on how much data it's retrieved from the statement.

[b]Example:[/b]

SQL> set long 10000
SQL> select DBMS_METADATA.GET_DDL('DB_LINK','CNV_LNK','PUBLIC') from dual;

DBMS_METADATA.GET_DDL('DB_LINK','CNV_LNK','PUBLIC')
--------------------------------------------------------------------------------

CREATE PUBLIC DATABASE LINK "CNV_LNK"
CONNECT TO "CNV_RO" IDENTIFIED BY VALUES '043C7G221E5F5C736E3D91704EAFG419HJ1411100639LLF12X'
USING '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = NJCVRNYKSPDB01)(PORT =1521))(CONNECT_DATA = (SID = PROD)))'

FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionals / Oracle Forum / Oracle Database Administration / Reverse Engineering Oracle Database Schema / Latest Posts

Reverse Engineering Oracle Database Schema