to monitor shanges in oracle database.
Idea:
Create sql file for sqlplus oracle tool that will create temporary file
that will do export
Name: oradump.sql
WHENEVER SQLERROR EXIT 1 SET SQLBLANKLINES ON SET HEADING OFF SET FEEDBACK OFF SET TRIMSPOOL ON --hope nobody produce one line longer then 2k SET LINESIZE 2000 SET LONGCHUNKSIZE 2000 --hope this will be enough to export views... SET LONG 64000 SET PAGESIZE 0 SET VERIFY OFF --the schema to export define p_schema="&1" --the schema to export define p_mask="&2" --the target directory define p_dir="&3" define p_pcr="CREATE OR REPLACE" select 'prepare export '||chr(10)||'objects like "&p_mask" from schema "&p_schema" into "&p_dir"' from dual / ALTER SESSION SET CURRENT_SCHEMA = &p_schema / --create temp export sctipt SET TERMOUT OFF spool tmp.sql / prompt WHENEVER SQLERROR EXIT 1 prompt SET SQLBLANKLINES ON prompt SET HEADING OFF prompt SET FEEDBACK OFF prompt SET TRIMSPOOL ON prompt SET LINESIZE 2000 prompt SET LONGCHUNKSIZE 2000 prompt SET LONG 64000 prompt SET PAGESIZE 0 prompt SET VERIFY OFF prompt SET TERMOUT OFF prompt ALTER SESSION SET CURRENT_SCHEMA = &p_schema prompt / --create out dirs for sources select 'host md "&p_dir'||chr(92)||type||'"' from user_source where type like '&p_mask' group by type order by type / select 'spool "&p_dir'||chr(92)||type||chr(92)||name||'.sql"'||chr(10) ||'select decode(line,1,''&p_pcr '','''')||text from user_source where name='''||name||''' and type='''||type||''' order by line'||chr(10) ||'/'||chr(10) ||'spool off' from user_source where type like '&p_mask' group by name,type order by type,name / --create out dirs for views select 'host md "&p_dir'||chr(92)||'VIEW'||'"' from dual where 'VIEW' like '&p_mask' / select 'spool "&p_dir'||chr(92)||'VIEW'||chr(92)||v.view_name||'.sql"'||chr(10) ||'select ''&p_pcr VIEW '||v.view_name||' (''||chr(10) from dual' ||chr(10)||'/'||chr(10) ||'select '' ''||column_name||decode(column_id,'||to_char(vc.colcount)||','''','','') ' ||'from user_tab_cols where table_name='''||v.view_name||''' order by column_id' ||chr(10)||'/'||chr(10) ||'select '') AS''||chr(10) from dual' ||chr(10)||'/'||chr(10) ||'select text from user_views where view_name='''||v.view_name||'''' ||chr(10)||'/'||chr(10) ||'select chr(10)||''/''||chr(10) from dual' ||chr(10)||'/'||chr(10) ||'spool off' from user_views v,(select max(column_id) colcount,table_name from user_tab_cols group by table_name) vc where v.view_name=vc.table_name and 'VIEW' like '&p_mask' and v.view_name like 'VACC%' order by v.view_name / prompt exit spool off exit /
Now let's create command file to start this script:
Name: oradump.cmd
@echo off cd "%~dp0" set oradump_srv=MY_SERVER set oradump_uid=MY_USER set oradump_sch=MY_SCHEMA set oradump_dir=.\output set oradump_obj=%% if "%oradump_pwd%"=="" set /P oradump_pwd=PASS (visible) sqlplus -L -S %oradump_uid%/%oradump_pwd%@%oradump_srv% "@oradump.sql" %oradump_sch% "%oradump_obj%" "%oradump_dir%" echo do export sqlplus -L -S %oradump_uid%/%oradump_pwd%@%oradump_srv% "@tmp.sql" del tmp.sql 2>nul exit 0
Now create "output" directory,
define correct values for variables in oradump.cmd
and you can export objects (types, packages, procedures, functions, views)
into separate files
Комментариев нет:
Отправить комментарий