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