20 General Management of Schema Objects

Managing Object Name Resolution

Object names referenced in SQL statements can consist of several pieces, separated by periods. The followin g describes how the database resolves an object name.

1. Oracle Database attempts to qualify the fir st piece of the name referenced in the SQL statement. For example, in scott.emp, scott is the first piece. If there is only one piece, the one piece is considered the first piece.

   1. In the current schema, the database searches for an object whose name matches the first piece of the object name. If it does not find such an object, it continues with step b.

   2. The da tabase searches for a public synonym that matches the first piece of the name. If it does not find one, it continues with step c.

   3. The database searches for a schema whose name matches the first piece of the object name. If it finds one, it returns to step b, now using the second piece of the nam e as the object to find in the qualified schema. If the second piece does not correspond to an object in the previously qualified sch ema or there is not a second piece, the database returns an error.

   If no schema is found in step c, the object cannot be qualified and the database returns an error.

2. A schema object has been qualified. Any re maining pieces of the name must match a valid part of the found object. For example, if scott.emp.deptno is the name, scott is qualified as a schema, emp is qualified as a table, and deptno must correspond to a col umn (because emp is a table). If emp is qualified as a package, deptno must correspond to a pu blic constant, variable, procedure, or function of that package.

When global object names are used in a distributed database, either explicitly or indirectly within a synonym, the local database resolves the reference locally. For example, it resolv es a synonym to global object name of a remote table. The partially resolved statement is shipped to the remote database, and the rem ote database completes the resolution of the object as described here.

Because of how the database resolves references, it is possible for an object to depend on the nonexistence of other objects. This situation occurs when the dependent object uses a referen ce that would be interpreted differently were another object present. For example, assume the following:

• At the current point in time, the company schema contains a table named emp.

• A PUBLIC synonym named emp is created for company.emp and the SELECT privilege fo r company.emp is granted to the PUBLIC role.

• The jward schema doe s not contain a table or private synonym named emp.

• The user jward creates a v iew in his schema with the following statement:

  CREATE VIEW dept_salaries AS 
       SELECT deptno, MIN(s
  al), AVG(sal), MAX(sal) FROM emp 
       GROUP BY deptno 
       ORDER BY deptno; 
  
  

When jward creates the dept_salaries view, the reference to emp is resolved by first looking for jward.emp as a t able, view, or private synonym, none of which is found, and then as a public synonym named emp, which is found. As a res ult, the database notes that jward.dept_salaries depends on the nonexistence of jward.emp and on the existe nce of public.emp.

Now assume that jward decides to create a new view named emp in his schema using the following statement:

CREATE VIEW emp AS 
     SELECT empno, ename, mgr, deptno 
     F
ROM company.emp; 

Notice that jward.emp does not have the same structure as company.emp.

As it attempts to resolve references in object definitions, the database internally makes note of dependencies that the new dependen t object has on "nonexistent" objects--schema objects that, if they existed, would change the interpretation of the object's definiti on. Such dependencies must be noted in case a nonexistent object is later created. If a nonexistent object is created, all dependent objects must be invalidated so that dependent objects can be recompiled and verified and all dependent function-based indexes must be marked unusable.

Therefore, in the previous example, as jward.emp is created, jward.dept_salaries i s invalidated because it depends on jward.emp. Then when jward.dept_salaries is used, the database attempts to recompile the view. As the database resolves the reference to emp, it finds jward.emp (public.emp is no longer the referenced object). Because jward.emp does not have a sal column, the database fin ds errors when replacing the view, leaving it invalid.

In summary, you must manage dependencies on nonexistent objects checked during object resolution in case the nonexistent object is later created.

Switching to a Di fferent Schema

The following statement sets the schema of the current session to the schema name specified in the stat ement.

ALTER SESSION SET CURRENT_SCHEMA = <schema name>

In subsequent SQL st atements, Oracle Database uses this schema name as the schema qualifier when the qualifier is omitted. In addition, the database uses the temporary tablespace of the specified schema for sorts, joins, and storage of temporary database objects. The session retains it s original privileges and does not acquire any extra privileges by the preceding ALTER SESSION statement.

For example:

CONNECT scott/tiger
ALTER SESSION SET CURRENT_SCHEMA = joe;
SELECT * FROM emp_tab;

Since emp_tab is not schema-qualified, the table name is resolved under schema joe. But if scott does not have select privilege on table joe.emp_tab, then scott cannot execute t he SELECT statement.

Displaying Information About Schema Objects

Oracle Database provides a P L/SQL package that enables you to determine the DDL that created an object and data dictionary views that you can use to display info rmation about schema objects. Packages and views that are unique to specific types of schema objects are described in the associated chapters. This section describes views and packages that are generic in nature and apply to multiple schema objects.

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(
     DBMS_METADATA.SESSION_TRANSFORM,'STO
RAGE',false);
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_ALL_TABLES u
     WHERE u.nested='NO' 
     AND (u.io
t_type is null or u.iot_type='IOT');
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(
     DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');



The output from DBMS_METADATA.GET_DDL is a LONG datatype. When using SQL*Plus, your output may be t
runcated by default. Issue the following SQL*Plus command before issuing the DBMS_METADATA.GET_DDL statement to ensure t
hat your output is not truncated:
SQL> SET LONG 9999









See Also:
Oracle XML Developer's Kit Programmer's Guide for detailed information and further examples relating to the
 use of the DBMS_METADATA package

SELECT OBJECT_NAME, OBJECT
_TYPE 
    FROM USER_OBJECTS;

OBJECT_NAME
 OBJECT_TYPE
-------------------------  -------------------
EMP_DEPT                   CLUSTER
EMP                        TABLE
DEPT
                      TABLE
EMP_DEPT_INDEX             INDEX
PUBLIC_EMP                 SYNONYM
EMP_MGR                    VIEW

SELECT TABLE_OWNER, TABLE_NAME, SYNONYM_NAME
    FROM DBA_SYNONYMS
    WHERE OWNER = 'JWARD';

TABLE_OWNER             TABLE_NAME   SYNONYM_NAME
----------------------  ----
-------  -----------------
SCOTT                   DEPT         DEPT
SCOTT                   EMP          EMP