Oracle8 SQL Reference 
Release 8.0 
A58225-01
 
Library
 
Product
 
Contents
 
Index
 

 


Filespec

Purpose

For illustrations, see "Examples".

Prerequisites

A filespec can appear in CREATE DATABASE, ALTER DATABASE, CREATE TABLESPACE, or ALTER TABLESPACE commands. You must have the privileges necessary to issue one of these commands. For information on these privileges, see CREATE DATABASE, ALTER DATABASE, CREATE TABLESPACE, and ALTER TABLESPACE.

Syntax


 

Keywords and Parameters

'filename' 

is the name of either a datafile or a redo log file member. A 'filename' can only contain single-byte such as characters from 7-bit ASCII or EBCDIC character sets. Multibyte characters are not valid. 

 

A redo log file group can have one or more members, or copies. Each 'filename' must be fully specified according to the conventions for your operating system. 

SIZE integer 

specifies the size of the file. If you omit this parameter, the file must already exist. Note that the tablespace size must be one block greater than the sum of the sizes of the objects contained in it. You can use K or M to specify the size in kilobytes or megabytes. 

REUSE 

allows Oracle to reuse an existing file. If the file already exists, Oracle verifies that its size matches the value of the SIZE parameter. If the file does not exist, Oracle creates it. If you omit this option, the file must not already exist and Oracle creates the file. 

 

The REUSE option is significant only when used with the SIZE option. If you omit the SIZE option, Oracle expects the file to exist already.  

Note: Whenever Oracle uses an existing file, the file's previous contents are lost. 

 

Examples

Example I

The following statement creates a database named PAYABLE that has two redo log file groups, each with two members, and one datafile:

CREATE DATABASE payable 
   LOGFILE GROUP 1 ('diska:log1.log', 'diskb:log1.log') SIZE 50K, 
           GROUP 2 ('diska:log2.log', 'diskb:log2.log') SIZE 50K 
   DATAFILE 'diskc:dbone.dat' SIZE 30M; 

The first filespec in the LOGFILE clause specifies a redo log file group with the GROUP value 1. This group has members named 'DISKA:LOG1.LOG' and 'DISKB:LOG1.LOG', each with size 50 kilobytes.

The second filespec in the LOGFILE clause specifies a redo log file group with the GROUP value 2. This group has members named 'DISKA:LOG2.LOG' and 'DISKB:LOG2.LOG', also with sizes of 50 kilobytes.

The filespec in the DATAFILE clause specifies a datafile named 'DISKC:DBONE.DAT' of size 30 megabytes.

All of these filespecs specify a value for the SIZE parameter and omit the REUSE option, so none of these files can already exist. Oracle must create them.

Example II

The following statement adds another redo log file group with two members to the PAYABLE database:

ALTER DATABASE payable 
   ADD LOGFILE GROUP 3 ('diska:log3.log', 'diskb:log3.log') 
   SIZE 50K REUSE; 

The filespec in the ADD LOGFILE clause specifies a new redo log file group with the GROUP value 3. This new group has members named 'DISKA:LOG3.LOG' and 'DISKB:LOG3.LOG' with sizes of 50 kilobytes each. Since the filespec specifies the REUSE option, each member can already exist. If a member exists, it must have a size of 50 kilobytes. If it does not exist, Oracle creates it with that size.

Example III

The following statement creates a tablespace named STOCKS that has three datafiles:

CREATE TABLESPACE stocks 
   DATAFILE 'diskc:stock1.dat', 
            'diskc:stock2.dat',
            'diskc:stock3.dat'; 

The filespecs for the datafiles specifies files named 'DISKC:STOCK1.DAT', 'DISKC:STOCK2.DAT', and 'DISKC:STOCK3.DAT'. Since each filespec omits the SIZE parameter, each file must already exist.

Example IV

The following statement alters the STOCKS tablespace and adds a new datafile:

ALTER TABLESPACE stocks 
   ADD DATAFILE 'diskc:stock4.dat' REUSE; 

The filespec specifies a datafile named 'DISKC:STOCK4.DAT'. Since the filespec omits the SIZE parameter, the file must already exist and the REUSE option is not significant.

Related Topics

CREATE DATABASE
ALTER DATABASE
CREATE TABLESPACE
ALTER TABLESPACE

GRANT (System Privileges and Roles)

Purpose

To grant system privileges and roles to users and roles. To grant object privileges, use the GRANT command (Object Privileges) described in the next section of this chapter. For more information, see "Granting System Privileges and Roles". For illustrations, see "Examples".


Note:  

Descriptions of commands and clauses preceded by  are available only if the Oracle objects option is installed on your database server. 


 
 

Prerequisites

To grant a system privilege, you must either have been granted the system privilege with the ADMIN OPTION or have been granted GRANT ANY PRIVILEGE system privilege.

To grant a role, you must either have been granted the role with the ADMIN OPTION or have been granted GRANT ANY ROLE system privilege, or you must have created the role. See also "Other Authorization Methods".

Syntax

 

Keywords and Parameters

system_priv 

is a system privilege to be granted. 

role 

is a role to be granted. 

TO 

identifies users or roles to which system privileges and roles are granted. 

PUBLIC 

grants system privileges or roles to all users. 

WITH ADMIN OPTION 

grant the system privilege or role to other users or roles. If you grant a role with ADMIN OPTION, the grantee can also alter or drop the role. See also "Granting the ADMIN OPTION"

 

Granting System Privileges and Roles

Use this form of the GRANT command to grant both system privileges and roles to users, roles, and PUBLIC. Table 4-10 indicates which user or role can be given which authorizations:

Table 4-10 DB GRANTS Allowed

User Type -> ___________ Type of Role  Grant to User Identified by Password  Grant to User Identified Externally  Grant to User Identified Globally  Grant to Local Role  Grant to External Role  Grant to Global Role 

Privileges 

Yes 

Yes 

Yes 

Yes 

Yes 

Yes 

Local Role 

Yes 

Yes 

Yes 

Yes 

Yes 

Yes 

External Role 

Yes 

Yes 

No 

Yes 

Yes 

No 

Global Role 

No 

No 

No 

No 

No 

No 

 

If you grant a privilege to a user, Oracle adds the privilege to the user's privilege domain. The user can immediately exercise the privilege.

If you grant a privilege to a role, Oracle adds the privilege to the role's privilege domain. Users who have been granted and have enabled the role can immediately exercise the privilege. Other users who have been granted the role can enable the role and exercise the privilege.

If you grant a privilege to PUBLIC, Oracle adds the privilege to the privilege domains of each user. All users can immediately perform operations authorized by the privilege.

If you grant a role to a user, Oracle makes the role available to the user. The user can immediately enable the role and exercise the privileges in the role's privilege domain.

If you grant a role to another role, Oracle adds the granted role's privilege domain to the grantee role's privilege domain. Users who have been granted the grantee role can enable it and exercise the privileges in the granted role's privilege domain.

If you grant a role to PUBLIC, Oracle makes the role available to all users. All users can immediately enable the role and exercise the privileges in the roles privilege domain.

In addition, the following restrictions apply:

Table 4-11 lists system privileges and the operations that they authorize. You can grant any of these system privileges with the GRANT command.

Table 4-11 System Privileges

System Privilege  Allows grantee to . . . 

ALTER ANY CLUSTER 

alter any cluster in any schema 

ALTER ANY INDEX 

alter any index in any schema 

ALTER ANY PROCEDURE 

alter any stored procedure, function, or package in any schema 

ALTER ANY ROLE 

alter any role in the database 

ALTER ANY SEQUENCE 

alter any sequence in the database 

ALTER ANY SNAPSHOT 

alter any snapshot in the database 

ALTER ANY TABLE 

alter any table or view in the schema 

ALTER ANY TYPE 

alter any type in any schema 

ALTER ANY TRIGGER 

enable, disable, or compile any database trigger in any schema 

ALTER DATABASE 

alter the database 

ALTER PROFILE 

alter profiles 

ALTER RESOURCE COST 

set costs for session resources 

ALTER ROLLBACK SEGMENT 

alter rollback segments 

ALTER SESSION 

issue ALTER SESSION statements 

ALTER SYSTEM 

issue ALTER SYSTEM statements 

ALTER TABLESPACE 

alter tablespaces 

ALTER USER 

alter any user. This privilege authorizes the grantee to 

  • change another user's password or authentication method, 
  • assign quotas on any tablespace, 
  • set default and temporary tablespaces, and 
  • assign a profile and default roles 
 

ANALYZE ANY 

analyze any table, cluster, or index in any schema 

AUDIT ANY 

audit any object in any schema using AUDIT (Schema Objects) statements 

AUDIT SYSTEM 

issue AUDIT (SQL Statements) statements 

BACKUP ANY TABLE 

use the Export utility to incrementally export objects from the schema of other users 

BECOME USER 

become another user. (Required by any user performing a full database import.) 

COMMENT ANY TABLE 

Comment on any table, view, or column in any schema 

CREATE ANY CLUSTER 

create a cluster in any schema. Behaves similarly to CREATE ANY TABLE. 

CREATE ANY DIRECTORY 

create a directory database object in any schema 

CREATE ANY INDEX 

create an index in any schema on any table in any schema 

CREATE ANY LIBRARY 

create external procedure/function libraries in any schema 

CREATE ANY PROCEDURE 

create stored procedures, functions, and packages in any schema 

CREATE ANY SEQUENCE 

create a sequence in any schema 

CREATE ANY SNAPSHOT 

create snapshots in any schema 

CREATE ANY SYNONYM 

create private synonyms in any schema 

CREATE ANY TABLE 

create tables in any schema. The owner of the schema containing the table must have space quota on the tablespace to contain the table. 

CREATE ANY TRIGGER 

create a database trigger in any schema associated with a table in any schema 

CREATE ANY TYPE 

create types and type bodies in any schema 

CREATE ANY VIEW 

create views in any schema 

CREATE CLUSTER 

create clusters in grantee's schema 

CREATE DATABASE LINK 

create private database links in grantee's schema 

CREATE ANY LIBRARY 

create external procedure/function libraries in grantee's schema 

CREATE PROCEDURE 

create stored procedures, functions, and packages in grantee's schema 

CREATE PROFILE 

create profiles 

CREATE PUBLIC DATABASE LINK 

create public database links 

CREATE PUBLIC SYNONYM 

create public synonyms 

CREATE ROLE 

create roles 

CREATE ROLLBACK SEGMENT 

create rollback segments 

CREATE SEQUENCE 

create sequences in grantee's schema 

CREATE SESSION 

connect to the database 

CREATE SNAPSHOT 

create snapshots in grantee's schema 

CREATE SYNONYM 

create synonyms in grantee's schema 

CREATE TABLE 

create tables in grantee's schema. To create a table, the grantee must also have space quota on the tablespace to contain the table. 

CREATE TABLESPACE 

create tablespaces 

CREATE TRIGGER 

create a database trigger in grantee's schema 

CREATE TYPE 

create types and type bodies in grantee's schema 

CREATE USER 

create users. This privilege also allows the creator to 

  • assign quotas on any tablespace, 
  • set default and temporary tablespaces, and 
  • assign a profile as part of a CREATE USER statement. 
 

CREATE VIEW 

create views in grantee's schema 

DELETE ANY TABLE 

  • delete rows from tables or views in any schema 
  • truncate tables in any schema 
 

DROP ANY CLUSTER 

drop clusters in any schema 

DROP ANY DIRECTORY 

drop directory database objects 

DROP ANY INDEX 

drop indexes in any schema 

DROP ANY LIBRARY 

drop external procedure/function libraries in any schema 

DROP ANY PROCEDURE 

drop stored procedures, functions, or packages in any schema 

DROP ANY ROLE 

drop roles 

DROP ANY SEQUENCE 

drop sequences in any schema 

DROP ANY SNAPSHOT 

drop snapshots in any schema 

DROP ANY SYNONYM 

drop private synonyms in any schema 

DROP ANY TABLE 

drop tables in any schema 

DROP ANY TRIGGER 

drop database triggers in any schema 

DROP ANY TYPE 

drop object types and object type bodies in any schema 

DROP ANY VIEW 

drop views in any schema 

DROP LIBRARY 

drop external procedure/function libraries 

DROP PROFILE 

drop profiles 

DROP PUBLIC DATABASE LINK 

drop public database links 

DROP PUBLIC SYNONYM 

drop public synonyms 

DROP ROLLBACK SEGMENT 

drop rollback segments 

DROP TABLESPACE 

drop tablespaces 

DROP USER 

drop users 

EXECUTE ANY PROCEDURE 

  • execute procedures or functions (standalone or packaged) 
  • reference public package variables in any schema 
 

EXECUTE ANY TYPE 

use and reference object types, and invoke methods of any type in any schema. You must grant EXECUTE ANY TYPE to a specific user. You cannot grant EXECUTE ANY TYPE to a role. 

FORCE ANY TRANSACTION 

  • force the commit or rollback of any in-doubt distributed transaction in the local database. 
  • induce the failure of a distributed transaction. 
 

FORCE TRANSACTION 

force the commit or rollback of grantee's in-doubt distributed transactions in the local database 

GRANT ANY PRIVILEGE 

grant any system privilege. 

GRANT ANY ROLE 

grant any role in the database 

INSERT ANY TABLE 

insert rows into tables and views in any schema 

LOCK ANY TABLE 

lock tables and views in any schema 

MANAGE TABLESPACE 

take tablespaces offline and online and begin and end tablespace backups 

RESTRICTED SESSION 

logon after the instance is started using the Server Manager STARTUP RESTRICT command 

SELECT ANY SEQUENCE 

reference sequences in any schema 

SELECT ANY TABLE 

query tables, views, or snapshots in any schema 

SYSDBA 

  • perform Server Manager STARTUP and SHUTDOWN commands, 
  • ALTER DATABASE OPEN/MOUNT/BACKUP, 
  • CREATE DATABASE, 
  • ARCHIVELOG and RECOVERY and 
  • includes the RESTRICTED SESSION privilege. 
 

SYSOPER 

  • perform Server Manager STARTUP and SHUTDOWN commands, 
  • ALTER DATABASE OPEN/MOUNT/BACKUP, 
  • ARCHIVELOG and RECOVERY 
  • includes the RESTRICTED SESSION privilege. 
 

UNLIMITED TABLESPACE 

use an unlimited amount of any tablespace. This privilege overrides any specific quotas assigned. If you revoke this privilege from a user, the grantee's schema objects remain but further tablespace allocation is denied unless authorized by specific tablespace quotas. You cannot grant this system privilege to roles. 

UPDATE ANY TABLE 

update rows in tables and views in any schema 

 

Granting the ADMIN OPTION

A grant with the ADMIN OPTION supersedes a previous identical grant without the ADMIN OPTION. If you grant a system privilege or role to a user without the ADMIN OPTION, and then subsequently grant the privilege or role to the user with the ADMIN OPTION, the user has the ADMIN OPTION on the privilege or role.

A grant without the ADMIN OPTION does not supersede a previous grant with the ADMIN OPTION. To revoke the ADMIN OPTION on a system privilege or role from a user, you must revoke the privilege or role from the user altogether and then grant the privilege or role to the user without the ADMIN OPTION.

Other Authorization Methods

You can authorize database users to use roles through means other than the database and the GRANT statement. For example, some operating systems have facilities that grant operating system privileges to operating system users. You can use such facilities to grant roles to Oracle users with the initialization parameter OS_ROLES. If you choose to grant roles to users through operating system facilities, you cannot also grant roles to users with the GRANT command, although you can use the GRANT command to grant system privileges to users and system privileges and roles to other roles.

For information about other authorization methods, see Oracle8 Administrator's Guide.

Examples

Example I

To grant the CREATE SESSION system privilege to RICHARD, allowing RICHARD to log on to Oracle, issue the following statement:

GRANT CREATE SESSION 
TO richard;
Example II

To grant the CREATE TABLE system privilege to the role TRAVEL_AGENT, issue the following statement:

GRANT CREATE TABLE 
TO travel_agent; 

TRAVEL_AGENT's privilege domain now contains the CREATE TABLE system privilege.

The following statement grants the TRAVEL_AGENT role to the EXECUTIVE role:

GRANT travel_agent 
TO executive; 

TRAVEL_AGENT is now granted to EXECUTIVE. EXECUTIVE's privilege domain contains the CREATE TABLE system privilege.

To grant the EXECUTIVE role with the ADMIN OPTION to THOMAS, issue the following statement:

GRANT executive 
TO thomas 
WITH ADMIN OPTION; 

THOMAS can now perform the following operations with the EXECUTIVE role:

Related Topics

ALTER USER
CREATE USER
GRANT (Object Privileges)
REVOKE (System Privileges and Roles)

GRANT (Object Privileges)

Purpose

To grant privileges for a particular object to users and roles. To grant system privileges and roles, use the GRANT command (System Privileges and Roles) described in the previous section of this chapter. See also "Database Object Privileges". For illustrations, see "Examples".

Prerequisites

You must own the object or the owner of the object must have granted you the object privileges with the GRANT OPTION. This rule applies to users with the DBA role.

Syntax

 

Keywords and Parameters

object_priv 

is an object privilege to be granted. You can substitute any of the following values: 

  • ALTER 
  • EXECUTE 
  • INDEX 
  • INSERT 
  • READ 
  • REFERENCES 
  • SELECT 
  • UPDATE 
 

ALL [PRIVILEGES] 

grants all the privileges for the object that you have been granted with the GRANT OPTION. The user who owns the schema containing an object automatically has all privileges on the object with the GRANT OPTION. (The keyword PRIVILEGES is optional.) 

column 

specifies a table or view column on which privileges are granted. You can specify columns only when granting the INSERT, REFERENCES, or UPDATE privilege. If you do not list columns, the grantee has the specified privilege on all columns in the table or view. 

ON 

identifies the object on which the privileges are granted. 

 

DIRECTORY 

identifies a directory_object on which privileges are granted by the DBA. You cannot qualify directory_object with a schema name. See also "Directory Privileges" 

See CREATE DIRECTORY

 

object 

identifies the schema object on which the privileges are granted. If you do not qualify object with schema, Oracle assumes the object is in your own schema. The object can be one of the following types: 

  • table 
  • view 
  • sequence 
  • procedure, function, or package 
  • snapshots 
  • synonym for a table, view, sequence, snapshot, procedure, function, or package (see also "Synonym Privileges"
  • library 
  • object types 
 

TO 

identifies users or roles to which the object privilege is granted. 

 

PUBLIC 

grants object privileges to all users. 

WITH GRANT OPTION 

allows the grantee to grant the object privileges to other users and roles. The grantee must be a user or PUBLIC, rather than a role. 

 

Database Object Privileges

You can use this form of the GRANT statement to grant object privileges to users, roles, and PUBLIC. Each database object privilege that you grant authorizes the grantee to perform some operation on the object. Table 4-12 summarizes the object privileges that you can grant on each type of object.

Table 4-12 Object Privileges

Object Privilege  Table  View  Sequence  ProcedureFunctionsPackages  Snapshot  Directory  Library 

ALTER 

 

 

 

 

 

DELETE 

 

 

Xa 

 

 

EXECUTE 

 

 

 

 

 

INDEX 

 

 

 

 

 

 

INSERT 

 

 

Xa 

 

 

READ 

 

 

 

 

 

 

REFERENCES 

 

 

 

 

 

 

SELECT 

 

 

 

UPDATE 

 

 

Xa 

 

 

a The DELETE, INSERT, and UPDATE privileges can be granted only to updatable snapshots. 
 
 

If you grant a privilege to a user, Oracle adds the privilege to the user's privilege domain. The user can immediately exercise the privilege.

If you grant a privilege to a role, Oracle adds the privilege to the role's privilege domain. Users who have been granted and have enabled the role can immediately exercise the privilege. Other users who have been granted the role can enable the role and exercise the privilege.

If you grant a privilege to PUBLIC, Oracle adds the privilege to the privilege domain of each user. All users can immediately exercise the privilege.

Table 4-13 lists object privileges and the operations that they authorize. You can grant any of these system privileges with the GRANT command.

Table 4-13 Object Privileges and the Operations They Authorize

Object Privilege  Allows Grantee to . . . 

Table privileges authorize operations on a table. Any one of following object privileges allows the grantee to lock the table in any lock mode with the LOCK TABLE command. 

ALTER 

allows the grantee to change the table definition with the ALTER TABLE command. 

DELETE 

remove rows from the table with the DELETE command.  

Note: You must grant the SELECT privilege on the table along with the DELETE privilege. 

INDEX 

create an index on the table with the CREATE INDEX command. 

INSERT 

add new rows to the table with the INSERT command. 

REFERENCES 

create a constraint that refers to the table. You cannot grant this privilege to a role. 

SELECT 

query the table with the SELECT command. 

UPDATE 

change data in the table with the UPDATE command.  

Note: You must grant the SELECT privilege on the table along with the UPDATE privilege. 

View privileges authorizes operations on a view. Any one of the above object privileges allows the grantee to lock the view in any lock mode with the LOCK TABLE command.  

To grant a privilege on a view, you must have that privilege with the GRANT OPTION on all of the view's base tables. 

DELETE 

remove rows from the view with the DELETE command. 

INSERT 

add new rows to the view with the INSERT command. 

SELECT 

query the view with the SELECT command. 

UPDATE 

change data in the view with the UPDATE command. 

Sequence privileges authorize operations on a sequence. 

ALTER 

change the sequence definition with the ALTER SEQUENCE command. 

SELECT 

examine and increment values of the sequence with the CURRVAL and NEXTVAL pseudocolumns. 

Procedure, function, and package privileges authorize operations on procedures, functions, or packages. 

EXECUTE 

execute the procedure or function or to access any program object declared in the specification of a package. 

Snapshot privileges authorize operations on a snapshot. 

SELECT 

query the snapshot with the SELECT command. 

Synonym privileges are the same as the privileges for the base object. See "Synonym Privileges" below. 

Directory privileges provide secured access to the files stored in the operating system directory. See "Directory Privileges" below. 

READ 

read files in the directory. 

 

Synonym Privileges

The object privileges available for a synonym are the same as the privileges for the synonym's base object. Granting a privilege on a synonym is equivalent to granting the privilege on the base object. Similarly, granting a privilege on a base object is equivalent to granting the privilege on all synonyms for the object. If you grant a user a privilege on a synonym, the user can use either the synonym name or the base object name in the SQL statement that exercises the privilege.

Directory Privileges

The object privileges available for a directory provide secured database access to the files stored in the operating system directory to which the directory object serves as a pointer. The directory object contains the full pathname of the operating system directory where the files reside. Because the files are actually stored outside the database, Oracle8 server processes also need to have appropriate file permissions on the file system server.

Granting object privileges on the directory database object to individual database users, rather than on the operating system, allows Oracle8 to enforce security during file operations.

Examples

Example I

To grant READ on directory BFILE_DIR1 to user SCOTT, with the GRANT OPTION, issue the following statement:

GRANT READ ON DIRECTORY bfile_dir1 TO scott
WITH GRANT OPTION;
Example II

To grant all privileges on the table BONUS to the user JONES with the GRANT OPTION, issue the following statement:

GRANT ALL ON bonus TO jones 
WITH GRANT OPTION; 

JONES can subsequently perform the following operations:

Example III

To grant SELECT and UPDATE privileges on the view GOLF_HANDICAP to all users, issue the following statement:

GRANT SELECT, UPDATE 
ON golf_handicap TO PUBLIC; 

All users can subsequently query and update the view of golf handicaps.

Example IV

To grant SELECT privilege on the ESEQ sequence in the schema ELLY to the user BLAKE, issue the following statement:

GRANT SELECT 
ON elly.eseq TO blake; 

BLAKE can subsequently generate the next value of the sequence with the following statement:

SELECT elly.eseq.NEXTVAL 
FROM DUAL;
Example V

To grant BLAKE the REFERENCES privilege on the EMPNO column and the UPDATE privilege on the EMPNO, SAL, and COMM columns of the EMP table in the schema SCOTT, issue the following statement:

GRANT REFERENCES (empno), UPDATE (empno, sal, comm) 
ON scott.emp
TO blake; 

BLAKE can subsequently update values of the EMPNO, SAL, and COMM columns. BLAKE can also define referential integrity constraints that refer to the EMPNO column. However, because the GRANT statement lists only these columns, BLAKE cannot perform operations on any of the other columns of the EMP table.

For example, BLAKE can create a table with a constraint:

CREATE TABLE dependent 
(dependno NUMBER, 
dependname VARCHAR2(10), 
employee NUMBER 
CONSTRAINT in_emp REFERENCES scott.emp(empno) );

The constraint IN_EMP ensures that all dependents in the DEPENDENT table correspond to an employee in the EMP table in the schema SCOTT.

Related Topics

GRANT (System Privileges and Roles)
REVOKE (Schema Object Privileges)

INSERT

Purpose

To add rows to:

For illustrations of inserting, see "Examples".


Note:  

Descriptions of commands and clauses preceded by  are available only if the Oracle objects option is installed on your database server. 


 
 

Prerequisites

For you to insert rows into a table, the table must be in your own schema or you must have INSERT privilege on the table.

For you to insert rows into the base table of a view, the owner of the schema containing the view must have INSERT privilege on the base table. Also, if the view is in a schema other than your own, you must have INSERT privilege on the view.

If you have the INSERT ANY TABLE system privilege, you can also insert rows into any table or any view's base table.

Syntax



 

Keywords and Parameters

schema 

is the schema containing the table or view. If you omit schema, Oracle assumes the table or view is in your own schema. 

table / view 

is the name of the table or object table into which rows are to be inserted. If you specify a view or object view, Oracle inserts rows into the view's base table. See also "Inserting Into Views"

PARTITION (partition_name) 

specifies partition-level row inserts for table. The partition_name is the name of the partition within table targeted for inserts. 

 

Note:  This option is not valid for object tables or object views. 

dblink 

is a complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see "Referring to Schema Objects and Parts". You can only insert rows into a remote table or view if you are using Oracle's distributed functionality. 

 

If you omit dblink, Oracle assumes that the table or view is on the local database. 

THE 

informs Oracle that the column value returned by the subquery is a nested table, not a scalar value. A subquery prefixed by THE is called a flattened subquery. See "Using Flattened Subqueries"

subquery_1 

is a subquery that Oracle treats in the same manner as a view. See "Subqueries"

column 

is a column of the table or view. In the inserted row, each column in this list is assigned a value from the VALUES clause or the subquery. 

 

If you omit one of the table's columns from this list, the column's value for the inserted row is the column's default value as specified when the table was created. If you omit the column list altogether, the VALUES clause or query must specify values for all columns in the table. 

VALUES 

specifies a row of values to be inserted into the table or view. See the syntax description in "Expressions". You must specify a value in the VALUES clause for each column in the column list. See also "The VALUES Clause and Subqueries"

subquery_2 

is a subquery that returns rows that are inserted into the table. The select list of this subquery must have the same number of columns as the column list of the INSERT statement. See "Subqueries"

RETURNING 

retrieves the rows affected by the INSERT. You can retrieve only scalar, LOB, ROWID, and REF types. See also "The RETURNING Clause"

expr 

is some of the syntax descriptions in "Expressions". You must specify a column expression in the RETURNING clause for each variable in the data_item_list

INTO 

indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item_list. 

data_item 

is a PL/SQL variable or bind variable that stores a retrieved expr value. 

You cannot use the RETURNING clause with Parallel DML or with remote objects. See also "Parallel DML"

 

The VALUES Clause and Subqueries

An INSERT statement with a VALUES clause adds to the table a single row containing the values specified in the VALUES clause.

An INSERT statement with a subquery instead of a VALUES clause adds to the table all rows returned by the subquery. Oracle processes the subquery and inserts each returned row into the table. If the subquery selects no rows, Oracle inserts no rows into the table. The subquery can refer to any table, view, or snapshot, including the target table of the INSERT statement.

The number of columns in the column list of the INSERT statement must be the same as the number of values in the VALUES clause or the number of columns selected by the subquery. If you omit the column list, then the VALUES clause or the subquery must provide values for every column in the table.

Oracle assigns values to fields in new rows based on the internal position of the columns in the table and the order of the values in the VALUES clause or in the select list of the query. You can determine the position of each column in the table by examining the data dictionary. See Oracle8 Reference.

If you omit any columns from the column list, Oracle assigns them their default values as specified when the table was created. For more information on default column values, see CREATE TABLE. If any of these columns has a NOT NULL constraint, then Oracle returns an error indicating that the constraint has been violated and rolls back the INSERT statement.

Issuing an INSERT statement against a table fires any INSERT triggers defined on the table.

Parallel DML

You can place a parallel hint immediately after the INSERT keyword to parallelize an INSERT operation. Parallel DML must also be enabled for the session. See ALTER SESSION for information about enabling parallel DML. For detailed information about Parallel DML, see Oracle8 Tuning, Oracle8 Parallel Server Concepts & Administration, and Oracle8 Concepts.

Inserting Into Views

If a view was created using the WITH CHECK OPTION, then you can insert into the view only rows that satisfy the view's defining query.

If a view was created using a single base table, then you can insert rows into the view and then retrieve those values using the RETURNING clause.

You cannot insert rows into a view if the view's defining query contains one of the following constructs:

The RETURNING Clause

An INSERT statement with a RETURNING clause retrieves the rows inserted and stores them in PL/SQL variables or bind variables. Using a RETURNING clause in INSERT statements with a VALUES clause enables you to return column expressions, ROWIDs, and REFs and store them in output bind variables. You can also use INSERT with a RETURNING clause for views with single base tables.

PL/SQL does not allow multiple row inserts; you can retrieve only a single row value into a PL/SQL variable. For information about using the RETURNING clause, see the PL/SQL User's Guide and Reference.

Examples

Example I

The following statement inserts a row into the DEPT table:

INSERT INTO dept   
   VALUES (50, 'PRODUCTION', 'SAN FRANCISCO');
Example II

The following statement inserts a row with six columns into the EMP table. One of these columns is assigned NULL and another is assigned a number in scientific notation:

INSERT INTO emp (empno, ename, job, sal, comm, deptno) 
   VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40);
Example III

The following statement has the same effect as Example II:

INSERT INTO (select empno, ename, job, sal, comm, deptno from emp)
   VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40);
Example IV

The following statement copies managers and presidents or employees whose commission exceeds 25% of their salary into the BONUS table:

INSERT INTO bonus 
   SELECT ename, job, sal, comm 
   FROM emp 
   WHERE comm > 0.25 * sal 
   OR job IN ('PRESIDENT', 'MANAGER');
Example V

The following statement inserts a row into the ACCOUNTS table owned by the user SCOTT on the database accessible by the database link SALES:

INSERT INTO scott.accounts@sales (acc_no, acc_name) 
   VALUES (5001, 'BOWER'); 

Assuming that the ACCOUNTS table has a BALANCE column, the newly inserted row is assigned the default value for this column because this INSERT statement does not specify a BALANCE value.

Example VI

The following statement inserts a new row containing the next value of the employee sequence into the EMP table:

INSERT INTO emp 
   VALUES  (empseq.nextval, 'LEWIS', 'CLERK', 
            7902, SYSDATE, 1200, NULL, 20);
Example VII

The following example adds rows from LATEST_DATA into partition OCT96 of the SALES table:

INSERT INTO sales PARTITION (oct96)
   SELECT * FROM latest_data;
Example VIII

The following example returns the values of the inserted rows into output bind variables :BND1 and :BND2:

INSERT INTO emp VALUES (empseq.nextval, 'LEWIS', 'CLARK',
                        7902, SYSDATE, 1200, NULL, 20) 
   RETURNING sal*12, job INTO :bnd1, :bnd2;
Example IX

The following example returns the reference value for the inserted row into bind array :1:

INSERT INTO employee 
   VALUES ('Kitty Mine', 'Peaches Fuzz', 'Meena Katz')
RETURNING REF(employee) INTO :1;

Related Topics

DELETE
UPDATE

LOCK TABLE

Purpose

To lock one or more tables in a specified mode. This lock manually overrides automatic locking and permits or denies access to a table or view by other users for the duration of your operation. See also "Locking Tables".

Prerequisites

The table or view must be in your own schema or you must have LOCK ANY TABLE system privilege or you must have any object privilege on the table or view.

Syntax

 

Keywords and Parameters

schema  

is the schema containing the table or view. If you omit schema, Oracle assumes the table or view is in your own schema. 

table / view  

is the name of the table to be locked. If you specify view, Oracle locks the view's base tables. 

dblink  

is a database link to a remote Oracle database where the table or view is located. For information on specifying database links, see the section, "Referring to Objects in Remote Databases". You can lock tables and views on a remote database only if you are using Oracle's distributed functionality. All tables locked by a LOCK TABLE statement must be on the same database. 

 

If you omit dblink, Oracle assumes the table or view is on the local database. 

lockmode  

is one of the following:  

ROW SHARE allows concurrent access to the locked table, but prohibits users from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE, which is included for compatibility with earlier versions of Oracle.  

ROW EXCLUSIVE is the same as ROW SHARE, but also prohibits locking in SHARE mode. Row Exclusive locks are automatically obtained when updating, inserting, or deleting.  

SHARE UPDATE--see ROW SHARE.  

SHARE allows concurrent queries but prohibits updates to the locked table.  

SHARE ROW EXCLUSIVE is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or updating rows.  

EXCLUSIVE allows queries on the locked table but prohibits any other activity on it. 

NOWAIT 

specifies that Oracle returns control to you immediately if the specified table is already locked by another user. In this case, Oracle returns a message indicating that the table is already locked by another user. 

 

If you omit this clause, Oracle waits until the table is available, locks it, and returns control to you. 

 

Locking Tables

Some forms of locks can be placed on the same table at the same time; other locks only allow one lock per table. For example, multiple users can place SHARE locks on the same table at the same time, but only one user can place an EXCLUSIVE lock on a table at a time. For a complete description of the interaction of lock modes, see Oracle8 Concepts.

When you lock a table, you choose how other users can access it. A locked table remains locked until you either commit your transaction or roll it back, either entirely or to a savepoint before you locked the table.

A lock never prevents other users from querying the table. A query never places a lock on a table. Readers never block writers and writers never block readers.

Example I

The following statement locks the EMP table in exclusive mode, but does not wait if another user already has locked the table:

LOCK TABLE emp 
IN EXCLUSIVE MODE 
NOWAIT;
Example II

The following statement locks the remote ACCOUNTS table that is accessible through the database link BOSTON:

LOCK TABLE accounts@boston 
IN SHARE MODE;

Related Topics

DELETE
INSERT
UPDATE
COMMIT
ROLLBACK
SAVEPOINT

NOAUDIT (SQL Statements)

Purpose

To stop auditing previously enabled by the AUDIT command (SQL Statements). To stop auditing enabled by the AUDIT command (Schema Objects), refer to NOAUDIT (Schema Objects). See also "Stopping Auditing".

Prerequisites

You must have AUDIT SYSTEM system privilege.

Syntax

 

Keywords and Parameters

statement_opt 

is a statement option for which auditing is stopped. For a list of the statement options and the SQL statements they audit, see Table 4-6 and Table 4-7

system_priv 

is a system privilege for which auditing is stopped. For a list of the system privileges and the statements they authorize, see Table 4-6

BY user 

stops auditing only for SQL statements issued by specified users in their subsequent sessions. If you omit this clause, Oracle stops auditing for all users' statements, except for the situation described in the section that follows. 

WHENEVER SUCCESSFUL 

stops auditing only for SQL statements that complete successfully. 

 

NOT 

stops auditing only for statements that result in Oracle errors. 

 

 

If you omit the WHENEVER clause entirely, Oracle stops auditing for all statements, regardless of success or failure. 

 

Stopping Auditing

The NOAUDIT statement must have the same syntax as the previous AUDIT statement. Further, it reverses the effects only of that particular statement. Therefore, if one AUDIT statement (statement A) enables auditing for a specific user, and a second (statement B) enables auditing for all users, then a NOAUDIT statement to disable auditing for all users (statement C) reverses statement B, but leaves statement A in effect and continues to audit the user that statement A specified. For information on auditing specific SQL commands, see the AUDIT (SQL Statements).

The following examples correspond to the first three examples listed in AUDIT (SQL Statements).

Example I

If you have chosen auditing for every SQL statement that creates or drops a role, you can stop auditing of such statements by issuing the following statement:

NOAUDIT ROLE;
Example II

If you have chosen auditing for any statement that queries or updates any table issued by the users SCOTT and BLAKE, you can stop auditing for SCOTT's queries by issuing the following statement:

NOAUDIT SELECT TABLE 
   BY scott; 

The above statement stops auditing only SCOTT's queries, so Oracle continues to audit BLAKE's queries and updates and SCOTT's updates.

Example III

To stop auditing on all statements that are authorized by DELETE ANY TABLE system privilege, issue the following statement:

NOAUDIT DELETE ANY TABLE;

Related Topics

NOAUDIT (Schema Objects)

NOAUDIT (Schema Objects)

Purpose

To stop auditing previously enabled by the AUDIT command (Schema Objects). To stop auditing enabled by the AUDIT command (SQL Statements), refer to NOAUDIT (SQL Statements). For illustrations, see "Examples".

Prerequisites

The object on which you stop auditing must be in your own schema or you must have AUDIT ANY system privilege. In addition, if the object you choose for auditing is a directory, even if you created it, you must have AUDIT ANY system privilege.

Syntax

 

Keywords and Parameters

object_opt 

stops auditing for particular operations on the object. For a list of these options, see Table 5-8

ON 

identifies the object on which auditing is stopped. If you do not qualify object with schema, Oracle assumes the object is in your own schema. 

object 

identifies the object on which auditing is stopped. The object must a table; view; sequence; stored procedure, function, or package; snapshot; or library. 

 

For information on auditing specific schema objects, refer to AUDIT (Schema Objects)

DIRECTORY directory_name 

identifies the name of the directory on which auditing is being stopped. 

DEFAULT 

removes the specified object options as default object options for subsequently created objects. 

WHENEVER SUCCESSFUL 

stops auditing only for SQL statements that complete successfully. 

 

NOT 

stops auditing only for statements that result in Oracle errors. 

 

If you omit the WHENEVER clause entirely, Oracle stops auditing for all statements, regardless of success or failure. 

 

Examples

If you have chosen auditing for every SQL statement that queries the EMP table in the schema SCOTT, you can stop auditing for such queries by issuing the following statement:

NOAUDIT SELECT 
   ON scott.emp; 

You can stop auditing for such queries that complete successfully by issuing the following statement:

NOAUDIT SELECT 
   ON scott.emp
   WHENEVER SUCCESSFUL; 

This statement stops auditing only for successful queries, Oracle continues to audit queries resulting in Oracle errors.

Related Topics

AUDIT (Schema Objects)
NOAUDIT (SQL Statements)

PARALLEL clause

Purpose

To specify whether Oracle should execute an operation serially or in parallel. See also "Using the PARALLEL Clause". For illustrations, see "Examples".

Prerequisites

This clause can only be used in the following commands:

Syntax

 

Keywords and Parameters

NOPARALLEL 

specifies serial execution of an operation. This is the default. 

PARALLEL 

specifies parallel execution of an operation. 

DEGREE 

determines the degree of parallelism for an operation on a single instance--that is, the number of query servers used in the parallel operation. 

 

integer 

uses integer query servers. 

 

DEFAULT 

the default number of query servers used is calculated from the number of CPUs and the number of DEVICES storing tables to be scanned in parallel. 

INSTANCES 

determines the number of parallel server instances used in the parallel operation. This keyword is ignored if you do not have a parallel server. 

 

integer 

uses integer instances 

 

DEFAULT 

uses all available instances 

 

 

Note: INSTANCES only applies to an instance using the Oracle Parallel Server.  

See also "Nonpartitioned Tables and Indexes" and "Partitioned Tables and Indexes"

 

Using the PARALLEL Clause

Use the PARALLEL clause to specify table parallelism in the CREATE TABLE and ALTER TABLE commands. When you specify this clause in a table definition, Oracle uses the clause to determine parallelism of DML statements as well as queries. Explicit parallel hints, however, override the effect of the PARALLEL clauses for that table.

If you do not specify the PARALLEL clause, Oracle determines the type of parallelism to use by the default PARALLEL attributes of the table or index.

For more information on parallelized operations, see Oracle8 Tuning, Oracle8 Concepts, and Oracle8 Parallel Server Concepts & Administration.

Nonpartitioned Tables and Indexes

Used in a CREATE command, the PARALLEL clause causes the creation of the schema object to be parallelized. If the CREATE command is CREATE TABLE, the PARALLEL clause also sets the default degree of parallelism for queries and DML on the table after creation.

Used in a command to alter an object, the PARALLEL clause changes the default degree of parallelism for queries and DML on the object. In an ALTER DATABASE RECOVER command, the PARALLEL clause causes the recovery to be parallelized.

Specifying PARALLEL (DEGREE 1 INSTANCES 1) is equivalent to specifying NOPARALLEL.

A hint in a query can override a default of NOPARALLEL. Likewise, a hint in a query can override a default of PARALLEL.

Partitioned Tables and Indexes

The INSTANCES parameter of CREATE TABLE ... AS SELECT and CREATE INDEX determines the number of instances used by the CREATE operation. Instances are chosen for physical affinity to the (first) datafiles underlying the partitions. If the INSTANCES parameter is greater than the number of instances with affinity to the underlying datafiles, additional instances (up to the total number of partitions) are chosen arbitrarily. The DEGREE and INSTANCES parameters, stored in the data dictionary, are used later to compute the default PARALLEL attributes of the schema object.

Examples

Example I

The following command creates a table using 10 query servers, 5 to scan SCOTT.EMP and another 5 to populate EMP_DEPT:

CREATE TABLE emp_dept
PARALLEL (DEGREE 5)
AS SELECT * FROM scott.emp
WHERE deptno = 10;
Example II

The following command creates an index using 10 query servers, 5 to scan SCOTT.EMP and another 5 to populate the EMP_IDX index:

CREATE INDEX emp_idx
ON scott.emp (ename)
PARALLEL 5;
Example III

The following command performs tablespace recovery using 5 recovery processes on 5 instances in a parallel server, for a total of 25 (5 * 5) query servers:

ALTER DATABASE
RECOVER TABLESPACE binky
PARALLEL (DEGREE 5 INSTANCES 5);
Example IV

The following command changes the default number of query servers used to query the EMP table:

ALTER TABLE emp
PARALLEL (DEGREE 9);
Example V

The following command causes the index to be rebuilt from the existing index by using 6 query servers, 3 each to scan the old and to build the new index:

ALTER INDEX emp_idx
REBUILD
PARALLEL 3;

Related Topics

ALTER CLUSTER
ALTER DATABASE
ALTER INDEX
ALTER TABLE
CREATE CLUSTER
CREATE INDEX
CREATE TABLE
Oracle8 Tuning
Oracle8 Parallel Server Concepts & Administration


 
Prev
 
Next
 
Oracle 
Copyright © 1997 Oracle Corporation. 
All Rights Reserved. 
 
Library
 
Product
 
Contents
 
Index