 Posted 2/23/2009 3:01:42 PM
|
|
|
|
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.
Example:
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)))'
|
|
|
|