oracle中备份package源码

要把某几个的package源代码备份导出，方法如下：

set   long   10240000
spool   c:\package.sql
select   text   from   all_source   where   ....
spool   off

 

--找出需要的包名

SELECT *
  FROM all_objects t
 WHERE t.object_type = 'PACKAGE'
   AND owner = 'APPS'
   AND trunc(t.last_ddl_time) > to_date('2011-3-11', 'YYYY-MM-DD')
   AND trunc(t.created) > to_date('2011-3-15', 'YYYY-MM-DD')
 ORDER BY t.created

 

--根据找出的包名，查找出对应的text。

SELECT x.*
  FROM all_source x
 WHERE x.owner = 'APPS'
   AND EXISTS
 (SELECT 1
    FROM all_objects t
   WHERE t.object_type = 'PACKAGE'
     AND owner = 'APPS'
     AND trunc(t.last_ddl_time) > to_date('2011-3-11', 'YYYY-MM-DD')
     AND trunc(t.created) > to_date('2011-3-15', 'YYYY-MM-DD')
     AND x.NAME = t.object_name)

 

然后导出到文本即可。

---

20110926

查看哪些package或procedure引用了某一张表

SELECT x.*
  FROM all_source x
 WHERE instr(x.text, 'TABLE_NAME') > 0