Miscellaneous Management Topics for Schema Objects

Creating Multiple Tables and Views in One Operation

For example, the following statement creates two tables and a view that joins data from the two tables:

CREATE SCHEMA AUTHORIZATION scott
   CREATE VIEW sales_staff AS
      SELECT empno, ename, sal, comm
      FROM emp
      WHERE deptno = 30  WITH CHECK OPTION CONSTRAINT
                         sales_staff_cnst
   CREATE TABLE emp (
      empno       NUMBER(5) PRIMARY KEY,
      ename       VARCHAR2(15) NOT NULL,
      job         VARCHAR2(10),
      mgr         NUMBER(5),
      hiredate    DATE DEFAULT (sysdate),
      sal         NUMBER(7,2),
      comm        NUMBER(7,2),
      deptno      NUMBER(3) NOT NULL
                  CONSTRAINT dept_fkey REFERENCES dept)
   CREATE TABLE dept (
      deptno      NUMBER(3) PRIMARY KEY,
      dname       VARCHAR2(15),
      loc         VARCHAR2(25))
   GRANT SELECT ON sales_staff TO human_resources;

The CREATE SCHEMA command does not support Oracle extensions to the ANSI CREATE TABLE and CREATE VIEW commands (for example, the STORAGE clause).

Privileges Required to Create Multiple Schema Objects

Naming Objects

In a distributed database system, each database should have a unique global name. The global name is composed of the database name and the network domain that contains the database. Each object in the database then has a global object name consisting of the object name and the global database name. Because Oracle ensures that the object name is unique within a database, you can ensure that it is unique across all databases by assigning unique global database names. You should coordinate with your database administrator on this task, as it is usually the DBA who is responsible for assigning database names.

Name Resolution in SQL Statements

[schema.]name[@database]

Some examples include the following:

emp
scott.emp
scott.emp@personnel

A session is established when a user logs onto a database. Object names are resolved relative to the current user session. The username of the current user is the default schema. The database to which the user has directly logged-on is the default database.

Oracle has separate namespaces for different classes of objects. All objects in the same namespace must have distinct names, but two objects in different namespaces can have the same name. Tables, views, snapshots, sequences, synonyms, procedures, functions, and packages are in a single namespace. Triggers, indexes, and clusters each have their own individual namespace. For example, there can be a table, trigger, and index all named SCOTT.EMP.

Based on the context of an object name, Oracle searches the appropriate namespace when resolving the name to an object. For example, in the statement

DROP CLUSTER test

Oracle looks up TEST in the cluster namespace.

Rather than supplying an object name directly, you can also refer to an object using a synonym. A private synonym name has the same syntax as an ordinary object name. A public synonym is implicitly in the PUBLIC schema, but users cannot explicitly qualify a synonym with the schema PUBLIC. Synonyms can only be used to reference objects in the same namespace as tables. Due to the possibility of synonyms, the following rules are used to resolve a name in a context that requires an object in the table namespace:

1. Look up the name in the table namespace.

2. If the name resolves to an object that is not a synonym, no further work is needed.

3. If the name resolves to a private synonym, replace the name with the definition of the synonym and return to step 1.

4. If the name was originally qualified with a schema, return an error; otherwise, check if the name is a public synonym.

5. If the name is not a public synonym, return an error; otherwise, replace the name with the definition of the public synonym and return to step 1. When global object names are used in a distributed database (either explicitly or indirectly within a synonym), the local Oracle session resolves the reference as is locally required (for example, resolving a synonym to a remote table's global object name). After the partially resolved statement is shipped to the remote database, the remote Oracle session completes the resolution of the object as above. See the Oracle7 Server Concepts manual for a complete description of name resolution in a distributed database.

Renaming Schema Objects

Attention: If you drop an object and re-create it, all privilege grants for the object are lost when the object is dropped. Privileges must be granted again when the object is re-created.

Alternatively, a table, view, sequence, or a private synonym of a table, view, or sequence can be renamed using the RENAME command. When using the RENAME command, grants made for the object are carried forward for the new name. For example, the following statement renames the SALES_STAFF view:

RENAME sales_staff TO dept_30;

You cannot rename a stored PL/SQL program unit, public synonym, index, or cluster. To rename such an object, you must drop and re-create it.

Renaming a schema object has the following effects:

• All views and PL/SQL program units dependent on a renamed object become invalid (must be recompiled before next use).

• All synonyms for a renamed object return an error when used.

Privileges Required to Rename an Object