I am thinking of creating a utility Proc that will capture all Development DDL changes from the Database.
This utility will baseline all DDl's for a given release say R1 and while we are developing for release R2
The utility will create the incremental scripts based on the Baseline that I did for R1 - with the current database changes.
Now I would want to know how would I differentiate between a column Rename / vs a column Drop and re-create
consider these 2 scenarios with table t1
scenario1>
table t1 ( col1 NUMBER, col2 NUMBER) --Baselined R1
I renamed col2 to col3
so t1 now becomes ( col1 NUMBER, col3 NUMBER) --R2
scenario2>
table t1 ( col1 NUMBER, col2 NUMBER) --Baselined R1
DROP col2
ADD col3
so t1 now becomes ( col1 NUMBER, col3 NUMBER) --R2
How can I say ( looking into the oracle data dictionary ) that from release R1 to R2 whether the column has undergone a Rename or a Drop Re-Create.
I hope my question makes sense.
Here's a basic ddl capture routine I wrote a while back. In my case, I was excluding PLSQL compiliations, but you can modify to suit your needs. Hope it helps
CREATE TABLE DDL_LOG
(
TSTAMP TIMESTAMP(6) NOT NULL,
HOST VARCHAR2(100),
IP_ADDRESS VARCHAR2(100),
MODULE VARCHAR2(100),
OS_USER VARCHAR2(100),
TERMINAL VARCHAR2(100),
OPERATION VARCHAR2(100),
OWNER VARCHAR2(50),
OBJECT_NAME VARCHAR2(50),
OBJECT_TYPE VARCHAR2(50),
SQLTEXT CLOB,
PREVSQLTEXT CLOB
)
LOB (SQLTEXT) STORE AS (
TABLESPACE USERS
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 0)
LOB (PREVSQLTEXT) STORE AS (
TABLESPACE USERS
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION)
RESULT_CACHE (MODE DEFAULT)
NOCOMPRESS
/
CREATE INDEX DDL_LOG_IX ON DDL_LOG
(TSTAMP)
/
DROP TRIGGER SYS.capture_all_ddl
/
CREATE OR REPLACE TRIGGER SYS.CAPTURE_ALL_DDL
after create or alter or drop on database
begin
if ora_dict_obj_owner in (...)
then
insert into ddl_log
values (systimestamp,
sys_context('USERENV','HOST'),
sys_context('USERENV','IP_ADDRESS'),
sys_context('USERENV','MODULE'),
sys_context('USERENV','OS_USER'),
sys_context('USERENV','TERMINAL'),
ora_sysevent,
ora_dict_obj_owner,
ora_dict_obj_name,
ora_dict_obj_type,
case when ora_dict_obj_type not in ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY') and ora_sysevent != 'DROP' then
( select sql_fulltext from v$sql
where sql_id = ( select sql_id from v$session where sid = sys_context('USERENV','SID') and rownum = 1 )
)
end,
case when ora_dict_obj_type not in ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY') and ora_sysevent != 'DROP' then
( select sql_fulltext from v$sql
where sql_id = ( select prev_sql_id from v$session where sid = sys_context('USERENV','SID') and rownum = 1 )
)
end
);
commit;
end if;
exception
when others then ...
end;
/
Addenda 2017
============
There is a parameter in recent versions of Oracle
enable_ddl_logging
which if set to true, will save DDL output to a file ddl_[oraclesid].log in your diagnostic repository.
Note that use of this parameter requires the Database Lifecycle Management Pack license.