oracle 批量编译 pl/sql 过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5335414/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
bulk compiling pl/sql procedures
提问by cherit
i have lots of packages which needed to be compiled when i move from development to production or when we release a change request.
当我从开发转移到生产或发布更改请求时，我有很多包需要编译。
right now , we compile each of the packages one by one using toad or sqldbx , is there a way that i can write a batch file with sqlplus command so that i can run all my packages in one go.. like *.sql
现在，我们使用 toad 或 sqldbx 一个一个编译每个包，有没有办法可以用 sqlplus 命令编写批处理文件，以便我可以一次性运行所有包.. 像 *.sql
回答by Rob van Wijk
You can execute dbms_utility.compile_schema(user,false);to compile all invalid objects in your schema at once.
您可以执行dbms_utility.compile_schema(user,false);一次编译架构中的所有无效对象。
You can read about that procedure here in the documentation: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_util.htm#ARPLS73226
您可以在文档中阅读有关该过程的信息：http: //download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_util.htm#ARPLS73226
Regards,
Rob.
问候，
罗伯。
回答by ik_zelf
Normally when we do lots of changes in a database that invalidates lots of objects, the easiest way to get them recompiled is by running sqlplus "/ as sysdba" @?/rdbms/admin/utlrpThis procedure gets smarter every release and from 10g it uses the Oracle Scheduler to work in parallel. This of course only works with dba access to the database. If you lack that Rob van Wijk's answer is the way to go.
通常，当我们在数据库中进行大量更改使大量对象无效时，重新编译它们的最简单方法是运行sqlplus "/ as sysdba" @?/rdbms/admin/utlrp此过程，每个版本都会变得更智能，并且从 10g 开始，它使用 Oracle 调度程序并行工作。这当然仅适用于 dba 对数据库的访问。如果您缺少 Rob van Wijk 的答案，那是您要走的路。
回答by Shamit Verma
You can put all the SQLs in a text file and execute that by:
您可以将所有 SQL 放在一个文本文件中并通过以下方式执行：
SQL > @/path/script.sql
You just need to provide path of script to be executed.
您只需要提供要执行的脚本的路径。
回答by pablo
My approach would be to copy all package scripts into a directory then create a single sql script in that directory to load all packages, see example below.
我的方法是将所有包脚本复制到一个目录中，然后在该目录中创建一个 sql 脚本来加载所有包，请参见下面的示例。
-- load package specifications
@@package1.pks
@@package2.pks
-- load package bodies
@@package1.pkb
@@package2.pkb
回答by David Aldridge
One way of tackling this is to deploy your code in the correct order.
解决此问题的一种方法是以正确的顺序部署您的代码。
PL/SQL packages themselves are the API for the code in the package body, and the packages themselves are not dependent on each other. Package bodies however can be dependent on packages, so if a package is recompiled than it runs the risk of invalidating package bodies that reference it.
PL/SQL包本身就是包体中代码的API，包本身不相互依赖。然而，包体可以依赖于包，所以如果一个包被重新编译，那么它就会冒着使引用它的包体失效的风险。
Unfortunately it's very common to see deployments that work in this order:
不幸的是，看到按以下顺序工作的部署是很常见的：
create or replace Package A ...;
create or replace Package Body A ...;
create or replace Package B ...;
create or replace Package Body B ...;
create or replace Package C ...;
create or replace Package Body C ...;
This has the side-effect that if code in Package Body A is dependent on Package B, then when Package B is (re)created it invalidates Package Body A.
这具有副作用，如果包体 A 中的代码依赖于包 B，那么当包 B 被（重新）创建时，它会使包体 A 失效。
The correct sequence for deployment is:
正确的部署顺序是：
create or replace Package A ...;
create or replace Package B ...;
create or replace Package C ...;
create or replace Package Body A ...;
create or replace Package Body B ...;
create or replace Package Body C ...;
If there have not been changes in the package itself then there is no need to deploy it at all, of course.
如果包本身没有变化，那么当然根本不需要部署它。
Respecting these methods should give you much fewer invalid objects.
尊重这些方法应该会给你更少的无效对象。
回答by Gustavo González
Package Headers first:
首先是包头：
for i in $(ls *.hed); do sqlplus user/password @$i; done
Then package bodies:
然后封装体：
for i in $(ls *.hed); do sqlplus user/password @$i; done
回答by tbone
you can use dba_objects to check for invalid objects and use dynamic sql to generate compile statements, something like:
您可以使用 dba_objects 检查无效对象并使用动态 sql 生成编译语句，例如：
select 'alter ' || object_type || ' ' || owner || '.' || object_name || ' compile;'
from dba_objects
where status = 'INVALID'
and object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION');
you can then put that into a sql script.
然后，您可以将其放入 sql 脚本中。
You can also look into utl_recomp package
您还可以查看 utl_recomp 包
