ITPub博客

A non-standard inquiry. An ad hoc query is created to obtain information as the need arises.Contrast with a query that is predefined and routinely performed[@more@]

Code in database applications can reference objects in the connected database. Forexample, OCI and precompiler applications can submit anonymous PL/SQL blocks.Triggers in Oracle Forms applications can reference a schema object.Such applications are dependent on the schema objects they reference. Dependencymanagement techniques vary, depending on the development environment.应用程序的依赖性数据库应用程序的代码中也会引用相关数据库中的对象[@more@]

Oracle does not manage dependencies among remote schema objects other thanlocal-procedure-to-remote-procedure dependencies.For example, assume that a local view is created and defined by a query thatreferences a remote table. Also assume that a local procedure includes a SQL statementthat references the same remote table. Later, the definition of the table is altered.As a result, the local view and procedure are never invalidated, even if the view orprocedure is used after the table is altered,

Oracle provides the additional capability of remote dependenciesusing signatures. The signature capability affects only remote dependencies. Localdependencies are not affected, as recompilation is always possible in this environment.The signature of a procedure contains information about the following items:■ Name of the package, procedure, or function■ Base types of the parameters■ Modes of the parameters (IN, OUT, and IN OUT)Note: Only the types and modes of parameters are significant. Then

In the time stamp checking dependency model, whenever aprocedure is compiled or recompiled its time stamp (the time it is created, altered, orreplaced) is recorded in the data dictionary. The time stamp is a record of the time theprocedure is created, altered, or replaced. Additionally, the compiled version of theprocedure contains information about each remote procedure that it references,including the remote procedure’s schema, package name, procedure name, and timestamp.When a dependent proc

Dependencies among stored procedures including functions, packages, and triggers ina distributed database system are managed using time stamp checking or signaturechecking.The dynamic initialization parameter REMOTE_DEPENDENCIES_MODE determineswhether time stamps or signatures govern remote dependencies.本地过程及远程过程间的依赖性1. 在分布式数据库系统中，函数，包，触发器等存储过程间的依赖性是通过时间戳检查或签名检查来进行管理的2. 初始话参数 REMOTE_DEPENDENCIES_MODE 决定使用时间戳还是签名检

Oracle also manages application-to-database and distributed database dependencies.For example, an Oracle Forms application might contain a trigger that references atable, or a local stored procedure might call a remote procedure in a distributeddatabase system. The database system must account for dependencies among suchobjects. Oracle uses different mechanisms to manage remote dependencies, dependingon the objects involved.远程依赖性管理[@more@]

Oracle manages all local dependencies using the database’s internal dependency table,which keeps track of each schema object’s dependent objects. When a referenced objectis modified, Oracle uses the depends-on table to identify dependent objects, which arethen invalidated.For example, assume a stored procedure UPDATE_SAL references the tableJWARD.employees. If the definition of the table is altered in any way, the status ofevery object that references JWARD.employees is changed to INVALID, inc

Tracking dependencies and completing necessary recompilations are performedautomatically by Oracle. Local dependency management occurs when Oraclemanages dependencies among the objects in a single database. For example, astatement in a procedure can reference a table in the same database.Remote dependency management occurs when Oracle manages dependencies indistributed environments across a network. For example, an Oracle Forms trigger candepend on a schema object in the database. In a distribut

In addition to managing dependencies among schema objects, Oracle also managesdependencies of each shared SQL area in the shared pool. If a table, view, synonym, orsequence is created, altered, or dropped, or a procedure or package specification isrecompiled, all dependent shared SQL areas are invalidated. At a subsequentexecution of the cursor that corresponds to an invalidated shared SQL area, Oraclereparses the SQL statement to regenerate the shared SQL area.共享SQL的依赖性管理1. Oracle不

Object names referenced in SQL statements can consist of several pieces, separated byperiods. The following describes how Oracle resolves an object name.1. Oracle attempts to qualify the first piece of the name referenced in the SQLstatement. For example, in hr.employees, hr is the first piece. If there is only onepiece, then the one piece is considered the first piece. a. In the current schema, Oracle searches for an object whose name matches the first piece of the object name. If it

Oracle notices when a DML object or system privilege is granted to or revoked from auser or PUBLIC and automatically invalidates all the owner’s dependent objects.Oracle invalidates the dependent objects to verify that an owner of a dependent objectcontinues to have the necessary privileges for all referenced objects. Internally, Oraclenotes that such objects do not have to be recompiled. Only security authorizationsneed to be validated, not the structure of any objects. This optimization elimi

Each session that references a package construct has its own instance of that package,including a persistent state of any public and private variables, cursors, and constants.All of a session’s package instantiations including state can be lost if any of thesession’s instantiated packages are subsequently invalidated and recompiled.[@more@]

Some data warehouses drop indexes on tables at night to facilitate faster loads.However, all views dependent on the table whose index is dropped get invalidated.This means that subsequently running any package that reference these droppedviews will invalidate the package.Remember that whenever you create a table, index, and view, and then drop the index,all objects dependent on that table are invalidated, including views, packages, packagebodies, functions, and procedures. This protects updatabl

Oracle automatically invalidates a program unit when the definition of a referencedobject is altered. For example, assume that a standalone procedure includes severalstatements that reference a table, a view, another standalone procedure, and a publicpackage procedure. In that case, the following conditions hold:■ If the referenced table is altered, then the dependent procedure is invalidated.■ If the base table of the referenced view is altered, then the view and the dependentprocedure are in

A view depends on the base tables or views referenced in its defining query. If thedefining query of a view is not explicit about which columns are referenced, forexample, SELECT * FROM table, then the defining query is expanded when storedin the data dictionary to include all columns in the referenced base table at that time.If a base table or view of a view is altered, renamed, or dropped, then the view isinvalidated, but its definition remains in the data dictionary along with the privileges,

When a schema object is referenced directly in a SQL statement or indirectly through areference to a dependent object, Oracle checks the status of the object explicitlyspecified in the SQL statement and any referenced objects, as necessary. Oracle’s actiondepends on the status of the objects that are directly and indirectly referenced in a SQLstatement:■ If every referenced object is valid, then Oracle runs the SQL statementimmediately without any additional work.■ If any referenced view or P

A view or PL/SQL program unit can be compiled and made valid if the followingconditions are satisfied:■ The definition of the view or program unit must be correct. All of the SQL andPL/SQL statements must be proper constructs.■ All referenced objects must be present and of the expected structure. For example,if the defining query of a view includes a column, the column must be present inthe base table.■ The owner of the view or program unit must have the necessary privileges for thereferenced

Some types of schema objects can reference other objects as part of their definition. Forexample, a view is defined by a query that references tables or other views. Aprocedure’s body can include SQL statements that reference other objects of adatabase. An object that references another object as part of its definition is called adependent object, while the object being referenced is a referenced object.If you alter the definition of a referenced object, dependent objects may or may notcontinue

全文摘自：http://amamn125.itpub.net/post/30871/306380如果是默认安装TOAD，在查看执行计划时会报一个错：ORA-02404: 未找到指定的计划表稍微研究了一下，解决这个问题基本上有3个方案：1、最直接的解决方案：直接创建TOAD所需要的计划表，脚本如下：CREATE TABLE TOAD_PLAN_TABLE ( STATEMENT_ID VARCHAR2 (32), TIMESTAMP DATE, REMARKS VARCHAR2 (80), OPERATION VARCHAR2 (30), OPTIONS VARCHAR2 (30), OBJECT_NODE VARCHAR2 (128), OBJECT_OWNER VARCHAR2 (30), OBJECT_NAME VARCHAR2 (30), OBJECT_INSTANCE NUMBER, OBJECT_TYPE V