基于oracle的数据挖掘,oracle 数据挖掘

抛砖引玉

我创建了一个视图，然后不小心删除了，想知道当时创建视图的定义是什么？

通过数据挖掘可以找到当时的执行SQL，但是想想数据挖掘不能就这么一个作用吧，请教各位大侠数据挖掘还有其它什么作用？希望各位不要吝惜笔墨。

在此先谢过了。

1. conn scott/tiger

create view v_dept_emp as

select a.deptno, a.dname, a.loc, b.empno

from dept a, emp b

where a.deptno = b.deptno;

2.过了一段时间后，不小心drop掉

SQL> drop view v_dept_emp;

3 建立存储目录

oracle@linux-oracle:~>pwd

/home/oracle

oracle@linux-oracle:~>mkdir logminer

oracle@linux-oracle:~>sqlplus /nolog

SQL> conn /as sysdba

connected

SQL>alter system set utl_file_dir='/home/oracle/logminer' scope=spfile;

必须重启数据库才能生效

SQL>shutdown immediate;

SQL>startup;

4 创建存储数据字典文件的文件

SQL>exec dbms_logmnr_d.build(dictionary_filename => 'logdict.ora',dictionary_location => '/home/oracle/logminer');

PL/SQL procedure successfully completed.

5 添加归档日志文件

SQL> exec dbms_logmnr.add_logfile(logfilename => '/u02/archlog2/1_76_779842559.dbf',options => dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile(logfilename => '/u02/archlog2/1_77_779842559.dbf',options => dbms_logmnr.new);

PL/SQL procedure successfully completed.

6 启动挖掘

SQL>exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logminer/logdict.ora')；

PL/SQL procedure successfully completed.

7. 查询

SQL> select sql_redo from v$logmnr_contents where seg_name='V_DEPT_EMP';

SQL_REDO

------------------------------------------------

create view v_dept_emp as

select a.deptno, a.dname, a.loc, b.empno

from dept a, emp b

where a.deptno = b.deptno

;

drop view v_dept_emp

;

如上得到曾经执行过的sql