创建一个用户。
SQL> grant connect,resource,dba to xta7 identified by xta7;
Grant succeeded.
SQL> conn xta7/xta7
Connected.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
TBSTMP
SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------
/oracle/oradata/antiper/system01.dbf
/oracle/oradata/antiper/undotbs01.dbf
创建用户使用的表空间
SQL> create tablespace users datafile '/oracle/oradata/antiper/users01.dbf' size 50M autoextend off;
Tablespace created.
SQL> alter user xta7 default tablespace users;
User altered.
SQL> conn xta7/xta7
Connected.
SQL> create table boss(id number,name varchar2(10));
Table created.
SQL> insert into boss values(1,'ajax');
1 row created.
SQL> insert into boss values(2,'kate');
1 row created.
SQL> commit;
conn / as sysdba
SQL> select group#,STATUS from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
4 INACTIVE
-------在线挖掘上述日志。
SQL> EXEC SYS.DBMS_LOGMNR.ADD_LOGFILE('/oracle/oradata/antiper/redo03.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> EXEC SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
SQL> select count(*) from v$logmnr_contents;
COUNT(*)
----------
98
SQL> desc v$logmnr_contents
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
SCN NUMBER
CSCN NUMBER
TIMESTAMP DATE
COMMIT_TIMESTAMP DATE
THREAD# NUMBER
LOG_ID NUMBER
XIDUSN NUMBER
XIDSLT NUMBER
XIDSQN NUMBER
PXIDUSN NUMBER
PXIDSLT NUMBER
PXIDSQN NUMBER
RBASQN NUMBER
RBABLK NUMBER
RBABYTE NUMBER
UBAFIL NUMBER
UBABLK NUMBER
UBAREC NUMBER
UBASQN NUMBER
ABS_FILE# NUMBER
REL_FILE# NUMBER
DATA_BLK# NUMBER
DATA_OBJ# NUMBER
DATA_OBJD# NUMBER
SEG_OWNER VARCHAR2(32)
SEG_NAME VARCHAR2(256)
SEG_TYPE NUMBER
SEG_TYPE_NAME VARCHAR2(32)
TABLE_SPACE VARCHAR2(32)
ROW_ID VARCHAR2(19)
SESSION# NUMBER
SERIAL# NUMBER
USERNAME VARCHAR2(30)
SESSION_INFO VARCHAR2(4000)
TX_NAME VARCHAR2(256)
ROLLBACK NUMBER
OPERATION VARCHAR2(32)
OPERATION_CODE NUMBER
SQL_REDO VARCHAR2(4000)
SQL_UNDO VARCHAR2(4000)
RS_ID VARCHAR2(32)
SEQUENCE# NUMBER
SSN NUMBER
CSF NUMBER
INFO VARCHAR2(32)
STATUS NUMBER
REDO_VALUE RAW(4)
UNDO_VALUE RAW(4)
SQL_COLUMN_TYPE VARCHAR2(32)
SQL_COLUMN_NAME VARCHAR2(32)
REDO_LENGTH NUMBER
REDO_OFFSET NUMBER
UNDO_LENGTH NUMBER
UNDO_OFFSET NUMBER
SQL> select sql_redo,sql_undo from v$logmnr_contents where instr(lower(sql_redo),'boss') > 0;
SQL_REDO SQL_UNDO
-------------------------------
create table boss(id number,name varchar2(10));
insert into "XTA7"."BOSS"("ID","NAME") values ('1','ajax');
delete from "XTA7"."BOSS" where "ID" = '1' and "NAME" = 'ajax' and ROWID = 'AAABiPAADAAAAAKAAA';
insert into "XTA7"."BOSS"("ID","NAME") values ('2','kate');
delete from "XTA7"."BOSS" where "ID" = '2' and "NAME" = 'kate' and ROWID = 'AAABiPAADAAAAAKAAB';
--对执行DDL进行跟踪
--执行创建几张表的DDL语句。
EXEC SYS.DBMS_LOGMNR.ADD_LOGFILE('/oracle/oradata/antiper/redo03.log',dbms_logmnr.new);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DDL_DICT_TRACKING);
如执行DDL进行跟踪的话，那么v$logmnr_contents中的结果就是所有的DDL与DCL语句，其它语句就是对数据字典表进行的DML操作。
create table boss(id number,name varchar2(10));
create table clerk as select * from boss;
create table down as select * from clerk;
create table xx as select * from v$logmnr_contents;
create table xx as select * from v$logmnr_contents;
create tablespace users datafile '/oracle/oradata/antiper/users01.dbf' size 50M autoextend off;
drop table xx;
grant connect,resource,dba to xta7 identified by VALUES '19BBFCBF0467D470' ;
从上可以看出，当语句中含有密码时，在logmnr中是被加密的。
阅读(702) | 评论(0) | 转发(0) |
