Oracle教程 2023-02-26

Oracle 参数 PLSCOPE_SETTINGS 官方解释，作用，如何配置最优化建议

本站中文解释

PLSCOPE_SETTINGS是一个在11g中新添加的参数，可以确定PL/SQL函数或过程中的变量是否可见。它的参数值有：

（1）FALSE（默认）——变量的定义和使用不被追踪，因此不可见。

（2）IMMEDIATE——立即开始追踪变量的定义和使用，从而使变量可见。

（3）DEFERRED——当编辑器运行时，才开始追踪变量的定义和使用，但不一定在追踪期间变量可见。

用户可以使用以下ALTER SESSION命令来正确设置PLSCOPE_SETTINGS参数：

ALTER SESSION SET PLSCOPE_SETTINGS = ‘IMMEDIATE’;

用户也可以通过在启动时设置SPFILE中的这个参数，使其在整个会话期间有效。

官方英文解释

PLSCOPE_SETTINGS controls the compile time collection, cross-reference, and storage of PL/SQL source code identifier data.

Property	Description
Parameter type	String
Syntax	`PLSCOPE_SETTINGS = ‘value_clause [, value_clause ]’` value_clause::= `{ IDENTIFIERS \| STATEMENTS } : { ALL \| NONE \| PUBLIC (for IDENTIFIERS only) \| SQL (for IDENTIFIERS only) \| PLSQL (for IDENTIFIERS only) }`
Default value	`‘IDENTIFIERS:NONE’`
Modifiable	`ALTER SESSION`, `ALTER SYSTEM`
Modifiable in a PDB	Yes
Basic	No

Values

IDENTIFIERS:ALL

Enables the collection of all source code identifier data.
IDENTIFIERS:NONE

Disables the collection of all source code identifier data.
IDENTIFIERS: PUBLIC

Enables the collection of all PUBLIC user identifier data (except for DEFINITION)
IDENTIFIERS:SQL

Enables the collection of all SQL identifier data.
IDENTIFIERS:PLSQL

Enables the collection of all PLSQL identifier data.
STATEMENTS:ALL

Enables the collection of all SQL statements used in PL/SQL.
STATEMENTS:NONE

Disables the collection of all statements.

PLSCOPE_SETTINGS can be set on a session, system, or per-library unit (ALTER COMPILE) basis. The current setting of PLSCOPE_SETTINGS for any library unit can be attained by querying the *_PLSQL_OBJECT_SETTINGS views. Any identifier data collected by setting this parameter can be accessed using the *_IDENTIFIERS views.

When a STATEMENTS setting is not specified, and IDENTIFIERS is specified but set to a value other than NONE, STATEMENTS defaults to a setting of ALL, which is equal to:

IDENTIFIERS: [ALL|PLSQL|PLSQL|PUBLIC]

Examples

The following parameter setting causes PL/SQL and SQL identifiers and statements to be collected:

PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL, STATEMENTS:ALL'

The following parameter setting causes only SQL identifiers and statements to be collected:

PLSCOPE_SETTINGS = 'IDENTIFIERS:SQL, STATEMENTS:ALL'

The following parameter setting causes only PL/SQL identifiers to be collected:

PLSCOPE_SETTINGS = 'IDENTIFIERS:PLSQL, STATEMENTS:NONE'

The following parameter setting causes no identifier data to be collected, and drops any existing identifier data:

PLSCOPE_SETTINGS = 'IDENTIFIERS: NONE'

本站中文解释

官方英文解释

相关推荐