本文主要是介绍Oracle procedure,package,function,triger 的Flashback Query,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
之前对Flashback 进行了一个总结,参考:
Oracle Flashback 技术 总结
http://www.cndba.cn/Dave/article/1276
在这篇文章里面,Flashback Query 示例中只提到了对Table的Flashback Query。
如果是其他的对象,比如function,procedure,trigger等。 这时候,就需要使用到ALL_SOURCE 表。
先看联机文档对该表的说明:
ALL_SOURCE describes the text source of the stored objects accessible to the current user.
DBA_SOURCE describes the text source of all stored objects in the database.
USER_SOURCE describes the text source of the stored objects owned by the current user. This view does not display the OWNER column.
Column | Datatype | NULL | Description |
OWNER | VARCHAR2(30) | NOT NULL | Owner of the object |
NAME | VARCHAR2(30) | NOT NULL | Name of the object |
TYPE | VARCHAR2(12) |
| Type of object: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY |
LINE | NUMBER | NOT NULL | Line number of this line of source |
TEXT | VARCHAR2(4000) |
| Text source of the stored object |
如果我们误删除了某些对象,如procedure,就可以使用all_source 表进行恢复。
SQL> desc dba_source
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
查看dba_source 的所有type
SQL> select type from dba_source group by type;
TYPE
------------
PROCEDURE
PACKAGE
PACKAGE BODY
TYPE BODY
TRIGGER
FUNCTION
TYPE
7 rows selected.
基于timestamp恢复的语句
SQL>SELECT text
FROM dba_source
AS OF TIMESTAMP TO_TIMESTAMP ('XXXXX', 'YYYY-MM-DD HH24:MI:SS')
WHERE owner = 'XXXX' AND name = '你删除的对象名'
ORDER BY line;
示例:
创建函数:
SQL> CREATE OR REPLACE function getdate return date
as
v_date date;
begin
select sysdate into v_date from dual;
return v_date;
end;
/
Function created.
查询函数:
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select getdate() from dual;
GETDATE()
-------------------
2011-04-07 21:02:09
查询dba_source 表:
SQL> select text from dba_source where name='GETDATE' order by line;
TEXT
--------------------------------------------------------------------------------
function getdate return date
as
v_date date;
begin
select sysdate into v_date from dual;
return v_date;
end;
7 rows selected.
drop 函数,在查询,记录不存在
SQL> drop function getdate;
Function dropped.
SQL> select text from dba_source where name='GETDATE' order by line;
no rows selected
使用我们的Flashback Query 查询:
SQL> select text from dba_source as of timestamp to_timestamp('2011-04-07 21:02:09','yyyy-mm-dd hh24:mi:ss') where name='GETDATE' order by line;
TEXT
--------------------------------------------------------------------------------
function getdate return date
as
v_date date;
begin
select sysdate into v_date from dual;
return v_date;
end;
7 rows selected.
这时候,又查看到了函数的代码,只需要把这些代码重新执行一下就ok了。 其他对象和这个类似。 这里就不演示了。
-------------------------------------------------------------------------------------------------------
QQ: 492913789
Email: ahdba@qq.com
Blog: http://www.cndba.cn/dave
DBA1 群:62697716(满); DBA2 群:62697977(满) DBA3 群:62697850(满)
DBA 超级群:63306533(满); DBA4 群: 83829929 DBA5群: 142216823
聊天 群:40132017 聊天2群:69087192
--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请
这篇关于Oracle procedure,package,function,triger 的Flashback Query的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!