Schedule a daily task to run this get_ddl_master.bat is like taking a daily backup of the database data definition.
You can use TortoiseSVN to check out the change over the time.
get_ddl_master.bat
This script will generate all the ddl statement, call sql.exe to dump the definitions and commit them to SVN.
@echo off
cd /D c:\WebAdmin
REM echo SET HEADING OFF > get_ddl_all.sql
REM echo SET ECHO OFF >> get_ddl_all.sql
REM echo SET PAGES 999 >> get_ddl_all.sql
REM echo SET LONG 90000 >> get_ddl_all.sql
REM echo SET TRIMSPOOL ON >> get_ddl_all.sql
REM echo SET PAGESIZE 50000 >> get_ddl_all.sql
REM echo SET VERIFY OFF >> get_ddl_all.sql
REM echo SET FEEDBACK OFF >> get_ddl_all.sql
echo SET TERMOUT OFF >> c:\WebAdmin\get_ddl_all.sql
"C:\Program Files\sqldeveloper\sqldeveloper\bin\sql.exe" sys/xxxx@berry.xxxx.local:1521:paris as sysdba @get_ddl_master.sql
echo exit; >> c:\WebAdmin\get_ddl_all.sql
"C:\Program Files\sqldeveloper\sqldeveloper\bin\sql.exe" sys/xxxx@berry.xxxx.local:1521:paris as sysdba @get_ddl_all.sql
REM
cd /D c:\WebAdmin\paris
svn cleanup c:\WebAdmin\paris
svn add * --force
svn -m "Nightly Backup" commit --username=chiup --password=xxxx --non-interactive
svn update
get_ddl_master.sql
This sql will generate all ddl statements. You should change the filter conditions to suit your needs.
SET HEADING OFF
SET ECHO OFF
SET PAGES 999
SET LONG 90000
set LINES 300
SET TRIMSPOOL ON
SET PAGESIZE 50000
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF
cd c:\webAdmin
spool get_ddl_all.sql append
SELECT 'spool c:\webadmin\paris\' || OWNER || '.' || OBJECT_TYPE || '.' || OBJECT_NAME ||'.sql;'||
chr(13)||chr(10)||
'ddl '||owner||'.'||object_name||';'
sql_cmd
FROM ALL_OBJECTS
WHERE OBJECT_TYPE IN ('TABLE','PROCEDURE','FUNCTION','TRIGGER','PACKAGE','VIEW')
AND OWNER NOT IN ('SYS','SYSTEM','SYSMAN','OUTLN','PORTAL','XDB','LBACSYS','OLAPSYS','WIRELESS','CTXSYS','EXFSYS')
order by owner, object_type, object_name;
spool off
exit
