Skip Headers

Oracle9i Application Developer's Guide - Fundamentals
Release 2 (9.2)

Part Number A96590-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

2
Managing Schema Objects

This chapter discusses the procedures necessary to create and manage the different types of objects contained in a user's schema. The topics include:

Managing Tables

A table is the data structure that holds data in a relational database. A table is composed of rows and columns.

A table can represent a single entity that you want to track within your system. This type of a table could represent a list of the employees within your organization, or the orders placed for your company's products.

A table can also represent a relationship between two entities. This type of a table could portray the association between employees and their job skills, or the relationship of products to orders. Within the tables, foreign keys are used to represent relationships.

Although some well designed tables could represent both an entity and describe the relationship between that entity and another entity, most tables should represent either an entity or a relationship. For example, the EMP_TAB table describes the employees in a firm, but this table also includes a foreign key column, DEPTNO, representing the relationships of employees to departments.

The following sections explain how to create, alter, and drop tables. Some simple guidelines to follow when managing tables in your database are included.

See Also:

The Oracle9i Database Administrator's Guide has more suggestions. You should also refer to a text on relational database or table design.

Designing Tables

Consider the following guidelines when designing your tables:

Before creating a table, you should also determine whether to use integrity constraints. Integrity constraints can be defined on the columns of a table to enforce the business rules of your database automatically.

See Also:

Creating Tables

To create a table, use the SQL command CREATE TABLE. For example, the following statement creates a non-clustered table named Emp_tab that is physically stored in the USERS tablespace. Notice that integrity constraints are defined on several columns of the table.

CREATE TABLE Emp_tab (
   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_afkey REFERENCES Dept_tab(Deptno))
   PCTFREE 10
   PCTUSED 40
   TABLESPACE users
   STORAGE (  INITIAL 50K
              NEXT 50K
              MAXEXTENTS 10
              PCTINCREASE 25 );

Managing Temporary Tables

Oracle8i provides a special kind of table to hold temporary data. You specify whether the data is specific to a session or to a transaction. When the session or transaction finishes, the rows that it inserted are deleted. Multiple sessions or transactions can use the same temporary table, and each session or transaction only sees the rows that it created.

Temporary tables are useful any time you want to buffer a result set or construct a result set by running multiple DML operations. Here are a few specific examples:

Creating Temporary Tables

You create a temporary table by using special ANSI keywords. You specify the data as session-specific by using the ON COMMIT PRESERVE ROWS keywords. You specify the data as transaction-specific by using the ON COMMIT DELETE ROWS keywords.

Example 2-1 Creating a Session-Specific Temporary Table

CREATE GLOBAL TEMPORARY TABLE ...
     [ON COMMIT PRESERVE ROWS ]

Example 2-2 Creating a Transaction-Specific Temporary Table

CREATE GLOBAL TEMPORARY TABLE ...
     [ON COMMIT DELETE ROWS ]

Using Temporary Tables

You can create indexes on temporary tables as you would on permanent tables.

For a session-specific temporary table, a session gets bound to the temporary table with the first insert in the table in the session. This binding goes away at the end of the session or by issuing a TRUNCATE of the table in the session.

For a transaction-specific temporary table, a session gets bound to the temporary table with the first insert in the table in the transaction. The binding goes away at the end of the transaction.

DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table.

Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed. This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, the table appears to be empty.

Temporary segments are deallocated at the end of the transaction for transaction-specific temporary tables and at the end of the session for session-specific temporary tables.

If you rollback a transaction, the data you entered is lost, although the table definition persists.

You cannot create a table that is simultaneously both transaction- and session-specific.

A transaction-specific temporary table allows only one transaction at a time. If there are several autonomous transactions in a single transaction scope, each autonomous transaction can use the table only as soon as the previous one commits.

Because the data in a temporary table is, by definition, temporary, backup and recovery of a temporary table's data is not available in the event of a system failure. To prepare for such a failure, you should develop alternative methods for preserving temporary table data.

Examples: Using Temporary Tables

Example: A Session-Specific Temporary Table

The following statement creates a session-specific temporary table, FLIGHT_SCHEDULE, for use in an automated airline reservation scheduling system. Each client has its own session and can store temporary schedules. The temporary schedules are deleted at the end of the session.

CREATE GLOBAL TEMPORARY TABLE flight_schedule (
   startdate DATE, 
   enddate DATE, 
   cost NUMBER)
ON COMMIT PRESERVE ROWS;

Example: Using Temporary Tables to Improve Performance

You can use temporary tables to improve performance when you run complex queries. Running multiple such queries is relatively slow because the tables are accessed multiple times for each returned row. It is faster to cache the values from a complex query in a temporary table, then run the queries against the temporary table.

For example, even with a view like this defined to simplify further queries, the queries against the view may be slow because the contents of the view are recalculated each time:

CREATE OR REPLACE VIEW Profile_values_view AS
SELECT d.Profile_option_name, d.Profile_option_id, Profile_option_value,
       u.User_name, Level_id, Level_code
  FROM Profile_definitions d, Profile_values v, Profile_users u
 WHERE d.Profile_option_id = v.Profile_option_id
   AND ((Level_code = 'USER' AND Level_id = U.User_id) OR
        (Level_code = 'DEPARTMENT' AND Level_id = U.Department_id) OR
        (Level_code = 'SITE'))
   AND NOT EXISTS (SELECT 1 FROM PROFILE_VALUES P
                    WHERE P.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID
                      AND ((Level_code = 'USER' AND
                            level_id = u.User_id) OR
                           (Level_code = 'DEPARTMENT' AND
                            level_id = u.Department_id) OR
                           (Level_code = 'SITE'))
                      AND INSTR('USERDEPARTMENTSITE', v.Level_code) >
                          INSTR('USERDEPARTMENTSITE', p.Level_code));

A temporary table allows us to run the computation once, and cache the result in later SQL queries and joins:

CREATE GLOBAL TEMPORARY TABLE Profile_values_temp
         (
             Profile_option_name   VARCHAR(60)   NOT NULL,
             Profile_option_id     NUMBER(4)     NOT NULL,
             Profile_option_value  VARCHAR2(20)  NOT NULL,
             Level_code            VARCHAR2(10)          ,
             Level_id              NUMBER(4)             ,
             CONSTRAINT Profile_values_temp_pk
                PRIMARY KEY (Profile_option_id)
         ) ON COMMIT PRESERVE ROWS ORGANIZATION INDEX;

INSERT INTO Profile_values_temp
       (Profile_option_name, Profile_option_id, Profile_option_value,
        Level_code, Level_id)
SELECT Profile_option_name, Profile_option_id, Profile_option_value,
        Level_code, Level_id
  FROM Profile_values_view;
COMMIT;

Now the temporary table can be used to speed up queries, and the results cached in the temporary table are freed automatically by the database when the session ends.

Tip: Referencing the Same Subquery Multiple Times

In complex queries that process the same subquery multiple times, you might be tempted to store the subquery results in a temporary table and perform additional queries against the temporary table. The WITH clause lets you factor out the subquery, give it a name, then reference that name multiple times within the original complex query.

This technique lets the optimizer choose how to deal with the subquery results -- whether to create a temporary table or inline it as a view.

For example, the following query joins two tables and computes the aggregate SUM(SAL) more than once. The bold text represents the parts of the query that are repeated.

SELECT dname, SUM(sal) AS dept_total
 FROM emp, dept
  WHERE emp.deptno = dept.deptno
  GROUP BY dname HAVING
    SUM(sal) >
   (
      SELECT SUM(sal) * 1/3
  FROM emp, dept
        WHERE emp.deptno = dept.deptno
   )
   ORDER BY SUM(sal) DESC;

You can improve the query by doing the subquery once, and referencing it at the appropriate points in the main query. The bold text represents the common parts of the subquery, and the places where the subquery is referenced.

WITH 
summary AS
(
 SELECT dname, SUM(sal) AS dept_total
 FROM emp, dept
 WHERE emp.deptno = dept.deptno
 GROUP BY dname
)
SELECT dname, dept_total 
 FROM summary 
  WHERE dept_total >
(
  SELECT SUM(dept_total) * 1/3
 FROM summary
)
ORDER BY dept_total DESC;

See Also:

Managing Views

A view is a logical representation of another table or combination of tables. A view derives its data from the tables on which it is based. These tables are called base tables. Base tables might in turn be actual tables or might be views themselves.

All operations performed on a view actually affect the base table of the view. You can use views in almost the same way as tables. You can query, update, insert into, and delete from views, just as you can standard tables.

Views can provide a different representation (such as subsets or supersets) of the data that resides within other tables and views. Views are very powerful because they allow you to tailor the presentation of data to different types of users.

The following sections explain how to create, replace, and drop views using SQL commands.

Creating Views

Use the SQL command CREATE VIEW to create a view. For example, the following statement creates a view on a subset of data in the EMP_TAB table:

CREATE VIEW Sales_staff AS
    SELECT Empno, Ename, Deptno
    FROM Emp_tab
    WHERE Deptno = 10
    WITH CHECK OPTION CONSTRAINT Sales_staff_cnst;

The object names are resolved when the view is created or when the program containing the SQL is compiled, relative to the schema of the view owner.

You can define views with any query that references tables, snapshots, or other views.

The query that defines the SALES_STAFF view references only rows in department 10. Furthermore, WITH CHECK OPTION creates the view with the constraint that INSERT and UPDATE statements issued against the view are not allowed to create or result in rows that the view cannot select.

Considering the example above, the following INSERT statement successfully inserts a row into the EMP_TAB table through the SALES_STAFF view:

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 not 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_tab and Dept_tab tables:

CREATE VIEW Division1_staff AS
    SELECT Ename, Empno, Job, Dname
    FROM Emp_tab, Dept_tab
    WHERE Emp_tab.Deptno IN (10, 30)
    AND Emp_tab.Deptno = Dept_tab.Deptno;

The Division1_staff view is defined by a query that joins information from the Emp_tab and Dept_tab tables. The WITH CHECK OPTION is not specified in the CREATE VIEW statement because rows cannot be inserted into or updated in a view defined with a query that contains a join that uses the WITH CHECK OPTION.

Expansion of Defining Queries at View Creation Time

In accordance with the ANSI/ISO standard, Oracle expands any wildcard in a top-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 account for the possibility that the columns of the base object were originally entered with quotes and require them for the query to be syntactically correct.

As an example, assume that the Dept_view view is created as follows:

CREATE VIEW Dept_view AS SELECT * FROM scott.Dept_tab;

Oracle stores the defining query of the Dept_view view as

SELECT "DEPTNO", "DNAME", "LOC" FROM scott.Dept_tab;

Views created with errors do not have wildcards expanded. However, if the view is eventually compiled without errors, then wildcards in the defining query are expanded.

Creating Views with Errors

A view can be created even if the defining query of the view cannot be executed, as long as the CREATE VIEW command has no syntax errors. We call such a view a view with errors. For example, if a view refers to a non-existent table or an invalid column of an existing table, or if the owner of the view does not have the required privileges, then the view can still be created and entered into the data dictionary.

You can only create a view with errors by using the FORCE option of the CREATE VIEW command:

CREATE FORCE VIEW AS ...;

When a view is created with errors, Oracle returns a message and leaves the status of the view as INVALID. If conditions later change so that the query of an invalid view can be executed, then the view can be recompiled and become valid. Oracle dynamically compiles the invalid view if you attempt to use it.

Privileges Required to Create a View

To create a view, you must have been granted the following privileges:

Replacing Views

To alter the definition of a view, you must replace the view using one of the following methods:

Replacing a view has the following effects:

Privileges Required to Replace a View

To replace a view, you must have all of the privileges necessary to drop the view, as well as all of those required to create the view.

Using Views in Queries

Views can be queried in the same manner as tables. For example, to query the Division1_staff view, enter a valid SELECT statement that references the view:

SELECT * FROM Division1_staff;
ENAME        EMPNO       JOB             DNAME
------------------------------------------------------
CLARK         7782       MANAGER         ACCOUNTING
KING          7839       PRESIDENT       ACCOUNTING
MILLER        7934       CLERK           ACCOUNTING
ALLEN         7499       SALESMAN        SALES
WARD          7521       SALESMAN        SALES
JAMES         7900       CLERK           SALES
TURNER        7844       SALESMAN        SALES
MARTIN        7654       SALESMAN        SALES
BLAKE         7698       MANAGER         SALES

With some restrictions, rows can be inserted into, updated in, or deleted from a base table using a view. The following statement inserts a new row into the EMP_TAB table using the SALES_STAFF view:

INSERT INTO Sales_staff
    VALUES (7954, 'OSTER', 30);

Restrictions on DML operations for views use the following criteria in the order listed:

  1. If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the view.
  2. If a view is defined with WITH CHECK OPTION, then a row cannot be inserted into, or updated in, the base table (using the view), if the view cannot select the row from the base table.
  3. If a NOT NULL column that does not have a DEFAULT clause is omitted from the view, then a row cannot be inserted into the base table using the view.
  4. If the view was created by using an expression, such as DECODE(deptno, 10, "SALES", ...), then rows cannot be inserted into or updated in the base table using the view.

The constraint created by WITH CHECK OPTION of the SALES_STAFF view only allows rows that have a department number of 10 to be inserted into, or updated in, the EMP_TAB table. Alternatively, assume that the SALES_STAFF view is defined by the following statement (that is, excluding the DEPTNO column):

CREATE VIEW Sales_staff AS
    SELECT Empno, Ename
    FROM Emp_tab
    WHERE Deptno = 10
    WITH CHECK OPTION CONSTRAINT Sales_staff_cnst;

Considering this view definition, you can update the EMPNO or ENAME fields of existing records, but you cannot insert rows into the EMP_TAB table through the SALES_STAFF view because the view does not let you alter the DEPTNO field. If you had defined a DEFAULT value of 10 on the DEPTNO field, then you could perform inserts.

Referencing Invalid Views

When a user attempts to reference an invalid view, Oracle returns an error message to the user:

ORA-04063: view 'view_name' has errors

This error message is returned when a view exists but is unusable due to errors in its query (whether it had errors when originally created or it was created successfully but became unusable later because underlying objects were altered or dropped).

Privileges Required to Use a View

To issue a query or an INSERT, UPDATE, or DELETE statement against a view, you must have the SELECT, INSERT, UPDATE, or DELETE object privilege for the view, respectively, either explicitly or through a role.

Dropping Views

Use the SQL command DROP VIEW to drop a view. For example:

DROP VIEW Sales_staff;

Privileges Required to Drop a View

You can drop any view contained in your schema. To drop a view in another user's schema, you must have the DROP ANY VIEW system privilege.

Modifying a Join View

Oracle allows you, with some restrictions, to modify views that involve joins. Consider the following simple view:

CREATE VIEW Emp_view AS
    SELECT Ename, Empno, deptno FROM Emp_tab;

This view does not involve a join operation. If you issue the SQL statement:

UPDATE Emp_view SET Ename = 'CAESAR' WHERE Empno = 7839;

then the EMP_TAB base table that underlies the view changes, and employee 7839's name changes from KING to CAESAR in the EMP_TAB table.

However, if you create a view that involves a join operation, such as:

CREATE VIEW Emp_dept_view AS
  SELECT e.Empno, e.Ename, e.Deptno, e.Sal, d.Dname, d.Loc
    FROM Emp_tab e, Dept_tab d    /* JOIN operation */
     WHERE e.Deptno = d.Deptno
       AND d.Loc IN ('DALLAS', 'NEW YORK', 'BOSTON');

then there are restrictions on modifying either the EMP_TAB or the DEPT_TAB base table through this view, for example, using a statement such as:

UPDATE Emp_dept_view SET Ename = 'JOHNSON' 
    WHERE Ename = 'SMITH';

A modifiable join view is a view that contains more than one table in the top-level FROM clause of the SELECT statement, and that does not contain any of the following:

A further restriction on which join views are modifiable is that if a view is a join on other nested views, then the other nested views must be mergeable into the top level view.

See Also:

Oracle9i Database Concepts for more information about mergeable views.

"Modifying Complex Views (INSTEAD OF Triggers)" for a way to simulate updating a join view by writing a customized trigger.

Scenario for Modifying a Join View

The examples in this section use the EMP_TAB and DEPT_TAB tables. However, the examples work only if you explicitly define the primary and foreign keys in these tables, or define unique indexes. Here are the appropriately constrained table definitions for EMP_TAB and DEPT_TAB:

CREATE TABLE Dept_tab (
        Deptno   NUMBER(4) PRIMARY KEY,
        Dname    VARCHAR2(14),
        Loc      VARCHAR2(13));

CREATE TABLE Emp_tab (
        Empno    NUMBER(4) PRIMARY KEY,
        Ename    VARCHAR2(10),
        Job      varchar2(9),
        Mgr      NUMBER(4),
        Hiredate DATE,
        Sal      NUMBER(7,2),
        Comm     NUMBER(7,2),
        Deptno   NUMBER(2),
FOREIGN KEY (Deptno) REFERENCES Dept_tab(Deptno));

You could also omit the primary and foreign key constraints listed above, and create a UNIQUE INDEX on DEPT_TAB (DEPTNO) to make the following examples work.

About Key-Preserved Tables

The concept of a key-preserved table is fundamental to understanding the restrictions 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 table 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 the table. For example, if in the EMP_TAB table there was at most one employee in each department, then DEPT_TAB.DEPTNO would be unique in the result of a join of EMP_TAB and DEPT_TAB, but DEPT_TAB would still not be a key-preserved table.

If you SELECT all rows from EMP_DEPT_VIEW defined in the "Modifying a Join View" section, then 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_TAB is a key-preserved table, because EMPNO is a key of the EMP_TAB table, and also a key of the result of the join. DEPT_TAB is not a key-preserved table, because although DEPTNO is a key of the DEPT_TAB table, it is not a key of the join.

Rule for DML Statements on Join Views

Any UPDATE, INSERT, or DELETE statement on a join view can modify only one underlying base table.

Updating a Join View

The following example shows an UPDATE statement that successfully modifies the EMP_DEPT_VIEW view:

UPDATE Emp_dept_view
  SET Sal = Sal * 1.10 
    WHERE Deptno = 10;

The following UPDATE statement would be disallowed on the EMP_DEPT_VIEW view:

UPDATE Emp_dept_view
  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 underlying DEPT_TAB table, and the DEPT_TAB table is not key preserved in the EMP_DEPT view.

In general, all modifiable columns of a join view must map to columns of a key-preserved table. If the view is defined using the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are not modifiable.

So, for example, if the EMP_DEPT view were defined using WITH CHECK OPTION, then the following UPDATE statement would fail:

UPDATE Emp_dept_view
    SET Deptno = 10
        WHERE Ename = 'SMITH';

The statement fails because it is trying to update a join column.

Deleting from a Join View

You can delete from a join view provided there is one and only one key-preserved table in the join.

The following DELETE statement works on the EMP_DEPT view:

DELETE FROM Emp_dept_view
    WHERE Ename = 'SMITH';

This DELETE statement on the EMP_DEPT view is legal because it can be translated to a DELETE operation on the base EMP_TAB table, and because the EMP_TAB table is the only key-preserved table in the join.

In the following view, a DELETE operation cannot be performed on the view because both E1 and E2 are key-preserved tables:

CREATE VIEW emp_emp AS
    SELECT e1.Ename, e2.Empno, e1.Deptno
        FROM Emp_tab e1, Emp_tab e2
        WHERE e1.Empno = e2.Empno;
        WHERE e1.Empno = e2.Empno;

If a view is defined using the WITH CHECK OPTION clause and the key-preserved table is repeated, then rows cannot be deleted from such a view. For example:

CREATE VIEW Emp_mgr AS
    SELECT e1.Ename, e2.Ename Mname
       FROM Emp_tab e1, Emp_tab e2
            WHERE e1.mgr = e2.Empno
            WITH CHECK OPTION;

No deletion can be performed on this view because the view involves a self-join of the table that is key preserved.

Inserting into a Join View

The following INSERT statement on the EMP_DEPT view succeeds, because only one key-preserved base table is being modified (EMP_TAB), and 40 is a valid DEPTNO in the DEPT_TAB table (thus satisfying the FOREIGN KEY integrity constraint on the EMP_TAB table).

INSERT INTO Emp_dept (Ename, Empno, Deptno)
    VALUES ('KURODA', 9010, 40);

The following INSERT statement fails for the same reason: This UPDATE on the base EMP_TAB table would fail: the FOREIGN KEY integrity constraint on the EMP_TAB table is violated.

INSERT INTO Emp_dept (Ename, Empno, Deptno)
    VALUES ('KURODA', 9010, 77);

The following INSERT statement fails with an ORA-01776 error ("cannot modify more than one base table through a view").

INSERT INTO Emp_dept (Ename, Empno, Deptno)
    VALUES (9010, 'KURODA', 'BOSTON');

An INSERT cannot, implicitly or explicitly, refer to columns of a non-key-preserved 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

Three views you can use for modifying join views are shown in Table 2-1.

ViuVie
Table 2-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 modifiable

DBA_UPDATABLE_COLUMNS

Shows all columns in all tables and views in the DBA schema that are modifiable

ALL_UPDATABLE_COLUMNS

Shows all columns in all tables and views that are modifiable

Outer Joins

Views that involve outer joins are modifiable in some cases. For example:

CREATE VIEW Emp_dept_oj1 AS
    SELECT Empno, Ename, e.Deptno, Dname, Loc
    FROM Emp_tab e, Dept_tab d
    WHERE e.Deptno = d.Deptno (+);

The statement:

SELECT * FROM Emp_dept_oj1;

Results in:

EMPNO   ENAME      DEPTNO  DNAME          LOC         
------- ---------- ------- -------------- -------------
7369    SMITH      40      OPERATIONS     BOSTON       
7499    ALLEN      30      SALES          CHICAGO      
7566    JONES      20      RESEARCH       DALLAS       
7654    MARTIN     30      SALES          CHICAGO      
7698    BLAKE      30      SALES          CHICAGO      
7782    CLARK      10      ACCOUNTING     NEW YORK     
7788    SCOTT      20      RESEARCH       DALLAS       
7839    KING       10      ACCOUNTING     NEW YORK     
7844    TURNER     30      SALES          CHICAGO      
7876    ADAMS      20      RESEARCH       DALLAS       
7900    JAMES      30      SALES          CHICAGO      
7902    FORD       20      RESEARCH       DALLAS       
7934    MILLER     10      ACCOUNTING     NEW YORK     
7521    WARD       30      SALES          CHICAGO      
14 rows selected.

Columns in the base EMP_TAB table of EMP_DEPT_OJ1 are modifiable through the view, because EMP_TAB is a key-preserved table in the join.

The following view also contains an outer join:

CREATE VIEW Emp_dept_oj2 AS
SELECT e.Empno, e.Ename, e.Deptno, d.Dname, d.Loc
FROM Emp_tab e, Dept_tab d
WHERE e.Deptno (+) = d.Deptno;

The statement:

SELECT * FROM Emp_dept_oj2;

Results in:

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
7499       ALLEN      30        SALES          CHICAGO
7698       BLAKE      30        SALES          CHICAGO
7654       MARTIN     30        SALES          CHICAGO
7900       JAMES      30        SALES          CHICAGO
7844       TURNER     30        SALES          CHICAGO
7521       WARD       30        SALES          CHICAGO
                                OPERATIONS     BOSTON
15 rows selected.

In this view, EMP_TAB is no longer a key-preserved table, because the EMPNO column in the result of the join can have nulls (the last row in the SELECT above). So, UPDATE, DELETE, and INSERT operations cannot be performed on this view.

In the case of views containing an outer join on other nested views, a table is key preserved if the view or views containing the table are merged into their outer views, all the way to the top. A view which is being outer-joined is currently merged only if it is "simple." For example:

SELECT Col1, Col2, ... FROM T;

The select list of the view has no expressions, and there is no WHERE clause.

Consider the following set of views:

CREATE VIEW Emp_v AS
    SELECT Empno, Ename, Deptno
        FROM Emp_tab;
CREATE VIEW Emp_dept_oj1 AS
    SELECT e.*, Loc, d.Dname
        FROM Emp_v e, Dept_tab d
            WHERE e.Deptno = d.Deptno (+);

In these examples, EMP_V is merged into EMP_DEPT_OJ1 because EMP_V is a simple view, and so EMP_TAB is a key-preserved table. But if EMP_V is changed as follows:

CREATE VIEW Emp_v_2 AS
    SELECT Empno, Ename, Deptno
        FROM Emp_tab
            WHERE Sal > 1000;

Then, because of the presence of the WHERE clause, EMP_V_2 cannot be merged into EMP_DEPT_OJ1, and hence EMP_TAB is no longer a key-preserved table.

If you are in doubt whether a view is modifiable, then you can SELECT from the view USER_UPDATABLE_COLUMNS to see if it is. For example:

SELECT * FROM USER_UPDATABLE_COLUMNS WHERE TABLE_NAME = 'EMP_DEPT_VIEW';

This might return:

OWNER       TABLE_NAME      COLUMN_NAM      UPD
----------  ----------      ----------      ---
SCOTT       EMP_DEPT_V      EMPNO           NO
SCOTT       EMP_DEPT_V      ENAME           NO
SCOTT       EMP_DEPT_V      DEPTNO          NO
SCOTT       EMP_DEPT_V      DNAME           NO
SCOTT       EMP_DEPT_V      LOC             NO
5 rows selected.

Managing Sequences

The sequence generator generates sequential numbers, which can help to generate unique primary keys automatically, and to coordinate keys across multiple rows or tables.

Without sequences, sequential values can only be produced programmatically. A new primary key value can be obtained by selecting the most recently produced value and incrementing it. This method requires a lock during the transaction and causes multiple users to wait for the next value of the primary key; this waiting is known as serialization. If you have such constructs in your applications, then you should replace them with access to sequences. Sequences eliminate serialization and improve the concurrency of your application.

The following sections explain how to create, alter, use, and drop sequences using SQL commands.

Creating Sequences

Use the SQL command CREATE SEQUENCE to create a sequence. The following statement creates a sequence used to generate employee numbers for the EMPNO column of the EMP_TAB table:

CREATE SEQUENCE Emp_sequence
    INCREMENT BY 1
    START WITH 1
    NOMAXVALUE
    NOCYCLE
    CACHE 10;

Notice that several parameters can be specified to control the function of sequences. You can use these parameters to indicate whether the sequence is ascending or descending, the starting point of the sequence, the minimum and maximum values, and the interval between sequence values. The NOCYCLE option indicates that the sequence cannot generate more values after reaching its maximum or minimum value.

The CACHE option of the CREATE SEQUENCE command pre-allocates a set of sequence numbers and keeps them in memory so that they can be accessed faster. When the last of the sequence numbers in the cache have been used, another set of numbers is read into the cache.

See Also:

For additional implications for caching sequence numbers when using Oracle Real Application Clusters, see Oracle9i Parallel Server Documentation Set: Oracle8i Parallel Server Concepts; Oracle8i Parallel Server Setup and Configuration Guide; Oracle8i Parallel Server Administration, Deployment, and Performance.

General information about caching sequence numbers is included in "Caching Sequence Numbers".

Privileges Required to Create a Sequence

To create a sequence in your schema, you must have the CREATE SEQUENCE system privilege. To create a sequence in another user's schema, you must have the CREATE ANY SEQUENCE privilege.

Altering Sequences

You can change any of the parameters that define how corresponding sequence numbers are generated; however, you cannot alter a sequence to change the starting number of a sequence. To do this, you must drop and re-create the sequence.

Use the SQL command ALTER SEQUENCE to alter a sequence. For example:

ALTER SEQUENCE Emp_sequence
INCREMENT BY 10
    MAXVALUE 10000
    CYCLE
    CACHE 20;

Privileges Required to Alter a Sequence

To alter a sequence, your schema must contain the sequence, or you must have the ALTER ANY SEQUENCE system privilege.

Using Sequences

Once a sequence is defined, it can be accessed and incremented by multiple users with no waiting. Oracle does not wait for a transaction that has incremented a sequence to complete before that sequence can be incremented again.

The examples outlined in the following sections show how sequences can be used in master/detail table relationships. Assume an order entry system is partially comprised of two tables, ORDERS_TAB (master table) and LINE_ITEMS_TAB (detail table), that hold information about customer orders. A sequence named ORDER_SEQ is defined by the following statement:

CREATE SEQUENCE Order_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOCYCLE
    CACHE 20;

Referencing a Sequence

A sequence is referenced in SQL statements with the NEXTVAL and CURRVAL pseudocolumns; each new sequence number is generated by a reference to the sequence's pseudocolumn NEXTVAL, while the current sequence number can be repeatedly referenced using the pseudo-column CURRVAL.

NEXTVAL and CURRVAL are not reserved words or keywords and can be used as pseudo-column names in SQL statements such as SELECTs, INSERTs, or UPDATEs.

Generating Sequence Numbers with NEXTVAL

To generate and use a sequence number, reference seq_name.NEXTVAL. For example, assume a customer places an order. The sequence number can be referenced in a values list. For example:

INSERT INTO Orders_tab (Orderno, Custno)
    VALUES (Order_seq.NEXTVAL, 1032);

Or, the sequence number can be referenced in the SET clause of an UPDATE statement. For example:

UPDATE Orders_tab
    SET Orderno = Order_seq.NEXTVAL
    WHERE Orderno = 10112;

The sequence number can also be referenced outermost SELECT of a query or subquery. For example:

SELECT Order_seq.NEXTVAL FROM dual;

As defined, the first reference to ORDER_SEQ.NEXTVAL returns the value 1. Each subsequent statement that references ORDER_SEQ.NEXTVAL generates the next sequence number (2, 3, 4,. . .). The pseudo-column NEXTVAL can be used to generate as many new sequence numbers as necessary. However, only a single sequence number can be generated for each row. In other words, if NEXTVAL is referenced more than once in a single statement, then the first reference generates the next number, and all subsequent references in the statement return the same number.

Once a sequence number is generated, the sequence number is available only to the session that generated the number. Independent of transactions committing or rolling back, other users referencing ORDER_SEQ.NEXTVAL obtain unique values. If two users are accessing the same sequence concurrently, then the sequence numbers each user receives might have gaps because sequence numbers are also being generated by the other user.

Using Sequence Numbers with CURRVAL

To use or refer to the current sequence value of your session, reference seq_name.CURRVAL. CURRVAL can only be used if seq_name.NEXTVAL has been referenced in the current user session (in the current or a previous transaction). CURRVAL can be referenced as many times as necessary, including multiple times within the same statement. The next sequence number is not generated until NEXTVAL is referenced. Continuing with the previous example, you would finish placing the customer's order by inserting the line items for the order:

INSERT INTO Line_items_tab (Orderno, Partno, Quantity)
    VALUES (Order_seq.CURRVAL, 20321, 3);

INSERT INTO Line_items_tab (Orderno, Partno, Quantity)
    VALUES (Order_seq.CURRVAL, 29374, 1);

Assuming the INSERT statement given in the previous section generated a new sequence number of 347, both rows inserted by the statements in this section insert rows with order numbers of 347.

Uses and Restrictions of NEXTVAL and CURRVAL

CURRVAL and NEXTVAL can be used in the following places:

CURRVAL and NEXTVAL cannot be used in these places:

Caching Sequence Numbers

Sequence numbers can be kept in the sequence cache in the System Global Area (SGA). Sequence numbers can be accessed more quickly in the sequence cache than they can be read from disk.

The sequence cache consists of entries. Each entry can hold many sequence numbers for a single sequence.

Follow these guidelines for fast access to all sequence numbers:

The Number of Entries in the Sequence Cache

When an application accesses a sequence in the sequence cache, the sequence numbers are read quickly. However, if an application accesses a sequence that is not in the cache, then the sequence must be read from disk to the cache before the sequence numbers are used.

If your applications use many sequences concurrently, then your sequence cache might not be large enough to hold all the sequences. In this case, access to sequence numbers might often require disk reads. For fast access to all sequences, be sure your cache has enough entries to hold all the sequences used concurrently by your applications.

The Number of Values in Each Sequence Cache Entry

When a sequence is read into the sequence cache, sequence values are generated and stored in a cache entry. These values can then be accessed quickly. The number of sequence values stored in the cache is determined by the CACHE parameter in the CREATE SEQUENCE statement. The default value for this parameter is 20.

This CREATE SEQUENCE statement creates the SEQ2 sequence so that 50 values of the sequence are stored in the SEQUENCE cache:

CREATE SEQUENCE Seq2
    CACHE 50;

The first 50 values of SEQ2 can then be read from the cache. When the 51st value is accessed, the next 50 values will be read from disk.

Choosing a high value for CACHE lets you access more successive sequence numbers with fewer reads from disk to the sequence cache. However, if there is an instance failure, then all sequence values in the cache are lost. Cached sequence numbers also could be skipped after an export and import if transactions continue to access the sequence numbers while the export is running.

If you use the NOCACHE option in the CREATE SEQUENCE statement, then the values of the sequence are not stored in the sequence cache. In this case, every access to the sequence requires a disk read. Such disk reads slow access to the sequence. This CREATE SEQUENCE statement creates the SEQ3 sequence so that its values are never stored in the cache:

CREATE SEQUENCE Seq3
    NOCACHE;

Privileges Required to Use a Sequence

To use a sequence, your schema must contain the sequence or you must have been granted the SELECT object privilege for another user's sequence.

Dropping Sequences

To drop a sequence, use the SQL command DROP SEQUENCE. For example, the following statement drops the ORDER_SEQ sequence:

DROP SEQUENCE Order_seq;

When you drop a sequence, its definition is removed from the data dictionary. Any synonyms for the sequence remain, but return an error when referenced.

Privileges Required to Drop a Sequence

You can drop any sequence in your schema. To drop a sequence in another schema, you must have the DROP ANY SEQUENCE system privilege.

Managing Synonyms

A synonym is an alias for a table, view, snapshot, sequence, procedure, function, package, or object type. Synonyms let you refer to objects from other schemas without including the schema qualifier. The following sections explain how to create, use, and drop synonyms using SQL commands.

Creating Synonyms

Use the SQL command CREATE SYNONYM to create a synonym. The following statement creates a public synonym named PUBLIC_EMP on the EMP_TAB table contained in the schema of JWARD:

CREATE PUBLIC SYNONYM Public_emp FOR jward.Emp_tab;

Privileges Required to Create a Synonym

You must have the CREATE SYNONYM system privilege to create a private synonym in your schema, or the CREATE ANY SYNONYM system privilege to create a private synonym in another user's schema. To create a public synonym, you must have the CREATE PUBLIC SYNONYM system privilege.

Using Synonyms in DML Statements

A synonym can be referenced in a DML statement the same way that the underlying object of the synonym can be referenced. For example, if a synonym named EMP_TAB refers to a table or view, then the following statement is valid:

INSERT INTO Emp_tab (Empno, Ename, Job)
    VALUES (Emp_sequence.NEXTVAL, 'SMITH', 'CLERK');

If the synonym named FIRE_EMP refers to a standalone procedure or package procedure, then you could execute it in SQL*Plus or Enterprise Manager with the command

EXECUTE Fire_emp(7344);

You can also use synonyms for GRANT and REVOKE statements, but not with other DML statements.

Privileges Required to Use a Synonym

You can successfully use any private synonym contained in your schema or any public synonym, assuming that you have the necessary privileges to access the underlying object, either explicitly, from an enabled role, or from PUBLIC. You can also reference any private synonym contained in another schema if you have been granted the necessary object privileges for the private synonym. You can only reference another user's synonym using the object privileges that you have been granted. For example, if you have the SELECT privilege for the JWARD.EMP_TAB synonym, then you can query the JWARD.EMP_TAB synonym, but you cannot insert rows using the synonym for JWARD.EMP_TAB.

Dropping Synonyms

To drop a synonym, use the SQL command DROP SYNONYM. To drop a private synonym, omit the PUBLIC keyword; to drop a public synonym, include the PUBLIC keyword. The following statement drops the private synonym named EMP_TAB:

DROP SYNONYM Emp_tab;

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 (for example, views and procedures) but become invalid.

Privileges Required to Drop a Synonym

You can drop any private synonym in your own schema. To drop a private synonym in another user's schema, you must have the DROP ANY SYNONYM system privilege. To drop a public synonym, you must have the DROP PUBLIC SYNONYM system privilege.

Creating Multiple Tables and Views in One Operation

You can create several tables and views and grant privileges in one operation using the SQL command CREATE SCHEMA. The CREATE SCHEMA command is useful if you want to guarantee the creation of several tables and views and grants in one operation; if an individual table or view creation fails or a grant fails, then the entire statement is rolled back, and none of the objects are created or the privileges granted.

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_tab
        WHERE Deptno = 30  WITH CHECK OPTION CONSTRAINT
               Sales_staff_cnst

CREATE TABLE Dept_tab (
    Deptno      NUMBER(3) PRIMARY KEY,
    Dname       VARCHAR2(15),
    Loc         VARCHAR2(25))
CREATE TABLE Emp_tab (
    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_tab(Deptno))

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

To create schema objects, such as multiple tables, using the CREATE SCHEMA command, you must have the required privileges for any included operation.

Naming Schema Objects

You should decide when you want to use partial and complete global object names in the definition of views, synonyms, and procedures. Keep in mind that database names should be stable, and databases should not be unnecessarily moved within a network.

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 schema object in the database then has a global object name consisting of the schema object name and the global database name.

Because Oracle ensures that the schema 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, because it is usually the DBA who is responsible for assigning database names.

Rules for Name Resolution in SQL Statements

An object name takes the following form:

[schema.]name[@database]

Some examples include:

Emp_tab
Scott.Emp_tab
Scott.Emp_tab@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_TAB.

Based on the context of an object name, Oracle searches the appropriate namespace when resolving the name to an object. For example, in the following 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, then no further work is necessary.
  3. If the name resolves to a private synonym, then replace the name with the definition of the synonym and return to step 1.
  4. If the name was originally qualified with a schema, then 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, then 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 Also:

See Oracle9i Database Concepts for more information about name resolution in a distributed database.

Renaming Schema Objects

If necessary, you can rename some schema objects using two different methods: drop and re-create the object, or rename the object using the SQL command RENAME.


Note:

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


If you use the RENAME command to rename a table, view, sequence, or a private synonym of a table, view, or sequence, then grants made for the object are carried forward for the new name, and the next 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:

Privileges Required to Rename an Object

To rename an object, you must be the owner of the object.

Switching to a Different Schema

The following statement sets the current schema of the session to the schema name given in the statement.

ALTER SESSION SET CURRENT_SCHEMA = <schema name>

Subsequent SQL statements use this schema name for the schema qualifier when the qualifier is missing. Note that the session still has only the privileges of the current user and does not acquire any extra privileges by the above 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 the SELECT statement.

Listing Information about Schema Objects

The data dictionary provides many views that provide information about schema objects. The following is a summary of the views associated with schema objects:

Example 1: Listing Different Schema Objects by Type

The following query lists all of the objects owned by the user issuing the query:

SELECT Object_name, Object_type FROM User_objects;

The query above might return results similar to the following:

OBJECT_NAME               OBJECT_TYPE
------------------------- -------------------
EMP_DEPT                  CLUSTER
EMP_TAB                   TABLE
DEPT_TAB                  TABLE
EMP_DEPT_INDEX            INDEX
PUBLIC_EMP                SYNONYM
EMP_MGR                   VIEW

Example 2: Listing Column Information

Column information, such as name, datatype, length, precision, scale, and default data values, can be listed using one of the views ending with the _COLUMNS suffix. For example, the following query lists all of the default column values for the EMP_TAB and DEPT_TAB tables:

SELECT Table_name, Column_name, Data_default
    FROM User_tab_columns
    WHERE Table_name = 'DEPT_TAB' OR Table_name = 'EMP_TAB';

Considering the example statements at the beginning of this section, a display similar to the one below is displayed:

TABLE_NAME  COLUMN_NAME     DATA_DEFAULT
----------  --------------- --------------------
DEPT_TAB    DEPTNO
DEPT_TAB    DNAME
DEPT_TAB    LOC             ('NEW YORK')
EMP_TAB     EMPNO
EMP_TAB     ENAME
EMP_TAB     JOB
EMP_TAB     MGR
EMP_TAB     HIREDATE        (sysdate)
EMP_TAB     SAL
EMP_TAB     COMM
EMP_TAB     DEPTNO


Note:

Not all columns have a user-specified default. These columns assume NULL when rows that do not specify values for these columns are inserted.


Example 3: Listing Dependencies of Views and Synonyms

When you create a view or a synonym, the view or synonym is based on its underlying base object. The _DEPENDENCIES data dictionary views can be used to reveal the dependencies for a view and the _SYNONYMS data dictionary views can be used to list the base object of a synonym. For example, the following query lists the base objects for the synonyms created by the user JWARD:

SELECT Table_owner, Table_name
    FROM All_synonyms
    WHERE Owner = 'JWARD';

This query could return information similar to the following:

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------
SCOTT                          DEPT_TAB
SCOTT                          EMP_TAB


Go to previous page Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback