Monday 13 August 2007

Oracle Schema changes

I use this script to track schema changes for a schema on Oracle server.
(Select from top to bottom and copy,paste to any editor to get all)

user=username
password=password
DWH=schema
OWNERDWH=schemaowner
WORKDIR=/var/log/
OUTPUT=/var/log/aname.log
mv $WORKDIR/schema_$DWH.log $WORKDIR/schema_$DWH.log.1
sqlplus $user/$password < $WORKDIR/schema_$DWH.log.draft
set pages 10000
set hea off
set lines 500
set feedback off
column TABLE_NAME format a30
column COLUMN_NAME format a25
column DATA_TYPE format a8
column DATA_LENGTH format 9999
column DATA_PRECISION format 999
column DATA_SCALE format 999
column NULLABLE format a1
column COLUMN_ID format 999
SELECT ATC.TABLE_NAME
,ATC.COLUMN_NAME
,ATC.DATA_TYPE
,ATC.DATA_LENGTH
,ATC.DATA_PRECISION
,ATC.DATA_SCALE
,ATC.NULLABLE
,ATC.COLUMN_ID
FROM ALL_TABLES ATT, ALL_TAB_COLUMNS ATC
WHERE ATT.OWNER = '$OWNERDWH'
AND ATC.OWNER = ATT.OWNER
AND ATC.TABLE_NAME = ATT.TABLE_NAME;
exit;
EOF
cat $WORKDIR/schema_$DWH.log.draft |grep -v '^$'| egrep -v "10.1.0.5.0|Copyright|Connected to|OLAP and Data Mining|SQL\>" > $WORKDIR/schema_$DWH.log
#diff $WORKDIR/schema_$DWH.log $WORKDIR/schema_$DWH.log.1
cmp $WORKDIR/schema_$DWH.log $WORKDIR/schema_$DWH.log.1 >> $OUTPUT

No comments: