Oracle8i SQL Reference
Release 8.1.5
A67779-01

Library
Product
Contents
Index

Prev Next

7
SQL Statements (continued)

filespec

Syntax

filespec_datafiles & filespec_tempfiles::=


filespec_redo_log_file_groups::=


Purpose

To specify a file as a datafile or tempfile

To specify a group of one or more files as a redo log file group.

Prerequisites

A filespec can appear in the following statements: "CREATE DATABASE", "ALTER DATABASE", "CREATE TABLESPACE", and "ALTER TABLESPACE", "CREATE CONTROLFILE", "CREATE LIBRARY", and "CREATE TEMPORARY TABLESPACE".

You must have the privileges necessary to issue one of these statements.

Keywords and Parameters

'filename'  

is the name of either a datafile, tempfile, or a redo log file member. A 'filename' can contain only single-byte 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 (copies). Each 'filename' must be fully specified according to the conventions for your operating system.  

SIZE integer  

specifies the size of the file. Use K or M to specify the size in kilobytes or megabytes.

  • You can omit this parameter only if the file already exists.

  • The size of a tablespace must be one block greater than the sum of the sizes of the objects contained in it.

 

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 you specify SIZE).

  • If the file does not exist, Oracle ignores this clause and creates the file.

  • You can omit this clause only if the file does not already exist. If you omit this clause, Oracle creates the file.

 

 

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

Examples

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 50 kilobytes in size.

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 50 kilobytes in size.

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

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

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', each 50 kilobytes in size. Because the filespec specifies the REUSE clause, 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.

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.

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 clause is not significant.

GRANT system_privileges_and_roles

Syntax


Purpose

To grant system privileges and roles to users and roles. Both privileges and roles are either local, global, or external (see "CREATE USER" and "CREATE ROLE" for definitions).

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 statement, although you can use the GRANT statement to grant system privileges to users and system privileges and roles to other roles. For information about other authorization methods, see Oracle8i Administrator's Guide.

For information on granting object privileges, see "GRANT object_privileges".

Prerequisites

To grant a system privilege, you must either have been granted the system privilege with the ADMIN OPTION or have been granted the 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 the GRANT ANY ROLE system privilege, or you must have created the role.

Keywords and Parameters

system_priv  

is a system privilege to be granted. Table 7-5 lists the system privileges (organized by the database object operated upon).  

 
  • 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.

 

 

Restrictions:

  • A privilege or role cannot appear more than once in the list of privileges and roles to be granted.

  • You cannot grant a role to itself.

  • You cannot grant a role IDENTIFIED GLOBALLY to anything.

  • You cannot grant a role IDENTIFIED EXTERNALLY to a global user or global role.

  • You cannot grant roles circularly. For example, if you grant the role BANKER to the role TELLER, you cannot subsequently grant TELLER to BANKER.

 

role  

is a role to be granted. You can grant an Oracle predefined role or a user-defined role. Table 7-6 lists the predefined roles. For information on creating a user-defined role, see "CREATE ROLE".  

 
  • 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.

 

TO  

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

Restriction: A user, role, or PUBLIC cannot appear more than once in the TO clause.  

PUBLIC  

grants system privileges or roles to all users.  

WITH ADMIN OPTION  

enables the grantee to

  • Grant the role to another user or role, unless the role is a GLOBAL role

  • Revoke the role from another user or role

  • Alter the role to change the authorization needed to access it

  • Drop the role

 

 

If you grant a system privilege or role to a user without specifying WITH ADMIN OPTION, and then subsequently grant the privilege or role to the user WITH ADMIN OPTION, the user has the ADMIN OPTION on the privilege or role.

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.  

Table 7-5 System Privileges
System Privilege  Allows grantee to . . . 

CLUSTERS  

CREATE CLUSTER

 

Create clusters in grantee's schema  

CREATE ANY CLUSTER

 

Create a cluster in any schema except SYS. Behaves similarly to CREATE ANY TABLE.  

ALTER ANY CLUSTER

 

Alter clusters in any schema except SYS  

DROP ANY CLUSTER

 

Drop clusters in any schema except SYS  

CONTEXTS  

CREATE ANY CONTEXT

 

Create any context namespace  

DROP ANY CONTEXT

 

Drop any context namespace  

DATABASE  

ALTER DATABASE

 

Alter the database  

ALTER SYSTEM

 

Issue ALTER SYSTEM statements  

AUDIT SYSTEM

 

Issue AUDIT sql_statements statements  

DATABASE LINKS  

CREATE DATABASE LINK

 

Create private database links in grantee's schema  

CREATE PUBLIC DATABASE LINK

 

Create public database links  

DROP PUBLIC DATABASE LINK

 

Drop public database links  

DIMENSIONS  

CREATE DIMENSION

 

Create dimensions in the grantee's schema  

CREATE ANY DIMENSION

 

Create dimensions in any schema except SYS  

ALTER ANY DIMENSION

 

Alter dimensions in any schema except SYS  

DROP ANY DIMENSION

 

Drop dimensions in any schema except SYS  

DIRECTORIES  

CREATE ANY DIRECTORY

 

Create directory database objects  

DROP ANY DIRECTORY

 

Drop directory database objects  

INDEXTYPES  

CREATE INDEXTYPE

 

Create an indextype in the grantee's schema  

CREATE ANY INDEXTYPE

 

Create an indextype in any schema except SYS  

DROP ANY INDEXTYPE

 

Drop an indextype in any schema except SYS  

EXECUTE ANY INDEXTYPE

 

Reference an indextype in any schema except SYS  

INDEXES  

CREATE INDEX

 

Create in the grantee's schema an index on any table in the grantee's schema or a domain index  

CREATE ANY INDEX

 

Create in any schema except SYS a domain index or an index on any table in any schema except SYS  

ALTER ANY INDEX

 

Alter indexes in any schema except SYS  

DROP ANY INDEX

 

Drop indexes in any schema except SYS  

QUERY REWRITE

 

Enable rewrite using a materialized view, or create a function-based index, when that materialized view or index references tables and views that are in the grantee's own schema.  

GLOBAL QUERY REWRITE

 

Enable rewrite using a materialized view, or create a function-based index, when that materialized view or index references tables or views in any schema except SYS.  

LIBRARIES  

CREATE LIBRARY

 

Create external procedure/function libraries in grantee's schema  

CREATE ANY LIBRARY

 

Create external procedure/function libraries in any schema except SYS  

DROP LIBRARY

 

Drop external procedure/function libraries in the grantee's schema  

DROP ANY LIBRARY

 

Drop external procedure/function libraries in any schema except SYS  

MATERIALIZED VIEWS (which are identical to SNAPSHOTS)  

CREATE MATERIALIZED VIEW

 

Create a materialized view in the grantee's schema  

CREATE ANY MATERIALIZED VIEW

 

Create materialized views in any schema except SYS  

ALTER ANY MATERIALIZED VIEW

 

Alter materialized views in any schema except SYS  

DROP ANY MATERIALIZED VIEW

 

Drop materialized views in any schema except SYS  

QUERY REWRITE

 

Enable rewrite using a materialized view, or create a function-based index, when that materialized view or index references tables and views that are in the grantee's own schema.  

GLOBAL QUERY REWRITE

 

Enable rewrite using a materialized view, or create a function-based index, when that materialized view or index references tables or views in any schema except SYS.  

OPERATORS  

CREATE OPERATOR

 

Create an operator and its bindings in the grantee's schema  

CREATE ANY OPERATOR

 

Create an operator and its bindings in any schema except SYS  

DROP ANY OPERATOR

 

Drop an operator in any schema except SYS  

EXECUTE ANY OPERATOR

 

Reference an operator in any schema except SYS  

OUTLINES  

CREATE ANY OUTLINE

 

Create outlines that can be used in any schema that uses outlines  

ALTER ANY OUTLINE

 

Modify outlines.  

DROP ANY OUTLINE

 

Drop outlines  

PROCEDURES  

CREATE PROCEDURE

 

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

CREATE ANY PROCEDURE

 

Create stored procedures, functions, and packages in any schema except SYS  

ALTER ANY PROCEDURE

 

Alter stored procedures, functions, or packages in any schema except SYS  

DROP ANY PROCEDURE

 

Drop stored procedures, functions, or packages in any schema except SYS  

EXECUTE ANY PROCEDURE

 

Execute procedures or functions (standalone or packaged)

Reference public package variables in any schema except SYS  

PROFILES  

CREATE PROFILE

 

Create profiles  

ALTER PROFILE

 

Alter profiles  

DROP PROFILE

 

Drop profiles  

ROLES  

CREATE ROLE

 

Create roles  

ALTER ANY ROLE

 

Alter any role in the database  

DROP ANY ROLE

 

Drop roles  

GRANT ANY ROLE

 

Grant any role in the database  

ROLLBACK SEGMENTS  

CREATE ROLLBACK SEGMENT

 

Create rollback segments  

ALTER ROLLBACK SEGMENT

 

Alter rollback segments  

DROP ROLLBACK SEGMENT

 

Drop rollback segments  

SEQUENCES  

CREATE SEQUENCE

 

Create sequences in grantee's schema  

CREATE ANY SEQUENCE

 

Create sequences in any schema except SYS  

ALTER ANY SEQUENCE

 

Alter any sequence in the database  

DROP ANY SEQUENCE

 

Drop sequences in any schema except SYS  

SELECT ANY SEQUENCE

 

Reference sequences in any schema except SYS  

SESSIONS  

CREATE SESSION

 

Connect to the database  

ALTER RESOURCE COST

 

Set costs for session resources  

ALTER SESSION

 

Issue ALTER SESSION statements  

RESTRICTED SESSION

 

Logon after the instance is started using the SQL*Plus STARTUP RESTRICT statement  

SNAPSHOTS (which are identical to MATERIALIZED VIEWS)  

CREATE SNAPSHOT

 

Create snapshots in grantee's schema  

CREATE ANY SNAPSHOT

 

Create snapshots in any schema except SYS  

ALTER ANY SNAPSHOT

 

Alter any snapshot in the database  

DROP ANY SNAPSHOT

 

Drop snapshots in any schema except SYS  

GLOBAL QUERY REWRITE

 

Enable rewrite using a snapshot, or create a function-based index, when that snapshot or index references tables or views in any schema except SYS.  

QUERY REWRITE

 

Enable rewrite using a snapshot, or create a function-based index, when that snapshot or index references tables and views that are in the grantee's own schema.  

SYNONYMS  

CREATE SYNONYM

 

Create synonyms in grantee's schema  

CREATE ANY SYNONYM

 

Create private synonyms in any schema except SYS  

CREATE PUBLIC SYNONYM

 

Create public synonyms  

DROP ANY SYNONYM

 

Drop private synonyms in any schema except SYS  

DROP PUBLIC SYNONYM

 

Drop public synonyms  

TABLES  

CREATE ANY TABLE

 

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

ALTER ANY TABLE

 

Alter any table or view in the schema  

BACKUP ANY TABLE

 

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

DELETE ANY TABLE

 

Delete rows from tables, table partitions, or views in any schema except SYS  

DROP ANY TABLE

 

Drop or truncate tables or table partitions in any schema except SYS  

INSERT ANY TABLE

 

Insert rows into tables and views in any schema except SYS  

LOCK ANY TABLE

 

Lock tables and views in any schema except SYS  

UPDATE ANY TABLE

 

Update rows in tables and views in any schema except SYS  

SELECT ANY TABLE

 

Query tables, views, or snapshots in any schema except SYS  

TABLESPACES  

CREATE TABLESPACE

 

Create tablespaces  

ALTER TABLESPACE

 

Alter tablespaces  

DROP TABLESPACE

 

Drop tablespaces  

MANAGE TABLESPACE

 

Take tablespaces offline and online and begin and end tablespace backups  

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 user's schema objects remain but further tablespace allocation is denied unless authorized by specific tablespace quotas. You cannot grant this system privilege to roles.  

TRIGGERS  

CREATE TRIGGER

 

Create a database trigger in grantee's schema  

CREATE ANY TRIGGER

 

Create database triggers in any schema except SYS  

ALTER ANY TRIGGER

 

Enable, disable, or compile database triggers in any schema except SYS  

DROP ANY TRIGGER

 

Drop database triggers in any schema except SYS  

ADMINISTER DATABASE TRIGGER

 

Create a trigger on DATABASE. (You must also have the CREATE TRIGGER or CREATE ANY TRIGGER privilege.)  

TYPES  

CREATE TYPE

 

Create object types and object type bodies in grantee's schema  

CREATE ANY TYPE

 

Create object types and object type bodies in any schema except SYS  

ALTER ANY TYPE

 

Alter object types in any schema except SYS  

DROP ANY TYPE

 

Drop object types and object type bodies in any schema except SYS  

EXECUTE ANY TYPE

 

Use and reference object types and collection types in any schema except SYS, and invoke methods of an object type in any schema if you make the grant to a specific user. If you grant EXECUTE ANY TYPE to a role, users holding the enabled role will not be able to invoke methods of an object type in any schema.  

USERS  

CREATE USER

 

Create users. This privilege also allows the creator to

  • assign quotas on any tablespace

  • set default and temporary tablespaces

  • assign a profile as part of a CREATE USER statement

 

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

 

BECOME USER

 

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

DROP USER

 

Drop users  

VIEWS  

CREATE VIEW

 

Create views in grantee's schema  

CREATE ANY VIEW

 

Create views in any schema except SYS  

DROP ANY VIEW

 

Drop views in any schema except SYS  

MISCELLANEOUS  

ANALYZE ANY

 

Analyze any table, cluster, or index in any schema except SYS  

AUDIT ANY

 

Audit any object in any schema except SYS using AUDIT schema_objects statements  

COMMENT ANY TABLE

 

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

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.  

SYSDBA

 

Perform STARTUP and SHUTDOWN operations

ALTER DATABASE: open, mount, back up, or change character set

CREATE DATABASE

ARCHIVELOG and RECOVERY

Includes the RESTRICTED SESSION privilege  

SYSOPER

 

Perform STARTUP and SHUTDOWN operations

ALTER DATABASE OPEN/MOUNT/BACKUP

ARCHIVELOG and RECOVERY

Includes the RESTRICTED SESSION privilege  
Table 7-6 Oracle Predefined Roles
Predefined Role  Purpose 

CONNECT, RESOURCE, and DBA  

These roles are provided for compatibility with previous versions of Oracle. You should not rely on these roles, because they may not be created automatically by future versions of Oracle. Rather, Oracle recommends that you to design your own roles for database security.  

DELETE_CATALOG_ROLE EXECUTE_CATALOG_ROLE SELECT_CATALOG_ROLE  

These roles are provided for accessing exported data dictionary views and packages. For more information on these roles, see Oracle8i Application Developer's Guide - Fundamentals.  

EXP_FULL_DATABASE IMP_FULL_DATABASE  

These roles are provided for convenience in using the Import and Export utilities. For more information on these roles, see Oracle8i Utilities.  

AQ_USER_ROLE AQ_ADMINISTRATOR_ROLE  

You need these roles to use Oracle's Advanced Queuing functionality. For more information on these roles, see Oracle8i Application Developer's Guide - Advanced Queuing.  

SNMPAGENT  

This role is used by Enterprise Manager/Intelligent Agent. For more information, see Oracle Enterprise Manager Administrator's Guide.  

RECOVERY_CATALOG_OWNER  

You need this role to create a user who owns a recovery catalog. For more information on recovery catalogs, see Oracle8i Backup and Recovery Guide.  

HS_ADMIN_ROLE  

A DBA using Oracle's heterogeneous services feature needs this role to access appropriate tables in the data dictionary and to manipulate them with the DBMS_HS package. For more information, refer to Oracle8i Distributed Database Systems and Oracle8i Supplied Packages Reference.  

Oracle also creates other roles that authorize you to administer the database. On many operating systems, these roles are called OSOPER and OSDBA. Their names may be different on your operating system.  

Examples

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;

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:

GRANT object_privileges

Syntax


Purpose

To grant privileges for a particular object to users, roles, and PUBLIC. To grant system privileges and roles, use the GRANT system_privileges_and_roles statement described in the previous section of this chapter. Table 7-7 summarizes the object privileges that you can grant on each type of object.

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 7-8 lists object privileges and the operations that they authorize. You can grant any of these system privileges with the GRANT statement.

For information on granting system privileges and roles, see "GRANT system_privileges_and_roles". For information on revoking object grants, see "REVOKE schema_object_privileges".

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.

Keywords and Parameters

object_priv  

is an object privilege to be granted. You can substitute any of the values shown in Table 7-7. See also Table 7-8.

Restriction: A privilege cannot appear more than once in the list of privileges to be granted.  

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 schema objects and Java source and resource schema objects are identified separately because they reside in separate namespaces.  

 

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 (see Table 7-7):

  • table, view, or materialized view / snapshot

  • sequence

  • procedure, function, or package

  • user-defined type

  • synonym for any of the above items

  • directory, library, operator, or indextype

  • a Java source, class, or resource

 

 

 

Note: You cannot grant privileges directly to a single partition of a partitioned table. For information on how to grant privileges to a single partition indirectly, refer to Oracle8i Concepts.  

 

DIRECTORY  

identifies a directory schema object on which privileges are granted by the DBA. You cannot qualify directory_name with a schema name.

See "CREATE DIRECTORY".  

 

JAVA SOURCE | RESOURCE  

identifies a Java source or resource schema object on which privileges are granted.

See "CREATE JAVA".  

TO  

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

Restriction: A user or role cannot appear more than once in the TO clause.  

 

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.  

Table 7-7 Object Privileges
Object Privilege  Table  View  Sequence  Procedures, Functions, Packagesa  Materialized View / Snapshot  Directory  Library  User- defined Type  Operator  Indextype 

ALTER  

X  

 

X  

 

 

 

 

 

 

 

DELETE  

X  

X  

 

 

Xb  

 

 

 

 

 

EXECUTE  

 

 

 

X  

 

 

X  

X  

X  

X  

INDEX  

X  

 

 

 

 

 

 

 

 

 

INSERT  

X  

X  

 

 

Xb  

 

 

 

 

 

READ  

 

 

 

 

 

X  

 

 

 

 

REFERENCES  

X  

 

 

 

 

 

 

 

 

 

SELECT  

X  

X  

X  

 

X  

 

 

 

 

 

UPDATE  

X  

X  

 

 

Xb  

 

 

 

 

 
aOracle treats a Java class, source, or resource as if it were a procedure for purposes of granting object privileges.
bThe DELETE, INSERT, and UPDATE privileges can be granted only to updatable materialized views.
 
Table 7-8 Object Privileges and the Operations They Authorize
Object Privilege  Allows Grantee to . . . 

The following 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 statement.  

ALTER  

Change the table definition with the ALTER TABLE statement.  

DELETE  

Remove rows from the table with the DELETE statement.

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 statement.  

INSERT  

Add new rows to the table with the INSERT statement.  

REFERENCES  

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

SELECT  

Query the table with the SELECT statement.  

UPDATE  

Change data in the table with the UPDATE statement.

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

The following view privileges authorize operations on a view. Any one of the following object privileges allows the grantee to lock the view in any lock mode with the LOCK TABLE statement.

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 statement.  

INSERT  

Add new rows to the view with the INSERT statement.  

SELECT  

Query the view with the SELECT statement.  

UPDATE  

Change data in the view with the UPDATE statement.  

The following sequence privileges authorize operations on a sequence.  

ALTER  

Change the sequence definition with the ALTER SEQUENCE statement.  

SELECT  

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

The following procedure, function, and package privilege authorizes operations on procedures, functions, or packages. This privilege also applies to Java sources, classes, and resources, which Oracle treats as though they were procedures for purposes of granting object privileges.  

EXECUTE  

Compile the procedure or function or execute it directly, or access any program object declared in the specification of a package.  

 

Note: Users do not need this privilege to execute a procedure, function, or package indirectly. For more information, refer to Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals.  

The following snapshot privilege authorizes operations on a snapshot.  

SELECT  

Query the snapshot with the SELECT statement.  

Synonym privileges are the same as the privileges for the 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.  

The following directory privilege provides secured 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, Oracle 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 Oracle to enforce security during file operations.  

READ  

Read files in the directory.  

The following object type privilege authorizes operations on an object type  

EXECUTE  

Use and reference the specified object and to invoke its methods.  

The following indextype privilege authorizes operations on indextypes.  

EXECUTE  

Reference an indextype.  

The following operator privilege authorizes operations on user-defined operators.  

EXECUTE  

Reference an operator.  

Examples

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;

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:

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.

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;

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.

INSERT

Syntax


table_expression_clause::=


subquery: see "SELECT and Subqueries".

with_clause::=


table_collection_expression::=


values_clause::=


returning_clause::=


Purpose

To add rows to a table, a view's base table, a partition of a partitioned table or a subpartition of a composite-partitioned table, or an object table or an object view's base table.

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.

Keywords and Parameters

hint  

is a comment that passes instructions to the optimizer on choosing an execution plan for the statement. For the syntax and description of hints, see "Hints" and Oracle8i Tuning.  

table_expression_clause  

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 | subquery  

is the name of the table or object table, or view or object view, or the column or columns returned by a subquery, into which rows are to be inserted. If you specify a view or object view, Oracle inserts rows into the view's base table.

If any value to be inserted is a REF to an object table, and if the object table has a primary key object identifier, then the column into which you insert the REF must be a REF column with a referential integrity or SCOPE constraint to the object table.  

 

If table (or the base table of view) contains one or more domain index columns, this statement executes the appropriate indextype insert routine. For more information on these routines, see Oracle8i Data Cartridge Developer's Guide.

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

 

Restrictions:

  • You cannot execute this statement if table (or the base table of view) contains any domain indexes marked LOADING or FAILED.

  • You cannot specify the sample_clause in an INSERT statement.

  • You cannot specify the ORDER BY clause in the subquery of the table_expression_clause.

  • 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 except with INSTEAD OF triggers if the view's defining query contains one of the following constructs:

    - A set operator

    - A DISTINCT operator

    - An aggregate function

    - A GROUP BY, ORDER BY, CONNECT BY, or START WITH clause

    - A collection expression in a SELECT list

    - A subquery in a SELECT list

    - Joins (with some exceptions). See Oracle8i Administrator's Guide for details.

  • If you specify an index, index partition, or index subpartition that has been marked UNUSABLE, the INSERT statement will fail unless the SKIP_UNUSABLE_INDEXES parameter has been set to TRUE. For more information, see "ALTER SESSION".

 

PARTITION (partition_name) | SUBPARTITION (subpartition_name)  

specifies the name of the partition or subpartition within table (or the base table of view) targeted for inserts.

If a row to be inserted does not map into a specified partition or subpartition, Oracle returns an error.

Restriction: This clause 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 insert rows into a remote table or view only if you are using Oracle's distributed functionality.  

 

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

with_clause  

restricts the subquery in one of the following ways:  

 
  • WITH READ ONLY specifies that the subquery cannot be updated.

 

 
  • WITH CHECK OPTION specifies that Oracle prohibits any changes to that table that would produce rows that are not included in the subquery. See the "WITH CHECK OPTION Example".

 

table_collection_expression  

informs Oracle that the collection value expression should be treated as a table. See "Table Collection Examples".  

 

collection_expression  

is a subquery that selects a nested table column from table or view.  

 

Note: In earlier releases of Oracle, table_collection_expression was expressed as "THE subquery". That usage is now deprecated.  

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. 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.

If you omit the column list altogether, the values_clause or query must specify values for all columns in the table.  

values_clause  

specifies a row of values to be inserted into the table or view. See the syntax description in "Expressions" and "SELECT and Subqueries". You must specify a value in the values_clause for each column in the column list. If you omit the column list, then the values_clause must provide values for every column in the table.  

 

Restrictions:

 

 

Note: If you insert string literals into a RAW column, during subsequent queries, Oracle will perform a full table scan rather than using any index that might exist on the RAW column.  

subquery  

is a subquery that returns rows that are inserted into the table. If the subquery selects no rows, Oracle inserts no rows into the table.

  • When specified with VALUES, the subquery returns values to be inserted into one row.

  • When specified without VALUES, the subquery can return values to be inserted into more than one row.

The subquery can refer to any table, view, or snapshot, including the target table of the INSERT statement. The select list of this subquery must have the same number of columns as the column list of the INSERT statement. If you omit the column list, then the subquery must provide values for every column in the table. See "SELECT and Subqueries".  

 

You can use subquery in combination with the TO_LOB function to convert the values in a LONG column to LOB values in another column in the same or another table. For a discussion of why and when to copy LONGs to LOBs, see Oracle8i Migration. For a description of how to use the TO_LOB function, see "Conversion Functions". See also the TO_LOB Example. To migrate LONGs to LOBs in a view, you must perform the migration on the base table, and then add the LOB to the view.  

 

Note: If subquery returns (in part or totally) the equivalent of an existing materialized view, Oracle may use the materialized view (for query rewrite) in place of one or more tables specified in subquery. For more information on materialized views and query rewrite, see Oracle8i Tuning.  

 

t_alias  

provides a correlation name for the table, view, or subquery to be referenced elsewhere in the statement.  

returning_clause  

retrieves the rows affected by the INSERT.

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