Oracle8i Application Developer's Guide - Fundamentals Release 8.1.5 A68003-01 |
|
This chapter discusses the procedures necessary to create and manage the different types of objects contained in a user's schema. The topics include:
Other information about managing schema objects can be found in the following locations:
See Also:
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
, which represents 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 Oracle8i Administrator's Guide has more suggestions. You should also refer to a text on relational database or table design. |
Consider the following guidelines when designing your tables:
COMMENT
command.
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.
To create a table, use the SQL command CREATE
TABLE
. For example, if the user SCOTT
issues the following statement, he creates a non-clustered table named Emp_tab
in his schema 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 );
The following sections explain how to use the PCTFREE
and PCTUSED
parameters to do the following:
The PCTFREE
default is 10 percent; any integer from 0 to 99 is acceptable, as long as the sum of PCTFREE
and PCTUSED
does not exceed 100. (If PCTFREE
is set to 99, then Oracle puts at least one row in each block, regardless of row size. If the rows are very small and blocks very large, then even more than one row might fit.)
A lower PCTFREE
:
UPDATE
, DELETE
, and SELECT
statements might need to read more blocks for a given row and because chained row pieces contain references to other pieces)
A higher PCTFREE
:
In setting PCTFREE
, you should understand the nature of the table or index data. Updates can cause rows to grow. When using NUMBER
, VARCHAR2
, LONG
, or LONG RAW
, new values might not be the same size as values they replace. If there are many updates in which data values get longer, then increase PCTFREE
; if updates to rows do not affect the total row width, then PCTFREE
can be low.
Your goal is to find a satisfactory trade-off between densely packed data (low PCTFREE
, full blocks) and good update performance (high PCTFREE
, less-full blocks).
PCTFREE
also affects the performance of a given user's queries on tables with uncommitted transactions belonging to other users. Assuring read consistency might cause frequent reorganization of data in blocks that have little free space.
If the data in the rows of a non-clustered table is likely to increase in size over time, then reserve space for these updates. If you do not reserve room for updates, then updated rows are likely to be chained between blocks, reducing I/O performance associated with these rows.
The discussion for non-clustered tables also applies to clustered tables. However, if PCTFREE
is reached, then new rows from any table contained in the same cluster key go into a new data block chained to the existing cluster key.
Indexes infrequently require the use of free space for updates to index data. Therefore, the PCTFREE
value for index segment data blocks is normally very low (for example, 5 or less).
Once the percentage of free space in a data block reaches PCTFREE
, no new rows are inserted in that block until the percentage of space used falls below PCTUSED
. Oracle tries to keep a data block at least PCTUSED
full. The percent is of block space available for data after overhead is subtracted from total space.
The default for PCTUSED
is 40 percent; any integer between 0 and 99, inclusive, is acceptable as long as the sum of PCTUSED
and PCTFREE
does not exceed 100.
A lower PCTUSED
:
PCTUSED
UPDATE
and DELETE
statements for moving a block to the free list when the block has fallen below that percentage of usage
A higher PCTUSED
:
PCTUSED
INSERTs
and UPDATEs
If you decide not to use the default values for PCTFREE
and PCTUSED
, then use the following guidelines.
PCTFREE
and PCTUSED
must be equal to or less than 100.
PCTFREE
and PCTUSED
that differs from 100 by the percentage of space in the available block that an average row occupies. For example, assume that the data block size is 2048 bytes, minus 100 bytes of overhead, leaving 1948 bytes available for data. If an average row requires 195 bytes, or 10% of 1948, then an appropriate combination of PCTUSED
and PCTFREE
that sums to 90% would make the best use of database space.
PCTFREE
free space, and the processing costs are highest.
PCTUSED
or PCTFREE
.
PCTFREE
and PCTUSED
(as in PCTUSED
of 75, PCTFREE
of 20), the more efficient space usage is at some performance cost.
The following examples illustrate correctly specifying values for PCTFREE
and PCTUSED
in given scenarios.
To create a new table in your schema, you must have the CREATE
TABLE
system privilege. To create a table in another user's schema, you must have the CREATE
ANY
TABLE
system privilege. Additionally, the owner of the table must have a quota for the tablespace that contains the table, or the UNLIMITED
TABLESPACE
system privilege.
Alter a table in an Oracle database for any of the following reasons:
NOT
NULL
integrity constraint)
PCTFREE
, PCTUSED
)
INITRANS
, MAXTRANS
)
NEXT
, PCTINCREASE
, etc.)
When altering the column definitions of a table, you can only increase the length of an existing column, unless the table has no records. You can also decrease the length of a column in an empty table. For columns of datatype CHAR
, increasing the length of a column might be a time consuming operation that requires substantial additional storage, especially if the table contains many rows. This is because the CHAR
value in each row must be blank-padded to satisfy the new column length.
If you change the datatype (for example, from VARCHAR2
to CHAR
), then the data in the column does not change. However, the length of new CHAR
columns might change, due to blank-padding requirements.
Use the SQL command ALTER
TABLE
to alter a table. For example:
ALTER TABLE Emp_tab PCTFREE 30 PCTUSED 60;
Altering a table has the following implications:
NOT
NULL
constraint to a table only if the table does not contain any rows.
To alter a table, the table must be contained in your schema, or you must have either the ALTER
object privilege for the table or the ALTER
ANY
TABLE
system privilege.
Use the SQL command DROP
TABLE
to drop a table. For example, the following statement drops the EMP_TAB
table:
DROP TABLE Emp_tab;
If the table that you are dropping contains any primary or unique keys referenced by foreign keys of other tables, and if you intend to drop the FOREIGN
KEY
constraints of the child tables, then include the CASCADE
option in the DROP
TABLE
command. For example:
DROP TABLE Emp_tab CASCADE CONSTRAINTS;
Dropping a table has the following effects:
If you want to delete all of the rows of a table, but keep the table definition, then you should use the TRUNCATE
TABLE
command.
To drop a table, the table must be contained in your schema or you must have the DROP
ANY
TABLE
system privilege.
A temporary table has a definition or structure that persists like that of a regular table, but the data it contains exists only for the duration of a transaction or session. Oracle8i allows you to create temporary tables to hold session-private data. You specify whether the data is specific to a session or to a transaction.
Here are a few examples of when temporary tables can be useful:
During your session, the data you enter is private. When you end your session, the optional itineraries you developed are dropped.
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.
CREATE GLOBAL TEMPORARY TABLE ... [ON COMMIT PRESERVE ROWS ]
CREATE GLOBAL TEMPORARY TABLE ... [ON COMMIT DELETE ROWS ]
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.
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;
This example shows how you can use temporary tables to improve performance when you run complex queries. In this example, you create four conventional tables, then run SQL statements against them. The example compares the way you would conventionally run SQL statements on those tables with the way you could run them using temporary tables. In the former case, the performance is relatively slow because the process requires hitting the table multiple times for each row returned. In the latter case, efficiency increases because you use temporary tables to cache the values from a more complex query, then run SQL statements against those temporary tables.
CREATE TABLE Profile_departments ( Department_id NUMBER(4) not null, Department_name VARCHAR2(20) not null, CONSTRAINT profile_departments_pk PRIMARY KEY (department_id) ); CREATE UNIQUE INDEX Profile_departments_u1 ON Profile_departments (Department_name); INSERT INTO Profile_departments (Department_id, Department_name) VALUES (3001, 'Accounting'); INSERT INTO Profile_departments (Department_id, Department_name) VALUES (3002, 'Marketing'); COMMIT;
The above script yields the following:
|
|
|
|
CREATE TABLE Profile_users ( User_id NUMBER(4) not null, User_name VARCHAR2(20) not null, Department_id NUMBER(4) not null, CONSTRAINT Profile_users_pk PRIMARY KEY (User_id) ); CREATE UNIQUE INDEX Profile_users_u1 ON Profile_users (User_name); INSERT INTO Profile_users (User_id, User_name, Department_id) VALUES (2001, 'John Doe', 3001); INSERT INTO Profile_users (User_id, User_name, Department_id) VALUES (2002, 'Jane Doe', 3002); INSERT INTO Profile_users (User_id, User_name, Department_id) VALUES (2003, 'Bill Smith', 3002); COMMIT;
The above script yields the following:
2001
John Doe
3001 (Accounting)
2002
Jane Doe
3002 (Marketing)
2003
Bill Smith
3002 (Marketing)
CREATE TABLE Profile_definitions ( Profile_option_id NUMBER(4) Not Null, Profile_option_name VARCHAR2(20) not null, CONSTRAINT Profile_definitions_pk PRIMARY KEY (Profile_option_id) ); CREATE UNIQUE INDEX Profile_definitions_u1 ON Profile_definitions (Profile_option_name); INSERT INTO Profile_definitions (Profile_option_id, Profile_option_name) VALUES (1001, 'Printer'); INSERT INTO Profile_definitions (Profile_option_id, Profile_option_name) VALUES (1002, 'Mail Database'); COMMIT;
The above script yields the following:
1001 |
Printer |
1002 |
Mail Database |
CREATE TABLE Profile_values ( Profile_option_id NUMBER(4) not null, Level_code VARCHAR2(10) not null, Level_id NUMBER(4) not null, Profile_option_value VARCHAR2(20) not null, CONSTRAINT profile_values_pk PRIMARY KEY (Profile_option_id,level_code,level_id), CONSTRAINT Profile_values_c1 CHECK (Level_code IN ('USER','DEPARTMENT','SITE')) ) ORGANIZATION INDEX; INSERT INTO Profile_values (Profile_option_id, Level_code, Level_id, Profile_option_value) VALUES (1001, 'DEPARTMENT', 3001, 'ACCT-LPT'); INSERT INTO Profile_values (Profile_option_id, Level_code, Level_id, Profile_option_value) VALUES (1001, 'DEPARTMENT', 3002, 'MKTG-LPT'); INSERT INTO Profile_values (Profile_option_id, Level_code, Level_id, Profile_option_value) VALUES (1001, 'USER', 2003, 'SMITH-LPT'); INSERT INTO Profile_values (Profile_option_id, Level_code, Level_id, Profile_option_value) VALUES (1002, 'SITE', 0, 'mail0'); INSERT INTO Profile_values (Profile_option_id, Level_code, Level_id, Profile_option_value) VALUES (1002, 'DEPARTMENT', 3001, 'mail1'); INSERT INTO Profile_values (Profile_option_id, Level_code, Level_id, Profile_option_value) VALUES (1002, 'USER', 2002, 'mail2'); COMMIT;
The above script creates the following table:
SELECT d.Profile_option_name, Profile_option_value, Level_id, Level_code FROM Profile_definitions d, Profile_values v, Profile_users u WHERE d.Profile_option_id = v.Profile_option_id AND u.User_name = 'John Doe' AND ((Level_code = 'USER' and level_id = U.User_id) OR (Level_code = 'DEPARTMENT' and Level_id = U.Department_id) OR (Level_code = 'SITE')) ORDER BY D.Profile_option_name, INSTR('USERDEPARTMENTSITE', Level_code);
The above script yields the following table. Note that there are multiple possible settings for the mail database, at different levels of the hierarchy.
|
|
|
|
|
|
|
|
|
|
|
|
Similar queries (shown below) for Jane Doe and Bill Smith, respectively:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Each shows multiple possible values for any particular parameter at different hierarchical levels.
SELECT d.Profile_option_name, Profile_option_value, Level_id, Level_code FROM Profile_definitions d, Profile_values v, Profile_users u WHERE d.Profile_option_id = v.Profile_option_id AND u.User_name = 'Jane Doe' AND ((Level_code = 'USER' and Level_id = u.User_id) OR (Level_code = 'DEPARTMENT' and Level_id = u.Department_id) OR (Level_code = 'SITE')) ORDER BY d.Profile_option_name, INSTR('USERDEPARTMENTSITE', Level_code); SELECT d.Profile_option_name, Profile_option_value, Level_id, Level_code FROM PROFILE_DEFINITIONS D, PROFILE_VALUES V, PROFILE_USERS U WHERE D.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID AND U.USER_NAME = 'Bill Smith' AND ((Level_code = 'USER' AND Level_id = u.User_id) OR (Level_code = 'DEPARTMENT' AND Level_id = u.Department_id) OR (Level_code = 'SITE')) ORDER BY D.Profile_option_name, instr('USERDEPARTMENTSITE', Level_code);
To produce a query that shows only the relevant (lowest-level) setting requires a complex sub-query. This sub-query reduces performance because it hits the table multiple times for each row produced.
The following script creates a view that produces the correct output:
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));
You can see from the following query that the values for each parameter are found at different levels of the hierarchy:
SELECT v.User_name, p.Profile_option_name, v.Profile_option_value, v.Level_code FROM Profile_definitions p, Profile_values_view v WHERE p.Profile_option_id = v.Profile_option_id ORDER BY v.User_name, p.Profile_option_name;
The above script yields the following:
This view can be queried to find the values for a user, as in:
SELECT Profile_option_name, Profile_option_value FROM Profile_values_view WHERE User_name = 'John Doe' ORDER BY Profile_option_name;
The above query yields the following:
Mail Database |
mail1 |
Printer |
ACCT-LPT |
This would be inefficient and complex to use if, for example, the parameters are used in other SQL statements; in effect, the data is re-calculated repeatedly rather than being calculated once and cached.
A temporary table would allow us to run the computation once, and still use the result in later SQL joins. For example:
DROP TABLE Profile_values_temp; CREATE TABLE Profile_values_temp ( 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) ) ORGANIZATION INDEX; INSERT INTO Profile_values_temp (Profile_option_id, Profile_option_value, Level_code, Level_id) SELECT Profile_option_id, Profile_option_value, Level_code, Level_id FROM Profile_values_view WHERE User_name = 'John Doe'; COMMIT;
By doing this, the application has computed and cached the results of the complex query into the temporary table.
Now the temporary table can be used in another SQL statement with high performance, and the application programmer can be certain that the results cached in the temporary table are freed automatically by the database when the session ends.
SELECT p.Profile_option_name, t.Profile_option_value, t.Level_code, NVL(u.User_name,NVL(d.Department_name,'site')) Level_value FROM Profile_definitions p, Profile_values_temp t, Profile_departments d, Profile_users u WHERE P.PROFILE_OPTION_ID = T.PROFILE_OPTION_ID AND T.Level_id = d.Department_id(+) AND T.Level_id = u.User_id(+) ORDER BY Profile_option_name;
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.
Use the SQL command CREATE
VIEW
to create a view. You can define views with any query that references tables, snapshots, or other views; however, the query that defines a view cannot contain the ORDER
BY
or FOR
UPDATE
clauses. 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 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 via 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
.
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.
Assuming no syntax errors, a view can be created (with errors) even if the defining query of the view cannot be executed. For example, if a view is created that 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 that indicates the view was created with errors. The status of such a view is left 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.
To create a view, you must have been granted the following privileges:
CREATE
VIEW
system privilege to create a view in your schema, or the CREATE
ANY
VIEW
system privilege to create a view in another user's schema. These privileges can be acquired explicitly or via a role.
INSERT
privilege for Scott's EMP_TAB
table, then you can create a view on his EMP_TAB
table, but you can only use this view to insert new rows into the EMP_TAB
table.
GRANT
OPTION
or the system privileges with the ADMIN
OPTION
; if not, then the view owner has insufficient privileges to grant access to the view to other users.
To alter the definition of a view, you must replace the view using one of the following methods:
CREATE
VIEW
statement that contains the OR
REPLACE
option. This option replaces the current definition of a view, but preserves the present security authorizations.
For example, assume that you create the SALES_STAFF
view, as given in a previous example. You also grant several object privileges to roles and other users. However, now you realize that you must redefine the SALES_STAFF
view to correct the department number specified in the WHERE
clause of the defining query, because it should have been 30. To preserve the grants of object privileges that you have made, 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_tab WHERE Deptno = 30 WITH CHECK OPTION CONSTRAINT Sales_staff_cnst;
Replacing a view has the following effects:
WITH
CHECK
OPTION
for a view's definition is dropped.
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.
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:
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.
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.
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.
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 via 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.
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).
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 via a role.
Use the SQL command DROP
VIEW
to drop a view. For example:
DROP VIEW Sales_staff;
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.
The Oracle Server 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:
DISTINCT
operator
AVG
, COUNT
, GLB
, MAX
, MIN
, STDDEV
, SUM
, or VARIANCE
UNION
, UNION
ALL
, INTERSECT
, MINUS
GROUP
BY
or HAVING
clauses
START
WITH
or CONNECT
BY
clauses
ROWNUM
pseudocolumn
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.
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.
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.
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.
Any UPDATE
, INSERT
, or DELETE
statement on a join view can modify only one underlying base table.
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:
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.
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.
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.
Three views you can use for modifying join views are shown in Table 3-1.
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 ASSELECT Empno, Ename, DeptnoFROM Emp_tab;CREATE VIEW Emp_dept_oj1 ASSELECT e.*, Loc, d.DnameFROM Emp_v e, Dept_tab dWHERE 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 ASSELECT Empno, Ename, DeptnoFROM Emp_tabWHERE 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.
The sequence generator generates sequential numbers. Sequence number generation is useful to generate unique primary keys for your data 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.
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_sequenceINCREMENT 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 the Oracle Parallel Server, see Oracle8i Parallel Server Concepts and Administration. General information about caching sequence numbers is included in "Caching Sequence Numbers". |
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.
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, the sequence must be dropped and re-created.
Use the SQL command ALTER
SEQUENCE
to alter a sequence. For example:
ALTER SEQUENCE Emp_sequenceINCREMENT BY 10 MAXVALUE 10000 CYCLE CACHE 20;
To alter a sequence, your schema must contain the sequence, or you must have the ALTER
ANY
SEQUENCE
system privilege.
The following sections provide some information on how to use a sequence once it has been defined. Once defined, a sequence can be made available to many users. A sequence 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_seqSTART WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 20;
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 SELECT
s, INSERT
s, or UPDATE
s.
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_tabSET 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 per 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.
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.
CURRVAL
and NEXTVAL
can be used in the following places:
VALUES
clause of INSERT
statements
SELECT
list of a SELECT
statement
SET
clause of an UPDATE
statement
CURRVAL
and NEXTVAL
cannot be used in these places:
SELECT
statement with the DISTINCT
operator
SELECT
statement with a GROUP
BY
or ORDER
BY
clause
SELECT
statement that is combined with another SELECT
statement with the UNION,
INTERSECT
, or MINUS
set operator
WHERE
clause of a SELECT
statement
DEFAULT
value of a column in a CREATE
TABLE
or ALTER
TABLE
statement
CHECK
constraint
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:
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 entries in the sequence cache is determined by the initialization parameter SEQUENCE_CACHE_ENTRIES
. The default value for this parameter is 10 entries. Oracle creates and uses sequences internally for auditing, grants of system privileges, grants of object privileges, profiles, debugging stored procedures, and labels. Be sure your sequence cache has enough entries to hold these sequences as well as sequences used by your applications.
If the value for your SEQUENCE_CACHE_ENTRIES
parameter is too low, then it is possible to skip sequence values. For example, assume that this parameter is set to 4, and that you currently have four cached sequences. If you create a fifth sequence, then it will replace the least recently used sequence in the cache. All of the remaining values in this displaced sequence are lost. In other words, if the displaced sequence originally held 10 cached sequence values, and only one had been used, then nine would be lost when the sequence was displaced.
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 Seq2CACHE 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
allows you to 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 Seq3NOCACHE;
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.
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.
You can drop any sequence in your schema. To drop a sequence in another schema, you must have the DROP
ANY
SEQUENCE
system privilege.
A synonym is an alias for a table, view, snapshot, sequence, procedure, function, or package. The following sections explain how to create, use, and drop synonyms using SQL commands.
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;
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.
A synonym can be referenced in a SQL 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 stand-alone procedure or package procedure, then you could execute it in SQL*Plus or Enterprise Manager with the command
EXECUTE Fire_emp(7344);
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
.
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.
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.
The following sections explain miscellaneous topics regarding the management of the various schema objects discussed in this chapter.
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 scottCREATE VIEW Sales_staff ASSELECT Empno, Ename, Sal, Comm FROM Emp_tab WHERE Deptno = 30 WITH CHECK OPTION CONSTRAINTSales_staff_cnstCREATE 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 NULLCONSTRAINT 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).
To create schema objects, such as multiple tables, using the CREATE
SCHEMA
command, you must have the required privileges for any included operation.
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.
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:
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 Oracle8i Concepts for more information about name resolution in a distributed database. |
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
.
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:
To rename an object, you must be the owner of the object.
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>
Any subsequent SQL statements will 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.
The data dictionary provides many views that provide information about schema objects. The following is a summary of the views associated with schema objects:
ALL_OBJECTS
, USER_OBJECTS
ALL_CATALOG
, USER_CATALOG
ALL_TABLES
, USER_TABLES
ALL_TAB_COLUMNS
, USER_TAB_COLUMNS
ALL_TAB_COMMENTS
, USER_TAB_COMMENTS
ALL_COL_COMMENTS
, USER_COL_COMMENTS
ALL VIEWS
, USER_VIEWS
ALL_INDEXES
, USER_INDEXES
ALL_IND_COLUMNS
, USER_IND_COLUMNS
USER_CLUSTERS
USER_CLU_COLUMNS
ALL_SEQUENCES
, USER_SEQUENCES
ALL_SYNONYMS
, USER_SYNONYMS
ALL_DEPENDENCIES
, USER_DEPENDENCIES
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
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_defaultFROM 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
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_nameFROM All_synonyms WHERE Owner = 'JWARD';
This query could return information similar to the following:
TABLE_OWNER TABLE_NAME
------------------------------ ------------
SCOTT DEPT_TAB
SCOTT EMP_TAB