Oracle Uso de Logminer

EXAMPLE 1

alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss' ;
alter session set nls_language = american ;
set lines 4000
set trimspool on
set feed off
col session_info format a40 word_wrap
col sql_redo format a90 word_wrap
col sql_undo format a90 word_wrap

begin
sys.dbms_logmnr.add_logfile ('/SCEL/data14/archives/hagc/arch_1_189771.arc',sys.dbms_logmnr.new) ;
sys.dbms_logmnr.add_logfile ('/SCEL/data14/archives/hagc/arch_1_189772.arc',sys.dbms_logmnr.addfile) ;
sys.dbms_logmnr.add_logfile ('/SCEL/data14/archives/hagc/arch_1_189773.arc',sys.dbms_logmnr.addfile) ;
end ;
/

-- Para rastrear DMLs
--
exec sys.dbms_logmnr.start_logmnr (options => sys.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + sys.DBMS_LOGMNR.COMMITTED_DATA_ONLY -
+ SYS.DBMS_LOGMNR.PRINT_PRETTY_SQL );

--
-- Para rastrear DDLs
--
--exec sys.dbms_logmnr.start_logmnr (options => sys.DBMS_LOGMNR.DDL_DICT_TRACKING)

spool /tmp/invest.lst

set feed on
set pages 200
set lines 4000
set trimspool on
set term off

select
username,
timestamp,
session_info,
sql_redo ,
sql_undo ,
operation
from
v$logmnr_contents t
WHERE
upper (seg_owner) = 'SISCEL'
and upper (seg_name) = 'GA_SERVSUPLABO'
--or lower (seg_name) = 'bpd_planes'
-- upper (username) in ('DYRUIZGO')
-- UPPER (session_info) like '%ELIPROGS%'
--operation = 'DDL'
;


spool off

EXEC sys.DBMS_LOGMNR.END_LOGMNR

EXAMPLE 2

SQL> connect / as sysdba
Conectado.
SQL> show parameters utl

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string c:\oraclefiles

SQL> exec DBMS_LOGMNR_D.BUILD( DICTIONARY_FILENAME =>'dictionary.ora', DICTIONARY_LOCATION => 'c:\oraclefiles');

Procedimiento PL/SQL terminado correctamente.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\ORCL\REDO04A.LOG
C:\ORACLE\ORADATA\ORCL\REDO04B.LOG
C:\ORACLE\ORADATA\ORCL\REDO05A.LOG
C:\ORACLE\ORADATA\ORCL\REDO05B.LOG
C:\ORACLE\ORADATA\ORCL\REDO06A.LOG
C:\ORACLE\ORADATA\ORCL\REDO06B.LOG

6 filas seleccionadas.

SQL> exec DBMS_LOGMNR.add_logfile('C:\ORACLE\ORADATA\ORCL\REDO04A.LOG');

Procedimiento PL/SQL terminado correctamente.

SQL> exec DBMS_LOGMNR.add_logfile('C:\ORACLE\ORADATA\ORCL\REDO05A.LOG');

Procedimiento PL/SQL terminado correctamente.

SQL> exec DBMS_LOGMNR.add_logfile('C:\ORACLE\ORADATA\ORCL\REDO06A.LOG');

Procedimiento PL/SQL terminado correctamente.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>'c:\oraclefiles\dictionary.ora');

Procedimiento PL/SQL terminado correctamente.

SQL> set pages 100
SQL> set lines 120
SQL> column sql_redo format a50
SQL> column sql_undo format a50

SQL> select sql_redo, sql_undo from v$logmnr_contents where rownum<10;

SQL_REDO SQL_UNDO
-------------------------------------------------- --------------------------------------------------