Database 版 (精华区)

发信人: rhine (风雨无阻), 信区: Database
标  题: Oracle DBA Guide, V8, part 13
发信站: 哈工大紫丁香 (2000年07月08日20:33:29 星期六), 站内信件

发信人: chendu (Oracle Only), 信区: Database
标  题: Oracle DBA Guide, V8, part 13
发信站: BBS 水木清华站 (Sat Nov 13 15:31:04 1999)

Hi, required by several friends, I am posting the
Oracle Database Administrator's Guide version 8
here.

Web Master, if my post it too long and not good for
the bbs, please delete it and I am sorry for it.

Hoping everyone enjoy it!

----------------------------------------------------------------------------
----
13
Managing Views, Sequences and Synonyms
This chapter describes aspects of view management, and includes the followin
g topics:
Managing Views
Managing Sequences
Managing Synonyms
Before attempting tasks described in this chapter, familiarize yourself with
 the concepts in Chapter 10, Guidelines for Managing Schema Objects.
Managing Views
A view is a tailored presentation of the data contained in one or more table
s (or other views), and takes the output of a query and treats it as a table
. You can think of a view as a "stored query" or a "virtual table." You can
use views in most places where a table can be used.
This section describes aspects of managing views, and includes the following
 topics:
Creating Views
Modifying a Join View
Replacing Views
Dropping Views
Creating Views
To create a view, you must fulfill the requirements listed below:
To create a view in your schema, you must have the CREATE VIEW privilege; to
 create a view in another user's schema, you must have the CREATE ANY VIEW s
ystem privilege. You may acquire these privileges explicitly or via a role.
The owner of the view (whether it is you or another user) must have been exp
licitly granted privileges to access all objects referenced in the view defi
nition; the owner cannot have obtained these privileges through roles. Also,
 the functionality of the view is dependent on the privileges of the view's
owner. For example, if the owner of the view has only the INSERT privilege f
owner. For example, if the owner of the view has only the INSERT privilege f
or Scott's EMP table, the view can only be used to insert new rows into the
EMP table, not to SELECT, UPDATE, or DELETE rows from it.
If the owner of the view intends to grant access to the view to other users,
 the owner must have received the object privileges to the base objects with
 the GRANT OPTION or the system privileges with the ADMIN OPTION.
You can create views using the SQL command CREATE VIEW. Each view is defined
 by a query that references tables, snapshots, or other views. The query tha
t defines a view cannot contain the ORDER BY or FOR UPDATE clauses. For exam
ple, the following statement creates a view on a subset of data in the EMP t
able:
CREATE VIEW sales_staff AS
           SELECT empno, ename, deptno
           FROM emp
           WHERE deptno = 10
           WITH CHECK OPTION CONSTRAINT sales_staff_cnst;
The query that defines the SALES_STAFF view references only rows in departme
nt 10. Furthermore, the CHECK OPTION creates the view with the constraint th
at INSERT and UPDATE statements issued against the view cannot result in row
s that the view cannot select. For example, the following INSERT statement s
uccessfully inserts a row into the EMP table by means of the SALES_STAFF vie
w, which contains all rows with department number 10:
INSERT INTO sales_staff VALUES (7584, 'OSTER', 10);
However, the following INSERT statement is rolled back and returns an error
because it attempts to insert a row for department number 30, which could no
t be selected using the SALES_STAFF view:
INSERT INTO sales_staff VALUES (7591, 'WILLIAMS', 30);
The following statement creates a view that joins data from the EMP and DEPT
 tables:
CREATE VIEW division1_staff AS
       SELECT ename, empno, job, dname
         FROM emp, dept
         WHERE emp.deptno IN (10, 30)
         AND emp.deptno = dept.deptno;
The DIVISION1_STAFF view joins information from the EMP and DEPT tables. The
 CHECK OPTION is not specified in the CREATE VIEW statement for this view.
Expansion of Defining Queries at View Creation Time
In accordance with the ANSI/ISO standard, Oracle expands any wildcard in a t
op-level view query into a column list when a view is created and stores the
 resulting query in the data dictionary; any subqueries are left intact. The
 column names in an expanded column list are enclosed in quote marks to acco
unt for the possibility that the columns of the base object were originally
entered with quotes and require them for the query to be syntactically corre
ct.
As an example, assume that the DEPT view is created as follows:
As an example, assume that the DEPT view is created as follows:
CREATE VIEW dept AS SELECT * FROM scott.dept;
Oracle stores the defining query of the DEPT view as:
SELECT "DEPTNO", "DNAME", "LOC" FROM scott.dept
Views created with errors do not have wildcards expanded. However, if the vi
ew is eventually compiled without errors, wildcards in the defining query ar
e expanded.
Creating Views with Errors
If there are no syntax errors in a CREATE VIEW statement, Oracle can create
the view even if the defining query of the view cannot be executed; the view
 is considered "created with errors." For example, when a view is created th
at refers to a non-existent table or an invalid column of an existing table,
 or when the view owner does not have the required privileges, the view can
be created anyway and entered into the data dictionary. However, the view is
 not yet usable.
To create a view with errors, you must include the FORCE option of the CREAT
E VIEW command:
CREATE FORCE VIEW AS ....;
By default, views are not created with errors. When a view is created with e
rrors, Oracle returns a message indicating the view was created with errors.
 The status of a view created with errors is INVALID. If conditions later ch
ange so that the query of an invalid view can be executed, the view can be r
ecompiled and become valid (usable).
See Also: For information changing conditions and their impact on views, see
 "Managing Object Dependencies".
Modifying a Join View
A modifiable join view is a view that contains more than one table in the to
p-level FROM clause of the SELECT statement, and that does not contain any o
f the following:
DISTINCT operator
aggregate functions: AVG, COUNT, GLB, MAX, MIN, STDDEV, SUM, or VARIANCE
set operations: UNION, UNION ALL, INTERSECT, MINUS
GROUP BY or HAVING clauses
START WITH or CONNECT BY clauses
ROWNUM pseudocolumn
With some restrictions, you can modify views that involve joins. If a view i
s a join on other nested views, then the other nested views must be mergeabl
e into the top level view.
The examples in following sections use the EMP and DEPT tables. These exampl
es work only if you explicitly define the primary and foreign keys in these
tables, or define unique indexes. Following are the appropriately constraine
d table definitions for EMP and DEPT:
CREATE TABLE dept (
         deptno        NUMBER(4) PRIMARY KEY,
         dname         VARCHAR2(14),
         dname         VARCHAR2(14),
         loc           VARCHAR2(13));
CREATE TABLE emp (
         empno        NUMBER(4) PRIMARY KEY,
         ename        VARCHAR2(10),
         job          varchar2(9),
         mgr          NUMBER(4),
         sal          NUMBER(7,2),
         comm         NUMBER(7,2),
         deptno       NUMBER(2),
         FOREIGN KEY       (DEPTNO) REFERENCES DEPT(DEPTNO));
You could also omit the primary and foreign key constraints listed above, an
d create a UNIQUE INDEX on DEPT (DEPTNO) to make the following examples work
.
See Also: For more information about mergeable views see the Oracle8 Tuning
manual.
Key-Preserved Tables
The concept of a key-preserved table is fundamental to understanding the res
trictions on modifying join views. A table is key preserved if every key of
the table can also be a key of the result of the join. So, a key-preserved t
able has its keys preserved through a join.
----------------------------------------------------------------------------
----
----
Note:
It is not necessary that the key or keys of a table be selected for it to be
 key preserved. It is sufficient that if the key or keys were selected, then
 they would also be key(s) of the result of the join.
----------------------------------------------------------------------------
----
The key-preserving property of a table does not depend on the actual data in
 the table. It is, rather, a property of its schema and not of the data in t
he table. For example, if in the EMP table there was at most one employee in
 each department, then DEPT.DEPTNO would be unique in the result of a join o
f EMP and DEPT, but DEPT would still not be a key-preserved table.
If you SELECT all rows from EMP_DEPT_VIEW, the results are:
EMPNO      ENAME      DEPTNO     DNAME          LOC
---------- ---------- ---------- -------------- -----
      7782 CLARK           10   ACCOUNTING     NEW YORK
      7839 KING            10   ACCOUNTING     NEW YORK
      7934 MILLER          10   ACCOUNTING     NEW YORK
      7369 SMITH           20   RESEARCH       DALLAS
      7876 ADAMS           20   RESEARCH       DALLAS
      7902 FORD            20   RESEARCH       DALLAS
      7788 SCOTT           20   RESEARCH       DALLAS
      7566 JONES           20   RESEARCH       DALLAS
8 rows selected.
In this view, EMP is a key-preserved table, because EMPNO is a key of the EM
P table, and also a key of the result of the join. DEPT is not a key-preserv
ed table, because although DEPTNO is a key of the DEPT table, it is not a ke
y of the join.
DML Statements and Join Views
Any UPDATE, INSERT, or DELETE statement on a join view can modify only one u
nderlying base table.
UPDATE Statements
The following example shows an UPDATE statement that successfully modifies t
he EMP_DEPT view:
UPDATE emp_dept
   SET sal = sal * 1.10
     WHERE deptno = 10;
The following UPDATE statement would be disallowed on the EMP_DEPT view:
UPDATE emp_dept
   SET loc = 'BOSTON'
     WHERE ename = 'SMITH';
This statement fails with an ORA-01779 error (``cannot modify a column which
 maps to a non key-preserved table''), because it attempts to modify the und
erlying DEPT table, and the DEPT table is not key preserved in the EMP_DEPT
view.
view.
In general, all modifiable columns of a join view must map to columns of a k
ey-preserved table. If the view is defined using the WITH CHECK OPTION claus
e, then all join columns and all columns of repeated tables are not modifiab
le.
So, for example, if the EMP_DEPT view were defined using WITH CHECK OPTION,
the following UPDATE statement would fail:
UPDATE emp_dept
   SET deptno = 10
     WHERE ename = 'SMITH';
The statement fails because it is trying to update a join column.
DELETE Statements
You can delete from a join view provided there is one and only one key-prese
rved table in the join.
The following DELETE statement works on the EMP_DEPT view:
DELETE FROM emp_dept
   WHERE ename = 'SMITH';
This DELETE statement on the EMP_DEPT view is legal because it can be transl
ated to a DELETE operation on the base EMP table, and because the EMP table
is the only key-preserved table in the join.
In the following view, a DELETE operation cannot be performed on the view be
cause both E1 and E2 are key-preserved tables:
CREATE VIEW emp_emp AS
   SELECT e1.ename, e2.empno, deptno
     FROM emp e1, emp e2
     WHERE e1.empno = e2.empno;
If a view is defined using the WITH CHECK OPTION clause and the key-preserve
d table is repeated, then rows cannot be deleted from such a view:
CREATE VIEW emp_mgr AS
   SELECT e1.ename, e2.ename mname
   FROM emp e1, emp e2
     WHERE e1.mgr = e2.empno
     WITH CHECK OPTION;
No deletion can be performed on this view because the view involves a self-j
oin of the table that is key preserved.
INSERT Statements
The following INSERT statement on the EMP_DEPT view succeeds:
INSERT INTO emp_dept (ename, empno, deptno)
   VALUES ('KURODA', 9010, 40);
This statement works because only one key-preserved base table is being modi
fied (EMP), and 40 is a valid DEPTNO in the DEPT table (thus satisfying the
FOREIGN KEY integrity constraint on the EMP table).
An INSERT statement like the following would fail for the same reason that s
uch an UPDATE on the base EMP table would fail: the FOREIGN KEY integrity co
nstraint on the EMP table is violated.
nstraint on the EMP table is violated.
INSERT INTO emp_dept (ename, empno, deptno)
   VALUES ('KURODA', 9010, 77);
The following INSERT statement would fail with an ORA-01776 error (``cannot
modify more than one base table through a view'').
INSERT INTO emp_dept (empno, ename, loc)
   VALUES (9010, 'KURODA', 'BOSTON');
An INSERT cannot implicitly or explicitly refer to columns of a non-key-pres
erved table. If the join view is defined using the WITH CHECK OPTION clause,
 then you cannot perform an INSERT to it.
Using the UPDATABLE_ COLUMNS Views
The views described in Table 13-1 can assist you when modifying join views.
Table 13-1 UPDATABLE_COLUMNS Views
View Name   Description
USER_UPDATABLE_COLUMNS
  Shows all columns in all tables and views in the user's schema that are mo
difiable.
DBA_UPDATABLE_COLUMNS
  Shows all columns in all tables and views in the DBA schema that are modif
iable.
ALL_UPDATABLE_VIEWS
  Shows all columns in all tables and views that are modifiable.
Replacing Views
Replacing Views
To replace a view, you must have all the privileges required to drop and cre
ate a view. If the definition of a view must change, the view must be replac
ed; you cannot alter the definition of a view. You can replace views in the
following ways:
You can drop and re-create the view.
----------------------------------------------------------------------------
----
warning:
When a view is dropped, all grants of corresponding object privileges are re
voked from roles and users. After the view is re-created, privileges must be
 re-granted.
----------------------------------------------------------------------------
----
You can redefine the view with a CREATE VIEW statement that contains the OR
REPLACE option. The OR REPLACE option replaces the current definition of a v
iew and preserves the current security authorizations. For example, assume t
hat you create the SALES_STAFF view as given in the previous example, and gr
ant several object privileges to roles and other users. However, now you nee
d to redefine the SALES_STAFF view to change the department number specified
 in the WHERE clause. You can replace the current version of the SALES_STAFF
 view with the following statement:
      CREATE OR REPLACE VIEW sales_staff AS
          SELECT empno, ename, deptno
          FROM emp
          WHERE deptno = 30
          WITH CHECK OPTION CONSTRAINT sales_staff_cnst;
Before replacing a view, consider the following effects:
Replacing a view replaces the view's definition in the data dictionary. All
underlying objects referenced by the view are not affected.
If a constraint in the CHECK OPTION was previously defined but not included
in the new view definition, the constraint is dropped.
All views and PL/SQL program units dependent on a replaced view become inval
id (not usable). See "Managing Object Dependencies" for more information on
how Oracle manages such dependencies.
Dropping Views
You can drop any view contained in your schema. To drop a view in another us
er's schema, you must have the DROP ANY VIEW system privilege. Drop a view u
sing the SQL command DROP VIEW. For example, the following statement drops a
 view named SALES_STAFF:
DROP VIEW sales_staff;
Managing Sequences
This section describes various aspects of managing sequences, and includes t
he following topics:
Creating Sequences
Creating Sequences
Altering Sequences
Initialization Parameters Affecting Sequences
Dropping Sequences
Creating Sequences
To create a sequence in your schema, you must have the CREATE SEQUENCE syste
m privilege; to create a sequence in another user's schema, you must have th
e CREATE ANY SEQUENCE privilege. Create a sequence using the SQL command CRE
ATE SEQUENCE. For example, the following statement creates a sequence used t
o generate employee numbers for the EMPNO column of the EMP table:
CREATE SEQUENCE emp_sequence
    INCREMENT BY 1
    START WITH 1
    NOMAXVALUE
    NOCYCLE
    CACHE 10;
The CACHE option pre-allocates a set of sequence numbers and keeps them in m
emory so that sequence numbers can be accessed faster. When the last of the
sequence numbers in the cache has been used, Oracle reads another set of num
bers into the cache.
Oracle might skip sequence numbers if you choose to cache a set of sequence
numbers. For example, when an instance abnormally shuts down (for example, w
hen an instance failure occurs or a SHUTDOWN ABORT statement is issued), seq
uence numbers that have been cached but not used are lost. Also, sequence nu
mbers that have been used but not saved are lost as well. Oracle might also
skip cached sequence numbers after an export and import; see Oracle8 Utiliti
es for details.
See Also: For information about how the Oracle Parallel Server affects cache
d sequence numbers, see Oracle8 Parallel Server Concepts and Administration.

For performance information on caching sequence numbers, see Oracle8 Tuning.

Altering Sequences
To alter a sequence, your schema must contain the sequence, or you must have
 the ALTER ANY SEQUENCE system privilege. You can alter a sequence to change
 any of the parameters that define how it generates sequence numbers except
the sequence's starting number. To change the starting point of a sequence,
drop the sequence and then re-create it. When you perform DDL on sequence nu
mbers you will lose the cache values.
Alter a sequence using the SQL command ALTER SEQUENCE. For example, the foll
owing statement alters the EMP_SEQUENCE:
ALTER SEQUENCE emp_sequence
    INCREMENT BY 10
    MAXVALUE 10000
    CYCLE
    CYCLE
    CACHE 20;
Initialization Parameters Affecting Sequences
The initialization parameter SEQUENCE_CACHE_ENTRIES sets the number of seque
nces that may be cached at any time. If auditing is enabled for your system,
 allow one additional sequence for the sequence to identify audit session nu
mbers.
If the value for SEQUENCE_CACHE_ENTRIES is too low, Oracle might skip sequen
ce values, as in the following scenario: assume you are using five cached se
quences, the cache is full, and SEQUENCE_CACHE_ENTRIES = 4. If four sequence
s are currently cached, then a fifth sequence replaces the least recently us
ed sequence in the cache and all remaining values (up to the last sequence n
umber cached) in the displaced sequence are lost.
Dropping Sequences
You can drop any sequence in your schema. To drop a sequence in another sche
ma, you must have the DROP ANY SEQUENCE system privilege. If a sequence is n
o longer required, you can drop the sequence using the SQL command DROP SEQU
ENCE. For example, the following statement drops the ORDER_SEQ sequence:
DROP SEQUENCE order_seq;
When a sequence is dropped, its definition is removed from the data dictiona
ry. Any synonyms for the sequence remain, but return an error when reference
d.
Managing Synonyms
Managing Synonyms
You can create both public and private synonyms. A public synonym is owned b
y the special user group named PUBLIC and is accessible to every user in a d
atabase. A private synonym is contained in the schema of a specific user and
 available only to the user and the user's grantees.
This section includes the following synonym management information:
Creating Synonyms
Dropping Synonyms
Creating Synonyms
To create a private synonym in your own schema, you must have the CREATE SYN
ONYM privilege; to create a private synonym in another user's schema, you mu
st have the CREATE ANY SYNONYM privilege. To create a public synonym, you mu
st have the CREATE PUBLIC SYNONYM system privilege.
Create a synonym using the SQL command CREATE SYNONYM. For example, the foll
owing statement creates a public synonym named PUBLIC_EMP on the EMP table c
ontained in the schema of JWARD:
CREATE PUBLIC SYNONYM public_emp FOR jward.emp;
Dropping Synonyms
You can drop any private synonym in your own schema. To drop a private synon
ym in another user's schema, you must have the DROP ANY SYNONYM system privi
lege. To drop a public synonym, you must have the DROP PUBLIC SYNONYM system
 privilege.
Drop a synonym that is no longer required using the SQL command DROP SYNONYM
. To drop a private synonym, omit the PUBLIC keyword; to drop a public synon
ym, include the PUBLIC keyword.
For example, the following statement drops the private synonym named EMP:
DROP SYNONYM emp;
The following statement drops the public synonym named PUBLIC_EMP:
DROP PUBLIC SYNONYM public_emp;
When you drop a synonym, its definition is removed from the data dictionary.
 All objects that reference a dropped synonym remain; however, they become i
nvalid (not usable).
See Also: For more information about how dropping synonyms can affect other
schema objects, see "Managing Object Dependencies".
----------------------------------------------------------------------------
----
------------------------------------------------------------------------

--
※ 来源:·BBS 水木清华站 bbs.net.tsinghua.edu.cn·[FROM: 162.105.41.166]

--
           海纳百川，
                   有容乃大，
                           壁立千尺，
                                   无欲则刚。    

※ 来源:·哈工大紫丁香 bbs.hit.edu.cn·[FROM: dip.hit.edu.cn]