Oracle7 Server SQL Reference Manual | Library |
Product |
Contents |
Index |
Purpose
describes the basic uses of the command.
Prerequisites
lists privileges you must have and steps that you must take before using the command. In addition to the prerequisites listed, most commands also require that the database be open by your instance, unless otherwise noted.
Syntax
shows the keywords and parameters that make up the command. The syntax diagrams used in this chapter are explained in the Preface of this manual.
Keywords and Parameters
describes the purpose of each keyword and parameter. The conventions for keywords and parameters used in this chapter are also explained in the Preface of this manual.
Usage Notes
discusses how and when to use the command.
Examples
shows example statements based on the command.
Related Topics
lists related commands, clauses, and sections of this and other manuals.
The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not required exclusive access to the object being acted upon. For example, you can analyze a table while other users are updating the table.
Oracle7 implicitly commits the current transaction before and after every Data Definition Language statement.
Many Data Definition Language statements may cause Oracle7 to recompile or reauthorize schema objects. For information on how Oracle7 recompiles and reauthorizes schema objects and the circumstances under which a Data Definition Language statement would cause this, see the "Dependencies Among Schema Objects" chapter of Oracle7 Server Concepts.
Data Definition Language commands are not directly supported by PL/SQL, but may be available using packaged procedures supplied by Oracle corporation. For more information, see PL/SQL User's Guide and Reference.
Table 4 - 1 shows the Data Definition Language Commands.
PL/SQL does not support session control commands.
ALTER SYSTEM is not supported in PL/SQL.
Command | Purpose |
ALTER SYSTEM | To alter the Oracle7 instance by performing a specialized function. |
Command | Purpose |
ALLOCATE | To allocate a cursor variable. |
CLOSE | To disable a cursor, releasing the resources it holds. |
CONNECT | To log on to an Oracle7 instance. |
DECLARE CURSOR | To declare a cursor, associating it with a query. |
DECLARE DATABASE | To declare the name of a remote database. |
DECLARE STATEMENT | To assign a SQL variable name to a SQL statement. |
DECLARE TABLE | To declare the structure of a table for semantic checking of embedded SQL statements by the Oracle Precompiler. |
DESCRIBE | To initialize a descriptor, a structure holding host variable descriptions. |
EXECUTE | To execute a prepared SQL statement or PL/SQL block or to execute an anonymous PL/SQL block. |
EXECUTE IMMEDIATE | To prepare and execute a SQL statement containing no host variables. |
FETCH | To retrieve rows selected by a query. |
OPEN | To execute the query associated with a cursor. |
PREPARE | To parse a SQL statement. |
TYPE | To perform user-defined equivalencing. |
VAR | To perform host variable equivalencing. |
WHENEVER | To specify handling for error and warning conditions. |
cursor_variable
is the cursor variable to be allocated.
For more information on this command, see PL/SQL User's Guide and Reference and Programmer's Guide to the Oracle Precompilers.
Example
This partial example illustrates the use of the ALLOCATE command in a Pro*C embedded SQL program:
EXEC SQL BEGIN DECLARE SECTION; SQL_CURSOR emp_cv; struct{ ... } emp_rec; EXEC SQL END DECLARE SECTION; EXEC SQL ALLOCATE emp_cursor; EXEC SQL EXECUTE BEGIN OPEN :emp_cv FOR SELECT * FROM emp; END; END-EXEC; for (;;) {EXEC SQL FETCH :emp_cv INTO emp_rec; }
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the cluster's creation label or you must satisfy one of these criteria:
schema
is the schema containing the cluster. If you omit schema, Oracle7 assumes the cluster is in your own schema.
cluster
is the name of the cluster to be altered.
SIZE
determines how many cluster keys will be stored in data blocks allocated to the cluster. You can only change the SIZE parameter for an indexed cluster, not for a hash cluster. For a description of the SIZE parameter, see the CREATE CLUSTER command .
PCTUSED PCTFREE INITRANS MAXTRANS
changes the values of these parameters for the cluster. See the PCTUSED, PCTFREE, INITRANS, and MAXTRANS parameters of the CREATE CLUSTER command .
STORAGE
changes the storage characteristics for the cluster. See the STORAGE clause .
ALLOCATE EXTENT
explicitly allocates a new extent for the cluster.
Explicitly allocating an extent with this clause does not cause Oracle7 to evaluate the cluster's storage parameters and determine a new size for the next extent to be allocated. You can only allocate a new extent for an indexed cluster, not a hash cluster.
DEALLOCATE UNUSED
explicitly deallocates unused space at the end of the cluster and make the freed space available for other segments. Only unused space above the high-water mark can be freed. If KEEP is omitted, all unused space is freed. For more information, see the deallocate_clause .
KEEP specifies the number of bytes above the high-water mark that the cluster will have after deallocation. If the number of remaining extents are less than MINEXTENTS, then MINEXTENTS is set to the current number of extents. If the initial extent becomes smaller than INITIAL, then INITIAL is set to the value of the current initial extent.
PARALLEL
specifies the degree of parallelism for creating the cluster and the default degree of parallelism for queries on the cluster once created. For more information, see the parallel_clause .
The following statement alters the CUSTOMER cluster in the schema SCOTT:
ALTER CLUSTER scott.customer
SIZE 512
STORAGE (MAXEXTENTS 25)
Oracle7 now allocates 512 bytes for each cluster key value. Assuming a data block size of 2 kilobytes, future data blocks within this cluster contain 4 cluster keys per data block, or 2 kilobytes divided by 512 bytes.
The cluster can have a maximum of 25 extents.
Example II
The following statement deallocates unused space from CUSTOMER cluster, keeping 30 Kilobytes of unused space for future use:
ALTER CLUSTER scott.customer DEALLOCATE UNUSED KEEP 30 K
database
identifies the database to be altered. If you omit database, Oracle7 alters the database identified by the value of the initialization parameter DB_NAME. You can only alter the database whose control files are specified by the initialization parameter CONTROL_FILES. Note that the database identifier is not related to the SQL*Net database specification.
You can only use the following options when the database is not mounted by your instance:
MOUNT
STANDBY DATABASE
mounts the standby database. For more information, see the Oracle7 Server Administrator's Guide.
The default is EXCLUSIVE.
CONVERT
completes the conversion of the Oracle Version 6 data dictionary. After you use this option, the Version 6 data dictionary no longer exists in the Oracle7 database. Only use this option when you are migrating to Oracle7. For more information on using this option, see Oracle7 Server Migration.
OPEN
opens the database, making it available for normal use. You must mount the database before you can open it. You cannot open a standby database that has not been activated.
RESETLOGS resets the current log sequence number to 1 and discards any redo information that was not applied during recovery; ensuring that it will never be applied. This effectively discards all changes to the database. You must use this option to open the database after performing media recovery with an incomplete recovery using the RECOVER UNTIL clause (see page 4 - 382) or with a backup controlfile. After opening the database with this option, you should perform a complete database backup.
NORESETLOGS
leaves the log sequence number and redo log files in their current state.
You can only specify the above options after performing incomplete media recovery or complete media recovery with a backup controlfile. In any other case, Oracle7 uses the NORESETLOGS automatically.
ACTIVATE STANDBY DATABASE
changes the state of a standby database to an active database. For more information, see
Oracle7 Server Administrator's Guide.
You can only use the following options when your instance has the database mounted in exclusive mode, but not open:
ARCHIVELOG
establishes archivelog mode for redo log file groups. In this mode, the contents of a redo log file group must be archived before the group can be reused. This option prepares for the possibility of media recovery. You can only use this option after shutting down your instance normally or immediately with no errors and then restarting it, mounting the database in exclusive mode.
NOARCHIVELOG
establishes noarchivelog mode for redo log files. In this mode, the contents of a redo log file group need not be archived so that the group can be reused. This mode does not prepare for recovery after media failure.
RECOVER
performs media recovery. See the RECOVER clause . You only recover the entire database when the database is closed. You can recover tablespaces or datafiles when the database is open or closed, provided the tablespaces or datafiles to be recovered are offline. You cannot perform media recovery if you are connected to Oracle7 through the multi-threaded server architecture. You can also perform media recovery with the Server Manager recovery dialog box.
You can use any of the following options when your instance has the database mounted, open or closed, and the files involved are not in use:
ADD LOGFILE
adds one or more redo log file groups to the specified thread, making them available to the instance assigned the thread. If you omit the THREAD parameter, the redo log file group is added to the thread assigned to your instance. You need only use the THREAD parameter if you are using Oracle7 with the Parallel Server option in parallel mode.
Each filespec specifies a redo log file group containing one or more members, or copies. See the syntax description of filespec .
You can choose the value of the GROUP parameter for each redo log file group. Each value uniquely identifies the redo log file group among all groups in all threads and can range from 1 to the MAXLOGFILES value. You cannot add multiple redo log file groups having the same GROUP value. If you omit this parameter, Oracle7 generates its value automatically. You can examine the GROUP value for a redo log file group through the dynamic performance table V$LOG.
ADD LOGFILE MEMBER
adds new members to existing redo log file groups. Each new member is specified by 'filename'. If the file already exists, it must be the same size as the other group members and you must specify the REUSE option. If the file does not exist, Oracle7 creates a file of the correct size. You cannot add a member to a group if all of the group's members have been lost through media failure.
You can specify an existing redo log file group in one of these ways:
GROUP parameter You can specify the value of the GROUP parameter that identifies the redo log file group.
list of filenames You can list all members of the redo log file group. You must fully specify each filename according to the conventions for your operating system.
DROP LOGFILE
drops all members of a redo log file group. You can specify a redo log file group in the same manner as the ADD LOGFILE MEMBER clause. You cannot drop a redo log file group if it needs archiving or is the currently active group. Nor can you drop a redo log file group if doing so would cause the redo thread to contain less than two redo log file groups.
DROP LOGFILE MEMBER
drops one or more redo log file members. Each 'filename' must fully specify a member using the conventions for filenames on your operating system.
You cannot use this clause to drop all members of a redo log file group that contain valid data. To perform this operation, use the DROP LOGFILE clause.
CLEAR LOGFILE
CLEAR LOGFILE cannot be used to clear a log needed for media recovery. If it is necessary to clear a log containing redo after the database checkpoint, then incomplete media recovery will be necessary. The current redo log of an open thread can never be cleared. The current log of a closed thread can be cleared by switching logs in the closed thread.
If the CLEAR LOGFILE command is interrupted by a system or instance failure, then the database may hang. If so, the command must be reissued once the database is restarted. If the failure occurred because of I/O errors accessing one member of a log group, then that member can be dropped and other members added.
UNARCHIVED you must specify UNARCHIVED if you want to reuse a redo log that was not archived.
Warning: Specifying UNARCHIVED will make backups unusable if the redo log is needed for recovery.
UNRECOVERABLE DATAFILE
you must specify UNRECOVERABLE DATAFILE if the tablespace has a datafile offline and the unarchived log must be cleared to bring the tablespace online. If so, then the datafile and entire tablespace must be dropped once the CLEAR LOGFILE command completes.
RENAME FILE
renames datafiles or redo log file members. This clause only renames files in the control file, it does not actually rename them on your operating system. You must specify each filename using the conventions for filenames on your operating system.
CREATE STANDBY CONTROLFILE
create a controlfile to be used to maintain a standby database. For more information, see Oracle7 Server Administrator's Guide.
BACKUP CONTROLFILE
backs up the current control file.
TO 'filename' specifies the file to which the control file is backed up. You must fully specify the 'filename' using the conventions for your operating system. If the specified file already exists, you must specify the REUSE option.
TO TRACE writes SQL statements to the database's trace file, rather than making a physical backup of the control file.
The SQL commands can be used to start up the database, re-create the control file, and recover and open the database appropriately, based on the created control file.
You can copy the commands from the trace file into a script file, edit the commands as necessary, and use the script to recover the database if all copies of the control file are lost (or to change the size of the control file).
RESETLOGS the SQL statement written to the trace file for starting the database is ALTER DATABASE OPEN RESETLOGS.
NORESETLOGS
the SQL statement written to the trace file for starting the database is ALTER DATABASE OPEN NORESETLOGS.
You can only use the following options when your instance has the database open:
ENABLE
in a parallel server, enables the specified thread of redo log file groups. The thread must have at least two redo log file groups before you can enable it.
If you omit the PUBLIC option, the thread is only available to the instance that explicitly requests it with the initialization parameter THREAD.
DISABLE
disables the specified thread, making it unavailable to all instances. You cannot disable a thread if an instance using it has the database mounted.
RENAME GLOBAL_NAME
changes the global name of the database. The database is the new database name and can be as long as eight bytes. The optional domains specifies where the database is effectively located in the network hierarchy. Renaming your database automatically clears all data from the shared pool in the SGA. However, renaming your database does not change global references to your database from existing database links, synonyms, and stored procedures and functions on remote databases. Changing such references is the responsibility of the administrator of the remote databases.
For more information on global names, see the "Network Administration" chapter of Oracle7 Server Distributed Systems, Volume I.
RESET COMPATIBILITY
mark the database to be reset to an earlier version of Oracle7 when the database is next restarted.
Note: RESET COMPATIBILITY will not work unless you have successfully disabled Oracle7 features that affect backward compatibility.
For more information on downgrading to an earlier version of Oracle7, see the "Upgrading and Downgrading" chapter of Oracle7 Server Migration.
SET
for Trusted Oracle7, changes one of the following:
DBHIGH equates the predefined label DBHIGH to the operating system label specified by 'text'.
DBLOW equates the predefined label DBLOW to the operating system label specified by 'text'.
DBMAC ON configures Trusted Oracle7 in DBMS MAC mode.
DBMAC OFF configures Trusted Oracle7 in OS MAC mode.
You must specify labels in the default label format for your session. Changes made by this option take effect when you next start your instance. You can only use this clause if you are using Trusted Oracle7. For more information on this clause, see the Trusted Oracle7 Server Administrator's Guide.
You can use any of the following options when your instance has the database mounted, open or closed, and the files involved are not in use:
CREATE DATAFILE
creates a new empty datafile in place of an old one. You can use this option to re-create a datafile that was lost with no backup. The '
filename' must identify a file that is or was once part of the database. The filespec specifies the name and size of the new datafile. If you omit the AS clause, Oracle7 creates the new file with the same name and size as the file specified by 'filename'.
During recovery, all archived redo logs written to since the original datafile was created must be applied to the new, empty version of the lost datafile.
Oracle7 creates the new file in the same state as the old file when it was created. You must perform media recovery on the new file to return it to the state of the old file at the time it was lost.
You cannot create a new file based on the first datafile of the SYSTEM tablespace.
DATAFILE
changes one of the following for your database:
ONLINE brings the datafile online.
OFFLINE takes the datafile offline.
If the database is open, then you must perform media recovery on the datafile before bringing it back online. This is because a checkpoint is not performed on the datafile before it is taken offline.
DROP takes a datafile offline when the database is in NOARCHIVELOG mode.
AUTOEXTEND enables or disables the automatic extension of a datafile.
OFF disable autoextend if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in further ALTER DATABASE AUTOEXTEND commands.
ON enable autoextend.
MAXSIZE maximum disk space allowed for automatic extension of the datafile.
UNLIMITED set no limit on allocating disk space to the datafile.
Warning: Do not use ALTER TABLESPACE ... END BACKUP if you have restored any of the files affected from a backup. Media recovery is fully described in the Oracle7 Server Administrator's Guide.
For more information on using the ALTER DATABASE command for database maintenance, see the Oracle7 Server Administrator's Guide.
Example I
The following statement mounts the database named STOCKS exclusively:
ALTER DATABASE stocks MOUNT EXCLUSIVE
Example II
ALTER DATABASE stocks ADD LOGFILE GROUP 3 ('diska:log3.log' , 'diskb:log3.log') SIZE 50K
Example III
The following statement adds a member to the redo log file group added in the previous example:
ALTER DATABASE stocks ADD LOGFILE MEMBER 'diskc:log3.log' TO GROUP 3
Example IV
The following statement drops the redo log file member added in the previous example:
ALTER DATABASE stocks DROP LOGFILE MEMBER 'diskc:log3.log'
Example V
The following statement renames a redo log file member:
ALTER DATABASE stocks RENAME FILE 'diskb:log3.log' TO 'diskd:log3.log'
The above statement only changes the member of the redo log group from one file to another. The statement does not actually change the name of the file 'DISKB:LOG3.LOG' to 'DISKD:LOG3.LOG'. You must perform this operation through your operating system.
Example VI
The following statement drops all members of the redo log file group 3:
ALTER DATABASE stocks DROP LOGFILE GROUP 3
Example VII
ALTER DATABASE stocks ADD LOGFILE THREAD 5 GROUP 4 ('diska:log4.log', 'diskb:log4:log', 'diskc:log4.log' )
Example VIII
The following statement disables thread 5 in a parallel server:
ALTER DATABASE stocks DISABLE THREAD 5
Example IX
ALTER DATABASE stocks ENABLE PUBLIC THREAD 5
Example X
The following statement creates the datafile 'DISK1:DB1.DAT' based on the file 'DISK2:DB1.DAT':
ALTER DATABASE
CREATE DATAFILE 'disk1:db1.dat' AS 'disk2:db1.dat'
Example XI
ALTER DATABASE RENAME GLOBAL_NAME TO sales.australia.acme.com
Example XII
The following statement attempts to change the size of datafile 'DISK1:DB1.DAT':
ALTER DATABASE DATAFILE 'disk1:db1.dat' RESIZE 10 M
For examples of performing media recovery, see the Oracle7 Server Administrator's Guide.
Example XIII
The following statement clears a log file:
ALTER DATABASE CLEAR LOGFILE 'disk3:log.dbf'
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the function's creation label or you must satisfy one of these criteria:
schema
is the schema containing the function. If you omit schema, Oracle7 assumes the function is in your own schema.
function
is the name of the function to be recompiled.
COMPILE
causes Oracle7 to recompile the function. The COMPILE keyword is required.
The ALTER FUNCTION command is similar to the ALTER PROCEDURE command on 4 - 42. For information on how Oracle7 recompiles functions and procedures, see the "Dependencies Among Schema Objects" chapter of Oracle7 Server Concepts.
Note: This command does not change the declaration or definition of an existing function. To re-declare or redefine a function, you must use the CREATE FUNCTION command () with the OR REPLACE option.
Example
ALTER FUNCTION merriweather.get_bal
COMPILE
If Oracle7 encounters no compilation errors while recompiling GET_BAL, GET_BAL becomes valid. Oracle7 can subsequently execute it without recompiling it at runtime. If recompiling GET_BAL results in compilation errors, Oracle7 returns an error message and GET_BAL remains invalid.
Oracle7 also invalidates all objects that depend upon GET_BAL. If you subsequently reference one of these objects without explicitly recompiling it first, Oracle7 recompiles it implicitly at runtime.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the index's creation label or you must satisfy one of these criteria:
schema
is the schema containing the index. If you omit schema, Oracle7 assumes the index is in your own schema.
index
is the name of the index to be altered.
PCTFREE INITRANS MAXTRANS
changes the values of these parameters for the index. See the PCTFREE, INITRANS and MAXTRANS parameters of the CREATE TABLE command .
STORAGE
changes the storage parameters for the index. See the STORAGE clause .
ALLOCATE EXTENT
explicitly allocates a new extent for the index.
Explicitly allocating an extent with this clause does affect the size for the next extent to be allocated as specified by the NEXT and PCTINCREASE storage parameters.
DEALLOCATE UNUSED
explicitly deallocates unused space at the end of the index and make the freed space available for other segments. Only unused space above the high-water mark can be freed. If KEEP is omitted, all unused space is freed. For more information, see the deallocate_clause .
KEEP specifies the number of bytes above the high-water mark that the index will have after deallocation. If the number of remaining extents are less than MINEXTENTS, then MINEXTENTS is set to the current number of extents. If the initial extent becomes smaller than INITIAL, then INITIAL is set to the value of the current initial extent.
REBUILD
create the index anew using the existing index.
PARALLEL use integer parallel processes to build the new index.
NOPARALLEL do not use parallel processes to build the new index. This is the default.
RECOVERABLE
specifies that the creation of the index will be logged in the redo log file. This is the default.
If the database is run in ARCHIVELOG mode, media recovery from a backup will recreate the index. You cannot specify RECOVERABLE when using NOARCHIVELOG mode.
UNRECOVERABLE
specifies that the creation of the index will not be logged in the redo log file. As a result, media recovery will not recreate the index.
When this option is used, index creation is faster than the RECOVERABLE option because no redo log entries are written.
TABLESPACE
specifies the tablespace where the rebuilt index will be stored. The default is the default tablespace of the user issuing the command.
Example
ALTER INDEX scott.customer INITRANS 5 STORAGE (NEXT 100K)
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the package's creation label or you must satisfy one of these criteria:
schema
is the schema containing the package. If you omit schema, Oracle7 assumes the package is in your own schema.
package
is the name of the package to be recompiled.
COMPILE
recompiles the package specification or body. The COMPILE keyword is required.
PACKAGE
recompiles the package body and specification.
BODY
recompiles only the package body.
The default option is PACKAGE.
Because all objects in a package are stored as a unit, the ALTER PACKAGE command recompiles all package objects together. You cannot use the ALTER PROCEDURE command or ALTER FUNCTION command to individually recompile a procedure or function that is part of a package.
Note: This command does not change the declaration or definition of an existing package. To re-declare or redefine a package, you must use the CREATE PACKAGE or the CREATE PACKAGE BODY command with the OR REPLACE option.
For more information on debugging packages, see the "Using Procedures and Packages" chapter of Oracle7 Server Application Developer's Guide. For information on how Oracle7 maintains dependencies among schema objects, including remote objects, see the "Dependencies Among Schema Objects" chapter of Oracle7 Server Concepts.
Example I
ALTER PACKAGE blair.accounting
COMPILE PACKAGE
If Oracle7 encounters no compilation errors while recompiling the ACCOUNTING specification and body, ACCOUNTING becomes valid. BLAIR can subsequently call or reference all package objects declared in the specification of ACCOUNTING without runtime recompilation. If recompiling ACCOUNTING results in compilation errors, Oracle7 returns an error message and ACCOUNTING remains invalid.
Oracle7 also invalidates all objects that depend upon ACCOUNTING. If you subsequently reference one of these objects without explicitly recompiling it first, Oracle7 recompiles it implicitly at runtime.
Example II
To recompile the body of the ACCOUNTING package in the schema BLAIR, issue the following statement:
ALTER PACKAGE blair.accounting
COMPILE BODY
If Oracle7 encounters no compilation errors while recompiling the package body, the body becomes valid. BLAIR can subsequently call or reference all package objects declared in the specification of ACCOUNTING without runtime recompilation. If recompiling the body results in compilation errors, Oracle7 returns an error message and the body remains invalid.
Because the following statement recompiles the body and not the specification of ACCOUNTING, Oracle7 does not invalidate dependent objects.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the procedure's creation label or you must satisfy one of these criteria:
schema
is the schema containing the procedure. If you omit schema, Oracle7 assumes the procedure is in your own schema.
procedure
is the name of the procedure to be recompiled.
COMPILE
causes Oracle7 to recompile the procedure. The COMPILE keyword is required.
You can use the ALTER PROCEDURE command to explicitly recompile a procedure that is invalid. Explicit recompilation eliminates the need for implicit runtime recompilation and prevents associated runtime compilation errors and performance overhead.
When you issue an ALTER PROCEDURE statement, Oracle7 recompiles the procedure regardless of whether it is valid or invalid.
You can only use the ALTER PROCEDURE command to recompile a stand-alone procedure. To recompile a procedure that is part of a package, you must recompile the entire package using the ALTER PACKAGE command.
When you recompile a procedure, Oracle7 first recompiles objects upon which the procedure depends, if any of these objects are invalid. Oracle7 also invalidates any local objects that depend upon the procedure, such as procedures that call the recompiled procedure or package bodies that define procedures that call the recompiled procedure. If Oracle7 recompiles the procedure successfully, the procedure becomes valid. If recompiling the procedure results in compilation errors, then Oracle7 returns an error and the procedure remains invalid. You can then debug procedures using the predefined package DBMS_OUTPUT. For information on debugging procedures, see the "Using Procedures and Packages" chapter of the Oracle7 Server Application Developer's Guide. For information on how Oracle7 maintains dependencies among schema objects, including remote objects, see the "Dependencies Among Schema Objects" chapter of Oracle7 Server Concepts.
Note: This command does not change the declaration or definition of an existing procedure. To re-declare or redefine a procedure, you must use the CREATE PROCEDURE command with the OR REPLACE option.
Example
ALTER PROCEDURE henry.close_acct COMPILE
If Oracle7 encounters no compilation errors while recompiling CLOSE_ACCT, CLOSE_ACCT becomes valid. Oracle7 can subsequently execute it without recompiling it at runtime. If recompiling CLOSE_ACCT results in compilation errors, Oracle7 returns an error and CLOSE_ACCT remains invalid.
Oracle7 also invalidates all dependent objects. These objects include any procedures, functions, and package bodies that call CLOSE_ACCT. If you subsequently reference one of these objects without first explicitly recompiling it, Oracle7 recompiles it implicitly at runtime.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the profile's creation label or you must satisfy one of these criteria:
profile
is the name of the profile to be altered.
integer
defines a new limit for a resource in this profile. For information on resource limits, see the CREATE PROFILE command .
UNLIMITED
specifies that this profile allows unlimited use of the resource.
DEFAULT
You cannot remove a limit from the DEFAULT profile.
Example I
This statement defines a new limit of 5 concurrent sessions for the ENGINEER profile:
ALTER PROFILE engineer LIMIT SESSIONS_PER_USER 5
If the ENGINEER profile does not currently define a limit for SESSIONS_PER_USER, the above statement adds the limit of 5 to the profile. If the profile already defines a limit, the above statement redefines it to 5. Any user assigned the ENGINEER profile is subsequently limited to 5 concurrent sessions.
Example II
This statement defines unlimited idle time for the ENGINEER profile:
ALTER PROFILE engineer LIMIT IDLE_TIME UNLIMITED
Any user assigned the ENGINEER profile is subsequently permitted unlimited idle time.
Example III
This statement removes the IDLE_TIME limit from the ENGINEER profile:
ALTER PROFILE engineer LIMIT IDLE_TIME DEFAULT
Any user assigned the ENGINEER profile is subject to the IDLE_TIME limit defined in the DEFAULT profile in their subsequent sessions.
Example IV
This statement defines a limit of 2 minutes of idle time for the DEFAULT profile:
ALTER PROFILE default LIMIT IDLE_TIME 2
This IDLE_TIME limit applies to these users:
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match DBLOW or you must have WRITEDOWN system privileges.
CPU_PER_SESSION
The amount of CPU time used by a session measured in hundredths of seconds.
CONNECT_TIME
The amount of CPU time used by a session measured in hundredths of seconds.
CPU_PER_SESSION
The elapsed time of a session measured in minutes.
LOGICAL_READS_PER_SESSION
The number of data blocks read during a session, including blocks read from both memory and disk.
The number of bytes of private space in the System Global Area (SGA) used by a session. This limit only applies if you are using the multi-threaded server architecture and allocating private space in the SGA for your session.
Oracle7 calculates the total resource cost by multiplying the amount of each resource used in the session by the resource's weight and summing the products for all four resources. Both the products and the total cost are expressed in units called service units.
Although Oracle7 monitors the use of other resources, only these four can contribute to the total resource cost for a session. For information on all resources, see the CREATE PROFILE command .
The weight that you assign to each resource determines how much the use of that resource contributes to the total resource cost. Using a resource with a lower weight contributes less to the cost than using a resource with a higher weight. If you do not assign a weight to a resource, the weight defaults to 0 and use of the resource subsequently does not contribute to the cost. The weights you assign apply to all subsequent sessions in the database.
Once you have specified a formula for the total resource cost, you can limit this cost for a session with the COMPOSITE_LIMIT parameter of the CREATE PROFILE command. If a session's cost exceeds the limit, Oracle7 aborts the session and returns an error. For information on establishing resource limits, see the CREATE PROFILE command . If you use the ALTER RESOURCE COST command to change the weight assigned to each resource, Oracle7 uses these new weights to calculate the total resource cost for all current and subsequent sessions.
Example
The following statement assigns weights to the resources CPU_PER_SESSION and CONNECT_TIME:
ALTER RESOURCE COST
CPU_PER_SESSION 100
CONNECT_TIME 1
The weights establish this cost formula for a session:
T = (100 * CPU) + CON
where:
T
is the total resource cost for the session expressed in service units.
CPU
is the CPU time used by the session measured in hundredths of seconds.
CON
is the elapsed time of a session measured in minutes.
Because the above statement assigns no weight to the resources LOGICAL_READS_PER_SESSION and PRIVATE_SGA, these resources do not appear in the formula.
If a user is assigned a profile with a COMPOSITE_LIMIT value of 500, a session exceeds this limit whenever T exceeds 500. For example, a session using 0.04 seconds of CPU time and 101 minutes of elapsed time exceeds the limit. A session 0.0301 seconds of CPU time and 200 minutes of elapsed time also exceeds the limit.
You can subsequently change the weights with another ALTER RESOURCE statement:
ALTER RESOURCE COST
LOGICAL_READS_PER_SESSION 2
CONNECT_TIME 0
These new weights establish a new cost formula:
T = (100 * CPU) + (2 * LOG)
where:
T CPU
are the same as in the previous formula.
LOG
is the number of data blocks read during the session.
This ALTER RESOURCE COST statement changes the formula in these ways:
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the role's creation label or you must satisfy one of these criteria:
Example
This statement changes the password on the TELLER role to LETTER:
ALTER ROLE teller IDENTIFIED BY letter
Users granted the TELLER role must subsequently specify the new password to enable the role.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the rollback segment's creation label or you must satisfy one of these criteria:
ONLINE
brings the rollback segment online.
OFFLINE
takes the rollback segment offline.
STORAGE
changes the rollback segment's storage characteristics. See the STORAGE clause .
SHRINK
attempts to shrink the rollback segment to an optimal or given size.
The ONLINE option brings the rollback segment online making it available for transactions by your instance. You can also bring a rollback segment online when you start your instance with the initialization parameter ROLLBACK_SEGMENTS.
The OFFLINE option takes the rollback segment offline. If the rollback segment does not contain information necessary to rollback any active transactions, Oracle7 takes it offline immediately. If the rollback segment does contain information for active transactions, Oracle7 makes the rollback segment unavailable for future transactions and takes it offline after all the active transactions are committed or rolled back. Once the rollback segment is offline, it can be brought online by any instance.
You cannot take the SYSTEM rollback segment offline.
You can tell whether a rollback segment is online or offline by querying the data dictionary view DBA_ROLLBACK_SEGS. Online rollback segments are indicated by a STATUS value of 'IN_USE'. Offline rollback segments are indicated by a STATUS value of 'AVAILABLE'.
For more information on making rollback segments available and unavailable, see the "Managing Rollback Segments" chapter of Oracle7 Server Administrator's Guide.
The STORAGE clause of the ALTER ROLLBACK SEGMENT command affects future space allocation in the rollback segment. You cannot change the values of the INITIAL and MINEXTENTS for an existing rollback segment.
The SHRINK clause of the ALTER ROLLBACK SEGMENT command initiates an attempt to reduce the specified rollback segment to an optimum size. If size is not specified, then the size defaults to the OPTIMAL value of the STORAGE clause of the CREATE ROLLBACK SEGMENT command that created the rollback segment. If the OPTIMAL value was not specified, then the size defaults to the MINEXTENTS value of the STORAGE clause. The specified size in a SHRINK is valid for the execution of the command; thereafter, OPTIMUM remains unchanged. Regardless of whether a size is specified or not, the rollback segment cannot shrink to less than two extents.
You can query the DBA_ROLLBACK_SEGS tables to determine the actual size of a rollback segment after attempting to shrink a rollback segment.
For a parallel server, you can only shrink rollback segments that are online to your instance.
The SHRINK option is an attempt to shrink the size of the rollback segment; the success and amount of shrinkage depends on the following:
This statement brings the rollback segment RSONE online:
ALTER ROLLBACK SEGMENT rsone ONLINE
Example II
This statement changes the STORAGE parameters for RSONE:
ALTER ROLLBACK SEGMENT rsone STORAGE (NEXT 1000 MAXEXTENTS 20)
Example III
This statement attempts to resize a rollback segment to an optimum size of one hundred megabytes:
ALTER ROLLBACK SEGMENT rsone SHRINK TO 100 M
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the sequence's creation label or you must satisfy one of these criteria:
Some validations are performed. For example, a new MAXVALUE cannot be imposed that is less than the current sequence number.
Example I
This statement sets a new maximum value for the ESEQ sequence:
ALTER SEQUENCE eseq
MAXVALUE 1500
Example II
This statement turns on CYCLE and CACHE for the ESEQ sequence:
ALTER SEQUENCE eseq
CYCLE
CACHE 5
To raise your session label, you must have WRITEUP and READUP system privileges. To lower your session label, you must have WRITEDOWN system privilege. To change your session label laterally, you must have READUP, WRITEUP, and WRITEDOWN system privileges.
To perform the other operations of this command, you do not need any privileges.
TRUE enables the SQL trace facility.
FALSE disables the SQL trace facility.
GLOBAL_NAMES
controls the enforcement of global name resolution for your session:
TRUE enables the enforcement of global name resolution.
FALSE disables the enforcement of global name resolution.
For information on enabling and disabling global name resolution with this parameter, see the ALTER SYSTEM command .
NLS_LANGUAGE
changes the language in which Oracle7 returns errors and other messages. This parameter also implicitly specifies new values for these items:
NLS_TERRITORY
implicitly specifies new values for these items:
NLS_DATE_FORMAT
explicitly specifies a new default date format. The 'fmt' value must be a date format model as specified in the section "Date Format" .
NLS_DATE_LANGUAGE
NLS_NUMERIC_CHARACTERS
explicitly specifies a new decimal character and group separator. The 'text' value must have this form:
'dg'
where:
d is the new decimal character.
g is the new group separator.
The decimal character and the group separator must be two different single-byte characters, and cannot be a numeric value or any of the following characters:
"+" plus "-" minus (or hyphen) "<" less-than ">" greater-than
NLS_ISO_CURRENCY
explicitly specifies the territory whose ISO currency symbol should be used.
NLS_CURRENCY
explicitly specifies a new local currency symbol. The symbol cannot exceed 10 characters.
NLS_SORT
changes the sequence into which Oracle7 sorts character values.
sort specifies the name of a linguistic sort sequence.
BINARY specifies a binary sort.
The default sort for all character sets is binary.
NLS_CALENDAR
explicitly specifies a new calendar type.
LABEL
changes your DBMS session label to either:
MLS_LABEL_FORMAT
changes the default label format for your session. For more information on this parameter, see the Trusted Oracle7 Server Administrator's Guide.
OPTIMIZER_GOAL
specifies the approach and goal of the optimizer for your session:
RULE specifies the rule-based approach.
ALL_ROWS specifies the cost-based approach and optimizes for best throughput.
FIRST_ROWS specifies the cost-based approach and optimizes for best response time.
CHOOSE causes the optimizer to choose an optimization approach based on the presence of statistics in the data dictionary.
FLAGGER
ENTRY flags for SQL92 Entry level
INTERMEDIATE
flags for SQL92 Intermediate level
FULL flags for SQL92 Full level
OFF turns off flagging
SESSION_CACHED_CURSORS
specify the size of the session cache for holding frequently used cursors.
integer specifies how many cursors can be retained in the cache.
CLOSE_OPEN_CACHED_CURSORS
controls whether cursors opened and cached in memory by PL/SQL are automatically closed at each COMMIT. A value of FALSE signifies that cursors opened by PL/SQL are held open so that subsequent executions need not open a new cursor. A value of TRUE causes open cursors to be closed at each COMMIT or ROLLBACK.
INSTANCE
HASH_JOIN_ENABLED
enables or disables the use of the hash join operation in queries. The default is TRUE, which allows hash joins.
HASH_AREA_SIZE
specifies in bytes the amount of memory to use for hash join operations. The default is twice the value of the SORT_AREA_SIZE initialization parameter.
HASH_MULTIBLOCK_IO_COUNT
specifies the number of data blocks to read and write during a hash join operation. The value multiplied by the DB_BLOCK_SIZE initialization parameter should not exceed 64 kilobytes. The default value for this parameter is 1. If the multi-threaded server is used, the value is always 1, and any value given here is ignored.
REMOTE_DEPENDENCIES_MODE
specifies how dependencies of remote stored procedures are handled by the session. For more information, refer to "Remote Dependencies" in the Oracle7 Server Application Developer's Guide.
ISOLATION_LEVEL
specifies how transactions containing database modifications are handled.
SERIALIZABLE
transactions in the session use the serializable transaction isolation mode as specified in SQL92. That is, if a serializable transaction attempts to execute a DML statement that updates rows that are updated by another uncommitted transaction at the start of the serializable transaction, then the DML statement fails. A serializable transaction can see its own updates. The COMPATIBLE initialization parameter must be set to 7.3.0 or higher for SERIALIZABLE mode to work.
READ COMMITTED
transactions in the session will use the default Oracle transaction behavior. Thus, if the transaction contains DML that require row locks held by another transaction, then the DML statement will wait until the row locks are released.
CLOSE DATABASE LINK
closes the database link dblink, eliminating your session's connection to the remote database. The database link cannot be currently in use by an active transaction or an open cursor.
ADVISE
COMMIT places the value 'C' in DBA_2PC_PENDING.ADVICE.
ROLLBACK places the value 'R' in DBA_2PC_PENDING.ADVICE.
NOTHING places the value ' ' in DBA_2PC_PENDING.ADVICE.
COMMIT IN PROCEDURE
specifies whether procedures and stored functions can issue COMMIT and ROLLBACK statements:
ENABLE permits procedures and stored functions to issue these statements.
DISABLE prohibits procedures and stored functions from issuing these statements.
For more information on the SQL trace facility, including how to format and interpret its output, see Appendix A "Performance Diagnostic Tools" of the Oracle7 Server Tuning.
Example I
To enable the SQL trace facility for your session, issue the following statement:
ALTER SESSION SET SQL_TRACE = TRUE
Example II
The following statement changes the language for error messages to the French:
ALTER SESSION SET NLS_LANGUAGE = French
Oracle7 returns error messages in French:
SELECT * FROM emp ORA-00942: Table ou vue n'existe pas
Example III
ALTER SESSION
SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS'
Oracle7 uses the new default date format:
SELECT TO_CHAR(SYSDATE) Today FROM DUAL TODAY ------------------- 1993 08 12 14:25:56
Example IV
The following statement changes the language for date format elements to the French:
ALTER SESSION SET NLS_DATE_LANGUAGE = French SELECT TO_CHAR(SYSDATE, 'Day DD Month YYYY') Today FROM DUAL TODAY --------------------------- Mardi 28 Février 1992
D (decimal character) | is the character that separates the integer and decimal portions of a number. |
G (group separator) | is the character that separates groups of digits in the integer portion of a number. |
The decimal character and the group separator can only be single-byte characters and cannot be the same character. If the decimal character is not a period (.), you must use single quotation marks to enclose all number values that appear in expressions in your SQL statements. When not using a period for the decimal point, you should always use the TO_NUMBER function to ensure that a valid number is retrieved.
Example V
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.'
Oracle7 returns these new characters when you use their number format elements:
SELECT TO_CHAR( SUM(sal), 'L999G999D99') Total FROM emp TOTAL ------------- FF29.025,00
Example VI
ALTER SESSION SET NLS_ISO_CURRENCY = America
SELECT TO_CHAR( SUM(sal), 'L999G999D99') Total FROM emp TOTAL ------------- USD29,025.00
Example VII
The following statement dynamically changes the local currency symbol to 'DM':
ALTER SESSION SET NLS_CURRENCY = 'DM' SELECT TO_CHAR( SUM(sal), 'L999G999D99') Total FROM emp TOTAL ------------- DM29.025,00
Example VIII
The following statement dynamically changes the linguistic sort sequence to Spanish:
ALTER SESSION
SET NLS_SORT = XSpanish
Oracle7 sorts character values based on their position in the Spanish linguistic sort sequence.
rule-based
The optimizer optimizes a SQL statement based on the indexes and clusters associated with the accessed tables, the syntactic constructs of the statement, and a heuristically ranked list of these constructs.
cost-based
The optimizer optimizes a SQL statement by considering statistics describing the tables, indexes, and clusters accessed by the statement as well as the information considered with the rule-based approach.
With the cost-based approach, the optimizer can optimize a SQL statement with one of these goals:
best throughput
or the minimal time necessary to return all rows accessed by the statement
best response time
or the minimal time necessary to return the first row accessed by the statement
When you start your instance, the optimization approach is established by the initialization parameter OPTIMIZER_MODE. If this parameter establishes the cost-based approach, the default goal is best throughput. You can subsequently change the optimization approach or the goal of the cost-based optimization approach for your session with the OPTIMIZER_GOAL parameter.
Example IX
The following statement changes the goal of the cost-based approach to best response time:
ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS
For information on how to choose a goal for the cost-based approach based on the characteristics of your application, see the Oracle7 Server Tuning.
Oracle7 uses the shared SQL area to determine if more than three parse requests were issued on a given statement. If so, Oracle7 moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session will find the cursor in the session cursor cache.
Session cursors are automatically cached if the initialization parameter, SESSION_CACHED_CURSORS is set to a positive value. This parameter specifies the maximum number of session cursors to be kept in the cache. A least recently used algorithm ages out entries in the cache to make room for new entries when needed. You use the ALTER SESSION SET SESSION_CACHED_CURSORS command to dynamically enable session cursor caching.
For more information on session cursor caching, see the Oracle7 Server Tuning.
You can use the CLOSE DATABASE LINK clause of the ALTER SESSION command to explicitly close a database link if you do not plan to use it again in your session. You may want to explicitly close a database link if the network overhead associated with leaving it open is costly. Before closing a database link, you must first close all cursors that use the link and then end your current transaction if it uses the link.
Example X
This example updates the employee table on the SALES database using a database link, commits the transaction, and explicitly closes the database link:
UPDATE emp@sales
SET sal = sal + 200
WHERE empno = 9001
COMMIT
ALTER SESSION
CLOSE DATABASE LINK sales
Before committing a distributed transaction, you can use the ADVISE clause of the ALTER SESSION command to send advice to a remote database in the event a distributed transaction becomes in-doubt. If the transaction becomes in-doubt, the advice appears in the ADVICE column of the DBA_2PC_PENDING view on the remote database. The administrator of that database can then use this advice to decide whether to commit or roll back the transaction on the remote database. For more information on distributed transactions and how to decide whether to commit or roll back in-doubt distributed transactions, see the "Database Administration" chapter of Oracle7 Server Distributed Systems, Volume I.
You issue multiple ALTER SESSION statements with the ADVISE clause in a single transaction. Each such statement sends advice to the databases referenced in the following statements in the transaction until another such statement is issued. This allows you to send different advice to different databases.
Example XI
ALTER SESSION
ADVISE COMMIT
INSERT INTO emp@site1
VALUES (8002, 'FERNANDEZ', 'ANALYST', 7566, TO_DATE('04-OCT-1992', 'DD-MON-YYYY'), 3000, NULL, 20)
ALTER SESSION
ADVISE ROLLBACK
DELETE FROM emp@site2
WHERE empno = 8002
COMMIT
This transaction has two ALTER SESSION statements with the ADVISE clause. If the transaction becomes in-doubt, SITE1 is sent the advice 'COMMIT' by virtue of the first ALTER SESSION statement and SITE2 is sent the advice 'ROLLBACK' by virtue of the second.
ALTER SESSION DISABLE COMMIT IN PROCEDURE
If you subsequently call a procedure or a stored function that issues a COMMIT or ROLLBACK statement, Oracle7 returns an error and does not commit or roll back the transaction. SQL*Forms automatically prohibits COMMIT and ROLLBACK statements in procedures and stored functions.
You can subsequently allow procedures and stored functions to issue COMMIT and ROLLBACK statements in your session by issuing the following statement:
ALTER SESSION ENABLE COMMIT IN PROCEDURE
This command does not apply to database triggers. Triggers can never issue COMMIT or ROLLBACK statements.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the snapshot's creation label or you must satisfy one of the following criteria:
schema
is the schema containing the snapshot. If you omit schema, Oracle7 assumes the snapshot is in your own schema.
snapshot
is the name of the snapshot to be altered.
PCTFREE PCTUSED INITRANS MAXTRANS
change the values of these parameters for the internal table that Oracle7 uses to maintain the snapshot's data. For information on the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters, see the CREATE TABLE command .
STORAGE
changes the storage characteristics of the internal table Oracle7 uses to maintain the snapshot's data. See the STORAGE clause .
USING INDEX
REFRESH
changes the mode and times for automatic refreshes:
FAST specifies a fast refresh, or a refresh using the snapshot log associated with the master table.
COMPLETE specifies a complete refresh, or a refresh that re-executes the snapshot's query.
If you omit the FAST, COMPLETE, and FORCE options, Oracle7 uses FORCE by default.
START WITH specifies a date expression for the next automatic refresh time.
NEXT specifies a new date expression for calculating the interval between automatic refreshes.
START WITH and NEXT values must evaluate to times in the future.
Example I
The following statement changes the automatic refresh mode for the HQ_EMP snapshot to FAST:
ALTER SNAPSHOT hq_emp REFRESH FAST
The next automatic refresh of the snapshot will be a fast refresh provided it is a simple snapshot and its master table has a snapshot log that was created before the snapshot was created or last refreshed.
Because the REFRESH clause does not specify START WITH or NEXT values, the refresh intervals established by the REFRESH clause when the HQ_EMP snapshot was created or last altered are still used.
Example II
ALTER SNAPSHOT branch_emp
REFRESH NEXT SYSDATE+7
Because the REFRESH clause does not specify a START WITH value, the next automatic refresh occurs at the time established by the START WITH and NEXT values specified when the BRANCH_EMP snapshot was created or last altered.
At the time of the next automatic refresh, Oracle7 refreshes the snapshot, evaluates the NEXT expression SYSDATE+7 to determine the next automatic refresh time, and continues to automatically refresh the snapshot once a week.
Because the REFRESH clause does not explicitly specify a refresh mode, Oracle7 continues to use the refresh mode specified by the REFRESH clause of a previous CREATE SNAPSHOT or ALTER SNAPSHOT statement.
Example III
ALTER SNAPSHOT sf_emp REFRESH COMPLETE START WITH TRUNC(SYSDATE+1) + 9/24 NEXT SYSDATE+7
The START WITH value establishes the next automatic refresh for the snapshot to be 9:00am tomorrow. At that point, Oracle7 performs a fast refresh of the snapshot, evaluates the NEXT expression, and subsequently refreshes the snapshot every week.
schema
is the schema containing the snapshot log and its master table. If you omit schema, Oracle7 assumes the snapshot log is in your own schema.
table
is the name of the master table associated with the snapshot log to be altered.
PCTFREE PCTUSED INITRANS MAXTRANS
change the values of these parameters for the snapshot log. See the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters of the CREATE TABLE command .
STORAGE
changes the storage characteristics of the snapshot log. See the STORAGE clause .
Example
The following statement changes the MAXEXTENTS value of a snapshot log:
ALTER SNAPSHOT LOG dept STORAGE MAXEXTENTS 50
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must be the equivalent of DBHIGH.
ENABLE RESTRICTED SESSION
allows only users with RESTRICTED SESSION system privilege to logon to Oracle7.
DISABLE RESTRICTED SESSION
reverses the effect of the ENABLE RESTRICTED SESSION option, allowing all users with CREATE SESSION system privilege to logon to Oracle7.
FLUSH SHARED_POOL
clears all data from the shared pool in the System Global Area (SGA).
You can use the following options when your instance has the database mounted, open or closed:
CHECKPOINT
GLOBAL performs a checkpoint for all instances that have opened the database.
If you omit both the GLOBAL and LOCAL options, Oracle7 performs a global checkpoint.
CHECK DATAFILES
verifies access to online data files.
GLOBAL verifies that all instances that have opened the database can access all online data files.
LOCAL verifies that your instance can access all online data files.
If you omit both the GLOBAL and LOCAL options, Oracle7 uses GLOBAL by default.
You can only use the following parameters and options when your instance has the database open:
RESOURCE_LIMIT
controls resource limits
TRUE enables resource limits.
FALSE disables resource limits.
GLOBAL_NAMES
controls the enforcement of global naming:
TRUE enables the enforcement of global names.
FALSE disables the enforcement of global names.
SCAN_INSTANCES
in a parallel server, specify the number of instances to participate in parallelized operations.
CACHE_INSTANCES
in a parallel server, specify the number of instances that will cache a table.
For more information on parallel operations, see the "Parallel Query Option" chapter of Oracle7 Server Tuning.
MTS_SERVERS
specifies a new minimum number of shared server processes.
MTS_DISPATCHERS
specifies a new number of dispatcher processes:
protocol is the network protocol of the dispatcher processes.
integer is the new number of dispatcher processes of the specified protocol.
You can specify multiple MTS_DISPATCHERS parameters in a single command for multiple network protocols.
LICENSE_MAX_SESSIONS
limits the number of sessions on your instance. A value of 0 disables the limit.
LICENSE_SESSIONS_WARNING
establishes a threshold of sessions over which Oracle7 writes warning messages to the ALERT file for subsequent sessions. A value of 0 disables the warning threshold.
LICENSE_MAX_USERS
limits the number of concurrent users on your database. A value of 0 disables the limit.
REMOTE_DEPENDENCIES_MODE
specifies how dependencies of remote stored procedures are handled by the server. For more information, refer to "Remote Dependencies" in the Oracle7 Server Application Developer's Guide.
SWITCH LOGFILE
switches
ENABLE DISTRIBUTED RECOVERY
enables
distributed recovery. In a single-process environment, you must use this option to initiate distributed recovery.
DISABLE DISTRIBUTED RECOVERY
disables distributed recovery.
ARCHIVE LOG
manually archives redo log files or enables or disables automatic archiving. See the ARCHIVE LOG clause .
KILL SESSION
terminates a session. You must identify the session with both of the following values from the V$SESSION view:
integer1 is the value of the SID column.
integer2 is the value of the SERIAL# column.
You may want to restrict logons if you are performing application maintenance and you want only application developers with RESTRICTED SESSION system privilege to log on. To restrict logons, issue the following statement:
ALTER SYSTEM
ENABLE RESTRICTED SESSION
You can then terminate any existing sessions using the KILL SESSION clause of the ALTER SYSTEM command.
After performing maintenance on your application, issue the following statement to allow any user with CREATE SESSION system privilege to log on:
ALTER SYSTEM
DISABLE RESTRICTED SESSION
ALTER SYSTEM FLUSH SHARED_POOL
The above statement does not clear shared SQL and PL/SQL areas for SQL statements, stored procedures, functions, packages, or triggers that are currently being executed or for SQL SELECT statements for which all rows have not yet been fetched.
The following statement forces a checkpoint:
ALTER SYSTEM CHECKPOINT
Oracle7 does not return control to you until the checkpoint is complete.
The following statement verifies that all instances that have opened the database can access all online data files:
ALTER SYSTEM
CHECK DATAFILES GLOBAL
Enabling resource limits only causes Oracle7 to enforce the resource limits assigned to users. To choose resource limit values for a user, you must create a profile, or a set of limits, and assign that profile to the user. For more information on this process, see the CREATE PROFILE command and the CREATE USER command .
This ALTER SYSTEM statement dynamically enables resource limits:
ALTER SYSTEM
SET RESOURCE_LIMIT = TRUE
It is recommended that you enable global name resolution. For more information on global name resolution and how Oracle7 enforces it, see section "Referring to Objects in Remote Databases" and Oracle7 Server Distributed Systems, Volume I.
MTS_SERVERS
This parameter specifies the initial and minimum number of shared server processes. Oracle7 may automatically change the number of shared server processes if the load on the existing processes changes. While your instance is running, the number of shared server processes can vary between the values of the initialization parameters MTS_SERVERS and MTS_MAX_SERVERS.
MTS_DISPATCHERS
For more information on the multi-threaded server architecture, see Oracle7 Server Concepts.
You can subsequently use the MTS_SERVERS and MTS_DISPATCHERS parameters of the ALTER SYSTEM command to perform one of the following operations while the instance is running:
To create additional shared server processes:
You can cause Oracle7 to create additional shared server processes by increasing the minimum number of shared server processes.
To terminate existing shared server processes:
Oracle7 terminates the shared server processes after finishing processing their current calls,unless the load on the server processes is so high that it cannot be managed by the remaining processes.
To create more dispatcher processes for a specific protocol:
You can create additional dispatcher processes up to a maximum across all protocols specified by the initialization parameter MTS_MAX_DISPATCHERS.
You cannot use this command to create dispatcher processes for network protocols that are not specified by the initialization parameter MTS_DISPATCHERS. To create dispatcher processes for a new protocol, you must change the value of the initialization parameter.
To terminate existing dispatcher processes for a specific protocol:
Oracle7 terminates the dispatcher processes only after their current user processes disconnect from the instance.
Example I
The following statement changes the minimum number of shared server processes to 25:
ALTER SYSTEM
SET MTS_SERVERS = 25
If there are currently fewer than 25 shared server processes, Oracle7 creates more. If there are currently more than 25, Oracle7 terminates some of them when they are finished processing their current calls if the load could be managed by the remaining 25.
Example II
The following statement dynamically changes the number of dispatcher processes for the TCP/IP protocol to 5 and the number of dispatcher processes for the DECNET protocol to 10:
ALTER SYSTEM
SET MTS_DISPATCHERS = 'TCP, 5'
MTS_DISPATCHERS = 'DECnet, 10'
If there are currently fewer than 5 dispatcher processes for TCP, Oracle7 creates new ones. If there are currently more than 5, Oracle7 terminates some of them after the connected users disconnect.
If there are currently fewer than 10 dispatcher processes for DECnet, Oracle7 creates new ones. If there are currently more than 10, Oracle7 terminates some of them after the connected users disconnect.
If there are currently existing dispatchers for another protocol, the above statement does not affect the number of dispatchers for this protocol.
LICENSE_MAX_SESSIONS
This parameter establishes the concurrent usage licensing limit, or the limit for concurrent sessions. Once this limit is reached, only users with RESTRICTED SESSION system privilege can connect.
LICENSE_SESSIONS_WARNING
This parameter establishes a warning threshold for concurrent usage. Once this threshold is reached, Oracle7 writes a warning message to the database ALERT file for each subsequent session. Also, users with RESTRICTED SESSION system privilege receive warning messages when they begin subsequent sessions.
LICENSE_MAX_USERS
This parameter establishes the limit for users connected to your database. Once this limit for users is reached, more users cannot connect.
You can subsequently use the LICENSE_MAX_SESSIONS, LICENSE_SESSIONS_WARNING, and LICENSE_MAX_USERS parameters of the ALTER SYSTEM command to dynamically change or disable limits or thresholds while your instance is running. Do not disable or raise session or user limits unless you have appropriately upgraded your Oracle7 license. For information on upgrading your license, contact your Oracle sales representative.
New limits apply only to future sessions and users:
The following statement dynamically changes the limit on sessions for your instance to 64 and the warning threshold for sessions on your instance to 54:
ALTER SYSTEM
SET LICENSE_MAX_SESSIONS = 64
LICENSE_SESSIONS_WARNING = 54
If the number of sessions reaches 54, Oracle7 writes a warning message to the ALERT file for each subsequent session. Also, users with RESTRICTED SESSION system privilege receive warning messages when they begin subsequent sessions.
If the number of sessions reaches 64, only users with RESTRICTED SESSION system privilege can begin new sessions until the number of sessions falls below 64 again.
Example IV
The following statement dynamically disables the limit for sessions on your instance:
ALTER SYSTEM
SET LICENSE_MAX_SESSIONS = 0
After you issue the above statement, Oracle7 no longer limits the number of sessions on your instance.
Example V
The following statement dynamically changes the limit on the number of users in the database to 200:
ALTER SYSTEM
SET LICENSE_MAX_USERS = 200
After you issue the above statement, Oracle7 prevents the number of users in the database from exceeding 200.
The following statement forces a log switch:
ALTER SYSTEM
SWITCH LOGFILE
If you are using Oracle7 in multiple-process mode, this distributed recovery is performed automatically. If you are using Oracle7 in single-process (single user) mode, such as on the MS-DOS operating system, you must explicitly initiate distributed recovery with the following statement.
ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY
You may need to issue the above statement more than once to recover an in-doubt transaction, especially if the remote node involved in the transaction is not accessible. In-doubt transactions appear in the data dictionary view DBA_2PC_PENDING. You can tell that the transaction is recovered when it no longer appears in DBA_2PC_PENDING. For more information about distributed transactions and distributed recovery, see Oracle7 Server Distributed Systems, Volume I.
ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY
You may want to disable distributed recovery for demonstration purposes. You can then enable distributed recovery again by issuing an ALTER SYSTEM statement with the ENABLE DISTRIBUTED RECOVERY clause.
If you try to kill a session that is performing some activity that must be completed, such as waiting for a reply from a remote database or rolling back a transaction, Oracle7 waits for this activity to complete, kills the session, and then returns control to you. If the waiting lasts as long as a minute, Oracle7 marks the session to be killed and returns control to you with a message indicating that the session is marked to be killed. Oracle7 then kills the session when the activity is complete.
Example VI
Consider this data from the V$SESSION dynamic performance table:
SELECT sid, serial#, username FROM v$session SID SERIAL# USERNAME ---------- ---------- ------------------------------ 1 1 2 1 3 1 4 1 5 1 7 1 8 28 OPS$BQUIGLEY 10 211 OPS$SWIFT 11 39 OPS$OBRIEN 12 13 SYSTEM 13 8 SCOTT
The following statement kills the session of the user SCOTT using the SID and SERIAL# values from V$SESSION:
ALTER SYSTEM KILL SESSION '13, 8'
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the table's creation label or you must satisfy one of the following criteria:
schema
is the schema containing the table. If you omit schema, Oracle7 assumes the table is in your own schema.
table
is the name of the table to be altered.
ADD
adds a column or integrity constraint.
MODIFY
modifies the definition of an existing column. If you omit any of the optional parts of the column definition (datatype, default value, or column constraint), these parts remain unchanged.
column
is the name of the column to be added or modified.
datatype
specifies a datatype for a new column or a new datatype for an existing column.
You can only omit the datatype if the statement also designates the column as part of the foreign key of a referential integrity constraint. Oracle7 automatically assigns the column the same datatype as the corresponding column of the referenced key of the referential integrity constraint.
DEFAULT
specifies a default value for a new column or a new default for an existing column. Oracle7 assigns this value to the column if a subsequent INSERT statement omits a value for the column. The datatype of the default value must match the datatype specified for the column. The column must also be long enough to hold the default value. A DEFAULT expression cannot contain references to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants that are not fully specified.
column_constraint
adds or removes a NOT NULL constraint to or from and existing column. See the syntax of column_constraint .
table_constraint
adds an integrity constraint to the table. See the syntax of table_constraint .
PCTFREE PCTUSED INITRANS MAXTRANS
changes the value of specified parameters for the table. See the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters of the CREATE TABLE command .
STORAGE
changes the storage characteristics of the table. See the STORAGE clause beginning .
DROP
drops an integrity constraint. See the DROP clause .
ALLOCATE EXTENT
explicitly allocates a new extent for the table.
INSTANCE makes the new extent available to the freelist group associated with the specified instance. If the instance number exceeds the maximum number of freelist groups, the former is divided by the latter, and the remainder is used to identify the freelist group to be used. An instance is identified by the value of its initialization parameter INSTANCE_NUMBER. If you omit this parameter, the space is allocated to the table, but is not drawn from any particular freelist group. Rather the master freelist is used, and space is allocated as needed. For more information, see Oracle7 Server Concepts. Only use this parameter if you are using Oracle7 with the Parallel Server option in parallel mode.
Explicitly allocating an extent with this clause does affect the size for the next extent to be allocated as specified by the NEXT and PCTINCREASE storage parameters.
DEALLOCATE UNUSED
explicitly deallocate unused space at the end of the table and make the freed space available for other segments. You can free only unused space above the high-water mark. If KEEP is omitted, all unused space is freed. For more information, see the deallocate_clause.
KEEP specifies the number of bytes above the high-water mark that the table will have after deallocation. If the number of remaining extents are less than MINEXTENTS, then MINEXTENTS is set to the current number of extents. If the initial extent becomes smaller than INITIAL, then INITIAL is set to the value of the current initial extent.
ENABLE enable_clause
enables a single integrity constraint or all triggers associated with the table. See the ENABLE clause .
ENABLE TABLE LOCK
enables DML and DDL locks on a table in a parallel server environment. For more information, see Oracle7 Parallel Server Concepts & Administration.
DISABLE disable_clause
disables a single integrity constraint or all triggers associated with the table. See the DISABLE clause .
Integrity constraints specified in DISABLE clauses must be defined in the ALTER TABLE statement or in a previously issued statement. You can also enable and disable integrity constraints with the ENABLE and DISABLE keywords of the CONSTRAINT clause. If you define an integrity constraint but do not explicitly enable or disable it, Oracle7 enables it by default.
DISABLE TABLE LOCK
disables DML and DDL locks on a table to improve performance in a parallel server environment. For more information, see Oracle7 Parallel Server Concepts & Administration.
PARALLEL
specifies the degree of parallelism for the table. See the parallel_clause .
CACHE
Specifies that the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.
NOCACHE
Specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the default behavior.
If you create a view with a query that uses the asterisk (*) in the select list to select all columns from the base table and you subsequently add columns to the base table, Oracle7 will not automatically add the new column to the view. To add the new column to the view, you can re-create the view using the CREATE VIEW command with the OR REPLACE option.
Operations performed by the ALTER TABLE command can cause Oracle7 to invalidate procedures and stored functions that access the table. For information on how and when Oracle7 invalidates such objects, see the "Dependencies Among Schema Objects" chapter of Oracle7 Server Concepts.
You can define a NOT NULL constraint on an existing column only if the column contains no nulls.
Example I
ALTER TABLE emp ADD (thriftplan NUMBER(7,2), loancode CHAR(1) NOT NULL)
Example II
The following statement increases the size of the THRIFTPLAN column to nine digits:
ALTER TABLE emp MODIFY (thriftplan NUMBER(9,2))
Because the MODIFY clause contains only one column definition, the parentheses around the definition are optional.
Example III
The following statement changes the values of the PCTFREE and PCTUSED parameters for the EMP table to 30 and 60, respectively:
ALTER TABLE emp PCTFREE 30 PCTUSED 60
Example IV
The following statement allocates an extent of 5 kilobytes for the EMP table and makes it available to instance 4:
ALTER TABLE emp ALLOCATE EXTENT (SIZE 5K INSTANCE 4)
Because this command omits the DATAFILE parameter, Oracle7 allocates the extent in one of the data files belonging to the tablespace containing the table.
Example V
This example modifies the BAL column of the ACCOUNTS table so that it has a default value of 0:
ALTER TABLE accounts MODIFY (bal DEFAULT 0)
If you subsequently add a new row to the ACCOUNTS table and do not specify a value for the BAL column, the value of the BAL column is automatically 0:
INSERT INTO accounts(accno, accname) VALUES (accseq.nextval, 'LEWIS') SELECT * FROM accounts WHERE accname = 'LEWIS' ACCNO ACCNAME BAL ------ ------- --- 815234 LEWIS 0
Other Examples
For examples of defining integrity constraints with the ALTER TABLE command, see the CONSTRAINT clause beginning .
For examples of enabling, disabling, and dropping integrity constraints and triggers with the ALTER TABLE command, see the ENABLE clause , the DISABLE clause , and DROP clause .
For examples of changing the value of a table's storage parameters, see the STORAGE clause .
tablespace
is the name of the tablespace to be altered.
ADD DATAFILE
adds the datafile specified by filespec to the tablespace. See the syntax description of filespec. You can add a datafile while the tablespace is online or offline. Be sure that the datafile is not already in use by another database.
AUTOEXTEND
enables or disables the autoextending of the size of the datafile in the tablespace.
OFF disable autoextend if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in further ALTER TABLESPACE AUTOEXTEND commands.
ON enable autoextend.
NEXT the size in bytes of the next increment of disk space to be automatically allocated to the datafile when more extents are required. You can also use K or M to specify this size in kilobytes or megabytes. The default is one data block.
MAXSIZE maximum disk space allowed for automatic extension of the datafile.
UNLIMITED set no limit on allocating disk space to the datafile.
RENAME DATAFILE
renames one or more of the tablespace's datafiles. Take the tablespace offline before renaming the datafile. Each 'filename' must fully specify a datafile using the conventions for filenames on your operating system.
This clause only associates the tablespace with the new file rather than the old one. This clause does not actually change the name of the operating system file. You must change the name of the file through your operating system.
COALESCE
for each datafile in the tablespace, coalesce all contiguous free extents into larger contiguous extents.
COALESCE cannot be specified with any other command option.
DEFAULT STORAGE
specifies the new default storage parameters for objects subsequently created in the tablespace. See the STORAGE clause.
ONLINE
OFFLINE
takes the tablespace offline and prevents further access to its segments.
TEMPORARY performs a checkpoint for all online datafiles in the tablespace but does not ensure that all files can be written. Any offline files may require media recovery before you bring the tablespace back online.
The default is NORMAL.
Suggestion: Before taking a tablespace offline for a long time, you may want to alter any users who have been assigned the tablespace as either a default or temporary tablespace. When the tablespace is offline, these users cannot allocate space for objects or sort areas in the tablespace. You can reassign users new default and temporary tablespaces with the ALTER USER command.
BEGIN BACKUP
signifies that an online backup is to be performed on the datafiles that comprise this tablespace. This option does not prevent users from accessing the tablespace. You must use this option before beginning an online backup. You cannot use this option on a read-only tablespace.
While the backup is in progress, you cannot:
END BACKUP
signifies that an online backup of the tablespace is complete. Use this option as soon as possible after completing an online backup. You cannot use this option on a read-only tablespace.
READ ONLY
signifies that no further write operations are allowed on the tablespace.
READ WRITE
signifies that write operations are allowed on a previously read only tablespace.
PERMANENT
specifies that the tablespace is to be converted from a temporary to a permanent one. A permanent tablespace is one wherein permanent database objects can be stored. This is the default when a tablespace is created.
TEMPORARY
specifies that the tablespace is to be converted from a permanent to a temporary one. A temporary tablespace is one wherein no permanent database objects can be stored.
Before taking a tablespace offline for a long time, you may want to alter any users who have been assigned the tablespace as either a default or temporary tablespace. When the tablespace is offline, these users cannot allocate space for objects or sort areas in the tablespace. You can reassign users new default and temporary tablespaces with the ALTER USER command.
Once a tablespace is read-only, you can copy its files to read-only media. You must then rename the datafiles in the control file to point to the new location by using the SQL command ALTER DATABASE RENAME.
If you forget to indicate the end of an online tablespace backup, and an instance failure or SHUTDOWN ABORT occurs, Oracle assumes that media recovery (possibly requiring archived redo log) is necessary at the next instance start up. To restart the database without media recovery, see Oracle7 Server Administrator's Guide.
Example I
The following statement signals to the database that a backup is about to begin:
ALTER TABLESPACE accounting
BEGIN BACKUP
Example II
The following statement signals to the database that the backup is finished:
ALTER TABLESPACE accounting
END BACKUP
Example III
This example moves and renames a datafile associated with the ACCOUNTING tablespace from 'DISKA:PAY1.DAT' to 'DISKB:RECEIVE1.DAT':
ALTER TABLESPACE accounting OFFLINE NORMAL
ALTER TABLESPACE accounting RENAME DATAFILE 'diska:pay1.dbf' TO 'diskb:receive1.dbf'
ALTER TABLESPACE accounting ONLINE
Example IV
The following statement adds a datafile to the tablespace; when more space is needed new extents of size 10 kilobytes will be added up to a maximum of 100 kilobytes:
ALTER TABLESPACE accounting ADD DATAFILE 'disk3:pay3.dbf' AUTOEXTEND ON NEXT 10 K MAXSIZE 100 K
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the trigger's creation label or you must satisfy one of the following criteria:
schema
is the schema containing the trigger. If you omit schema, Oracle7 assumes the trigger is in your own schema.
trigger
is the name of the trigger to be altered.
ENABLE
DISABLE
COMPILE
compiles the trigger.
When you issue an ALTER TRIGGER statement, Oracle7 recompiles the trigger regardless of whether it is valid or invalid.
When you recompile a trigger, Oracle7 first recompiles objects upon which the trigger depends, if any of these objects are invalid. If Oracle7 recompiles the trigger successfully, the trigger becomes valid. If recompiling the trigger results in compilation errors, then Oracle7 returns an error and the trigger remains invalid. You can then debug triggers using the predefined package DBMS_OUTPUT. For information on debugging procedures, see the "Using Procedures and Packages" chapter of the Oracle7 Server Application Developer's Guide. For information on how Oracle7 maintains dependencies among schema objects, including remote objects, see the "Dependencies Among Schema Objects" chapter of Oracle7 Server Concepts.
Note: This command does not change the declaration or definition of an existing trigger. To redeclare or redefine a trigger, you must use the CREATE TRIGGER command with the OR REPLACE option.
enabled
If a trigger is enabled, Oracle7 fires the trigger when a triggering statement is issued.
disabled
If the trigger is disabled, Oracle7 does not fire the trigger when a triggering statement is issued.
When you create a trigger, Oracle7 enables it automatically. You can use the ENABLE and DISABLE options of the ALTER TRIGGER command to enable and disable a trigger.
You can also use the ENABLE and DISABLE clauses of the ALTER TABLE command to enable and disable all triggers associated with a table.
Note: The ALTER TRIGGER command does not change the definition of an existing trigger. To redefine a trigger, you must use the CREATE TRIGGER command with the OR REPLACE option.
Example
When this trigger is created, Oracle7 enables it automatically. You can subsequently disable the trigger with the following statement:
ALTER TRIGGER reorder
DISABLE
When the trigger is disabled, Oracle7 does not fire the trigger when an UPDATE statement causes the part's inventory to fall below its reorder point.
After disabling the trigger, you can subsequently enable it with the following statement:
ALTER TRIGGER reorder
ENABLE
After you reenable the trigger, Oracle7 fires the trigger whenever a part's inventory falls below its reorder point as a result of an UPDATE statement. Note that a part's inventory may have fallen below its reorder point while the trigger was disabled. When you reenable the trigger, Oracle7 does not automatically fire the trigger for this part.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the user's creation label or you must satisfy one of the following criteria:
You can only establish a default or temporary tablespace if both your DBMS label and the user's creation label dominates the tablespace's creation label or if both you and the user have READUP system privilege.
You can only change a user's profile if both your DBMS label and the user's creation label dominate the profile's creation label or if both you and the user have READUP system privilege.
user
is the user to be altered.
indicates how Oracle7 permits user access.
BY specifies a new password for the user. The password is not usually quoted and must also follow the rules described in the section "Object Naming Rules" . A password can only contain single-byte characters from your database character set regardless of whether your character set also contains multi-byte characters.
EXTERNALLY indicates that Oracle7 verifies user access with the operating system, rather than with a password. See the CREATE USER command .
Although you do not need privileges to change your own password, you must have ALTER USER system privilege to change from BY password to EXTERNALLY or vice versa.
DEFAULT TABLESPACE
specifies the default tablespace for object creation.
TEMPORARY TABLESPACE
specifies the tablespace for the creation of temporary segments for operations such as sorting that require more space than is available in memory.
QUOTA
establishes a space quota of integer bytes on the tablespace for the user. This quota is the maximum space in tablespace that can be allocated for objects in the user's schema. You can use K or M to specify the quota in kilobytes or megabytes. You need not have quota on the tablespace to establish a quota on the tablespace for another user. See the CREATE USER command .
If you reduce an existing quota to a value below the space allocated for existing objects in the user's schema in the tablespace, no more space in the tablespace can be allocated to objects in the schema.
Note that an ALTER USER statement can contain multiple QUOTA clauses for multiple tablespaces.
UNLIMITED places no limit on the space in the tablespace allocated to objects in the user's schema.
PROFILE
changes the user's profile to profile. In subsequent sessions, the user is subject to the limits defined in the new profile.
To assign the default limits to the user, assign the user the DEFAULT profile.
DEFAULT ROLE
establishes default roles for the user. Oracle7 enables the user's default roles at logon. By default, all roles granted to the user are default roles.
ALL makes all the roles granted to the user default roles, except those listed in the EXCEPT clause.
NONE makes none of the roles granted to the user default roles.
Example I
ALTER USER scott
IDENTIFIED BY lion
DEFAULT TABLESPACE tstest
Example II
The following statement assigns the CLERK profile to SCOTT:
ALTER USER scott
PROFILE clerk
In subsequent sessions, SCOTT is restricted by limits in the CLERK profile.
Example III
The following statement makes all roles granted directly to SCOTT default roles, except the AGENT role:
ALTER USER scott
DEFAULT ROLE ALL EXCEPT agent
At the beginning of SCOTT's next session, Oracle7 enables all roles granted directly to SCOTT except the AGENT role.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the view's creation label or you must satisfy one of the following criteria:
schema
is the schema containing the view. If you omit schema, Oracle7 assumes the view is in your own schema.
view
is the name of the view to be recompiled.
COMPILE
causes Oracle7 to recompile the view. The COMPILE keyword is required.
When you issue an ALTER VIEW statement, Oracle7 recompiles the view regardless of whether it is valid or invalid. Oracle7 also invalidates any local objects that depend on the view. For more information, see the "Dependencies Among Schema Objects" chapter of Oracle7 Server Concepts.
Note: This command does not change the definition of an existing view. To redefine a view, you must use the CREATE VIEW command with the OR REPLACE option.
Example
To recompile the view CUSTOMER_VIEW, issue the following statement:
ALTER VIEW customer_view
COMPILE
If Oracle7 encounters no compilation errors while recompiling CUSTOMER_VIEW, CUSTOMER_VIEW becomes valid. If recompiling results in compilation errors, Oracle7 returns an error and CUSTOMER_VIEW remains invalid.
Oracle7 also invalidates all dependent objects. These objects include any procedures, functions, package bodies, and views that reference CUSTOMER_VIEW. If you subsequently reference one of these objects without first explicitly recompiling it, Oracle7 recompiles it implicitly at runtime.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the creation label of the object to be analyzed or you must satisfy one of the following criteria:
INDEX
identifies an index to be analyzed (if no FOR clause is used). If you omit schema, Oracle7 assumes the index is in your own schema.
TABLE
identifies a table to be analyzed. If you omit schema, Oracle7 assumes the table is in your own schema. When you collect statistics for a table, Oracle7 also automatically collects the statistics for each of the table's indexes, provided that no FOR clauses are used.
CLUSTER
identifies a cluster to be analyzed. If you omit schema, Oracle7 assumes the cluster is in your own schema. When you collect statistics for a cluster, Oracle7 also automatically collects the statistics for all the cluster's tables and all their indexes, including the cluster index.
COMPUTE STATISTICS
computes exact statistics about the analyzed object and stores them in the data dictionary.
ESTIMATE STATISTICS
estimates statistics about the analyzed object and stores them in the data dictionary.
ROWS causes Oracle7 to sample integer rows of the table or cluster or integer entries from the index. The integer must be at least 1.
PERCENT causes Oracle7 to sample integer percent of the rows from the table or cluster or integer percent of the index entries. The integer can range from 1 to 99.
Histogram statistics are described in Oracle7 Server Tuning. The following clauses only apply to the ANALYZE TABLE version of this command:
FOR TABLE collect table statistics for the table.
FOR ALL COLUMNS
collect column statistics for all columns in the table.
FOR ALL INDEXED COLUMNS
collect column statistics for all indexed columns in the table.
FOR COLUMNS
collect column statistics for the specified columns.
FOR ALL INDEXES
all indexes associated with the table will be analyzed.
SIZE specifies the maximum number of partitions in the histogram. The default value is 75, minimum value is 1, and maximum value is 254.
DELETE STATISTICS
deletes any statistics about the analyzed object that are currently stored in the data dictionary.
VALIDATE STRUCTURE
validates the structure of the analyzed object. If you use this option when analyzing a cluster, Oracle7 automatically validates the structure of the cluster's tables.
CASCADE
LIST CHAINED ROWS
identifies migrated and chained rows of the analyzed table or cluster. You cannot use this option when analyzing an index.
INTO specifies a table into which Oracle7 lists the migrated and chained rows. If you omit schema, Oracle7 assumes the list table is in your own schema. If you omit this clause altogether, Oracle7 assumes that the table is named CHAINED_ROWS. The list table must be on your local database.
If the data dictionary already contains statistics for the analyzed object, Oracle7 updates the existing statistics with the new ones.
The statistics are used by the Oracle7 optimizer to choose the execution plan for SQL statements that access analyzed objects. These statistics may also be useful to application developers who write such statements. For information on how these statistics are used, see Oracle7 Server Tuning.
The following sections list the statistics for indexes, tables, columns, and clusters.
Index statistics appear in the data dictionary views USER_INDEXES, ALL_INDEXES, and DBA_INDEXES.
Table statistics appear in the data dictionary views USER_TABLES, ALL_TABLES, and DBA_TABLES.
The size parameter specifies how many bands the column should be divided into. A size of 1 treats the entire column as a single band, which is equivalent to not using histograms at all.
The column statistics that Oracle7 collects are the following:
When to use Histograms For uniformly distributed data, the cost-based approach makes fairly accurate guesses at the cost of executing a particular statement. For non-uniformly distributed data, Oracle allows you to store histograms describing the data distribution of a particular column. These histograms are stored in the dictionary and can be used by the cost-based optimizer.
Since they are persistent objects, there is a maintenance and space cost for using histograms. You should only compute histograms for columns that you know have highly-skewed data distribution. Also, be aware that histograms, as well as all optimizer statistics, are static. If the data distribution of a column changes frequently, you must reissue the ANALYZE command to recompute the histogram for that column.
Histograms are not useful for columns with the following characteristics:
ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS sal SIZE 10;
Column statistics appear in the data dictionary views USER_TAB_COLUMNS, ALL_TAB_COLUMNS, and DBA_TAB_COLUMNS. Histograms appear in the data dictionary views USER_HISTOGRAMS, DBA_HISTOGRAMS, and ALL_HISTOGRAMS.
Example I
The following statement estimates statistics for the CUST_HISTORY table and all of its indexes:
ANALYZE TABLE cust_history
ESTIMATE STATISTICS
When you use the DELETE STATISTICS option on a table, Oracle7 also automatically removes statistics for all the table's indexes. When you use the DELETE STATISTICS option on a cluster, Oracle7 also automatically removes statistics for all the cluster's tables and all their indexes, including the cluster index.
Example II
The following statement deletes statistics about the CUST_HISTORY table and all its indexes from the data dictionary:
ANALYZE TABLE cust_history
DELETE STATISTICS
Since the validating the structure of a object prevents SELECT, INSERT, UPDATE, and DELETE statements from concurrently accessing the object, do not use this option on the tables, clusters, and indexes of your production applications during periods of high database activity.
When you use the VALIDATE STRUCTURE option on an index, Oracle7 also collects statistics about the index and stores them in the data dictionary view INDEX_STATS. Oracle7 overwrites any existing statistics about previously validated indexes. At any time, INDEX_STATS can contain only one row describing only one index. The INDEX_STATS view is described in the Oracle7 Server Reference.
The statistics collected by this option are not used by the Oracle7 optimizer. Do not confuse these statistics with the statistics collected by the COMPUTE STATISTICS and ESTIMATE STATISTICS options.
Example III
The following statement validates the structure of the index PARTS_INDEX:
ANALYZE INDEX parts_index
VALIDATE STRUCTURE
The following statement analyzes the EMP table and all of its indexes:
ANALYZE TABLE emp
VALIDATE STRUCTURE CASCADE
Example V
The following statement analyzes the ORDER_CUSTS cluster, all of its tables, and all of their indexes, including the cluster index:
ANALYZE CLUSTER order_custs VALIDATE STRUCTURE CASCADE
You can use the INTO clause to specify an output table into which Oracle7 places this information. The definition of a sample output table CHAINED_ROWS is provided in a SQL script available on your distribution media. Your list table must have the same column names, types, and sizes as the CHAINED_ROWS table. On many operating systems, the name of this script is UTLCHAIN.SQL. The actual name and location of this script may vary depending on your operating system.
Example VI
The following statement collects information about all the chained rows of the table ORDER_HIST:
ANALYZE TABLE order_hist LIST CHAINED ROWS INTO cr
The preceding statement places the information into the table CR.
You can then examine the rows with this query:
SELECT * FROM cr OWNER_NAME TABLE_NAME CLUSTER_NAME HEAD_ROWID TIMESTAMP ---------- ---------- ------------ ------------------ --------- SCOTT ORDER_HIST 0000346A.000C.0003 15-MAR-93
You must also have the OSDBA or OSOPER role enabled.
You can use most of the options of this clause when your instance has the database mounted, open or closed. Options that require your instance to have the database open are noted.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must be the equivalent of DBHIGH.
THREAD
SEQ
manually archives the online redo log file group identified by the log sequence number integer in the specified thread. If you omit the THREAD parameter, Oracle7 archives the specified group from the thread assigned to your instance.
CHANGE
manually archives the online redo log file group containing the redo log entry with the system change number (SCN) specified by integer in the specified thread. If the SCN is in the current redo log file group, Oracle7 performs a log switch. If you omit the THREAD parameter, Oracle7 archives the groups containing this SCN from all enabled threads. You can only use this option when your instance has the database open.
CURRENT
GROUP
manually archives the online redo log file group with the specified GROUP value. You can determine the GROUP value for a redo log file group by examining the data dictionary view DBA_LOG_FILES. If you specify both the THREAD and GROUP parameters, the specified redo log file group must be in the specified thread.
LOGFILE
manually archives the online redo log file group containing the redo log file member identified by 'filename'. If you specify both the THREAD and LOGFILE parameters, the specified redo log file group must be in the specified thread.
NEXT
ALL
START
enables automatic archiving of redo log file groups. You can only enable automatic archiving for the thread assigned to your instance.
TO
STOP
disables automatic archiving of redo log file groups. You can only disable automatic archiving for the thread assigned to your instance.
You can also manually archive redo log file groups with the ARCHIVE LOG Server Manager command. For information on this command, see the Oracle Server Manager User's Guide.
You can also choose to have Oracle7 archive redo log files groups automatically. For information on automatic archiving, see the "Archiving Redo Information" chapter of the Oracle7 Server Administrator's Guide. Note that you can always manually archive redo log file groups regardless of whether automatic archiving is enabled.
Example I
ALTER SYSTEM ARCHIVE LOG THREAD 3 SEQ 4
Example II
ALTER SYSTEM ARCHIVE LOG CHANGE 9356083
Example III
The following statement manually archives the redo log file group containing a member named 'DISKL:LOG6.LOG' to an archived redo log file in the location 'DISKA:[ARCH$]':
ALTER SYSTEM ARCHIVE LOG LOGFILE 'diskl:log6.log' TO 'diska:[arch$]'
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of the users whose SQL statements you are auditing.
statement_opt
chooses specific SQL statements for auditing. For a list of these statement options and the SQL statements they audit, see Table 4 - 7 and Table 4 - 8 .
system_priv
chooses SQL statements that are authorized by the specified system privilege for auditing. For a list of all system privileges and the SQL statements that they authorize, see Table 4 - 11 .
BY user
BY SESSION
BY ACCESS
causes Oracle7 to write one record for each audited statement.
If you specify statement options or system privileges that audit Data Definition Language statements, Oracle7 automatically audits by access regardless of whether you specify the BY SESSION or BY ACCESS option.
For statement options and system privileges that audit other types of SQL statements, you can specify either the BY SESSION or BY ACCESS option. BY SESSION is the default.
WHENEVER SUCCESSFUL
chooses auditing only for SQL statements that complete successfully.
NOT chooses auditing only for statements that fail, or result in errors.
If you omit the WHENEVER clause, Oracle7 audits SQL statements regardless of success or failure.
Enable auditing: You must enable auditing with the initialization parameter AUDIT_TRAIL.
Specify auditing options: To specify auditing options, you must use the AUDIT command. Auditing options choose which SQL commands, operations, database objects, and users Oracle7 audits. After you specify auditing options, they appear in the data dictionary. For more information on data dictionary views containing auditing options see the "Data Dictionary" chapter of Oracle7 Server Reference.
You can specify auditing options regardless of whether auditing is enabled. However, Oracle7 does not generate audit records until you enable auditing.
Auditing options specified by the AUDIT command (SQL Statements) apply only to subsequent sessions, rather than to current sessions.
CONNECT
This short cut is equivalent to specifying the CREATE SESSION system privilege.
RESOURCE
This short cut is equivalent to specifying the following system privileges:
DBA
ALL
This short cut is equivalent to specifying all statement options shown in Table 4 - 7, but not the additional statement options shown in Table 4 - 8.
ALL PRIVILEGES
This short cut is equivalent to specifying all system privileges.
Oracle Corporation encourages you to choose individual system privileges and statement options for auditing, rather than these short cuts. These short cuts may not be supported in future versions of Oracle.
Statement Option | SQL Statements and Operations |
ALTER SEQUENCE | ALTER SEQUENCE |
ALTER TABLE | ALTER TABLE |
COMMENT TABLE | COMMENT ON TABLE table, view, snapshot COMMENT ON COLUMN table.column, view.column, snapshot.column |
DELETE TABLE | DELETE FROM table, view |
EXECUTE PROCEDURE | Execution of any procedure or function or access to any variable or cursor inside a package. |
GRANT PROCEDURE | GRANT privilege ON procedure, function, package REVOKE privilege ON procedure, function, package |
GRANT SEQUENCE | GRANT privilege ON sequence REVOKE privilege ON sequence |
GRANT TABLE | GRANT privilege ON table, view, snapshot. REVOKE privilege ON table, view, snapshot |
INSERT TABLE | INSERT INTO table, view |
LOCK TABLE | LOCK TABLE table, view |
SELECT SEQUENCE | Any statement containing sequence.CURRVAL or sequence.NEXTVAL |
SELECT TABLE | SELECT FROM table, view, snapshot |
UPDATE TABLE | UPDATE table, view |
Table 4 - 8. Additional Statement Auditing Options | |
AUDIT ROLE
To choose auditing for every statement that successfully creates, alters, drops, or sets a role, issue the following statement:
AUDIT ROLE
WHENEVER SUCCESSFUL
To choose auditing for every CREATE ROLE, ALTER ROLE, DROP ROLE, or SET ROLE statement that results in an Oracle7 error, issue the following statement:
AUDIT ROLE
WHENEVER NOT SUCCESSFUL
Example II
To choose auditing for any statement that queries or updates any table, issue the following statement:
AUDIT SELECT TABLE, UPDATE TABLE
To choose auditing for statements issued by the users SCOTT and BLAKE that query or update a table or view, issue the following statement:
AUDIT SELECT TABLE, UPDATE TABLE
BY scott, blake
Example III
To choose auditing for statements issued using the DELETE ANY TABLE system privilege, issue the following statement:
AUDIT DELETE ANY TABLE
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the object's creation label or you must satisfy one of the following criteria:
object_opt
specifies a particular operation for auditing. Table 4 - 9 shows each object option and the types of objects for which it applies.
schema
is the schema containing the object chosen for auditing. If you omit schema, Oracle7 assumes the object is in your own schema.
object
identifies the object chosen for auditing. The object must be one of the following types:
You can also specify a synonym for a table, view, sequence, procedure, stored function, package, or snapshot.
DEFAULT
establishes the specified object options as default object options for subsequently created objects.
If you omit both of the following options, Oracle7 audits by session.
BY SESSION
BY ACCESS
means that Oracle7 writes one record for each audited operation.
WHENEVER SUCCESSFUL
chooses auditing only for SQL statements that complete successfully.
NOT
chooses auditing only for statements that fail, or result in errors.
If you omit the WHENEVER clause entirely, Oracle7 audits all SQL statements, regardless of success or failure.
ALL
If you change the default auditing options, the auditing options for previously-created objects remain the same. You can only change the auditing options for an existing object by specifying the object in the ON clause of the AUDIT command.
Example I
AUDIT SELECT
ON scott.emp
To choose auditing for every statement that successfully queries the EMP table in the schema SCOTT, issue the following statement:
AUDIT SELECT
ON scott.emp
WHENEVER SUCCESSFUL
To choose auditing for every statement that queries the EMP table in the schema SCOTT and results in an Oracle7 error, issue the following statement:
AUDIT SELECT
ON scott.emp
WHENEVER NOT SUCCESSFUL
Example II
To choose auditing for every statement that inserts or updates a row in the DEPT table in the schema BLAKE, issue the following statement:
AUDIT INSERT, UPDATE
ON blake.dept
Example III
To choose auditing for every statement that performs any operation on the ORDER sequence in the schema ADAMS, issue the following statement:
AUDIT ALL
ON adams.order
The above statement uses the ALL short cut to choose auditing for the following statements that operate on the sequence:
The following statement specifies default auditing options for objects created in the future:
AUDIT ALTER, GRANT, INSERT, UPDATE, DELETE
ON DEFAULT
Any objects created later are automatically audited with the specified options that apply to them, provided that auditing has been enabled:
cursor
is the cursor to be closed. The cursor must currently be open.
Example
This example illustrates the use of the CLOSE command:
EXEC SQL CLOSE emp_cursor
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the creation label of the table, view, snapshot, or column.
TABLE
specifies the schema and name of the table, view, or snapshot to be commented.
COLUMN
specifies the name of the column of a table, view, or snapshot to be commented.
If you omit schema, Oracle7 assumes the table, view, or snapshot is in your own schema.
IS 'text'
is the text of the comment. See the syntax description of 'text' on page 2 - 15.
Example
COMMENT ON COLUMN shipping.notes
IS 'Special packing or shipping instructions'
To drop this comment from the database, issue the following statement:
COMMENT ON COLUMN shipping.notes IS ''
You can also use this command to manually commit an in-doubt distributed transaction.
To manually commit a distributed in-doubt transaction that you originally committed, you must have FORCE TRANSACTION system privilege. To manually commit a distributed in-doubt transaction that was originally committed by another user, you must have FORCE ANY TRANSACTION system privilege.
If you are using Trusted Oracle7 in DBMS MAC mode, you can only commit an in-doubt transaction if your DBMS label matches the label the transaction's label and the creation label of the user who originally committed the transaction or if you satisfy one of the following criteria:
WORK
COMMENT
specifies a comment to be associated with the current transaction. The 'text' is a quoted literal of up to 50 characters that Oracle7 stores in the data dictionary view DBA_2PC_PENDING along with the transaction ID if the transaction becomes in-doubt.
FORCE
manually commits an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING. You can also use the integer to specifically assign the transaction a system change number (SCN). If you omit the integer, the transaction is committed using the current SCN.
COMMIT statements using the FORCE clause are not supported in PL/SQL.
A normal exit from most Oracle7 utilities and tools causes the current transaction to be committed. A normal exit from an Oracle Precompiler program does not commit the transaction and relies on Oracle7 to rollback the current transaction. See the COMMIT command (Embedded SQL) .
You can also use a COMMIT or ROLLBACK statement to terminate a read only transaction begun by a SET TRANSACTION statement.
Example I
This example inserts a row into the DEPT table and commits this change:
INSERT INTO dept VALUES (50, 'MARKETING', 'TAMPA')
COMMIT WORK
Example II
The following statement commits the current transaction and associates a comment with it:
COMMIT WORK
COMMENT 'In-doubt transaction Code 36, Call (415) 555-2637'
If a network or machine failure prevents this distributed transaction from committing properly, Oracle7 stores the comment in the data dictionary along with the transaction ID. The comment indicates the part of the application in which the failure occurred and provides information for contacting the administrator of the database where the transaction was committed.
If a network or machine failure during the commit process for a distributed transaction, the state of the transaction may be unknown, or in-doubt. After consultation with the administrators of the other databases involved in the transaction, you may decide to manually commit or roll back the transaction on your local database. You can manually commit the transaction on your local database by using the FORCE clause of the COMMIT command. For more information on these topics, see the "Database Administration" chapter of Oracle7 Server Distributed Systems, Volume I.
Note that a COMMIT statement with a FORCE clause only commits the specified transaction. Such a statement does not affect your current transaction.
Example III
The following statement manually commits an in-doubt distributed transaction:
COMMIT FORCE '22.57.53'
To manually commit a distributed in-doubt transaction that you originally committed, you must have FORCE TRANSACTION system privilege. To manually commit a distributed in-doubt transaction that was originally committed by another user, you must have FORCE ANY TRANSACTION system privilege.
If you are using Trusted Oracle7 in DBMS MAC mode, you can only commit an in-doubt transaction if your DBMS label matches the label the transaction's label and the creation label of the user who originally committed the transaction or if you satisfy one of the following criteria:
AT
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, Oracle7 issues the statement to your default database.
WORK
COMMENT
specifies a comment to be associated with the current transaction. The 'text' is a quoted literal of up to 50 characters that Oracle7 stores in the data dictionary view DBA_2PC_PENDING along with the transaction ID if the transaction becomes in-doubt.
RELEASE
frees all resources and disconnects you from Oracle7.
FORCE
manually commits an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING. You can also use the optional integer to explicitly assign the transaction a system change number (SCN). If you omit the integer, the transaction is committed using the current SCN.
The COMMIT command has no effect on host variables or on the flow of control in the program.
For more information on this command, see Programmer's Guide to the Oracle Precompilers.
Example
This example illustrates the use of the embedded SQL COMMIT command:
EXEC SQL AT sales_db COMMIT RELEASE
If you are using Trusted Oracle7 in DBMS MAC mode, your operating system label must dominate both your creation label and the label at which you were granted CREATE SESSION system privilege. Your operating system label must also fall between the operating system equivalents of DBHIGH and DBLOW, inclusive.
If you are using Trusted Oracle7 in OS MAC mode, your operating system label must match the label of the database to which you are connecting.
:user :password
specifies your username and password separately.
:user_password
is a single host variable containing the Oracle7 username and password separated by a slash (/).
To allow Oracle7 to verify your connection through your operating system, specify a :user_password value of '/'.
AT
identifies the database to which the connection is made. The database can be identified by either:
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
USING
Example
The following example illustrate the use of CONNECT:
EXEC SQL CONNECT :username
IDENTIFIED BY :password
You can also use this statement in which the value of :userid is the value of :username and :password separated by a "/" such as 'SCOTT/TIGER':
EXEC SQL CONNECT :userid
Defining a constraint may also require additional privileges or preconditions that depend on the type of constraint. For information on these privileges, see the descriptions of each type of integrity constraint beginning .
CONSTRAINT
identifies the integrity constraint by the name constraint. Oracle7 stores this name in the data dictionary along with the definition of the integrity constraint. If you omit this identifier, Oracle7 generates a name with this form:
SYS_Cn
n is an integer that makes the name unique within the database. For the names and definitions of integrity constraints, query the data dictionary. For information on data dictionary views that contain constraints, see the "Data Dictionary Reference" chapter of Oracle7 Server Reference.
NULL
specifies that a column can contain null values.
NOT NULL
specifies that a column cannot contain null values.
If you do not specify NULL or NOT NULL in a column definition, NULL is the default.
UNIQUE
designates a column or combination of columns as a unique key.
PRIMARY KEY
designates a column or combination of columns as the table's primary key.
FOREIGN KEY
designates a column or combination of columns as the foreign key in a referential integrity constraint.
REFERENCES
identifies the primary or unique key that is referenced by a foreign key in a referential integrity constraint.
ON DELETE CASCADE
specifies that Oracle7 maintains referential integrity by automatically removing dependent foreign key values if you remove a referenced primary or unique key value.
CHECK
specifies a condition that each row in the table must satisfy.
USING INDEX
specifies parameters for the index Oracle7 uses to enforce a UNIQUE or PRIMARY KEY constraint. The name of the index is the same as the name of the constraint. You can choose the values of the INITRANS, MAXTRANS, TABLESPACE, STORAGE, PCTFREE, RECOVERABLE, and UNRECOVERABLE parameters for the index. For information on these parameters, see the CREATE TABLE command .
Only use this clause when enabling UNIQUE and PRIMARY KEY constraints.
NOSORT
indicates that the rows are stored in the database in ascending order and therefore Oracle7 does not have to sort the rows when creating the index.
DISABLE
disables the integrity constraint. If an integrity constraint is disabled, Oracle7 does not enforce it. If you do not specify this option, Oracle7 automatically enables the integrity constraint.
You can also enable and disable integrity constraints with the ENABLE and DISABLE clauses of the CREATE TABLE and ALTER TABLE commands. See the ENABLE clause and DISABLE clause on pages 4 - 295.
table_constraint
The table_constraint syntax is part of the table definition. An integrity constraint defined with this syntax can impose rules on any columns in the table.
The table_constraint syntax can appear in a CREATE TABLE or ALTER TABLE statement. This syntax can define any type of integrity constraint except a NOT NULL constraint.
The column_constraint syntax is part of a column definition. Usually, an integrity constraint defined with this syntax can only impose rules on the column in which it is defined.
The column_constraint syntax that appears in a CREATE TABLE statement can define any type of integrity constraint. Column_constraint syntax that appears in an ALTER TABLE statement can only define or remove a NOT NULL constraint.
The table_constraint syntax and the column_constraint syntax are simply different syntactic means of defining integrity constraints. A constraint that references more than one column must be defined as a table constraint. There is no other functional difference between an integrity constraint defined with table_constraint syntax and the same constraint defined with column_constraint syntax.
The NULL keyword indicates that a column can contain nulls. It does not actually define an integrity constraint. If you do not specify either NOT NULL or NULL, the column can contain nulls by default.
You can only specify NOT NULL or NULL with column_constraint syntax in a CREATE TABLE or ALTER TABLE statement, not with table_constraint syntax.
Example I
ALTER TABLE emp
MODIFY (sal NUMBER CONSTRAINT nn_sal NOT NULL)
NN_SAL ensures that no employee in the table has a null salary.
A unique key column cannot be of datatype LONG or LONG RAW. You cannot designate the same column or combination of columns as both a unique key and a primary key or as both a unique key and a cluster key. However, you can designate the same column or combination of columns as both a unique key and a foreign key.
Example II
The following statement creates the DEPT table and defines and enables a unique key on the DNAME column:
CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(9) CONSTRAINT unq_dname UNIQUE, loc VARCHAR2(10) )
The constraint UNQ_DNAME identifies the DNAME column as a unique key. This constraint ensures that no two departments in the table have the same name. However, the constraint does allow departments without names.
Alternatively, you can define and enable this constraint with the table_constraint syntax:
CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(9), loc VARCHAR2(10), CONSTRAINT unq_dname UNIQUE (dname) USING INDEX PCTFREE 20 TABLESPACE user_x STORAGE (INITIAL 8K NEXT 6K) )
The above statement also uses the USING INDEX option to specify storage characteristics for the index that Oracle7 creates to enforce the constraint.
To satisfy a constraint that designates a composite unique key, no two rows in the table can have the same combination of values in the key columns. Also, any row that contains nulls in all key columns automatically satisfies the constraint. However, two rows that contain nulls for one or more key columns and the same combination of values for the other key columns violate the constraint.
Example III
The following statement defines and enables a composite unique key on the combination of the CITY and STATE columns of the CENSUS table:
ALTER TABLE census ADD CONSTRAINT unq_city_state UNIQUE (city, state) USING INDEX PCTFREE 5 TABLESPACE user_y EXCEPTIONS INTO bad_keys_in_ship_cont
The UNQ_CITY_STATE constraint ensures that the same combination of CITY and STATE values does not appear in the table more than once.
The CONSTRAINT clause also specifies other properties of the constraint:
A primary key column cannot be of datatype LONG or LONG RAW. You cannot designate the same column or combination of columns as both a primary key and a unique key or as both a primary key and a cluster key. However, you can designate the same column or combination of columns as both a primary key and a foreign key.
The following statement creates the DEPT table and defines and enables a primary key on the DEPTNO column:
CREATE TABLE dept (deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY, dname VARCHAR2(9), loc VARCHAR2(10) )
The PK_DEPT constraint identifies the DEPTNO column as the primary key of the DEPTNO table. This constraint ensures that no two departments in the table have the same department number and that no department number is NULL.
Alternatively, you can define and enable this constraint with table_constraint syntax:
CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(9), loc VARCHAR2(10), CONSTRAINT pk_dept PRIMARY KEY (deptno) )
Example V
The following statement defines a composite primary key on the combination of the SHIP_NO and CONTAINER_NO columns of the SHIP_CONT table:
ALTER TABLE ship_cont ADD PRIMARY KEY (ship_no, container_no) DISABLE
This constraint identifies the combination of the SHIP_NO and CONTAINER_NO columns as the primary key of the SHIP_CONTAINER. The constraint ensures that no two rows in the table have the same values for both the SHIP_NO column and the CONTAINER_NO column.
The CONSTRAINT clause also specifies the following properties of the constraint:
FOREIGN KEY
identifies the column or combination of columns in the child table that makes up of the foreign key. Only use this keyword when you define a foreign key with a table constraint clause.
REFERENCES
identifies the parent table and the column or combination of columns that make up the referenced key.
If you only identify the parent table and omit the column names, the foreign key automatically references the primary key of the parent table.
The corresponding columns of the referenced key and the foreign key must match in number and datatypes.
ON DELETE CASCADE
allows deletion of referenced key values in the parent table that have dependent rows in the child table and causes Oracle7 to automatically delete dependent rows from the child table to maintain referential integrity.
If you omit this option, Oracle7 forbids deletions of referenced key values in the parent table that have dependent rows in the child table.
Before you define a referential integrity constraint in the child table, the referenced UNIQUE or PRIMARY KEY constraint on the parent table must already be defined. Also, the parent table must be in your own schema or you must have REFERENCES privilege on the columns of the referenced key in the parent table. Before you enable a referential integrity constraint, its referenced constraint must be enabled.
You cannot define a referential integrity constraint in a CREATE TABLE statement that contains an AS clause. Instead, you can create the table without the constraint and then add it later with an ALTER TABLE statement.
A foreign key column cannot be of datatype LONG or LONG RAW. You can designate the same column or combination of columns as both a foreign key and a primary or unique key. You can also designate the same column or combination of columns as both a foreign key and a cluster key.
You can define multiple foreign keys in a table. Also, a single column can be part of more than one foreign key.
Example VI
CREATE TABLE emp
(empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno CONSTRAINT fk_deptno REFERENCES dept(deptno) )
The constraint FK_DEPTNO ensures that all departments given for employees in the EMP table are present in the DEPT table. However, employees can have null department numbers, meaning they are not assigned to any department. If you wished to prevent the latter, you could create a NOT NULL constraint on the deptno column in the EMP table, in addition to the REFERENCES constraint.
Before you define and enable this constraint, you must define and enable a constraint that designates the DEPTNO column of the DEPT table as a primary or unique key. For the definition of such a constraint, see Example IV .
Note that the referential integrity constraint definition does not use the FOREIGN KEY keyword to identify the columns that make up the foreign key. Because the constraint is defined with a column constraint clause on the DEPTNO column, the foreign key is automatically on the DEPTNO column.
Note that the constraint definition identifies both the parent table and the columns of the referenced key. Because the referenced key is the parent table's primary key, the referenced key column names are optional.
Note that the above statement omits the DEPTNO column's datatype. Because this column is a foreign key, Oracle7 automatically assigns it the datatype of the DEPT.DEPTNO column to which the foreign key refers.
Alternatively, you can define a referential integrity constraint with table_constraint syntax:
CREATE TABLE emp (empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno, CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno) )
Note that the foreign key definitions in both of the above statements omit the ON DELETE CASCADE option, causing Oracle7 to forbid the deletion of a department if any employee works in that department.
Example VII
This example creates the EMP table, defines and enables the referential integrity constraint FK_DEPTNO, and uses the ON DELETE CASCADE option:
CREATE TABLE emp (empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) CONSTRAINT fk_deptno REFERENCES dept(deptno) ON DELETE CASCADE )
Because of the ON DELETE CASCADE option, Oracle7 cascades any deletion of a DEPTNO value in the DEPT table to the DEPTNO values of its dependent rows of the EMP table. For example, if department 20 is deleted from the DEPT table, Oracle7 deletes the department's employees from the EMP table.
To satisfy a referential integrity constraint involving composite keys, each row in the child table must satisfy one of the following conditions:
ALTER TABLE phone_calls
ADD CONSTRAINT fk_areaco_phoneno
FOREIGN KEY (areaco, phoneno)
REFERENCES customers(areaco, phoneno)
EXCEPTIONS INTO wrong_numbers
The constraint FK_AREACO_PHONENO ensures that all the calls in the PHONE_CALLS table are made from phone numbers that are listed in the CUSTOMERS table. Before you define and enable this constraint, you must define and enable a constraint that designates the combination of the AREACO and PHONENO columns of the CUSTOMERS table as a primary or unique key.
The EXCEPTIONS option causes Oracle7 to write information to the WRONG_NUMBERS about any rows in the PHONE_CALLS table that violate the constraint.
If you create multiple CHECK constraints for a column, design them carefully so their purposes do not conflict. Oracle7 does not verify that CHECK conditions are not mutually exclusive.
Example IX
The following statement creates the DEPT table and defines a CHECK constraint in each of the table's columns:
CREATE TABLE dept (deptno NUMBER CONSTRAINT check_deptno CHECK (deptno BETWEEN 10 AND 99) DISABLE, dname VARCHAR2(9) CONSTRAINT check_dname CHECK (dname = UPPER(dname)) DISABLE, loc VARCHAR2(10) CONSTRAINT check_loc CHECK (loc IN ('DALLAS','BOSTON', 'NEW YORK','CHICAGO')) DISABLE)
Each constraint restricts the values of the column in which it is defined:
CHECK_DEPTNO
ensures that no department numbers are less than 10 or greater than 99.
CHECK_DNAME
ensures that all department names are in uppercase.
CHECK_LOC
restricts department locations to Dallas, Boston, New York, or Chicago.
Unlike other types of constraints, a CHECK constraint defined with column_constraint syntax can impose rules on any column in the table, rather than only on the column in which it is defined.
Because each CONSTRAINT clause contains the DISABLE option, Oracle7 only defines the constraints and does not enforce them.
Example X
The following statement creates the EMP table and uses a table constraint clause to define and enable a CHECK constraint:
CREATE TABLE emp
(empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2), CHECK (sal + comm <= 5000) )
This constraint uses an inequality condition to limit an employee's total compensation, the sum of salary and commission, to $5000:
Example XI
CREATE TABLE order_detail (CONSTRAINT pk_od PRIMARY KEY (order_id, part_no), order_id NUMBER CONSTRAINT fk_oid REFERENCES scott.order (order_id), part_no NUMBER CONSTRAINT fk_pno REFERENCES scott.part (part_no), quantity NUMBER CONSTRAINT nn_qty NOT NULL CONSTRAINT check_qty_low CHECK (quantity > 0), cost NUMBER CONSTRAINT check_cost CHECK (cost > 0) )
The constraints enforce the following rules on table data:
PK_OD
identifies the combination of the ORDER_ID and PART_NO columns as the primary key of the table. To satisfy this constraint, the following conditions must be true:
FK_OID
identifies the ORDER_ID column as a foreign key that references the ORDER_ID column in the ORDER table in SCOTT's schema. All new values added to the column ORDER_DETAIL.ORDER_ID must already appear in the column SCOTT.ORDER.ORDER_ID.
FK_PNO
identifies the PART_NO column as a foreign key that references the PART_NO column in the PART table owned by SCOTT. All new values added to the column ORDER_DETAIL.PART_NO must already appear in the column SCOTT.PART.PART_NO.
NN_QTY
forbids nulls in the QUANTITY column.
CHECK_QTY
ensures that values in the QUANTITY column are always greater than 0.
CHECK_COST
ensures the values in the COST column are always greater than 0.
This example also illustrates the following points about constraint clauses and column definitions:
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the label of the tablespace to contain the cluster. To create a cluster in another user's schema, your DBMS label must dominate the creation label of the owner of the schema.
schema
is the schema to contain the cluster. If you omit schema, Oracle7 creates the cluster in your current schema.
cluster
is the name of the cluster to be created.
column
is the name of a column in the cluster key.
datatype
is the datatype of a cluster key column. A cluster key column can have any datatype except LONG or LONG RAW. You cannot use the HASH IS clause if any column datatype is not INTEGER or NUMBER with scale 0. For information on datatypes, see the section "Datatypes" .
PCTUSED
specifies the limit that Oracle7 uses to determine when additional rows can be added to a cluster's data block. The value of this parameter is expressed as a whole number and interpreted as a percentage.
PCTFREE
specifies the space reserved in each of the cluster's data blocks for future expansion. The value of the parameter is expressed as a whole number and interpreted as a percentage.
INITRANS
specifies the initial number of concurrent update transactions allocated for data blocks of the cluster. The value of this parameter for a cluster cannot be less than 2 or more than the value of the MAXTRANS parameter. The default value is the greater of the INITRANS value for the cluster's tablespace and 2.
MAXTRANS
specifies the maximum number of concurrent update transactions for any given data block belonging to the cluster. The value of this parameter cannot be less than the value of the INITRANS parameter. The maximum value of this parameter is 255. The default value is the MAXTRANS value for the tablespace to contain the cluster.
For a complete description of the PCTUSED, PCTFREE, INITRANS, and MAXTRANS parameters, see the CREATE TABLE command .
SIZE
specifies the amount of space in bytes to store all rows with the same cluster key value or the same hash value. You can use K or M to specify this space in kilobytes or megabytes. If you omit this parameter, Oracle7 reserves one data block for each cluster key value or hash value.
TABLESPACE
specifies the tablespace in which the cluster is created.
STORAGE
specifies how data blocks are allocated to the cluster. See the STORAGE clause .
INDEX
HASHKEYS
creates a hash cluster and specifies the number of hash values for a hash cluster. Oracle7 rounds the HASHKEYS value up to the nearest prime number to obtain the actual number of hash values. The minimum value for this parameter is 2. If you omit both the INDEX option and the HASHKEYS parameter, Oracle7 creates an indexed cluster by default.
HASH IS
specifies a expression to be used as the hash function for the hash cluster.
The expression must:
The expression:
If you omit the HASH IS clause, Oracle7 uses an internal hash function for the hash cluster.
The cluster key of a hash column can have one or more columns of any datatype. Hash clusters with composite cluster keys or cluster keys made up of non-integer columns must use the internal hash function.
PARALLEL
specifies the degree of parallelism to use when creating the cluster and the default degree of parallelism to use when querying the cluster after creation. See the parallel_clause .
CACHE
specifies that the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.
NOCACHE
specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the default behavior.
Clustering provides more control over the physical storage of rows within the database. Clustering can reduce both the time it takes to access clustered tables and the space needed to store the table. After you create a cluster and add tables to it, the cluster is transparent. You can access clustered tables with SQL statements just as you can non-clustered tables.
If you cannot fit all rows for one hash value into a data block, do not use hash clusters. Performance is very poor in this circumstance because an insert or update of a row in a hash cluster with a size exceeding the data block size fills the block and row chaining to contain the rest of the row.
Generally, you should only cluster tables that are frequently joined on the cluster key columns in SQL statements. While clustering multiple tables improves the performance of joins, it is likely to reduce the performance of full table scans, INSERT statements, and UPDATE statements that modify cluster key values. Before clustering, consider its benefits and tradeoffs in light of the operations you plan to perform on your data. For more information on the performance implications of clustering, see the "Tuning SQL Statements" chapter of Oracle7 Server Tuning.
When you create a cluster in Trusted Oracle7, it is labeled with your DBMS label.
You cannot specify integrity constraints as part of the definition of a cluster key column. Instead, you can associate integrity constraints with the tables that belong to the cluster.
You may want to use indexed clusters in the following cases:
A cluster index provides quick access to rows within a cluster based on the cluster key. If you issue a SQL statement that searches for a row in the cluster based on its cluster key value, Oracle7 searches the cluster index for the cluster key value and then locates the row in the cluster based on its ROWID.
You may want to use hash clusters in the following cases:
Oracle7's internal hash function returns values ranging from 0 to the value of HASHKEYS - 1. If you specify a column with the HASH IS clause, the column values need not fall into this range. Oracle7 divides the column value by the HASHKEYS value and uses the remainder as the hash value. The hash value for null is HASHKEYS - 1. Oracle7 also rounds the HASHKEYS value up to the nearest prime number to obtain the actual number of hash values. This rounding reduces the likelihood of hash collisions, or multiple cluster key values having the same hash value.
You cannot create a cluster index for a hash cluster, and you need not create an index on a hash cluster key.
Oracle7 also considers the length of the cluster key when determining how much space to reserve for the rows having a cluster key value. Larger cluster keys require larger sizes. To see the actual size, query the KEY_SIZE column of the USER_CLUSTERS data dictionary view. This does not apply to hash clusters because hash values are not actually stored in the cluster.
Although the maximum number of cluster and hash key values per data block is fixed on a per cluster basis, Oracle7 does not reserve an equal amount of space for each cluster or hash key value. Varying this space stores data more efficiently because the data stored per cluster or hash key value is rarely fixed.
A SIZE value smaller than the space needed by the average cluster or hash key value may require the data for one cluster key or hash key value to occupy multiple data blocks. A SIZE value much larger results in wasted space.
When you create a hash cluster, Oracle7 immediately allocates space for the cluster based on the values of the SIZE and HASHKEYS parameters. For more information on how Oracle7 allocates space for clusters, see the "Schema Objects" chapter of Oracle7 Server Concepts.
All tables in the cluster have the cluster's storage characteristics as specified by the PCTUSED, PCTFREE, INITRANS, MAXTRANS, TABLESPACE, and STORAGE parameters.
Example I
CREATE CLUSTER personnel ( department_number NUMBER(2) ) SIZE 512 STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 10)
The following statements add the EMP and DEPT tables to the cluster:
CREATE TABLE emp (empno NUMBER PRIMARY KEY, ename VARCHAR2(10) NOT NULL CHECK (ename = UPPER(ename)), job VARCHAR2(9), mgr NUMBER REFERENCES scott.emp(empno), hiredate DATE CHECK (hiredate >= SYSDATE), sal NUMBER(10,2) CHECK (sal > 500), comm NUMBER(9,0) DEFAULT NULL, deptno NUMBER(2) NOT NULL ) CLUSTER personnel (deptno) CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(9), loc VARCHAR2(9)) CLUSTER personnel (deptno)
The following statement creates the cluster index on the cluster key of PERSONNEL:
CREATE INDEX idx_personnel ON CLUSTER personnel
After creating the cluster index, you can insert rows into either the EMP or DEPT tables.
Example II
The following statement creates a hash cluster named PERSONNEL with the cluster key column DEPARTMENT_NUMBER, a maximum of 503 hash key values, each of size 512 bytes, and storage parameter values:
CREATE CLUSTER personnel
( department_number NUMBER )
SIZE 512 HASHKEYS 500 STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 10)
Because the above statement omits the HASH IS clause, Oracle7 uses the internal hash function for the cluster.
Example III
The following statement creates a hash cluster named PERSONNEL with the cluster key comprised of the columns HOME_AREA_CODE and HOME_PREFIX, and uses a SQL expression containing these columns for the hash function:
CREATE CLUSTER personnel ( home_area_code NUMBER, home_prefix NUMBER ) HASHKEYS 20 HASH IS MOD(home_area_code + home_prefix, 101)
If you are using Trusted Oracle7 in DBMS MAC mode, your operating system label must be the equivalent of DBHIGH.
REUSE
SET DATABASE
changes the name of the database. The name of a database can be as long as eight bytes.
DATABASE
LOGFILE
specifies the redo log file groups for your database. You must list all members of all redo log file groups. See the syntax description of filespec .
RESETLOGS
ignores the contents of the files listed in the LOGFILE clause. These files do not have to exist. Each filespec in the LOGFILE clause must specify the SIZE parameter. Oracle7 assigns all redo log file groups to thread 1 and enables this thread for public use by any instance. After using this option, you must open the database using the RESETLOGS option of the ALTER DATABASE command.
NORESETLOGS
specifies that all files in the LOGFILE clause should be used as they were when the database was last open. These files must exit and must be the current redo log files rather than restored backups. Oracle7 reassigns the redo log file groups to the threads to which they were previously assigned and re-enables the threads as they were previously enabled. If you specify GROUP values, Oracle7 verifies these values with the GROUP values when the database was last open.
DATAFILE
specifies the data files of the database. You must list all data files. These files must all exist, although they may be restored backups that require media recovery. See the syntax description of filespec .
MAXLOGFILES
specifies the maximum number of redo log file groups that can ever be created for the database. Oracle7 uses this value to determine how much space in the control file to allocate for the names of redo log files. The default and maximum values depend on your operating system. The value that you specify should not be less than the greatest GROUP value for any redo log file group.
Note that the number of redo log file groups accessible to your instance is also limited by the initialization parameter LOG_FILES.
MAXLOGMEMBERS
specifies the maximum number of members, or copies, for a redo log file group. Oracle7 uses this value to determine how much space in the control file to allocate for the names of redo log files. The minimum value is 1. The maximum and default values depend on your operating system.
MAXLOGHISTORY
specifies the maximum number of archived redo log file groups for automatic media recovery of the Oracle7 Parallel Server. Oracle7 uses this value to determine how much space in the control file to allocate for the names of archived redo log files. The minimum value is 0. The default value is a multiple of the MAXINSTANCES value and varies depending on your operating system. The maximum value is limited only by the maximum size of the control file. Note that this parameter is only useful if you are using Oracle7 with the Parallel Server option in both parallel mode and archivelog mode.
MAXDATAFILES
Note that the number of data files accessible to your instance is also limited by the initialization parameter DB_FILES.
MAXINSTANCES
ARCHIVELOG
NOARCHIVELOG
establishes the initial mode of reusing redo log files without archiving their contents. This option prepares for the possibility of instance recovery but not media recovery.
If you omit both the ARCHIVELOG and NOARCHIVELOG options, Oracle7 chooses noarchivelog mode by default. After creating the control file, you can change between archivelog mode and noarchivelog mode with the ALTER DATABASE command.
When you issue a CREATE CONTROLFILE statement, Oracle7 creates a new control file based on the information you specify in the statement. If you omit any of the options from the statement, Oracle7 uses the default options, rather than the options for the previous control file. After successfully creating the control file, Oracle7 mounts the database in exclusive mode. You then must perform media recovery before opening the database. It is recommended that you then shutdown the instance and take a full backup of all files in the database.
For more information on using this command, see the "Recovering a Database" chapter of Oracle7 Server Administrator's Guide.
When you create a control file in Trusted Oracle7, it is labeled with your DBMS label. The control file cannot be used unless it is labeled at the operating system equivalent of DBHIGH. If you issue a CREATE CONTROLFILE statement in DBMS MAC mode, Trusted Oracle7 automatically switches to OS MAC mode. You can then return to DBMS MAC mode by issuing an ALTER DATABASE statement with the SET DBMAC ON clause.
Example
This example recreates a control file:
CREATE CONTROLFILE REUSE
SET DATABASE orders_2
LOGFILE GROUP 1 ('diskb:log1.log', 'diskc:log1.log') SIZE 50K,
GROUP 2 ('diskb:log2.log', 'diskc:log2.log') SIZE 50K
NORESETLOGS
DATAFILE 'diska:dbone.dat' SIZE 2M
MAXLOGFILES 5
MAXLOGHISTORY 100
MAXDATAFILES 10
MAXINSTANCES 2
ARCHIVELOG
If you are using Trusted Oracle7 and you plan to use the database in DBMS MAC mode, your operating system label should be the equivalent of DBLOW.
database
is the name of the database to be created and can be up to eight bytes long. Oracle7 writes this name into the control file. If you subsequently issue an ALTER DATABASE statement and that explicitly specifies a database name, Oracle7 verifies that name with the name in the control file. Database names should adhere to the rules described in section, "Object Naming Rules," .
Note: You cannot use special characters from European or Asian character sets in a database name. For example, the umlaut is not allowed.
The database cannot be a Server Manager reserved word as documented in the Oracle Server Manager Manual. If you omit the database name from a CREATE DATABASE statement, the name specified by the initialization parameter DB_NAME is used.
CONTROLFILE REUSE
reuses existing control files identified by the initialization parameter CONTROL_FILES, thus ignoring and overwriting any information they currently contain. This option is usually used only when you are recreating a database, rather than creating one for the first time. You cannot use this option if you also specify a parameter value that requires that the control file be larger than the existing files. These parameters are MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES.
If you omit this option and any of the files specified by CONTROL_FILES already exist, Oracle7 returns an error message.
LOGFILE
specifies one or more files to be used as redo log files. Each filespec specifies a redo log file group containing one or more redo log file members, or copies. See the syntax description of filespec . All redo log files specified in a CREATE DATABASE statement are added to redo log thread number 1.
You can also choose the value of the GROUP parameter for the redo log file group. Each value uniquely identifies a redo log file group and can range from 1 to the value of the MAXLOGFILES parameter. You cannot specify multiple redo log file groups having the same GROUP value. If you omit this parameter, Oracle7 generates its value automatically. You can examine the GROUP value for a redo log file group through the dynamic performance table V$LOG.
If you omit the LOGFILE clause, Oracle7 creates two redo log file groups by default. The names and sizes of the default files vary depending on your operating system.
MAXLOGFILES
specifies the maximum number of redo log file groups that can ever be created for the database. Oracle7 uses this value to determine how much space in the control file to allocate for the names of redo log files. The default, minimum, and maximum values vary depending on your operating system.
The number of redo log file groups accessible to your instance is also limited by the initialization parameter LOG_FILES.
MAXLOGMEMBERS
specifies the maximum number of members, or copies, for a redo log file group. Oracle7 uses this value to determine how much space in the control file to allocate for the names of redo log files. The minimum value is 1. The maximum and default values vary depending on your operating system.
MAXLOGHISTORY
MAXDATAFILES
specifies the maximum number of data files that can ever be created for the database.
The minimum value is 1. The maximum and default values depend on your operating system. The number of data files accessible to your instance is also limited by the initialization parameter
MAXINSTANCES
specifies the maximum number of instances that can simultaneously have this database mounted and open. This value takes precedence over the
value of the initialization parameter
INSTANCES. The minimum value is 1. The maximum and default values depend on your operating system.
ARCHIVELOG
establishes archivelog mode for redo log file groups. In this mode, the contents of a redo log file group must be archived before the group can be reused. This option prepares for the possibility of media recovery.
NOARCHIVELOG
establishes noarchivelog mode for redo log files groups. In this mode, the contents of a redo log file group need not be archived before the group can be reused. This option does not prepares for the possibility of media recovery.
The default is noarchivelog mode. After creating the database, you can change between archivelog mode and noarchivelog mode with the ALTER DATABASE command.
EXCLUSIVE
For multiple instances to access the database, you must first create the database, close and dismount the database, and then mount it in parallel mode. For information on closing, dismounting, and mounting the database, see the ALTER DATABASE command .
CHARACTER SET
DATAFILE
specifies one or more files to be used as data files. See the syntax description of filespec . These files all become part of the SYSTEM tablespace. If you omit this clause, Oracle7 creates one data file by default. The name and size of this default file depends on your operating system.
AUTOEXTEND
enables or disables the automatic extension of a datafile.
OFF disable autoextend if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in ALTER DATABASE AUTOEXTEND or ALTER TABLESPACE AUTOEXTEND commands.
ON enable autoextend.
NEXT the size in bytes of the next increment of disk space to be automatically allocated to the datafile when more extents are required. You can also use K or M to specify this size in kilobytes or megabytes. The default is one data block.
MAXSIZE maximum disk space allowed for automatic extension of the datafile.
UNLIMITED set no limit on allocating disk space to the datafile.
After creating the database, this command mounts it in exclusive mode and opens it, making it available for normal use.
If you create a database using Trusted Oracle7, it is labeled with your operating system label and is created in OS MAC mode. If you plan to use the database in DBMS MAC mode, be sure you set values for DBHIGH and DBLOW. For more information on creating Trusted Oracle7 databases, see Trusted Oracle7 Server Administrator's Guide.
Example
The following statement creates a small database using defaults for all arguments:
CREATE DATABASE
The following statement creates a database and fully specifies each argument:
CREATE DATABASE newtest CONTROLFILE REUSE LOGFILE GROUP 1 ('diskb:log1.log', 'diskc:log1.log') SIZE 50K, GROUP 2 ('diskb:log2.log', 'diskc:log2.log') SIZE 50K MAXLOGFILES 5 MAXLOGHISTORY 100 DATAFILE 'diska:dbone.dat' SIZE 2M MAXDATAFILES 10 MAXINSTANCES 2 ARCHIVELOG EXCLUSIVE CHARACTER SET US7ASCII DATAFILE 'disk1:df1.dbf' AUTOEXTEND ON 'disk2:df2.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
PUBLIC
dblink
is the complete or partial name of the database link. For guidelines for naming database links, see "Referring to Objects In Remote Databases," .
CONNECT TO user IDENTIFIED BY password
is the username and password used to connect to the remote database. If you omit this clause, the database link uses the username and password of each user who uses the database link.
USING
For information on specifying remote databases, see the
SQL*Net User's Guide for your specific SQL*Net protocol.
Read-only mounts are only available in Trusted Oracle7 and can only be specified for public database links. For more information on specifying read-only mounts, see Trusted Oracle7 Server Administrator's Guide.
Once you have created a database link, you can use it to refer to tables and views on the remote database. You can refer to a remote table or view in a SQL statement by appending @dblink to the table or view name. You can query a remote table or view with the SELECT command. If you are using Oracle7 with the distributed option, you can also access remote tables and views in any of the following commands:
The number of different database links that can appear in a single statement is limited to the value of the initialization parameter OPEN_LINKS.
When you create a database link in Trusted Oracle7, it is labeled with your DBMS label.
Example
The following statement defines a database link named SALES.HQ.ACME.COM that refers to user SCOTT with password TIGER on the database specified by the string D:BOSTON-MFG:
CREATE DATABASE LINK sales.hq.acme.com CONNECT TO scott IDENTIFIED BY tiger USING 'D:BOSTON-MFG'
Once this database link is created, you can query tables in the schema SCOTT on the remote database in this manner:
SELECT *
FROM emp@sales.hq.acme.com
You can also use Data Manipulation Language commands to modify data on the remote database:
INSERT INTO accounts@sales.hq.acme.com(acc_no, acc_name, balance) VALUES (5001, 'BOWER', 2000) UPDATE accounts@sales.hq.acme.com SET balance = balance + 500 DELETE FROM accounts@sales.hq.acme.com WHERE acc_name = 'BOWER'
You can also access tables owned by other users on the same database. This example assumes SCOTT has access to ADAM's DEPT table:
SELECT *
FROM adams.dept@sales.hq.acme.com
The previous statement connects to the user SCOTT on the remote database and then queries ADAM's DEPT table.
A synonym may be created to hide the fact that SCOTT's EMP table is on a remote database. The following statement causes all future references to EMP to access a remote EMP table owned by SCOTT.
CREATE SYNONYM emp FOR scott.emp@sales.hq.acme.com
User functions can be used as part of a SQL expression.
To create a function in your own schema, you must have CREATE PROCEDURE system privilege. To create a function in another user's schema, you must have CREATE ANY PROCEDURE system privilege.
If you are using Trusted Oracle7 in DBMS MAC mode, you can create a function in another user's schema if your DBMS label dominates the creation label of the other user.
To create a stored function, you must be using Oracle7 with PL/SQL installed. For more information, see PL/SQL User's Guide and Reference.
OR REPLACE
recreates the function if it already exists. You can use this option to change the definition of an existing function without dropping, recreating, and regranting object privileges previously granted on the function. If you redefine a function, Oracle7 recompiles it. For information on recompiling functions, see the ALTER FUNCTION command .
Users who had previously been granted privileges on a redefined function can still access the function without being regranted the privileges.
schema
is the schema to contain the function. If you omit schema, Oracle7 creates the function in your current schema.
function
is the name of the function to be created.
argument
is the name of an argument to the function. If the function does not accept arguments, you can omit the parentheses following the function name.
IN
specifies that you must supply a value for the argument when calling the function. This is the default.
OUT
specifies the function will set the value of the argument.
IN OUT
specifies that a value for the argument can be supplied by you and may be set by the function.
datatype
is the datatype of an argument. An argument can have any datatype supported by PL/SQL.
The datatype cannot specify a length, precision, or scale. Oracle7 derives the length, precision, or scale of an argument from the environment from which the function is called.
RETURN datatype
specifies the datatype of the function's return value. Because every function must return a value, this clause is required. The return value can have any datatype supported by PL/SQL.
The datatype cannot specify a length, precision, or scale. Oracle7 derives the length, precision, or scale of the return value from the environment from which the function is called. For information on PL/SQL datatypes, see the PL/SQL User's Guide and Reference.
pl/sql_subprogram_body
is the definition of the function. Function definitions are written in PL/SQL. For information on PL/SQL, including
To embed a CREATE FUNCTION statement inside an Oracle Precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.
The CREATE FUNCTION command creates a function as a stand-alone schema object. You can also create a function as part of a package. For information on creating packages, see the CREATE PACKAGE command 4 - 198.
When you create a stored function in Trusted Oracle7, it is labeled with your DBMS label.
Example
The following statement creates the function GET_BAL:
CREATE FUNCTION get_bal(acc_no IN NUMBER)
RETURN NUMBER
IS
acc_bal NUMBER(11,2);
BEGIN
SELECT balance
INTO acc_bal
FROM accounts
WHERE account_id = acc_no;
RETURN(acc_bal);
END
The GET_BAL function returns the balance of a specified account.
When you call the function, you must specify the argument ACC_NO, the number of the account whose balance is sought. The datatype of ACC_NO is NUMBER.
The function returns the account balance. The RETURN clause of the CREATE FUNCTION statement specifies the datatype of the return value to be NUMBER.
The function uses a SELECT statement to select the BALANCE column from the row identified by the argument ACC_NO in the ACCOUNTS table. The function uses a RETURN statement to return this value to the environment in which the function is called.
The above function can be used in a SQL statement. For example:
SELECT get_bal(100) FROM DUAL;
Also, the owner of the schema to contain the index must have either space quota on the tablespace to contain the index or UNLIMITED TABLESPACE system privilege.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the tablespace's label and match the table's label. If the table was created at DBHIGH or DBLOW, you must explicitly set your label to DBHIGH or DBLOW. You can create an index in another user's schema if your DBMS label dominates the creation label of the other user.
UNIQUE
specifies that the value of the column (or combination of columns) in the table to be indexed must be unique.
schema
is the schema to contain the index. If you omit schema, Oracle7 creates the index in your own schema.
index
is the name of the index to be created.
table
is the name of the table for which the index is to be created. If you do not qualify table with schema, Oracle7 assumes the table is contained in your own schema.
column
is the name of a column in the table. An index can have as many as 16 columns. A column of an index cannot be of datatype LONG or LONG RAW.
ASC DESC
are allowed for DB2 syntax compatibility, although indexes are always created in ascending order. Indexes on character data are created in ascending order of the character values in the database character set.
CLUSTER
specifies the cluster for which a cluster index is to be created. If you do not qualify cluster with schema, Oracle7 assumes the cluster is contained in your current schema. You cannot create a cluster index for a hash cluster.
INITRANS MAXTRANS
establishes values for these parameters for the index. See the INITRANS and MAXTRANS parameters of the CREATE TABLE command .
TABLESPACE
is the name of the tablespace to hold the index. If you omit this option, Oracle7 creates the index in the default tablespace of the owner of the schema containing the index.
STORAGE
establishes the storage characteristics for the index. See the STORAGE clause .
PCTFREE
is the percentage of space to leave free for updates and insertions within each of the index's data blocks.
NOSORT
RECOVERABLE
specifies that the creation of the index will be logged in the redo log file. This is the default.
If the database is run in ARCHIVELOG mode, media recovery from a backup will recreate the index. You cannot specify RECOVERABLE when using NOARCHIVELOG mode.
UNRECOVERABLE
specifies that the creation of the index will not be logged in the redo log file. As a result, media recovery will not recreate the index.
Using this keyword makes index creation faster than using the RECOVERABLE option because redo log entries are not written.
PARALLEL
specifies the degree of parallelism for creating the index. See the parallel_clause .
Oracle recommends that you do not explicitly define UNIQUE indexes on tables; uniqueness is strictly a logical concept and should be associated with the definition of a table. Alternatively, define UNIQUE integrity constraints on the desired columns. Oracle enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key. Exceptions to this recommendation are usually performance related. For example, using a CREATE TABLE ... AS SELECT with a UNIQUE constraint is very much slower than creating the table without the constraint and then manually creating the UNIQUE index.
If indexes contain NULLs, the NULLS generally are considered distinct values. There is, however, one exception: if all the non-NULL values in two or more rows of an index are identical, the rows are considered identical; therefore, UNIQUE indexes prevent this from occurring. This does not apply if there are no non-NULL values--in other words, if the rows are entirely NULL..
When you create an index in Trusted Oracle7, it is labeled with your DBMS label.
When appropriate, Oracle7 uses the entire index or a leading portion of the index. Assume an index named IDX1 is created on columns A, B, and C of table TAB1 (in the order A, B, C). Oracle7 uses the index for references to columns A, B, C (the entire index); A, B; or just column A. References to columns B and C do not use the IDX1 index. Of course, you can also create another index just for columns B and C.
CREATE INDEX emp_idx1 ON emp (ename, job); CREATE INDEX emp_idx2 ON emp (job, ename);
You cannot create an index that references only one column in a table if another such index already exists.
Note that each index increases the processing time needed to maintain the table during updates to indexed data.
Note that there is overhead in maintaining indexes when a table is updated. Thus, updating a table with a single index will take less time than if the table had five indexes.
You cannot use the NOSORT option to create a cluster index.
The NOSORT option also reduces the amount of space required to build the index. Oracle7 uses temporary segments during the sort. Since a sort is not performed, the index is created with much less temporary space.
To use the NOSORT option, you must guarantee that the rows are physically sorted in ascending order. Because of the physical data independence inherent in relational database management systems, especially Oracle7, there is no way to force a physical internal order on a table. The CREATE INDEX command with the NOSORT option should be used immediately after the initial load of rows into a table.
You run no risk by trying the NOSORT option. If your rows are not in the ascending order, Oracle7 returns an error. You can issue another CREATE INDEX without the NOSORT option.
Example I
To quickly create an index in parallel on a table that was created using a fast parallel load (so all rows are already sorted), you might issue the following statement:
CREATE INDEX i_loc ON big_table (akey) NOSORT UNRECOVERABLE PARALLEL (DEGREE 5)
Example II
Consider the following statement:
SELECT ename
FROM emp
WHERE comm IS NULL
The above query does not use an index created on the COMM column.
Example III
To create an index for the EMPLOYEE cluster, issue the following statement:
CREATE INDEX ic_emp ON CLUSTER employee
Note that no index columns are specified since the index is automatically built on all the columns of the cluster key.
To create a package in your own schema, you must have CREATE PROCEDURE system privilege. To create a package in another user's schema, you must have CREATE ANY PROCEDURE system privilege.
If you are using Trusted Oracle7 in DBMS MAC mode, you can only create a package in another user's schema if your DBMS label dominates the creation label of the other user.
To create a package, you must be using Oracle7 with PL/SQL installed. For more information, see PL/SQL User's Guide and Reference.
OR REPLACE
recreates the package specification if it already exists. You can use this option to change the specification of an existing package without dropping, recreating, and regranting object privileges previously granted on the package. If you change a package specification, Oracle7 recompiles it. For information on recompiling package specifications, see the ALTER PROCEDURE command .
Users who had previously been granted privileges on a redefined package can still access the package without being regretted the privileges.
schema
is the schema to contain the package. If you omit schema, Oracle7 creates the package in your own schema.
package
is the name of the package to be created.
pl/sql_package_spec
is the package specification. The package specification can declare program objects. Package specifications are written in PL/SQL. For information on PL/SQL, including writing package specifications, see PL/SQL User's Guide and Reference.
To embed a CREATE PACKAGE statement inside an Oracle Precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.
When you create a package in Trusted Oracle7, it is labeled with your DBMS label.
1. Create the package specification with the CREATE PACKAGE command. You can declare program objects in the package specification. Such objects are called public objects. Public objects can be referenced outside the package as well as by other objects in the package.
2. Create the package body with the CREATE PACKAGE BODY command. You can declare and define program objects in the package body:
Example
This SQL statement creates the specification of the EMP_MGMT package:
CREATE PACKAGE emp_mgmt AS FUNCTION hire(ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER) RETURN NUMBER; FUNCTION create_dept(dname VARCHAR2, loc VARCHAR2) RETURN NUMBER; PROCEDURE remove_emp(empno NUMBER); PROCEDURE remove_dept(deptno NUMBER); PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER); PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER); no_comm EXCEPTION; no_sal EXCEPTION; END emp_mgmt
The specification for the EMP_MGMT package declares the following public program objects:
Before you can call this package's procedures and functions, you must define these procedures and functions in the package body. For an example of a CREATE PACKAGE BODY statement that creates the body of the EMP_MGMT package, see the CREATE PACKAGE BODY command .
To create a package in your own schema, you must have CREATE PROCEDURE system privilege. To create a package in another user's schema, you must have CREATE ANY PROCEDURE system privilege.
If you are using Trusted Oracle7 in DBMS MAC mode, you can only create a package in another user's schema if your DBMS label dominates the creation label of the other user.
To create a package, you must be using Oracle7 with PL/SQL installed. For more information, see PL/SQL User's Guide and Reference.
OR REPLACE
recreates the package body if it already exists. You can use this option to change the body of an existing package without dropping, recreating, and regranting object privileges previously granted on it. If you change a package body, Oracle7 recompiles it. For information on recompiling package bodies, see the ALTER PACKAGE BODY command .
Users who had previously been granted privileges on a redefined package can still access the package without being regranted the privileges.
schema
is the schema to contain the package. If you omit schema, Oracle7 creates the package in your current schema.
package
is the name of the package to be created.
pl/sql_package_ body
is the package body. The package body can declare and define program objects. Package bodies are written in PL/SQL. For information on PL/SQL, including writing package bodies, see PL/SQL User's Guide and Reference.
To embed a CREATE PACKAGE BODY statement inside an Oracle Precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.
Example
This SQL statement creates the body of the EMP_MGMT package:
CREATE PACKAGE BODY emp_mgmt AS tot_emps NUMBER; tot_depts NUMBER; FUNCTION hire(ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER) RETURN NUMBER IS new_empno NUMBER(4); BEGIN SELECT empseq.NEXTVAL INTO new_empno FROM DUAL; INSERT INTO emp VALUES (new_empno, ename, job, mgr, sal, comm, deptno, tot_emps := tot_emps + 1; RETURN(new_empno); END; FUNCTION create_dept(dname VARCHAR2, loc VARCHAR2) RETURN NUMBER IS new_deptno NUMBER(4); BEGIN SELECT deptseq.NEXTVAL INTO new_deptno FROM dual; INSERT INTO dept VALUES (new_deptno, dname, loc); tot_depts := tot_depts + 1; RETURN(new_deptno); END;
PROCEDURE remove_emp(empno NUMBER) IS BEGIN DELETE FROM emp WHERE emp.empno = remove_emp.empno; tot_emps := tot_emps - 1; END; PROCEDURE remove_dept(deptno NUMBER) IS BEGIN DELETE FROM dept WHERE dept.deptno = remove_dept.deptno; tot_depts := tot_depts - 1; SELECT COUNT(*) INTO tot_emps FROM emp; /* In case Oracle7 deleted employees from the EMP table to enforce referential integrity constraints, reset the value of the variable TOT_EMPS to the total number of employees in the EMP table. */ END; PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER) IS curr_sal NUMBER(7,2); BEGIN SELECT sal INTO curr_sal FROM emp WHERE emp.empno = increase_sal.empno; IF curr_sal IS NULL THEN RAISE no_sal; ELSE UPDATE emp SET sal = sal + sal_incr WHERE empno = empno; END IF; END; PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER) IS curr_comm NUMBER(7,2); BEGIN SELECT comm INTO curr_comm FROM emp WHERE emp.empno = increase_comm.empno IF curr_comm IS NULL THEN RAISE no_comm; ELSE UPDATE emp SET comm = comm + comm_incr; END IF; END; END emp_mgmt
This package body corresponds to the package specification in the example of the CREATE PACKAGE statement earlier in this chapter. The package body defines the public program objects declared in the package specification:
Since these objects are defined in the package body, you can change their definitions without causing Oracle7 to invalidate dependent schema objects. For example, if you subsequently change the definition of HIRE, Oracle7 need not recompile INCREASE_ALL_COMMS before executing it.
The package body in this example also declares private program objects, the variables TOT_EMPS and TOT_DEPTS. Since these objects are declared in the package body rather than the package specification, they are accessible to other objects in the package, but they are not accessible outside the package. For example, you cannot develop an application that explicitly changes the value of the variable TOT_DEPTS. However, since the function CREATE_DEPT is part of the package, CREATE_DEPT can change the value of TOT_DEPTS.
To create a procedure in your own schema, you must have CREATE PROCEDURE system privilege. To create a procedure in another schema, you must have CREATE ANY PROCEDURE system privilege. To replace a procedure in another schema, you must have REPLACE ANY PROCEDURE system privilege.
If you are using Trusted Oracle7 in DBMS MAC mode, you can only create a procedure in another user's schema if your DBMS label dominates the creation label of the other user.
To create a procedure, you must be using Oracle7 with PL/SQL installed. For more information, see PL/SQL User's Guide and Reference.
OR REPLACE
recreates the procedure if it already exists. You can use this option to change the definition of an existing procedure without dropping, recreating, and regranting object privileges previously granted on it. If you redefine a procedure, Oracle7 recompiles it. For information on recompiling procedures, see the ALTER PROCEDURE command .
Users who had previously been granted privileges on a redefined procedure can still access the procedure without being regranted the privileges.
schema
is the schema to contain the procedure. If you omit schema, Oracle7 creates the procedure in your current schema.
procedure
is the name of the procedure to be created.
argument
is the name of an argument to the procedure. If the procedure does not accept arguments, you can omit the parentheses following the procedure name.
IN
specifies that you must specify a value for the argument when calling the procedure.
OUT
specifies that the procedure passes a value for this argument back to its calling environment after execution.
IN OUT
specifies that you must specify a value for the argument when calling the procedure and that the procedure passes a value back to its calling environment after execution.
If you omit IN, OUT, and IN OUT, the argument defaults to IN.
datatype
is the datatype of an argument. As long as no length specifier is used, an argument can have any datatype supported by PL/SQL. For information on PL/SQL datatypes, see PL/SQL User's Guide and Reference.
Datatypes are specified without a length, precision, or scale. For example, VARCHAR2(10) is not valid, but VARCHAR2 is valid. Oracle7 derives the length, precision, or scale of an argument from the environment from which the procedure is called.
pl/sql_subprogram_body
is the definition of the procedure. Procedure definitions are written in PL/SQL. For information on PL/SQL, including how to write a PL/SQL subprogram body, see
PL/SQL User's Guide and Reference.
To embed a CREATE PROCEDURE statement inside an Oracle Precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.
With PL/SQL, you can group multiple SQL statements together with procedural PL/SQL statements similar to those in programming languages such as Ada and C. With the CREATE PROCEDURE command, you can create a procedure and store it in the database. You can call a stored procedure from any environment from which you can issue a SQL statement.
Stored procedures offer you advantages in the following areas:
When you create a procedure in Trusted Oracle7, it is labeled with your DBMS label.
The CREATE PROCEDURE command creates a procedure as a stand-alone schema object. You can also create a procedure as part of a package. For information on creating packages, see the CREATE PACKAGE command .
Example
The following statement creates the procedure CREDIT in the schema SAM:
CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER) AS BEGIN UPDATE accounts SET balance = balance + amount WHERE account_id = acc_no; END;
The CREDIT procedure credits a specified bank account with a specified amount. When you call the procedure, you must specify the following arguments:
ACC_NO
This argument is the number of the bank account to be credited. The argument's datatype is NUMBER.
AMOUNT
This argument is the amount of the credit. The argument's datatype is NUMBER.
The procedure uses an UPDATE statement to increase the value in the BALANCE column of the ACCOUNTS table by the value of the argument AMOUNT for the account identified by the argument ACC_NO.
profile
is the name of the profile to be created.
SESSIONS_PER_USER
limits a user to
integer concurrent sessions.
CPU_PER_SESSION
limits the CPU time for a session. This value is expressed in hundredths of seconds.
CPU_PER_CALL
CONNECT_TIME
limits the total elapsed time of a session. This value is expressed in minutes.
IDLE_TIME
LOGICAL_READS_PER_SESSION
limits the number of data blocks read in a session, including blocks read from memory and disk, to
integer blocks.
LOGICAL_READS_PER_CALL
limits the number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch) to
integer blocks.
PRIVATE_SGA
limits the amount of private space a session can allocate in the shared pool of the System Global Area (SGA) to integer bytes. You can also use the K or M to specify this limit in kilobytes or megabytes. This limit only applies if you are using the multi-threaded server architecture. The private space for a session in the SGA includes private SQL and PL/SQL areas, but not shared SQL and PL/SQL areas.
COMPOSITE_LIMIT
limits the total resource cost for a session. You must express the value of this parameter in service units.
Oracle7 calculates the total resource cost as a weighted sum of the following resources:
For information on how to specify the weight for each session resource see the ALTER RESOURCE COST command .
UNLIMITED
indicates that a user assigned this profile can use an unlimited amount of this resource.
DEFAULT
Enable resource limits: You can enable resource limits through one of the following ways:
Any user who is not explicitly assigned a profile is subject to the limits defined in the DEFAULT profile. Also, if the profile that is explicitly assigned to a user omits limits for some resources or specifies DEFAULT for some limits, the user is subject to the limits on those resources defined by the DEFAULT profile.
Example
The following statement creates the profile SYSTEM_MANAGER:
CREATE PROFILE system_manager LIMIT SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL 3000 CONNECT_TIME 45 LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL 1000 PRIVATE SGA 15K COMPOSITE_LIMIT 5000000
If you then assign the SYSTEM_MANAGER profile to a user, the user is subject to the following limits in subsequent sessions:
role Keywords and Parameters
is the name of the role to be created. It is recommended that the role contain at least one single-byte character regardless of whether the database character set also contains multi-byte characters.
indicates that a user granted the role need not be verified when enabling it.
IDENTIFIED
indicates that a user granted the role must be verified when enabling it with the SET ROLE command:
BY password The user must specify the password to Oracle7 when enabling the role. The password can only contain single-byte characters from your database character set regardless of whether this character set also contains multi-byte characters.
If you omit both the NOT IDENTIFIED option and the IDENTIFIED clause, the role defaults to NOT IDENTIFIED.
A role's privilege domain contains all privileges granted to the role and all privileges in the privilege domains of the other roles granted to it. A new role's privilege domain is initially empty. You can add privileges to a role's privilege domain with the GRANT command.
When you create a role, Oracle7 grants you the role with ADMIN OPTION. The ADMIN OPTION allows you to perform the following operations:
The EXP_FULL_DATABASE and IMP_FULL_DATABASE roles are provided for convenience in using the Import and Export utilities.
For more information on these roles, see Table 4 - 12 .
Oracle7 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.
Example
The following statement creates the role TELLER:
CREATE ROLE teller IDENTIFIED BY cashflow
Users who are subsequently granted the TELLER role must specify the passwords CASHFLOW to enable the role.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the tablespace's label.
PUBLIC
specifies that the rollback segment is public and is available to any instance. If you omit this option, the rollback segment is private and is only available to the instance naming it in its initialization parameter ROLLBACK_SEGMENTS.
rollback_segment
is the name of the rollback segment to be created.
TABLESPACE
identifies the tablespace in which the rollback segment is created. If you omit this option, Oracle7 creates the rollback segment in the SYSTEM tablespace.
STORAGE
specifies the characteristics for the rollback segment. See the STORAGE clause .
OPTIMAL
specifies an optimal size in bytes for a rollback segment. You can also use K or M to specify this size in kilobytes or megabytes. Oracle7 tries to maintain this size for the rollback segment by dynamically deallocating extents when their data is no longer needed for active transactions. Oracle7 deallocates as many extents as possible without reducing the total size of the rollback segment below the OPTIMAL value.
The value of this parameter cannot be less than the space initially allocated for the rollback segment specified by the MINEXTENTS, INITIAL, NEXT, and PCTINCREASE parameters. The maximum value varies depending on your operating system. Oracle7 rounds values to the next multiple of the data block size.
When you create a rollback segment, it is initially offline. To make it available for transactions by your Oracle7 instance, you must bring it online using one of the following:
A tablespace can have multiple rollback segments. Generally, multiple rollback segments improve performance. When you create a rollback segment in Trusted Oracle7, it is labeled with your DBMS label.
Example
CREATE ROLLBACK SEGMENT rbs_2 TABLESPACE system;
The above statement is the equivalent of the following:
CREATE ROLLBACK SEGEMENT rbs_2 TABLESPACE system STORAGE ( INITIAL 2 MINEXTENTS 121 MAXEXTENTS 10240 NEXT 10240 PCT_INCREASE 0 )
schema
is the name of the schema. The schema name must be the same as your Oracle7 username.
CREATE TABLE command
is a CREATE TABLE statement to be issued as part of this CREATE SCHEMA statement
. See the CREATE TABLE command .
CREATE VIEW command
is a CREATE VIEW statement
to be issued as part of this CREATE SCHEMA statement. See the CREATE VIEW command .
GRANT command
is a GRANT statement (Objects Privileges) to be issued as part of this CREATE SCHEMA statement. See the GRANT command .
The CREATE SCHEMA statement only supports the syntax of these commands as defined by standard SQL, rather than the complete syntax supported by Oracle7. For information on which parts of the syntax for these commands are standard SQL and which are Oracle7 extensions, see Appendix B of this manual.
Terminate a CREATE SCHEMA statement just as you would any other SQL statement using the terminator character specific to your tool. For example, if you issue a CREATE SCHEMA statement in SQL*Plus or Server Manager, terminate the statement with a semicolon (;). Do not separate the individual statements within a CREATE SCHEMA statement with the terminator character.
The order in which you list the CREATE TABLE, CREATE VIEW, and GRANT statements is unimportant:
Example
The following statement creates a schema named BLAIR for the user BLAIR:
CREATE SCHEMA AUTHORIZATION blair CREATE TABLE sox (color VARCHAR2(10) PRIMARY KEY, quantity NUMBER) CREATE VIEW red_sox AS SELECT color, quantity FROM sox WHERE color = 'RED' GRANT select ON red_sox TO waites
The following statement creates the table SOX, creates the view RED_SOX, and grants SELECT privilege on the RED_SOX view to the user WAITES.
To create a sequence in another user's schema, you must have CREATE ANY SEQUENCE privilege. If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of the owner of the schema to contain the sequence.
schema
is the schema to contain the sequence. If you omit schema, Oracle7 creates the sequence in your own schema.
sequence
is the name of the sequence to be created.
INCREMENT BY
specifies the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or less digits. The absolute of this value must be less than the difference of MAXVALUE and MINVALUE. If this value is negative, then the sequence descends. If the increment is positive, then the sequence ascends. If you omit this clause, the interval defaults to 1.
MINVALUE
NOMINVALUE
specifies a minimum value of 1 for an ascending sequence or -(1026) for a descending sequence.
The default is NOMINVALUE.
MAXVALUE
NOMAXVALUE
specifies a maximum value of 1027 for an ascending sequence or -1 for a descending sequence.
The default is NOMAXVALUE.
START WITH
CYCLE
NOCYCLE
specifies that the sequence cannot generate more values after reaching its maximum or minimum value.
The default is NOCYCLE.
CACHE
(CEIL (MAXVALUE-MINVALUE)) / ABS(INCREMENT)
NOCACHE
specifies that values of the sequence are not pre-allocated.
If you omit both the CACHE parameter and the NOCACHE option, Oracle7 caches 20 sequence numbers by default. However, if you are using Oracle7 with the Parallel Server option in parallel mode and you specify the ORDER option, sequence values are never cached, regardless of whether you specify the CACHE parameter or the NOCACHE option.
ORDER
NOORDER
does not guarantee sequence numbers are generated in order of request.
If you omit both the ORDER and NOORDER options, Oracle7 chooses NOORDER by default. Note that the ORDER option is only necessary to guarantee ordered generation if you are using Oracle7 with the Parallel Server option in parallel mode. If you are using exclusive mode, sequence numbers are always generated in order.
Values for a given sequence are automatically generated by special Oracle7 routines and, consequently, sequences avoid the performance bottleneck which results from implementation of sequences at the application level. For example, one common application-level implementation is to force each transaction to lock a sequence number table, increment the sequence, and then release the table. Under this implementation, only one sequence number may be generated at a time. In contrast, Oracle7 sequences permit the simultaneous generation of multiple sequence numbers while guaranteeing that every sequence number is unique.
When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, the sequence numbers each user acquires may have gaps because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. Once a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.
Because sequence numbers are generated independently of tables, the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.
To create a sequence that stops at a predefined limit, specify a value for the MAXVALUE parameter for an ascending sequence or a value for the MINVALUE parameter for a descending sequence. Also specify the NOCYCLE option. Any attempt to generate a sequence number once the sequence has reached its limit results in an error.
To create a sequence that restarts after reaching a predefined limit, specify values for both the MAXVALUE and MINVALUE parameters. Also specify the CYCLE option. If you do not specify MINVALUE, then it defaults to NOMINVALUE; that is, the value 1.
The value of the START WITH parameter establishes the initial value generated after the sequence is created. Note that this value is not necessarily the value to which an ascending cycling sequence cycles after reaching its maximum or minimum value.
A CACHE of 20 future sequence numbers is the default.
CURRVAL
returns the current value of the sequence.
NEXTVAL
increments the sequence and returns the new value.
For more information on using the above pseudocolumns, see the section "Pseudocolumns" beginning .
Example
The following statement creates the sequence ESEQ:
CREATE SEQUENCE eseq
INCREMENT BY 10
The first reference to ESEQ.NEXTVAL returns 1. The second returns 11. Each subsequent reference will return a value 10 greater than the one previous.
When you create a snapshot, Oracle7 creates a table, two views, and an index in the schema of the snapshot. Oracle7 uses these objects to maintain the snapshot's data. You must have the privileges necessary to create these objects. For information on these privileges, see the CREATE TABLE command on 4 - 245, the CREATE VIEW command on 4 - 271, and the CREATE INDEX command on 4 - 192.
The owner of the schema containing the snapshot must have either space quota on the tablespace to contain the snapshot or UNLIMITED TABLESPACE system privilege. Also, both you (the creator) and the owner must also have the privileges necessary to issue the snapshot's query. For information on these privileges, see the SELECT command .
To create or refresh a snapshot, Oracle7 must be installed with PL/SQL. To create a snapshot on a remote table or view, Oracle7 must be installed with the distributed option.
schema
is the schema to contain the snapshot. If you omit schema, Oracle7 creates the snapshot in your schema.
snapshot
is the name of the snapshot to be created.
Oracle7 chooses names for the table, views, and index used to maintain the snapshot by adding a prefix and suffix to the snapshot name. To limit these names to 30 bytes and allow them to contain the entire snapshot name, It is recommended that you limit your snapshot names to 19 bytes.
PCTFREE PCTUSED INITRANS MAXTRANS
establishes values for the specified parameters for the internal table Oracle7 uses to maintain the snapshot's data. For information on the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters, see the CREATE TABLE command on 4 - 245. For information on the STORAGE clause, see page 4 - 449.
TABLESPACE
STORAGE
establishes storage characteristics for the table Oracle7 uses to maintain the snapshot's data.
CLUSTER
creates the snapshot as part of the specified cluster. Since a clustered snapshot uses the cluster's space allocation, do not use the PCTFREE, PCTUSED, INITRANS, MAXTRANS, TABLESPACE, or STORAGE parameters with the CLUSTER option.
USING INDEX
specifies parameters for the index Oracle7 creates to maintain the snapshot. You can choose the values of the INITRANS, MAXTRANS, TABLESPACE, STORAGE, and PCTFREE parameters for the index. For information on the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters, see the CREATE TABLE command on 4 - 245. For information on the STORAGE clause, see page 4 - 449.
REFRESH
specifies how and when Oracle7 automatically refreshes the snapshot:
COMPLETE specifies a complete refresh, or a refresh that re-executes the snapshot's query.
If you omit the FAST, COMPLETE, and FORCE options, Oracle7 uses FORCE by default.
START WITH specifies a date expression for the first automatic refresh time.
NEXT specifies a date expression for calculating the interval between automatic refreshes.
Both the START WITH and NEXT values must evaluate to a time in the future. If you omit the START WITH value, Oracle7 determines the first automatic refresh time by evaluating the NEXT expression when you create the snapshot. If you specify a START WITH value but omit the NEXT value, Oracle7 refreshes the snapshot only once. If you omit both the START WITH and NEXT values or if you omit the REFRESH clause entirely, Oracle7 does not automatically refresh the snapshot.
FOR UPDATE
Allows a simple snapshot to be updated. When used in conjunction with the Replication Option, these updates will be propagated to the master. For more information, see Oracle7 Server Distributed Systems, Volume II.
AS subquery
specifies the snapshot query. When you create the snapshot, Oracle7 executes this query and places the results in the snapshot. The select list can contain up to 253 expressions. For the syntax of a snapshot query, see the syntax description of subquery . The syntax of a snapshot query is subject to the same restrictions as a view query. For a list of these restrictions, see the CREATE VIEW command on 4 - 271.
Snapshots are useful in distributed databases. Snapshots allow you to maintain read-only copies of remote data on your local node. You can select data from a snapshot as if it were a table or view.
It is recommended that you qualify each table and view in the FROM clause of the snapshot query with the schema containing it.
Snapshots cannot contain long columns.
For more information on snapshots, see Oracle7 Server Distributed Systems, Volume II.
simple
A simple snapshot is one in which the snapshot query selects rows from only one master table. This master table must be a table, not a view. Each row of a simple snapshot must be based on a single row of this table. The query for a simple snapshot cannot contain any of the following SQL constructs:
complex
A complex snapshot is one in which the snapshot query contains one or more of the constructs not allowed in the query of a simple snapshot. A complex snapshot can be based on multiple master tables on multiple master databases.
After you create a snapshot, you can subsequently change its automatic refresh mode and time with the REFRESH clause of the ALTER SNAPSHOT command. You can also refresh a snapshot immediately with the DBMS_SNAPSHOT.REFRESH() procedure.
Fast To perform a fast refresh, Oracle7 updates the snapshot with the changes to the master table recorded in its snapshot log. For more information on snapshot logs, see the CREATE SNAPSHOT LOG command on 4 - 238.
Oracle7 can only perform a fast refresh if all of the following conditions are true:
Complete To perform a complete refresh, Oracle7 executes the snapshot query and places the results in the snapshot. If you specify a complete refresh, Oracle7 performs a complete refresh regardless of whether a fast refresh is possible.
A fast refresh is often faster than a complete refresh because it sends less data from the master database across the network to the snapshot's database. A fast refresh sends only changes to master table data, while a complete refresh sends the complete result of the snapshot query.
You can also use the FORCE option of the REFRESH clause to allow Oracle7 to decide how to refresh the snapshot at the scheduled refresh time. If a fast refresh is possible based on the fast refresh conditions, then Oracle7 performs a fast refresh. If a fast refresh is not possible, then Oracle7 performs a complete refresh.
Example I
CREATE SNAPSHOT emp_sf
PCTFREE 5 PCTUSED 60
TABLESPACE users
STORAGE INITIAL 50K NEXT 50K
REFRESH FAST NEXT sysdate + 7 AS SELECT * FROM scott.emp@ny
Since the statement does not include a START WITH parameter, Oracle7 determines the first automatic refresh time by evaluating the NEXT value using the current SYSDATE. Provided a snapshot log currently exists for the employee table in New York, Oracle7 performs a fast refresh of the snapshot every 7 days, beginning 7 days after the snapshot is created.
The above statement also establishes storage characteristics for the table that Oracle7 uses to maintain the snapshot.
Example II
The following statement creates the complex snapshot ALL_EMPS that queries the employee tables in Dallas and Baltimore:
CREATE SNAPSHOT all_emps PCTFREE 5 PCTUSED 60 TABLESPACE users STORAGE INITIAL 50K NEXT 50K USING INDEX STORAGE (INITIAL 25K NEXT 25K) REFRESH START WITH ROUND(SYSDATE + 1) + 11/24 NEXT NEXT_DAY(TRUNC(SYSDATE, 'MONDAY') + 15/24 AS SELECT * FROM fran.emp@dallas UNION SELECT * FROM marco.emp@balt
Oracle7 automatically refreshes this snapshot tomorrow at 11:00am. and subsequently every Monday at 3:00pm. Since this command does not specify either fast or complete refreshes, Oracle7 must decide how to refresh the snapshot. Since ALL_EMPS is a complex snapshot, Oracle7 must perform a complete refresh.
The above statement also establishes storage characteristics for both the table and the index that Oracle7 uses to maintain the snapshot:
If you own the master table, you can create an associated snapshot log if you have the CREATE TABLE and CREATE TRIGGER system privileges. If you are creating a snapshot log for a table in another user's schema, you must have the CREATE ANY TABLE and CREATE ANY TRIGGER system privileges. In either case, the owner of the snapshot log must have sufficient quota in the tablespace intended to hold the snapshot log.
Before a snapshot log can be created, the user SYS must run the SQL scripts DBMSSNAP.SQL and PRVTSNAP.PLB on the database containing the master table.. If you have the procedural option, this is done automatically This script creates the package DBMS_SNAPSHOT, which contains the stored procedures used for refreshing the snapshot and for purging the snapshot log. The exact name and location of this script may vary depending on your operating system.
You must also have the privileges to create a trigger on the master table. For information on these privileges, see the CREATE TRIGGER command .
To create a snapshot log, you must be using Oracle7 with PL/SQL installed.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the label of the tablespace in which the snapshot log is to be stored.
schema
is the schema containing the snapshot log's master table. If you omit schema, Oracle7 assumes the master table is contained in your own schema. Oracle7 creates the snapshot log in the schema of its master table. You cannot create a snapshot log for a table in the schema of the user SYS.
table
is the name of the master table for which the snapshot log is to be created. You cannot create a snapshot log for a view.
Oracle7 chooses names for the table and trigger used to maintain the snapshot log by prefixing and suffixing the master table name. To limit these names to 30 bytes and allow them to contain the entire master table name, It is recommended that you limit master table names to 20 bytes.
PCTFREE PCTUSED INITRANS MAXTRANS
establishes values for the specified parameters for the snapshot log. See the descriptions of these parameters in the CREATE TABLE command .
TABLESPACE
STORAGE
establishes storage characteristics for the snapshot log. See the STORAGE clause .
A snapshot log is located in the master database in the same schema as the master table. You can create only a single snapshot log for a master table. Oracle7 can use this snapshot log to perform fast refreshes for all simple snapshots based on the master table. Oracle7 records changes in the snapshot log only if there is a simple snapshot based on the master table. For more information on snapshots, including how Oracle7 refreshes snapshots, see the CREATE SNAPSHOT command and Oracle7 Server Distributed Systems, Volume II.
Example
The following statement creates a snapshot log on the employee table:
CREATE SNAPSHOT LOG ON emp
PCTFREE 5
TABLESPACE users
STORAGE (INITIAL 10K NEXT 10K PCTINCREASE 50)
Oracle7 can use this snapshot log to perform a fast refresh on any simple snapshot subsequently created on the EMP table.
To create a private synonym in another user's schema, you must have CREATE ANY SYNONYM system privilege. If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of the owner of schema to contain the synonym.
To create a PUBLIC synonym, you must have CREATE PUBLIC SYNONYM system privilege.
PUBLIC
schema
is the schema to contain the synonym. If you omit schema, Oracle7 creates the synonym in your own schema. You cannot specify schema if you have specified PUBLIC.
synonym
is the name of the synonym to be created.
FOR
identifies the object for which the synonym is created. If you do not qualify object with schema, Oracle7 assumes that the object is in your own schema. The object can be of the following types:
The object cannot be contained in a package.
Note that the object need not currently exist and you need not have privileges to access the object.
You can use a complete or partial dblink to create a synonym for an object on a remote database where the object is located. For more information on referring to database links, see the section, "Referring to Objects in Remote Databases," . If you specify dblink and omit schema, the synonym refers to an object in the schema specified by the database link. It is recommended that you specify the schema containing the object in the remote database.
If you omit dblink, Oracle7 assumes the object is located on the local database.
A synonym can be used to stand for its base object in any of the following Data Manipulation Language statements:
SELECT *
FROM dept
To retrieve rows from BLAKE.DEPT, the user SCOTT must preface DEPT with the schema name:
SELECT *
FROM blake.dept
If the user ADAM's schema does not contain an object named DEPT, then ADAM can access the DEPT table in BLAKE's schema by using the public synonym DEPT:
SELECT *
FROM dept
Example I
CREATE SYNONYM market
FOR scott.market_research
Example II
To create a PUBLIC synonym for the EMP table in the schema SCOTT on the remote SALES database, you could issue the following statement:
CREATE PUBLIC SYNONYM emp
FOR scott.emp@sales
Note that a synonym may have the same name as the base table provided the base table is contained in another schema.
schema
is the schema to contain the table. If you omit schema, Oracle7 creates the table in your own schema.
table
is the name of the table to be created.
column
specifies the name of a column of the table. A table can have up to 254 columns. You may only omit column definitions when using the AS subquery clause.
datatype
is the datatype of a column. Datatypes are defined .
You can omit the datatype only if the statement also designates the column as part of a foreign key in a referential integrity constraint. Oracle7 automatically assigns the column the datatype of the corresponding column of the referenced key of the referential integrity constraint.
DEFAULT
specifies a value to be assigned to the column if a subsequent INSERT statement omits a value for the column. The datatype of the expression must match the datatype of the column. The column must also be long enough to hold this expression. For the syntax of expr, see page 3 - 73. A DEFAULT expression cannot contain references to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants that are not fully specified.
column_constraint
defines an integrity constraint as part of the column definition. See the syntax description of column_constraint .
table_constraint
defines an integrity constraint as part of the table definition. See the syntax description of table_constraint .
PCTFREE
specifies the percentage of space in each of the table's data blocks reserved for future updates to the table's rows. The value of PCTFREE must be a value from 0 to 99. A value of 0 allows the entire block to be filled by inserts of new rows. The default value is 10. This value reserves 10% of each block for updates to existing rows and allows inserts of new rows to fill a maximum of 90% of each block.
PCTFREE has the same function in the commands that create and alter clusters, indexes, snapshots, and snapshot logs. The combination of PCTFREE and PCTUSED determines whether inserted rows will go into existing data blocks or into new blocks.
PCTUSED
specifies the minimum percentage of used space that Oracle7 maintains for each data block of the table. A block becomes a candidate for row insertion when its used space falls below PCTUSED. PCTUSED is specified as a positive integer from 1 to 99 and defaults to 40.
PCTUSED has the same function in the commands that create and alter clusters, snapshots, and snapshot logs.
The sum of PCTFREE and PCTUSED must be less than 100. You can use PCTFREE and PCTUSED together use space within a table more efficiently. For information on the performance effects of different values PCTUSED and PCTFREE, see Oracle7 Server Tuning.
INITRANS
specifies the initial number of transaction entries allocated within each data block allocated to the table. This value can range from 1 to 255 and defaults to 1. In general, you should not change the INITRANS value from its default.
Each transaction that updates a block requires a transaction entry in the block. The size of a transaction entry depends on your operating system.
This parameter ensures that a minimum number of concurrent transactions can update the block and helps avoid the overhead of dynamically allocating a transaction entry.
The INITRANS parameter serves the same purpose in clusters, indexes, snapshots, and snapshot logs as in tables. The minimum and default INITRANS value for a cluster or index is 2, rather than 1.
MAXTRANS
specifies the maximum number of concurrent transactions that can update a data block allocated to the table. This limit does not apply to queries. This value can range from 1 to 255 and the default is a function of the data block size. You should not change the MAXTRANS value from its default.
If the number concurrent transactions updating a block exceeds the INITRANS value, Oracle7 dynamically allocates transaction entries in the block until either the MAXTRANS value is exceeded or the block has no more free space.
The MAXTRANS parameter serves the same purpose in clusters, snapshots, and snapshot logs as in tables.
TABLESPACE
specifies the tablespace in which Oracle7 creates the table. If you omit this option, then Oracle7 creates the table in the default tablespace of the owner of the schema containing the table.
STORAGE
specifies the storage characteristics for the table. This clause has performance ramifications for large tables. Storage should be allocated to minimize dynamic allocation of additional space. See the STORAGE clause .
RECOVERABLE
specifies that the creation of the table (and any indices required because of constraints) will be logged in the redo log file. This is the default.
If the database is run in ARCHIVELOG mode, media recovery from a backup will recreate the table (and any indices required because of constraints). You cannot specify RECOVERABLE when using NOARCHIVELOG mode.
UNRECOVERABLE
specifies that the creation of the table (and any indices required because of constraints) will not be logged in the redo log file. As a result, media recovery will not recreate the table (and any indices required because of constraints).
This keyword can only be specified with the AS subquery clause. Using this keyword makes table creation faster than using the RECOVERABLE option because redo log entries are not written.
CLUSTER
specifies that the table is to be part of the cluster. The columns listed in this clause are the table columns that correspond to the cluster's columns. Generally, the cluster columns of a table are the column or columns that comprise its primary key or a portion of its primary key.
Specify one column from the table for each column in the cluster key. The columns are matched by position, not by name. Since a clustered table uses the cluster's space allocation, do not use the PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameters, the TABLESPACE option, or the STORAGE clause with the CLUSTER option.
PARALLEL
specifies the degree of parallelism for creating the table and the default degree of parallelism for queries on the table once created. For more information, see the parallel_clause .
ENABLE
enables an integrity constraint. See the ENABLE clause .
DISABLE
disables an integrity constraint. See the DISABLE clause .
Constraints specified in the ENABLE and DISABLE clauses of a CREATE TABLE statement must be defined in the statement. You can also enable and disable constraints with the ENABLE and DISABLE keywords of the CONSTRAINT clause. If you define a constraint but do not explicitly enable or disable it, Oracle7 enables it by default.
You cannot use the ENABLE and DISABLE clauses in a CREATE TABLE statement to enable and disable triggers.
AS subquery
inserts the rows returned by the subquery into the table upon its creation. See the syntax description of subquery .
The number of columns in the table must equal the number of expressions in the subquery. The column definitions can only specify column names, default values, and integrity constraints, not datatypes. Oracle7 derives datatypes and lengths from the subquery. Oracle7 also follows the following rules for integrity constraints:
If all expressions in the subquery are columns, rather than expressions, you can omit the columns from the table definition entirely. In this case, the names of the columns of table are the same as the columns in the subquery.
CACHE
specifies that the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.
NOCACHE
specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the default behavior.
After creating a table, you can define additional columns and integrity constraints with the ADD clause of the ALTER TABLE command. You can change the definition of an existing column with the MODIFY clause of the ALTER TABLE command. To modify an integrity constraint, you must drop the constraint and redefine it.
Example I
To define the EMP table owned by SCOTT, you could issue the following statement:
CREATE TABLE scott.emp (empno NUMBER CONSTRAINT pk_emp PRIMARY KEY, ename VARCHAR2(10) CONSTRAINT nn_ename NOT NULL CONSTRAINT upper_ename CHECK (ename = UPPER(ename)), job VARCHAR2(9), mgr NUMBER CONSTRAINT fk_mgr REFERENCES scott.emp(empno), hiredate DATE DEFAULT SYSDATE, sal NUMBER(10,2) CONSTRAINT ck_sal CHECK (sal > 500), comm NUMBER(9,0) DEFAULT NULL, deptno NUMBER(2) CONSTRAINT nn_deptno NOT NULL CONSTRAINT fk_deptno REFERENCES scott.dept(deptno) ) PCTFREE 5 PCTUSED 75 ;
This table contains 8 columns. For example, the EMPNO column is of datatype NUMBER and has an associated integrity constraint named PK_EMP. The HIRDEDATE column is of datatype DATE and has a default value of SYSDATE.
This table definition specifies a PCTFREE of 5 and a PCTUSED of 75, which is appropriate for a relatively static table. The definition also defines integrity constraints on the columns of the EMP table.
Example II
CREATE TABLE salgrade ( grade NUMBER CONSTRAINT pk_salgrade PRIMARY KEY USING INDEX TABLESPACE users_a, losal NUMBER, hisal NUMBER ) TABLESPACE human_resource STORAGE (INITIAL 6144 NEXT 6144 MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 5);
The above statement also defines a PRIMARY KEY constraint on the GRADE column and specifies that the index Oracle7 creates to enforce this constraint is created in the USERS_A tablespace.
For more examples of defining integrity constraints, see the CONSTRAINT clause . For examples of enabling and disabling integrity constraints, see the ENABLE and DISABLE clauses on pages 4 - 326 and 4 - 295, respectively.
Example III
Assuming you have the parallel query option, then the fastest method to create a table that has the same columns as the EMP table, but only for those employees in department 10, is to issue a command similar to the following:
CREATE TABLE emp_tmp UNRECOVERABLE PARALLEL (DEGREE 3) AS SELECT * FROM emp WHERE deptno = 10;
The UNRECOVERABLE keyword speeds up table creation because there is no overhead in generating and logging redo information.
Using parallelism speeds up the creation of the table because three processes are used to create the table. After the table is created, querying the table is also faster because the same degree of parallelism is used to access the table.
tablespace
is the name of the tablespace to be created.
DATAFILE
specifies the data file or files to comprise the tablespace. See the syntax description of filespec .
AUTOEXTEND
enables or disables the automatic extension of datafile.
OFF disable autoextend if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in further ALTER TABLESPACE AUTOEXTEND commands.
ON enable autoextend.
NEXT disk space to allocate to the datafile when more extents are required.
MAXSIZE maximum disk space allowed for allocation to the datafile.
UNLIMITED set no limit on allocating disk space to the datafile.
DEFAULT STORAGE
specifies the default storage parameters for all objects created in the tablespace. For information on storage parameters, see the STORAGE clause.
ONLINE
makes the tablespace available immediately after creation to users who have been granted access to the tablespace.
OFFLINE
makes the tablespace unavailable immediately after creation.
If you omit both the ONLINE and OFFLINE options, Oracle7 creates the tablespace online by default. The data dictionary view DBA_TABLESPACES indicates whether each tablespace is online or offline.
PERMANENT
specifies that the tablespace will be used to hold permanent objects. This is the default.
TEMPORARY
specifies that the tablespace will only be used to hold temporary objects. For example, segments used by implicit sorts to handle ORDER BY clauses.
When you create a tablespace, it is initially a read-write tablespace. After creating the tablespace, you can subsequently use the ALTER TABLESPACE command to take it offline or online, add data files to it, or make it a read-only tablespace.
Many schema objects have associated segments that occupy space in the database. These objects are located in tablespaces. The user creating such an object can optionally specify the tablespace to contain the object. The owner of the schema containing the object must have space quota on the object's tablespace. You can assign space quota on a tablespace to a user with the QUOTA clause of the CREATE USER or ALTER USER commands.
Warning: For operating systems that support raw devices, be aware that the STORAGE clause REUSE keyword has no meaning when specifying a raw device as a datafile in a CREATE TABLESPACE command; such a command will always succeed even if REUSE is not specified.
Example I
This command creates a tablespace named TABSPACE_2 with one datafile:
CREATE TABLESPACE tabspace_2 DATAFILE 'diska:tabspace_file2.dat' SIZE 20M DEFAULT STORAGE (INITIAL 10K NEXT 50K MINEXTENTS 1 MAXEXTENTS 999 PCTINCREASE 10) ONLINE
Example II
This command creates a tablespace named TABSPACE_3 with one datafile; when more space is required, 50 kilobyte extents will be added up to a maximum size of 10 megabytes:
CREATE TABLESPACE tabspace_3 DATAFILE 'diskb:tabspace_file3.dat' SIZE 500K REUSE AUTOEXTEND ON NEXT 500K MAXSIZX 10M
To issue this statement, you must have one of the following system privileges:
CREATE TRIGGER
This system privilege allows you to create a trigger in your own schema on a table in your own schema.
CREATE ANY TRIGGER
This system privilege allows you to create a trigger in any user's schema on a table in any user's schema.
If the trigger issues SQL statements or calls procedures or functions, then the owner of the schema to contain the trigger must have the privileges necessary to perform these operations. These privileges must be granted directly to the owner, rather than acquired through roles.
To create a trigger, you must be using Oracle7 with PL/SQL installed.
OR REPLACE
schema
is the schema to contain the trigger. If you omit schema, Oracle7 creates the trigger in your own schema.
trigger
is the name of the trigger to be created.
BEFORE
AFTER
DELETE
indicates that Oracle7 fires the trigger whenever a DELETE statement removes a row from the table.
INSERT
indicates that Oracle7 fires the trigger whenever an INSERT statement adds a row to table.
UPDATE OF
indicates that Oracle7 fires the trigger whenever an UPDATE statement changes a value in one of the columns specified in the OF clause. If you omit the OF clause, Oracle7 fires the trigger whenever an UPDATE statement changes a value in any column of the table.
ON
specifies the schema and name of the table on which the trigger is to be created. If you omit schema, Oracle7 assumes the table is in your own schema. You cannot create a trigger on a table in the schema SYS.
REFERENCING
FOR EACH ROW
If you omit this clause, the trigger is a statement trigger. Oracle7 fires a statement trigger only once when the triggering statement is issued if the optional trigger constraint is met.
WHEN
specifies the trigger restriction. The trigger restriction contains a SQL condition that must be satisfied for Oracle7 to fire the trigger. See the syntax description of condition . This condition must contain correlation names and cannot contain a query.
You can only specify a trigger restriction for a row trigger. Oracle7 evaluates this condition for each row affected by the triggering statement.
pl/sql_block
is the PL/SQL block that Oracle7 executes to fire the trigger. For information on PL/SQL, including how to write PL/SQL blocks, see PL/SQL User's Guide and Reference.
Note that the PL/SQL block of a trigger cannot contain transaction control SQL statements (COMMIT, ROLLBACK, and SAVEPOINT).
If a trigger produces compilation errors, it still will be created, but it will fail on execution. This means it effectively blocks all triggering DML statements until it is disabled, replaced by a version without compilation errors, or dropped.
To embed a CREATE TRIGGER statement inside an Oracle Precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.
You can use triggers for the following purposes:
Triggering statement The definition of the triggering statement specifies what SQL statements cause Oracle7 to fire the trigger.
DELETE INSERT UPDATE
You must specify at least one of these commands that causes Oracle7 to fire the trigger. You can specify as many as three.
ON
Trigger restriction The trigger restriction specifies an additional condition that must be satisfied for a row trigger to be fired. You can specify this condition with the WHEN clause. This condition must be a SQL condition, rather than a PL/SQL condition.
Trigger action The trigger action specifies the PL/SQL block Oracle7 executes to fire the trigger.
Oracle7 evaluates the condition of the trigger restriction whenever a triggering statement is issued. If this condition is satisfied, then Oracle7 fires the trigger using the trigger action.
FOR EACH ROW option | ||
BEFORE Option | BEFORE statement trigger: Oracle7 fires the trigger once before executing the triggering statement. | BEFORE row trigger: Oracle7 fires the trigger before modifying each row affected by the triggering statement. |
AFTER Option | AFTER statement trigger: Oracle7 fires the trigger once after executing the triggering statement. | AFTER row trigger: Oracle7 fires the trigger after modifying each row affected by the triggering statement. |
Table 4 - 10. Types of Triggers | ||
If you create multiple triggers of the same type that fire for the same command on the same table, the order in which Oracle7 fires these triggers is indeterminate. If your application requires that one trigger be fired before another of the same type for the same command, combine these triggers into a single trigger whose trigger action performs the trigger actions of the original triggers in the appropriate order.
enabled
If a trigger is enabled, Oracle7 fires the trigger whenever a triggering statement is issued and the condition of the trigger restriction is met.
disabled
If a trigger is disabled, Oracle7 does not fire the trigger when a triggering statement is issued and the condition of the trigger restriction is met.
When you create a trigger, Oracle7 enables it automatically.
You can subsequently disable and enable a trigger with one of the following commands:
Example I
CREATE TRIGGER scott.emp_permit_changes BEFORE DELETE OR INSERT OR UPDATE ON scott.emp DECLARE dummy INTEGER; BEGIN /* If today is a Saturday or Sunday, then return an error.*/ IF (TO_CHAR(SYSDATE, 'DY') = 'SAT' OR TO_CHAR(SYSDATE, 'DY') = 'SUN') THEN raise_application_error( -20501, 'May not change employee table during the weekend'); END IF; /* Compare today's date with the dates of all company holidays. If today is a company holiday, then return an error. */ SELECT COUNT(*) INTO dummy FROM company_holidays WHERE day = TRUNC(SYSDATE); IF dummy > 0 THEN raise_application_error( -20501, 'May not change employee table during a holiday'); END IF; /* If the current time is before 8:00AM or after 6:00PM, then return an error. */ IF (TO_CHAR(SYSDATE, 'HH24') < 8 OR TO_CHAR(SYSDATE, 'HH24') >= 18) THEN raise_application_error( -20502, 'May only change employee table during working hours'); END IF; END;
Oracle7 fires this trigger whenever a DELETE, INSERT, or UPDATE statement affects the EMP table in the schema SCOTT.
Since EMP_PERMIT_CHANGES is a BEFORE statement trigger, Oracle7 fires it once before executing the triggering statement.
The trigger performs the following operations:
CREATE TRIGGER scott.salary_check BEFORE INSERT OR UPDATE OF sal, job ON scott.emp FOR EACH ROW WHEN (new.job <> 'PRESIDENT') DECLARE minsal NUMBER; maxsal NUMBER; BEGIN /* Get the minimum and maximum salaries for the employee's job from the SAL_GUIDE table. */ SELECT minsal, maxsal INTO minsal, maxsal FROM sal_guide WHERE job = :new.job; /* If the employee's salary is below the minimum or */ /* above the maximum for the job, then generate an */ /* error. */ IF (:new.sal < minsal OR :new.sal > maxsal) THEN raise_application_error( -20601, 'Salary ' || :new.sal || ' out of range for job ' || :new.job || ' for employee ' || :new.ename ); END IF; END;
Oracle7 fires this trigger whenever one of the following statements is issued:
SALARY_CHECK has a trigger restriction that prevents it from checking the salary of the company president. For each new or modified employee row that meets this condition, the trigger performs the following steps:
If you are using Trusted Oracle7 in DBMS MAC mode, you must meet additional prerequisites to perform the optional assignments of this statement:
user Keywords and Parameters
is the name of the user to be created. This name can only contain characters from your database character set and must follow the rules described in the section "Object Naming on Rule" . It is recommended that the user contain at least one single-byte character regardless of whether the database character set also contains multi-byte characters.
indicates how Oracle7 permits user access:
BY password The user must specify this password to logon. Password must follow the rules described in the section "Object Naming Rules" and can only contain single-byte characters from your database character set regardless of whether this character set also contains multi-byte characters.
DEFAULT TABLESPACE
identifies the default tablespace for objects that the user creates. If you omit this clause, objects default to the SYSTEM tablespace.
TEMPORARY TABLESPACE
identifies the tablespace for the user's temporary segments. If you omit this clause, temporary segments default to the SYSTEM tablespace.
QUOTA
allows the user to allocate space in the tablespace and optionally establishes a quota of integer bytes. This quota is the maximum space in the tablespace the user can allocate. You can also use the K or M to specify the quota in kilobytes or megabytes.
Note that a CREATE USER command can have multiple QUOTA clauses for multiple tablespaces.
UNLIMITED allows the user to allocate space in the tablespace without bound.
PROFILE
reassigns the profile named profile to the user. The profile limits the amount of database resources the user can use. If you omit this clause, Oracle7 assigns the DEFAULT profile to the user.
Oracle Corporation strongly recommends that you do not use IDENTIFIED EXTERNALLY with operating systems that have inherently weak login security.
Note that you need not have a quota on a tablespace to establish a quota for another user on that tablespace.
Note: To logon to Oracle7, a user must have CREATE SESSION system privilege. After creating a user, you should grant the user this privilege.
Example I
You can create the user SIDNEY by issuing the following statement:
CREATE USER sidney IDENTIFIED BY carton DEFAULT TABLESPACE cases_ts QUOTA 10M ON cases_ts QUOTA 5M ON temp_ts QUOTA 5M ON system PROFILE engineer
The user SIDNEY has the following characteristics:
To create a user accessible only by the operating system account GEORGE, prefix GEORGE by the value of the initialization parameter OS_AUTHENT_PREFIX. For example, if this value is "OPS$", you can create the user OPS$GEORGE with the following statement:
CREATE USER ops$george
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE accs_ts
TEMPORARY TABLESPACE temp_ts
QUOTA UNLIMITED ON accs_ts
QUOTA UNLIMITED ON temp_ts
The user OPS$GEORGE has the following additional characteristics:
The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. For information on these privileges, see the SELECT command , the INSERT command , the UPDATE command , and the DELETE command . The owner must be granted these privileges directly, rather than through a role.
OR REPLACE
FORCE
NOFORCE
The default is NOFORCE.
schema
is the schema to contain the view. If you omit schema, Oracle7 creates the view in your own schema.
view
is the name of the view.
alias
specifies names for the expressions selected by the view's query. The number of aliases must match the number of expressions selected by the view. Aliases must follow the rules for naming schema objects in the section, "Naming Objects and Parts," . Aliases must be unique within the view.
If you omit the aliases, Oracle7 derives them from the columns or column aliases in the view's query. For this reason, you must use aliases if the view's query contains expressions rather than only column names.
AS subquery
identifies columns and rows of the table(s) that the view is based on. A view's query can be any SELECT statement without the ORDER BY or FOR UPDATE clauses. Its select list can contain up to 254 expressions. See the syntax description of subquery .
WITH READ ONLY
specifies that no deletes, inserts, or updates can be performed through the view.
WITH CHECK OPTION
CONSTRAINT
SYS_Cn
where
n is an integer that makes the constraint name unique within the database.
Views are used for the following purposes:
CREATE VIEW emp_vu AS SELECT * FROM emp
If the view query contains any of the following constructs, you cannot perform inserts, updates, or deletes on the view:
If at least one column in the subquery join has a unique index, then it may be possible to modify one base table in a join view. You can query USER_UPDATABLE_COLUMNS to see whether the columns in a join view are updatable. For example:
CREATE VIEW ed AS SELECT e.empno, e.ename, d.deptno, d.loc FROM emp e, dept d WHERE e.deptno = d.deptno View created. SELECT column_name, updatable FROM user_updatable_columns WHERE table_name = 'ED'; COLUMN_NAME UPD --------------- --- ENAME YES DEPTNO NO EMPNO YES LOC NO
In the above example, note that there is a unique index on the DEPTNO column of the DEPT table.
In the above example, you may insert, update or delete a row from the EMP base table because all the columns in the view mapping to the emp table are marked as updatable and because the primary key of emp is included in the view. For more information on updating join views, see "Modifying a Join View" in the Oracle7 Server Application Developer's Guide. If there were not null columns in the base EMP table that were not specified in the view subquery, then you could not insert into the table using the view.
Example I
CREATE VIEW dept20
AS SELECT ename, sal*12 annual_salary
FROM emp
WHERE deptno = 20
Note that the view declaration need not define a name for the column based on the expression SAL*12 because the subquery uses a column alias (ANNUAL_SALARY) for this expression.
Example II
The following statement creates an updatable view named CLERKS of all clerks in the employee table; only the employees' IDs, names, and department numbers are visible in this view and only these columns can be updated in rows identified as clerks:
CREATE VIEW clerk (id_number, person, department, position) AS SELECT empno, ename, deptno, job FROM emp WHERE job = 'CLERK' WITH CHECK OPTION CONSTRAINT wco
Example III
The following statement creates a read only view named CLERKS of all clerks in the employee table; only the employee's IDs, names, and department numbers are visible in this view:
CREATE VIEW clerk (id_number, person, department, position) AS SELECT empno, ename, deptno, job FROM emp WHERE job = 'CLERK' WITH READ ONLY
Because of the CHECK OPTION, you cannot subsequently insert a new row into CLERK if the new employee is not a clerk.
KEEP
specifies the amount of unused space to keep.
integer the number of bytes to keep. You can also use K or M to specify the size in kilobytes or megabytes.
You use the DEALLOCATE clause to reclaim unused space in extents in a cluster, table or index for reuse by other objects in the tablespace. The user quota for the tablespace in which the deallocation occurs is credited by the amount of the released space.
Unused space is deallocated from the end of the object toward the high water mark at the beginning of the object. If an extent is completely contained in the deallocation, then the whole extent is freed for reuse. If an extent is partially contained in the deallocation, then the used part up to the high water mark becomes the extent and the remaining unused space is freed for reuse.
INITIAL, MINEXTENTS and NEXT are described in the STORAGE clause .
If you omit the KEEP option and the high water mark is above the size of INITIAL and MINEXTENTS, then all unused space above the high water mark is freed. When the high water mark is less than the size of INITIAL or MINEXTENTS, then all unused space above MINEXTENTS is freed.
If you use the KEEP option, then the specified amount of space is kept and the remaining space is freed. When the remaining number of extents is less than MINEXTENTS, then MINEXTENTS is adjusted to the new number of extents. If the initial extent becomes smaller than INITIAL, then INITIAL is adjusted to the new size.
NEXT is set to the size of the last extent that was deallocated.
Example I
The following command frees all unused space for reuse in table EMP, where the high water mark is above MINEXTENTS:
ALTER TABLE emp DEALLOCATE UNUSED
AT
identifies the database on which the cursor is declared. The database can be identified by either:
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, Oracle7 declares the cursor on your default database.
cursor
is the name of the cursor to be declared.
SELECT command
is a SELECT statement to be associated with the cursor. The following statement cannot contain an INTO clause.
statement_name block_name
identifies a SQL statement or PL/SQL block to be associated with the cursor. The statement_name or block_name must be previously declared in a DECLARE STATEMENT statement.
You can reference the cursor in the WHERE clause of an UPDATE or DELETE statement using the CURRENT OF syntax, provided that the cursor has been opened with an OPEN statement and positioned on a row with a FETCH statement. For more information on this command, see Programmer's Guide to the Oracle Precompilers.
Example
This example illustrates the use of a DECLARE CURSOR:
EXEC SQL DECLARE emp_cursor CURSOR
FOR SELECT ename, empno, job, sal
FROM emp
WHERE deptno = :deptno
FOR UPDATE OF sal
db_name
is the identifier established for the non-default database.
For more information on this command, see Programmer's Guide to the Oracle Precompilers.
Example
This example illustrates the use of a DECLARE DATABASE statement:
EXEC SQL DECLARE oracle3 DATABASE
AT
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, Oracle7 declares the SQL statement or PL/SQL block on your default database.
statement_name block_name
is the declared identifier for the statement.
The scope of a statement declaration is global within its precompilation unit, like a cursor declaration. For more information on this command, see Programmer's Guide to the Oracle Precompilers.
Example I
This example illustrates the use of the DECLARE STATEMENT statement:
EXEC SQL AT remote_db
DECLARE my_statement STATEMENT
EXEC SQL PREPARE my_statement FROM :my_string
EXEC SQL EXECUTE my_statement
Example II
In this example from a Pro*C embedded SQL program, the DECLARE STATEMENT statement is required because the DECLARE CURSOR statement precedes the PREPARE statement:
EXEC SQL DECLARE my_statement STATEMENT;
call prepare_my_statement;
EXEC SQL DECLARE emp_cursor CURSOR FOR my_statement;
...
PROCEDURE prepare_my_statement
BEGIN
EXEC SQL PREPARE my_statement FROM :my_string;
END;
table
is the name of the declared table.
column
is a column of the table.
datatype
is the datatype of a column. For information on Oracle7 datatypes, see the section "Datatypes" .
DEFAULT
specifies the default value of a column.
NULL
specifies that a column can contain nulls.
NOT NULL
specifies that a column cannot contain nulls.
WITH DEFAULT
is supported for compatibility with IBM's DB2 database.
Example
The following statement declares the PARTS table with the PARTNO, BIN, and QTY columns:
EXEC SQL DECLARE parts TABLE
(partno NUMBER NOT NULL,
bin NUMBER,
qty NUMBER)
For you to delete rows from the base table of a view, the owner of the schema containing the view must have DELETE privilege on the base table. Also, if the view is in a schema other than your own, you must be granted DELETE privilege on the view.
The DELETE ANY TABLE system privilege also allows you to delete rows from any table or any view's base table.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of the table or view or you must meet one of the following criteria:
schema
is the schema containing the table or view. If you omit schema, Oracle7 assumes the table or view is in your own schema.
table view
is the name of a table from which the rows are to be deleted. If you specify view, Oracle7 deletes rows from the view's base table.
dblink
is the 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 the section "Referring to Objects in Remote Databases" . You can only delete rows from a remote table or view if you are using Oracle7 with the distributed option.
If you omit dblink, Oracle7 assumes that the table or view is located on the local database.
subquery
is a subquery from which data is selected for deletion. For the syntax of subquery, see page 4 - 431. Oracle executes the subquery and then uses the resulting rows as a table in the FROM clause. The subquery cannot query a table that appears in the same FROM clause as the subquery.
alias
is an alias assigned to the table, view or subquery. Aliases are generally used in DELETE statements with correlated queries.
WHERE
deletes only rows that satisfy the condition. The condition can reference the table and can contain a subquery. See the syntax description of condition . You can only delete rows from a remote table or view if you are using Oracle7 with the distributed option.
If you omit dblink, Oracle7 assumes that the table or view is located on the local database.
Example I
The following statement deletes all rows from a table named TEMP_ASSIGN.
DELETE FROM temp_assign
Example II
The following statement deletes from the employee table all sales staff who made less than $100 commission last month:
DELETE FROM emp WHERE JOB = 'SALESMAN' AND COMM < 100
Example III
The following statement has the same effect as in Example II:
DELETE FROM (select * from emp) WHERE JOB = 'SALESMAN' AND COMM < 100
Example IV
The following statement deletes all rows from the bank account table owned by the user BLAKE on a database accessible by the database link DALLAS:
DELETE FROM blake.accounts@dallas
For you to delete rows from the base table of a view, the owner of the schema containing the view must have DELETE privilege on the base table. Also, if the view is in a schema other than your own, you must be granted DELETE privilege on the view.
The DELETE ANY TABLE system privilege also allows you to delete rows from any table or any view's base table.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of the table or view or you must meet one of the following criteria:
AT Keywords and Parameters
identifies the database to which the DELETE statement is issued. The database can be identified by either:
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
is a host variable whose value is a previously declared db_name.
If you omit this clause, the DELETE statement is issued to your default database.
FOR :host_integer
limits the number of times the statement is executed if the WHERE clause contains array host variables. If you omit this clause, Oracle7 executes the statement once for each component of the smallest array.
schema
is the schema containing the table or view. If you omit schema, Oracle7 assumes the table or view is in your own schema.
table view
is the name of a table from which the rows are to be deleted. If you specify view, Oracle7 deletes rows from the view's base table.
dblink
is the 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 the section "Referring to Objects in Remote Databases" . You can only delete rows from a remote table or view if you are using Oracle7 with the distributed option.
If you omit dblink, Oracle7 assumes that the table or view is located on the local database.
subquery
is a subquery from which data is selected for deletion. For the syntax of subquery, see page 4 - 431. Oracle executes the subquery and then uses the resulting rows as a table in the FROM clause. The subquery cannot query a table that appears in the same FROM clause as the subquery.
alias
is an alias assigned to the table. Aliases are generally used in DELETE statements with correlated queries.
WHERE
specifies which rows are deleted:
condition deletes only rows that satisfy the condition. This condition can contain host variables and optional indicator variables. See the syntax description of condition .
CURRENT OF deletes only the row most recently fetched by the cursor. The cursor cannot be associated with a SELECT statement that performs a join, unless its FOR UPDATE clause specifically locks only one table.
If you omit this clause entirely, Oracle7 deletes all rows from the table or view.
Array host variables in the WHERE clause can have different sizes. In this case, the number of times Oracle7 executes the statement is determined by the smaller of the following values:
The cumulative number of rows deleted is returned through the SQLCA. If the WHERE clause contains array host variables, this value reflects the total number of rows deleted for all components of the array processed by the DELETE statement.
If no rows satisfy the condition, Oracle7 returns an error through the SQLCODE of the SQLCA. If you omit the WHERE clause, Oracle7 raises a warning flag in the 5th component of SQLWARN in the SQLCA. For more information on this command and the SQLCA, see Programmer's Guide to the Oracle Precompilers.
You can use comments in a DELETE statement to pass instructions, or hints, to the Oracle7 optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, see Oracle7 Server Tuning.
Example
This example illustrates the use of the DELETE statement within a Pro*C embedded SQL program:
EXEC SQL DELETE FROM emp WHERE deptno = :deptno AND job = :job; ... EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT empno, comm FROM emp; EXEC SQL OPEN emp_cursor; EXEC SQL FETCH c1 INTO :emp_number, :commission; EXEC SQL DELETE FROM emp WHERE CURRENT OF emp_cursor;
initializes the descriptor to hold information about the input variables for the SQL statement or PL/SQL block.
SELECT LIST
initializes the descriptor to hold information about the select list of a SELECT statement.
The default is SELECT LIST FOR.
statement_name block_name
identifies a SQL statement or PL/SQL block previously prepared with a PREPARE statement.
descriptor
is the name of the descriptor to be initialized.
You cannot describe both input variables and output variables into the same descriptor.
The number of variables found by a DESCRIBE statement is the total number of placeholders in the prepare SQL statement or PL/SQL block, rather than the total number of uniquely named placeholders. For more information on this command, see Programmer's Guide to the Oracle Precompilers.
Example
This example illustrates the use of the DESCRIBE statement in a Pro*C embedded SQL program:
EXEC SQL PREPARE my_statement FROM :my_string;
EXEC SQL DECLARE emp_cursor
FOR SELECT empno, ename, sal, comm
FROM emp
WHERE deptno = :dept_number
EXEC SQL DESCRIBE BIND VARIABLES FOR my_statement
INTO bind_descriptor;
EXEC SQL OPEN emp_cursor
USING bind_descriptor;
EXEC SQL DESCRIBE SELECT LIST FOR my_statement
INTO select_descriptor;
EXEC SQL FETCH emp_cursor
INTO select_descriptor;
For an integrity constraint to appear in a DISABLE clause, one of the following conditions must be true:
UNIQUE Keywords and Parameters
disables the UNIQUE constraint defined on the specified column or combination of columns.
disables the table's PRIMARY KEY constraint.
CONSTRAINT
disables the integrity constraint with the name constraint.
CASCADE
ALL TRIGGERS
You can also disable an integrity constraint with the DISABLE keyword in the CONSTRAINT clause that defines the integrity constraint. For information on this keyword, see the CONSTRAINT clause .
How Oracle7 Disables Integrity Constraints If you disable an integrity constraint, Oracle7 does not enforce it. If you define an integrity constraint and disable it, Oracle7 does not apply it to existing rows of the table, although Oracle7 does store it in the data dictionary along with enabled integrity constraints. Also, Oracle7 can execute Data Manipulation Language statements that change table data and violate a disabled integrity constraint.
If you disable a UNIQUE or PRIMARY KEY constraint that was previously enabled, Oracle7 drops the index that enforces the constraint.
You can enable a disabled integrity constraint with the ENABLE clause.
Disabling Referenced Keys in Referential Integrity Constraints To disable a UNIQUE or PRIMARY KEY constraint that identifies the referenced key of a referential integrity constraint, you must also disable the foreign key. To disable a constraint and all its dependent constraints, use the CASCADE option of the DISABLE clause.
You cannot enable a foreign key that references a unique or primary key that is disabled.
Example I
The following statement creates the DEPT table and defines a disabled PRIMARY KEY constraint:
CREATE TABLE dept
(deptno NUMBER(2) PRIMARY KEY,
dname VARCHAR2(10),
loc VARCHAR2(9) )
DISABLE PRIMARY KEY
Since the primary key is disabled, you can add rows to the table that violate the primary key. You can add departments with null department numbers or multiple departments with the same department number.
Example II
The following statement defines and disables a CHECK constraint on the EMP table:
ALTER TABLE emp
ADD (CONSTRAINT check_comp CHECK (sal + comm <= 5000) )
DISABLE CONSTRAINT check_comp
The constraint CHECK_COMP ensures that no employee's total compensation exceeds $5000. Since the constraint is disabled, you can increase an employee's compensation above this limit.
Example III
Consider a referential integrity constraint involving a foreign key on the combination of the AREACO and PHONENO columns of the PHONE_CALLS table. The foreign key references a unique key on the combination of the AREACO and PHONENO columns of the CUSTOMERS table. The following statement disables the unique key on the combination of the AREACO and PHONENO columns of the CUSTOMERS table:
ALTER TABLE customers DISABLE UNIQUE (areaco, phoneno) CASCADE
Since the unique key in the CUSTOMERS table is referenced by the foreign key in the PHONE_CALLS table, you must use the CASCADE option to disable the unique key. This option disables the foreign key as well.
Example IV
The following statement disables all triggers associated with the EMP table:
ALTER TABLE emp
DISABLE ALL TRIGGERS
PRIMARY KEY
drops the table's PRIMARY KEY constraint.
UNIQUE
drops the UNIQUE constraint on the specified columns.
CONSTRAINT
drops the integrity constraint named constraint.
CASCADE
drops all other integrity constraints that depend on the dropped integrity constraint.
You cannot drop a unique or primary key that is part of a referential integrity constraint without also dropping the foreign key. You can drop the referenced key and the foreign key together by specifying the referenced key with the CASCADE option in the DROP clause.
Example I
The following statement drops the primary key of the DEPT table:
ALTER TABLE dept
DROP PRIMARY KEY CASCADE
If you know that the name of the PRIMARY KEY constraint is PK_DEPT, you could also drop it with the following statement:
ALTER TABLE dept
DROP CONSTRAINT pk_dept CASCADE
The CASCADE option drops any foreign keys that reference the primary key.
Example II
The following statement drops the unique key on the DNAME column of the DEPT table:
ALTER TABLE dept
DROP UNIQUE (dname)
Note that the DROP clause in this example omits the CASCADE option. Because of this omission, Oracle7 does not drop the unique key if any foreign key references it.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the cluster's creation label or you must satisfy one of the following criteria:
schema
is the schema containing the cluster. If you omit schema, Oracle7 assumes the cluster is in your own schema.
cluster
is the name of the cluster to be dropped.
INCLUDING TABLES
drops all tables that belong to the cluster. If you omit this clause, and the cluster still contains tables, Oracle7 returns an error and does not drop the cluster.
CASCADE CONSTRAINTS
drops all referential integrity constraints from tables outside the cluster that refer to primary and unique keys in the tables of the cluster. If you omit this option and such referential integrity constraints exist, Oracle7 returns an error message and does not drop the cluster.
You cannot un-cluster an individual table. To create an un-clustered table identical to an existing clustered table, follow the following steps:
Example
DROP CLUSTER geography
INCLUDING TABLES
CASCADE CONSTRAINTS
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the database link's creation label or you must satisfy one of the following criteria:
PUBLIC
must be specified to drop a PUBLIC database link.
dblink
specifies the database link to be dropped.
Example
The following statement drops a private database link named BOSTON:
DROP DATABASE LINK boston
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the function's creation label or you must satisfy one of the following criteria:
schema
is the schema containing the function. If you omit schema, Oracle7 assumes the function is in your own schema.
function
is the name of the function to be dropped.
You can only use this command to drop a stand-alone function. To remove a function that is part of a package, use one of the following methods:
The following statement drops the function NEW_ACCT in the schema RIDDLEY:
DROP FUNCTION riddley.new_acct
When you drop the NEW_ACCT function, Oracle7 invalidates all objects that depend upon NEW_ACCT.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the index's creation label or you must satisfy one of the following criteria:
schema
is the schema containing the index. If you omit schema, Oracle7 assumes the index is in your own schema.
index
is the name of the index to be dropped.
Example
This command drops an index named MONOLITH:
DROP INDEX monolith
CREATE INDEX command on 4 - 192
CREATE TABLE command on 4 - 245
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the cluster's creation label or you must satisfy one of the following criteria:
BODY
schema
is the schema containing the package. If you omit schema, Oracle7 assumes the package is in your own schema.
package
is the name of the package to be dropped.
When you drop only the body of a package but not its specification, Oracle7 does not invalidate dependent objects. However, you cannot call one of the procedures or stored functions declared in the package specification until you recreate the package body.
The DROP PACKAGE command drops the package and all its objects together. To remove a single object from a package, you can recreate the package without the object using the CREATE PACKAGE and CREATE PACKAGE BODY commands with the OR REPLACE option.
Example
DROP PACKAGE banking
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the cluster's creation label or you must satisfy one of the following criteria:
schema
is the schema containing the procedure. If you omit schema, Oracle7 assumes the procedure is in your own schema.
procedure
is the name of the procedure to be dropped.
For information on how Oracle7 maintains dependencies among schema objects, including remote objects, see the "Dependencies Among Schema Objects" chapter of Oracle7 Server Concepts.
You can only use this command to drop a stand-alone procedure. To remove a procedure that is part of a package, use one of the following methods:
The following statement drops the procedure TRANSFER owned by the user KERNER:
DROP PROCEDURE kerner.transfer
When you drop the TRANSFER procedure, Oracle7 invalidates all objects that depend upon TRANSFER.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the profile's creation label or you must satisfy one of the following criteria:
profile
is the name of the profile to be dropped.
CASCADE
de-assigns the profile from any users to whom it is assigned. Oracle7 automatically assigns the DEFAULT profile to such users. You must specify this option to drop a profile that is currently assigned to users.
Example
The following statement drops the profile ENGINEER:
DROP PROFILE engineer
CASCADE
Oracle7 assigns the DEFAULT profile to any users currently assigned the ENGINEER profile.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the role's creation label or you must satisfy one of the following criteria:
role
is the role to be dropped.
Example
To drop the role FLORIST, issue the following statement:
DROP ROLE florist
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the rollback segment's creation label or you must satisfy one of the following criteria:
rollback_segment
is the name the rollback segment to be dropped.
You can only drop a rollback segment that is offline. To determine whether a rollback segment is offline, query the data dictionary view DBA_ROLLBACK_SEGS. Offline rollback segments have the value 'AVAILABLE' in the STATUS column. You can take a rollback segment offline with the OFFLINE option of the ALTER ROLLBACK SEGMENT command.
You cannot drop the SYSTEM rollback segment.
Example
The following statement drops the rollback segment ACCOUNTING:
DROP ROLLBACK SEGMENT accounting
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the sequence's creation label or you must satisfy one of the following criteria:
schema
is the schema containing the sequence. If you omit schema, Oracle7 assumes the sequence is in your own schema.
sequence
is the name of the sequence to be dropped.
The following statement drops the sequence ESEQ owned by the user ELLY:
DROP SEQUENCE elly.eseq
To issue the above statement, you must either be connected as the user ELLY or have DROP ANY SEQUENCE system privilege.
schema
is the schema containing the snapshot. If you omit schema, Oracle7 assumes the snapshot is in your own schema.
snapshot
is the name of the snapshot to be dropped.
When you drop a master table, Oracle7 does not automatically drop snapshots based on the table. However, Oracle7 returns an error message when it tries to refresh a snapshot based on a master table that has been dropped.
Example
The following statement drops the snapshot PARTS owned by the user HQ:
DROP SNAPSHOT hq.parts
schema
is the schema containing the snapshot log and its master table. If you omit schema, Oracle7 assumes the snapshot log and master table are in your own schema.
table
is the name of the master table associated with the snapshot log to be dropped.
Example
The following statement drops the snapshot log on the PARTS master table:
DROP SNAPSHOT LOG ON parts
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the synonym's creation label or you must satisfy one of the following criteria:
PUBLIC
must be specified to drop a public synonym. You cannot specify schema if you have specified PUBLIC.
schema
is the schema containing the synonym. If you omit schema, Oracle7 assumes the synonym is in your own schema.
synonym
is the name of the synonym to be dropped.
Example
To drop a synonym named MARKET, issue the following statement:
DROP SYNONYM market
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the table's creation label or you must satisfy one of the following criteria:
schema
is the schema containing the table. If you omit schema, Oracle7 assumes the table is in your own schema.
table
is the name of the table to be dropped.
CASCADE CONSTRAINTS
drops all referential integrity constraints that refer to primary and unique keys in the dropped table. If you omit this option, and such referential integrity constraints exist, Oracle7 returns an error message and does not drop the table.
Example
The following statement drops the TEST_DATA table:
DROP TABLE test_data
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the tablespace's creation label or you must satisfy one of the following criteria:
tablespace Keywords and Parameters
is the name of the tablespace to be dropped.
drops all the contents of the tablespace
CASCADE CONSTRAINTS
drops all referential integrity constraints from tables outside the tablespace that refer to primary and unique keys in the tables of the tablespace. If you omit this option and such referential integrity constraints exist, Oracle7 returns an error message and does not drop the tablespace.
You may want to alter any users who have been assigned the tablespace as either a default or temporary tablespace. After the tablespace has been dropped, these users cannot allocate space for objects or sort areas in the tablespace. You can reassign users new default and temporary tablespaces with the ALTER USER command.
You cannot drop the SYSTEM tablespace.
Example
The following statement drops the MFRG tablespace and all its contents:
DROP TABLESPACE mfrg
INCLUDING CONTENTS
CASCADE CONSTRAINTS
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the trigger's creation label or you must satisfy one of the following criteria:
schema
is the schema containing the trigger. If you omit schema, Oracle7 assumes the trigger is in your own schema.
trigger
is the name of the trigger to be dropped.
Example
The following statement drops the REORDER trigger in the schema RUTH:
DROP TRIGGER ruth.reorder
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the user's creation label or you must satisfy one of the following criteria:
user
is the user to be dropped.
drops all objects in the user's schema before dropping the user. You must specify this option to drop a user whose schema contains any objects.
Example I
If BRADLEY's schema contains no objects, you can drop BRADLEY by issuing the statement:
DROP USER bradley
Example II
If BRADLEY's schema contains objects, you must use the CASCADE option to drop BRADLEY and the objects:
DROP USER bradley CASCADE
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the view's creation label or you must satisfy one of the following criteria:
schema
is the schema containing the view. If you omit schema, Oracle7 assumes the view is in your own schema.
view
is the name of the view to be dropped.
You can change the definition of a view by dropping and recreating it.
Example
The following statement drops the VIEW_DATA view:
DROP VIEW view_data
If you enable a UNIQUE or PRIMARY KEY constraint, Oracle7 creates an index on the columns of the unique or primary key in the schema containing the table. To enable such a constraint, you must have the privileges necessary to create the index. For information on these privileges, see the CREATE INDEX command .
If you enable a referential integrity constraint, the referenced UNIQUE or PRIMARY KEY constraint must already be enabled.
For an integrity constraint to appear in an ENABLE clause, one of the following conditions must be true:
UNIQUE
enables the UNIQUE constraint defined on the specified column or combination of columns.
PRIMARY KEY
enables the table's PRIMARY KEY constraint.
CONSTRAINT
enables the integrity constraint named constraint.
USING INDEX
specifies parameters for the index Oracle7 creates to enforce a UNIQUE or PRIMARY KEY constraint. Oracle7 gives the index the same name as the constraint. You can choose the values of the INITRANS, MAXTRANS, TABLESPACE, STORAGE, and PCTFREE parameters for the index. For information on these parameters, see the CREATE TABLE command .
Only use these parameters when enabling UNIQUE and PRIMARY KEY constraints.
EXCEPTIONS INTO
identifies a table into which Oracle7 places information about rows that violate the integrity constraint. The table must exist before you use this option. If you omit
schema, Oracle7 assumes the exception table is in your own schema. The exception table must be on your local database.
ALL TRIGGERS
You can also enable an integrity constraint by including the ENABLE keyword in CONSTRAINT clause that defines the integrity constraint. For information on this keyword, see the CONSTRAINT clause .
If you define an integrity constraint and do not explicitly enable or disable it, Oracle7 enables it by default.
How Oracle7 Enforces Integrity Constraints When you attempt to enable an integrity constraint, Oracle7 applies the integrity constraint to any existing rows in the table:
To specify an exception table in an ENABLE clause, you must have the privileges necessary to insert rows into the table. For information on these privileges, see the INSERT command . To examine the identified exceptions, you must have the privileges necessary to query the exceptions table. For information on these privileges, see the SELECT command .
If a CREATE TABLE statement contains both the AS clause and an ENABLE clause with the EXCEPTIONS option, Oracle7 ignores the EXCEPTIONS option. If there are any exceptions, Oracle7 does not create the table and returns an error message.
Example I
The following statement creates the DEPT table and defines and enables a PRIMARY KEY constraint:
CREATE TABLE dept
(deptno NUMBER(2) PRIMARY KEY,
dname VARCHAR2(10),
loc VARCHAR2(9) )
TABLESPACE user_a
ENABLE PRIMARY KEY USING INDEX INITRANS 3
STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 2 MAXEXTENTS 10)
TABLESPACE user_b PCTFREE 5
Oracle7 enforces the PRIMARY KEY constraint with an index. The ENABLE clause specifies INITRANS, STORAGE parameters, TABLESPACE, and PCTFREE values for the data blocks of the index.
Example II
The following statement enables an integrity constraint named FK_DEPTNO in the EMP table:
ALTER TABLE emp
ENABLE CONSTRAINT fk_deptno
EXCEPTIONS INTO except_table
Each row of the EMP table must satisfy the constraint for Oracle7 to enable the constraint. If any row violates the constraint, the constraint remains disabled. Oracle7 lists any exceptions in the table EXCEPT_TABLE. You can query this table with the following statement:
SELECT *
FROM except_table
The output of this query might look like this:
ROW_ID OWNER TABLE_NAME CONSTRAINT
------------------ ----- ---------- ---------- 0000346A.0001.0003 SCOTT EMP FK_DEPTNO
You can also identify the exceptions in the EMP table with the following statement:
SELECT emp.*
FROM emp, except_table
WHERE emp.row_id except_table.row_id
AND except_table.table_name = 'EMP'
AND except_table.constraint = 'FK_DEPTNO'
If there are exceptions to the FK_DEPTNO constraint, the output of this query might look like this:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- -------- ------ -------- ---------- -------- -------- ------- 8001 JACK CLERK 7788 25-AUG-92 1100 70
Example III
The following statement tries to enable two constraints on the EMP table:
ALTER TABLE emp
ENABLE UNIQUE (ename)
ENABLE CONSTRAINT nn_ename
The preceding statement has two ENABLE clauses:
Example IV
The following statement enables all triggers associated with the EMP table:
ALTER TABLE emp
ENABLE ALL TRIGGERS
FOR :host_integer
statement_name block_name
identifies the SQL statement or PL/SQL block to be executed. The SQL statement can only be a DELETE, INSERT, or UPDATE statement. You must use the embedded SQL PREPARE command to associate this identifier with the statement.
USING
specifies a list of host variables with optional indicator variables that Oracle7 substitutes as input variables into the statement to be executed. The host and indicator variables must be either all scalars or all arrays.
Example
This example illustrates the use of the EXECUTE statement in a Pro*C embedded SQL program:
EXEC SQL PREPARE my_statement
FROM :my_string;
EXEC SQL EXECUTE my_statement
USING :my_var;
AT
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, the PL/SQL block is executed on your default database.
pl/sql_block
For information on PL/SQL, including how to write PL/SQL blocks, see PL/SQL User's Guide and Reference.
END-EXEC
must appear after the embedded PL/SQL block, regardless of which programming language your Oracle Precompiler program uses. Of course, the keyword END-EXEC must be followed by the embedded SQL statement terminator for the specific language.
Example
EXEC SQL EXECUTE
BEGIN
SELECT ename, job, sal
INTO :emp_name:ind_name, :job_title, :salary
FROM emp
WHERE empno = :emp_number;
IF :emp_name:ind_name IS NULL
THEN RAISE name_missing;
END IF;
END;
END-EXEC
AT
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, the statement or block is executed on your default database.
:host_string
is a host variable whose value is the SQL statement or PL/SQL block to be executed.
'text'
is a quoted text literal containing the SQL statement or PL/SQL block to be executed.
The SQL statement can only be a DELETE, INSERT, or UPDATE statement.
For more information on this command, see Programmer's Guide to the Oracle Precompilers.
Example
This example illustrates the use of the EXECUTE IMMEDIATE statement:
EXEC SQL EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = 9460'
You must also have the privileges necessary to execute the SQL statement for which you are determining the execution plan. If the SQL statement accesses a view, you must have privileges to access any tables and views on which the view is based. If the view is based on another view that is based on a table, you must have privileges to access both the other view and its underlying table.
To examine the execution plan produced by an EXPLAIN PLAN statement, you must have the privileges necessary to query the output table. For more information on these privileges, see the SELECT command .
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the output table's creation label or you must satisfy one of the following criteria:
SET
specifies the schema, name, and database containing the output table. This table must exist before you use the EXPLAIN PLAN command. If you omit schema, Oracle7 assumes the table is in your own schema.
The dblink can be a complete or partial name of a database link to a remote Oracle7 database where the output table is located. For information on referring to database links, see the section, "Referring to Objects in Remote Objects," . You can only specify a remote output table if you are using Oracle7 with the distributed option. If you omit dblink, Oracle7 assumes the table is on your local database.
If you omit the INTO clause altogether, Oracle7 assumes an output table named PLAN_TABLE in your own schema on your local database.
FOR
specifies a SELECT, INSERT, UPDATE, or DELETE statement for which the execution plan is generated.
The value you specify in the SET clause appears in the STATEMENT_ID column in the rows of the execution plan. You can then use this value to identify these rows among others in the output table. Be sure to specify a STATEMENT_ID value if your output table contains rows from many execution plans.
Since the EXPLAIN PLAN command is a Data Manipulation Language command, rather than a Data Definition Language command, Oracle7 does not implicitly commit the changes made by an EXPLAIN PLAN statement. If you want to keep the rows generated by an EXPLAIN PLAN statement in the output table, you must commit the transaction containing the statement.
You should not use the EXPLAIN PLAN command to determine the execution plans of SQL statements that access data dictionary views or dynamic performance tables.
You can also issue the EXPLAIN PLAN command as part of the SQL trace facility. For information on how to use the SQL trace facility and how to interpret execution plans, see Appendix A "Performance Diagnostic Tools" of Oracle7 Server Tuning.
Example
EXPLAIN PLAN
SET STATEMENT_ID = 'Raise in Chicago'
INTO output
FOR UPDATE emp
SET sal = sal * 1.10
WHERE deptno = (SELECT deptno FROM dept WHERE loc = 'CHICAGO')
This SELECT statement queries the OUTPUT table and returns the execution plan and the cost:
SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options,
object_name, position
FROM output
START WITH id = 0 AND statement_id = 'Raise in Chicago'
CONNECT BY PRIOR id = parent_id AND
statement_id = 'Raise in Chicago'
The query returns this execution plan:
OPERATION OPTIONS OBJECT_NAME POSITION ---------------------------------------------------------- UPDATE STATEMENT 1 FILTER 0 TABLE ACCESS FULL EMP 1 TABLE ACCESS FULL DEPT 2
The value in the POSITION column of the first row shows that the statement has a cost of 1.
FOR :host_integer
limits the number of rows fetched if you are using array host variables. If you omit this clause, Oracle7 fetches enough rows to fill the smallest array.
cursor
is a cursor that has been declared by a DECLARE CURSOR statement. The FETCH statement returns one of the rows selected by the query associated with the cursor.
INTO
USING
The number of rows retrieved is specified by the size of the output host variables and the value specified in the FOR clause. The host variables to receive the data must be either all scalars or all arrays. If they are scalars, Oracle7 fetches only one row. If they are arrays, Oracle7 fetches enough rows to fill the arrays.
Array host variables can have different sizes. In this case, the number of rows Oracle7 fetches is determined by the smaller of the following values:
If a FETCH statement does not retrieve all rows returned by the query, the cursor is positioned on the next returned row. When the last row returned by the query has been retrieved, the next FETCH statement results in an error code returned in the SQLCODE element of the SQLCA.
Note that the FETCH command does not contain an AT clause. You must specify the database accessed by the cursor in the DECLARE CURSOR statement.
You can only move forward through the active set with FETCH statements. If you want to revisit any of the previously fetched rows, you must reopen the cursor and fetch each row in turn. If you want to change the active set, you must assign new values to the input host variables in the cursor's query and reopen the cursor.
Example
This example illustrates the FETCH command in a pseudo-code embedded SQL program:
EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT job, sal FROM emp WHERE deptno = 30;
...
EXEC SQL WHENEVER NOT FOUND GOTO ...
LOOP
EXEC SQL FETCH emp_cursor INTO :job_title1, :salary1;
EXEC SQL FETCH emp_cursor INTO :job_title2, :salary2;
...
END LOOP;
...
'filename'
is the name of either a data file or a redo log file member. 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
K specifies the size in kilobytes.
M specifies the size in megabytes.
If you omit K and M, the size is specified in bytes.
REUSE
The REUSE option is only significant when used with the SIZE option. If you omit the SIZE option, Oracle7 expects the file to exist already. Note that whenever Oracle7 uses an existing file, the file's previous contents are lost.
Example I
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 data file named 'DISKC:DBONE.DAT' of size 30 megabytes.
Since all of these filespecs specify a value for the SIZE parameter and omit the REUSE option, these files must not already exist. Oracle7 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, Oracle7 creates it with that size.
Example III
The following statement creates a tablespace named STOCKS that has three data files:
CREATE TABLESPACE stocks
DATAFILE 'diskc:stock1.dat',
'diskc:stock2.dat',
'diskc:stock3.dat'
The filespecs for the data files specifies files named 'DISKC:STOCK1.DAT', 'DISKC:STOCK2.DAT', '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 data file:
ALTER TABLESPACE stocks
ADD DATAFILE 'diskc:stock4.dat' REUSE
The filespec specifies a data file named 'DISKC:STOCK4.DAT'. Since the filespec omits the SIZE parameter, the file must already exist and the REUSE option is not significant.
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 have created the role.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate both the label at which the system privilege or role was granted to you and the creation label of the grantee user or role.
system_priv
is a system privilege to be granted.
role
is a role to be granted.
identifies users or roles to which system privileges and roles are granted.
PUBLIC grants system privileges or roles to all users.
WITH ADMIN OPTION
If you grant a privilege to a user: Oracle7 adds the privilege to the user's privilege domain. The user can immediately exercise the privilege.
If you grant a privilege to a role: Oracle7 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: Oracle7 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: Oracle7 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: Oracle7 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: Oracle7 makes the role available to all users. All users can immediately enable the role and exercise the privileges in the roles privilege domain.
A privilege or role cannot appear more than once in the list of privileges and roles to be granted. A user, role, or PUBLIC cannot appear more than once in the TO clause.
You cannot grant roles circularly. For example, if you grant the role BANKER to the role TELLER, you cannot subsequently grant TELLER to BANKER. Also, you cannot grant a role to itself.
Role | System Privileges and Roles Granted |
CONNECT | ALTER SESSION CREATE CLUSTER CREATE DATABASE LINK CREATE SEQUENCE CREATE SESSION CREATE SYNONYM CREATE TABLE CREATE VIEW |
RESOURCE | CREATE CLUSTER CREATE PROCEDURE CREATE SEQUENCE CREATE TABLE CREATE TRIGGER |
DBA | All systems privileges WITH ADMIN OPTION EXP_FULL_DATABASE role IMP_FULL_DATABASE role |
EXP_FULL_DATABASE | SELECT ANY TABLE BACKUP ANY TABLE INSERT, UPDATE, DELETE ON sys.incexp sys.incvid sys.incfil |
IMP_FULL_DATABASE | BECOME USER WRITEDOWN (in Trusted Oracle7) |
Table 4 - 12. Roles defined by Oracle7 | |
The CONNECT, RESOURCE, and DBA are provided for compatibility with previous versions of Oracle7. The SQL script SQL.BSQ creates these roles, grants privileges to them, and grants the DBA role with ADMIN OPTION to the users SYS and SYSTEM. This script is available on your distribution media, although its exact name and location may vary depending on your operating system. It is recommended that you to design your own roles for database security, rather than rely on these roles. These roles may not be automatically created by future versions of Oracle7.
The EXP_FULL_DATABASE and IMP_FULL_DATABASE roles are provided for convenience in using the Import and Export utilities. The SQL script CATEXP.SQL creates these roles, grants privileges to them, and grants them to the DBA role. This script is available on your distribution media, although its exact name and location may vary depending on your operating system.
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.
Example I
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:
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the label at which the object privilege was granted to you and the creation label of the grantee user or role.
object_priv
is an object privilege to be granted. You can substitute any of the following values:
ALL PRIVILEGES
column
specifies a table or view column on which privileges are granted. You can only specify columns 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. If you do not qualify object with schema, Oracle7 assumes the object is in your own schema. The object can be one of the following 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.
If you grant a privilege to a user: Oracle7 adds the privilege to the user's privilege domain. The user can immediately exercise the privilege.
If you grant a privilege to a role: Oracle7 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: Oracle7 adds the privilege to the privilege domain of each user. All users can immediately exercise the privilege.
A privilege cannot appear more than once in the list of privileges to be granted. A user or role cannot appear more than once in the TO clause.
Object Privilege | Tables | Views | Sequences | Procedure Functions Packages | Snapshots |
ALTER | 3 | 3 | |||
DELETE | 3 | 3 | |||
EXECUTE | 3 | ||||
INDEX | 3 | ||||
INSERT | 3 | 3 | |||
REFERENCES | 3 | ||||
SELECT | 3 | 3 | 3 | 3 | |
UPDATE | 3 | 3 | |||
ALTER
allows the grantee to change the table definition with the ALTER TABLE command.
DELETE
allows the grantee to remove rows from the table with the DELETE command.
INDEX
allows the grantee to create an index on the table with the CREATE INDEX command.
INSERT
allows the grantee to add new rows to the table with the INSERT command.
REFERENCES
SELECT
allows the grantee to query the table with the SELECT command.
UPDATE
allows the grantee to change data in the table with the UPDATE command.
Any one of above object privileges allows the grantee to lock the table in any lock mode with the LOCK TABLE command.
DELETE
allows the grantee to remove rows from the view with the DELETE command.
INSERT
allows the grantee to add new rows to the view with the INSERT command.
SELECT
allows the grantee to query the view with the SELECT command.
UPDATE
allows the grantee to change data in the view with the UPDATE command.
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.
ALTER
allows the grantee to change the sequence definition with the ALTER SEQUENCE command.
SELECT
EXECUTE
SELECT
allows the grantee to query the snapshot with the SELECT command.
Example I
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.
Example III
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 IV
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, since 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.
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.
The INSERT ANY TABLE system privilege also allows you to insert rows into any table or any view's base table.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the creation label of the table or view:
schema
is the schema containing the table or view. If you omit schema, Oracle7 assumes the table or view is in your own schema.
table view
is the name of the table into which rows are to be inserted. If you specify view, Oracle7 inserts rows into the view's base table.
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 the section "Referring to Objects" . You can only insert rows into a remote table or view if you are using Oracle7 with the distributed option.
If you omit dblink, Oracle7 assumes that the table or view is on the local database.
subquery_1
is a subquery that Oracle treats in the same manner as a view. For the syntax of subquery, see page 4 - 431.
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 of expr . You must specify a value in the VALUES clause for each column in the column list.
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. For the syntax description of subquery, see page 4 - 436.
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. If you are using Trusted Oracle7 in DBMS MAC mode and you omit a value for the ROWLABEL column, the new row is automatically labeled with your DBMS label.
Oracle7 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 the "Data Dictionary" chapter in Oracle7 Server Reference.
If you omit any columns from the column list, Oracle7 assigns them their default values as specified when the table was created. For more information on the default column values, see the CREATE TABLE command . If any of these columns has a NOT NULL constraint, then Oracle7 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.
You cannot insert rows into a view if the view's defining query contains one of the following constructs:
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, 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
INSERT INTO emp
VALUES (empseq.nextval, 'LEWIS', 'CLERK',
7902, SYSDATE, 1200, NULL, 20)
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.
The INSERT ANY TABLE system privilege also allows you to insert rows into any table or any view's base table.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the creation label of the table or view:
AT
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name
If you omit this clause, the INSERT statement is executed on your default database.
FOR :host_integer
schema
is the schema containing the table or view. If you omit schema, Oracle7 assumes the table or view is in your own schema.
table view
is the name of the table into which rows are to be inserted. If you specify view, Oracle7 inserts rows into the view's base table.
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 the section, "Referring to Objects in Remote Databases," . You can only insert rows into a remote table or view if you are using Oracle7 with the distributed option.
If you omit dblink, Oracle7 assumes that the table or view is on the local database.
subquery_1
is a subquery that Oracle treats in the same manner as a view. For the syntax of subquery, see page 4 - 431.
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 query.
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 of expr . Note that the expressions can be host variables with optional indicator variables. You must specify an expression in the VALUES clause for each column in the column list.
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. For the syntax description of subquery, see page 4 - 436.
Array host variables in the WHERE clause can have different sizes. In this case, the number of times Oracle7 executes the statement is determined by the smaller of the following values:
Example I
This example illustrates the use of the embedded SQL INSERT command:
EXEC SQL INSERT INTO emp (ename, empno, sal)
VALUES (:ename, :empno, :sal);
Example II
This example shows an embedded SQL INSERT command with a subquery:
EXEC SQL INSERT INTO new_emp (ename, empno, sal)
SELECT ename, empno, sal FROM emp
WHERE deptno = :deptno;
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of the table or view or you must have READUP system privilege.
schema
is the schema containing the table or view. If you omit schema, Oracle7 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, Oracle7 locks the view's base tables.
dblink
is a database link to a remote Oracle7 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 only lock tables and views on a remote database if you are using Oracle7 with the distributed option. All tables locked by a LOCK TABLE statement must be on the same database.
If you omit dblink, Oracle7 assumes the table or view is on the local database.
lockmode
is one of the following:
NOWAIT
If you omit this clause, Oracle7 waits until the table is available, locks it, and returns control to you.
Share locks allow concurrent queries but prohibit updates to the locked table.
Row Share locks allow concurrent access to the locked table. They prohibit users from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE.
Row Exclusive locks are the same as ROW SHARE locks, but also prohibit locking in SHARE mode. Row Exclusive locks are automatically obtained when updating, inserting, or deleting.
Share Row Exclusive locks are 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.
Share Update locks are synonymous with ROW SHARE and included for compatibility with earlier versions of the Oracle7 RDBMS.
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 the "Data Concurrency" chapter of Oracle7 Server 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 entirely or to a savepoint before you locked the table.
Example I
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
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the label at which the auditing option was set or you must satisfy one of the following criteria:
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 - 7 beginning and Table 4 - 8 .
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 - 7 .
BY
WHENEVER SUCCESSFUL
stops auditing only for SQL statements that complete successfully.
NOT
stops auditing only for statements that result in Oracle7 errors.
If you omit the WHENEVER clause entirely, Oracle7 stops auditing for all statements, regardless of success or failure.
Example I
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
Since the above statement only stops auditing SCOTT's queries, Oracle7 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 privileges chosen for auditing, issue the following statement:
NOAUDIT ALL
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the label at which the auditing option was set or you must satisfy one of the following criteria:
object_opt
stops auditing for particular operations on the object. For a list of these options, see Table 4 - 9 .
ON
identifies the object on which auditing is stopped. If you do not qualify object with schema, Oracle7 assumes the object is in your own schema.
WHENEVER SUCCESSFUL
stops auditing only for SQL statements that complete successfully.
NOT
option stops auditing only for statements that result in Oracle7 errors.
If you omit the WHENEVER clause entirely, Oracle7 stops auditing for all statements, regardless of success or failure.
Example
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
Since you only stopped auditing for successful queries, Oracle7 continues to audit queries resulting in Oracle7 errors.
cursor
is the cursor to be opened.
specifies the host variables to be substituted into the WHERE clause of the associated query.
:host_variable specifies a host variable with an optional indicator variable to be substituted into the statement associated with the cursor.
DESCRIPTOR
specifies a descriptor that describes the host variables to be substituted into the WHERE clause of the associated query. The descriptor must be initialized in a previous DESCRIBE statement.
The substitution is based on position. The host variable names specified in this statement can be different from the variable names in the associated query.
Once you have opened a cursor, its input host variables are not reexamined until you reopen the cursor. To change any input host variables and therefore the active set, you must reopen the cursor.
All cursors in a program are in a closed state when the program is initiated or when they have been explicitly closed using the CLOSE command.
You can reopen a cursor without first closing it. For more information on this command, see Programmer's Guide to the Oracle Precompilers.
Example
This example illustrates the use of the OPEN command in a Pro*C embedded SQL program:
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, empno, job, sal
FROM emp
WHERE deptno = :deptno;
EXEC SQL OPEN emp_cursor;
NOPARALLEL
specifies serial execution of an operation. This is the default.
PARALLEL
specifies parallel execution of an operation.
DEGREE
integer use integer query servers.
DEFAULT the number of query servers used is calculated from such things as 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 use integer instances
DEFAULT use all available instances
Note: INSTANCES only applies to an instance using the Oracle7 Parallel Server.
Used in a CREATE command, the PARALLEL clause causes the creation of the object to be parallelized; if the CREATE command is CREATE TABLE, the PARALLEL clause sets the default degree of parallelism for queries on the table after creation.
Used in a command to alter an object, the PARALLEL clause changes the default degree of parallelism for queries on the object. In an ALTER DATABASE RECOVER command, the PARALLEL clause causes the recovery to be parallelized.
You cannot use the PARALLEL clause in an ALTER INDEX command unless you specify the REBUILD clause.
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.
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
statement_name block_name
is the identifier to be associated with the prepared SQL statement or PL/SQL block. If this name has been previously assigned to another statement or block, the prior assignment is superseded.
:host_string
is a host variable whose value is the text of a SQL statement or PL/SQL block to be prepared.
'text'
is a string literal containing a SQL statement or PL/SQL block to be prepared.
A SQL statement is prepared only once, but can be executed any number of times.
Example
This example illustrates the use of a PREPARE statement in a Pro*C embedded SQL program:
EXEC SQL PREPARE my_statement FROM :my_string;
EXEC SQL EXECUTE my_statement
You must also have the OSDBA role enabled. You cannot be connected to Oracle7 through the multi-threaded server architecture. Your instance must have the database mounted in exclusive mode.
Note: It is recommended that you perform media recovery using Server Manager rather than using the ALTER DATABASE command with the RECOVER clause.
AUTOMATIC
FROM
STANDBY
recovers the standby database using the controlfile and archived redo log files copied over from the primary database. For more information, see the Oracle7 Server Administrator's Guide.
DATABASE
UNTIL CANCEL
UNTIL TIME
performs time-based recovery. This parameter recovers the database to the time specified by the date. The date must be a character literal in the format 'YYYY-MM-DD:HH24:MI:SS'.
UNTIL CHANGE
USING BACKUP CONTROLFILE
specifies that a backup control file is being used instead of the current control file.
TABLESPACE
recovers only the specified tablespaces. You can use this option if the database is open or closed, provided the tablespaces to be recovered are offline.
DATAFILE
LOGFILE
continues media recovery by applying the specified redo log file.
CONTINUE
continues multi-instance recovery after it has been interrupted to disable a thread.
CONTINUE DEFAULT
continues recovery by applying the redo log file that Oracle7 has automatically generated.
CANCEL
terminates cancel-based recovery.
PARALLEL
specifies degree of parallelism to use when recovering. See parallel_clause .
For most purposes, the RECOVER Server Manager command is easier to use than the ALTER DATABASE command. For information on this command, see Oracle Server Manager User's Guide.
For more information on media recovery, see the "Recovering a Database" chapter of Oracle7 Server Administrator's Guide.
You can use the ALTER DATABASE command with the RECOVER clause if you want to write your own specialized media recovery application using SQL.
Example I
The following statement performs complete recovery of the entire database:
ALTER DATABASE RECOVER AUTOMATIC DATABASE
Oracle7 automatically generates the names of redo log files to apply and prompts you with them. The following statement applies a suggested file:
ALTER DATABASE RECOVER CONTINUE DEFAULT
The following statement explicitly names a redo log file for Oracle7 to apply:
ALTER DATABASE RECOVER LOGFILE 'diska:arch0006.arc'
Example II
The following statement performs time-based recovery of the database:
ALTER DATABASE AUTOMATIC
RECOVER UNTIL TIME '1992-10-27:14:00:00'
Oracle7 recovers the database until 2:00pm on October 27, 1992.
Example III
The following statement recovers the tablespace USER5:
ALTER DATABASE
RECOVER TABLESPACE user5
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the object's creation label or you must satisfy one of the following criteria:
old
is the current name of an existing table, view, sequence, or private synonym.
new
is the new name to be given to the existing object.
Integrity constraints, indexes, and grants on the old object are automatically transferred to the new object. Oracle7 invalidates all objects that depend on the renamed object, such as views, synonyms, and stored procedures and functions that refer to a renamed table.
You cannot use this command to rename public synonyms. To rename a public synonym, you must first drop it with the DROP SYNONYM command and then create another public synonym with the new name using the CREATE SYNONYM command.
You cannot use this command to rename columns. You can rename a column using the CREATE TABLE command with the AS clause. This example recreates the table STATIC, renaming a column from OLDNAME to NEWNAME:
CREATE TABLE temporary (newname, col2, col3)
AS SELECT oldname, col2, col3 FROM static
DROP TABLE static
RENAME temporary TO static
Example
To change the name of table DEPT to EMP_DEPT:
RENAME dept TO emp_dept
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the label at which the system privilege or role was granted or you must satisfy one of the following criteria:
system_priv
is a system privilege to be revoked. For a list of the system privileges, see Table 4 - 11 .
role
is a role to be revoked. For a list of the roles predefined by Oracle7, see Table 4 - 12 .
FROM
identifies users and roles from which the system privileges or roles are revoked.
PUBLIC revokes the system privilege or role from all users.
If you revoke a privilege from a user: Oracle7 removes the privilege from the user's privilege domain. Effective immediately, the user cannot exercise the privilege.
If you revoke a privilege from a role: Oracle7 removes the privilege from the role's privilege domain. Effective immediately, users with the role enabled cannot exercise the privilege. Also, other users who have been granted the role and subsequently enable the role cannot exercise the privilege.
If you revoke a privilege from PUBLIC: Oracle7 removes the privilege from the privilege domain of each user who has been granted the privilege through PUBLIC. Effective immediately, such users can no longer exercise the privilege. Note that the privilege is not revoked from users who have been granted the privilege directly or through roles.
If you revoke a role from a user: Oracle7 makes the role unavailable to the user. If the role is currently enabled for the user, the user can continue to exercise the privileges in the role's privilege domain as long as it remains enabled. However, the user cannot subsequently enable the role.
If you revoke a role from another role: Oracle7 removes the revoked role's privilege domain from the revokee role's privilege domain. Users who have been granted and have enabled the revokee role can continue to exercise the privileges in the revoked role's privilege domain as long as the revokee role remains enabled. However, other users who have been granted the revokee role and subsequently enable it cannot exercise the privileges in the privilege domain of the revoked role.
If you revoke a role from PUBLIC: Oracle7 makes the role unavailable to all users who have been granted the role through PUBLIC. Any user who has enabled the role can continue to exercise the privileges in its privilege domain as long as it remains enabled. However, users cannot subsequently enable the role. Note that the role is not revoked from users who have been granted the privilege directly or through other roles.
The REVOKE command can only revoke privileges and roles that have been granted directly with a GRANT statement. The REVOKE command cannot perform the following operations:
Example I
The following statement revokes DROP ANY TABLE system privilege from the users BILL and MARY:
REVOKE DROP ANY TABLE
FROM bill, mary
BILL and MARY can no longer drop tables in schemas other than their own.
Example II
The following statement revokes the role CONTROLLER from the user HANSON:
REVOKE controller
FROM hanson
HANSON can no longer enable the CONTROLLER role.
Example III
The following statement revokes the CREATE TABLESPACE system privilege from the CONTROLLER role:
REVOKE CREATE TABLESPACE
FROM controller
Enabling the CONTROLLER role no longer allows users to create tablespaces.
Example IV
To revoke the role VP from the role CEO, issue the following statement:
REVOKE vp
FROM ceo
VP is no longer granted to CEO.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the label at which you granted the object privilege or you must satisfy one of the following criteria:
object_priv
is an object privilege to be revoked. You can substitute any of the following values:
ALL PRIVILEGES
revokes all object privileges that you have granted to the revokee.
ON
If you do not qualify object with schema, Oracle7 assumes the object is in your own schema.
FROM
identifies users and roles from which the object privileges are revoked.
PUBLIC revokes object privileges from all users.
CASCADE CONSTRAINTS
drops any referential integrity constraints that the revokee has defined using REFERENCES privilege that you are now revoking. You must specify this option along with the REFERENCES privilege or the ALL PRIVILEGES option if the revokee has exercised the REFERENCES privilege to define a referential integrity constraint.
If you revoke a privilege from a user: Oracle7 removes the privilege from the user's privilege domain. Effective immediately, the user cannot exercise the privilege.
If you revoke a privilege from a role: Oracle7 removes the privilege from the role's privilege domain. Effective immediately, users with the role enabled cannot exercise the privilege. Other users who have been granted the role cannot exercise the privilege after enabling the role.
If you revoke a privilege from PUBLIC: Oracle7 removes the privilege from the privilege domain of each user who has been granted the privilege through PUBLIC. Effective immediately, all such users are restricted from exercising the privilege. Note that the privilege is not revoked from users who have been granted the privilege directly or through roles.
You can only use the REVOKE command to revoke object privileges that you previously granted directly to the revokee. You cannot use the REVOKE command to perform the following operations:
GRANT ALL
ON bonus
TO pedro
To revoke DELETE privilege on BONUS from PEDRO, issue the following statement:
REVOKE DELETE
ON bonus
FROM pedro
To revoke the remaining privileges on BONUS that you granted to PEDRO, issue the following statement:
REVOKE ALL
ON bonus
FROM pedro
Example II
You can grant SELECT and UPDATE privileges on the view REPORTS to all users by granting the privileges to the role PUBLIC:
GRANT SELECT, UPDATE
ON reports
TO public
The following statement revokes UPDATE privilege on REPORTS from all users:
REVOKE UPDATE
ON reports
FROM public
Users can no longer update the REPORTS view, although users can still query it. However, if you have also granted UPDATE privilege on REPORTS to any users (either directly or through roles), these users retain the privilege.
Example III
You can grant the user BLAKE SELECT privilege on the ESEQ sequence in the schema ELLY with the following statement:
GRANT SELECT
ON elly.eseq
TO blake
To revoke SELECT privilege on ESEQ from BLAKE, issue the following statement:
REVOKE SELECT
ON elly.eseq
FROM blake
However, if the user ELLY has also granted SELECT privilege on ESEQ to BLAKE, BLAKE can still use ESEQ by virtue of ELLY's grant.
Example IV
You can grant BLAKE the privileges REFERENCES and UPDATE on the EMP table in the schema SCOTT with the following statement:
GRANT REFERENCES, UPDATE
ON scott.emp
TO blake
BLAKE can exercise the REFERENCES privilege to define a constraint in his own DEPENDENT table that refers to the EMP table in the schema SCOTT:
CREATE TABLE dependent
(dependno NUMBER,
dependname VARCHAR2(10),
employee NUMBER
CONSTRAINT in_emp REFERENCES scott.emp(ename) )
You can revoke REFERENCES privilege on SCOTT.EMP from BLAKE, by issuing the following statement that contains the CASCADE CONSTRAINTS option:
REVOKE REFERENCES
ON scott.emp
FROM blake
CASCADE CONSTRAINTS
Revoking BLAKE's REFERENCES privilege on SCOTT.EMP causes Oracle7 to drop the IN_EMP constraint because BLAKE required the privilege to define the constraint.
However, if BLAKE has also been granted REFERENCES privilege on SCOTT.EMP by a user other than you, Oracle7 does not drop the constraint. BLAKE still has the privilege necessary for the constraint by virtue of the other user's grant.
You can also use this command to manually undo the work done by an in-doubt distributed transaction.
To manually roll back an in-doubt distributed transaction that you originally committed, you must have FORCE TRANSACTION system privilege. To manually roll back an in-doubt distributed transaction originally committed by another user, you must have FORCE ANY TRANSACTION system privilege.
WORK
is optional and is provided for ANSI compatibility.
rolls back the current transaction to the specified savepoint. If you omit this clause, the ROLLBACK statement rolls back the entire transaction.
FORCE
manually rolls back an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING.
ROLLBACK statements with the FORCE clause are not supported in PL/SQL.
Using the ROLLBACK command without the TO SAVEPOINT clause performs the following operations:
Example I
The following statement rolls back your entire current transaction:
ROLLBACK
Example II
The following statement rolls back your current transaction to savepoint SP5:
ROLLBACK TO SAVEPOINT sp5
If there is a network failure during the commit process for a distributed transaction, the state of the transaction may be unknown, or in-doubt. After consultation with the administrators of the other databases involved in the transaction, you may decide to manually commit or roll back the transaction on your local database. You can manually roll back the transaction on your local database by issuing a ROLLBACK statement with the FORCE clause.
For more information on when to roll back in-doubt transactions, see Oracle7 Server Distributed Systems, Volume I.
You cannot manually roll back an in-doubt transaction to a savepoint.
A ROLLBACK statement with a FORCE clause only rolls back the specified transaction. Such a statement does not affect your current transaction.
Example III
The following statement manually rolls back an in-doubt distributed transaction:
ROLLBACK WORK
FORCE '25.32.87'
To manually roll back an in-doubt distributed transaction that you originally committed, you must have FORCE TRANSACTION system privilege. To manually roll back an in-doubt distributed transaction originally committed by another user, you must have FORCE ANY TRANSACTION system privilege.
AT
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, the statement is issued to your default database.
WORK
is optional and has no effect on ROLLBACK.
TO
rolls back the transaction to a previously declared savepoint.
RELEASE
releases all resources and disconnects you from the database.
FORCE
manually rolls back an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING.
Oracle7 automatically rolls back your current transaction if the program terminates abnormally.
The ROLLBACK command has no effect on the contents of the host variables or on the control flow of the program.
Example
This example illustrates the use of the embedded SQL ROLLBACK command:
EXEC SQL ROLLBACK TO SAVEPOINT point4
savepoint
is the name of the savepoint to be created.
Savepoints are useful in interactive programs, because you can create and name intermediate steps of a program. This allows you more control over longer, more complex programs. For example, you can use savepoints throughout a long complex series of updates, so that if you make an error, you need not resubmit every statement.
Savepoints are useful in application programs in a similar way. If a program contains several subprograms, you can create a savepoint before each subprogram begins. If a subprogram fails, it is easy to return the data to its state before the subprogram began and then re-execute the subprogram with revised parameters or perform a recovery action.
Savepoint names must be distinct within a given transaction. If you create a second savepoint with the same identifier as an earlier savepoint, the earlier savepoint is erased. After a savepoint has been created, you can either continue processing, commit your work, rollback the entire transaction, or rollback to the savepoint.
Example
UPDATE emp
SET sal = 2000
WHERE ename = 'BLAKE'
SAVEPOINT blake_sal
UPDATE emp
SET sal = 1500
WHERE ename = 'CLARK'
SAVEPOINT clark_sal
SELECT SUM(sal) FROM emp
ROLLBACK TO SAVEPOINT blake_sal
UPDATE emp
SET sal = 1300
WHERE ename = 'CLARK'
COMMIT
AT
identifies the database on which the savepoint is created. The database can be identified by either:
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, the savepoint is created on your default database.
savepoint
is the name of the savepoint to be created.
Example
This example illustrates the use of the embedded SQL SAVEPOINT command:
EXEC SQL SAVEPOINT save3
For you to select rows from the base tables of a view, the owner of the schema containing the view must have SELECT privilege on the base tables. Also, if the view is in a schema other than your own, you must have SELECT privilege on the view.
The SELECT ANY TABLE system privilege also allows you to select data from any table or any snapshot or any view's base table.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of each queried table, view, or snapshot or you must have READUP system privileges.
DISTINCT
ALL
returns all rows selected, including all copies of duplicates. The default is ALL.
*
selects all columns from all tables, views, or snapshots, listed in the FROM clause.
table.* view.* snapshot.*
selects all columns from the specified table, view, or snapshot. You can use the schema qualifier to select from a table, view, or snapshot in a schema other than your own.
If you are using Trusted Oracle, the * does not select the ROWLABEL column. To select this column, you must explicitly specify it in the select list.
expr
selects an expression. See the syntax description of expr . A column name in this list can only be qualified with schema if the table, view, or snapshot containing the column is qualified with schema in the FROM clause.
c_alias
provides a different name for the column expression and causes the alias to be used in the column heading. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the ORDER BY clause, but not other clauses in the query.
schema
is the schema containing the selected table, view, or snapshot. If you omit schema, Oracle7 assumes the table, view, or snapshot is in your own schema.
table view snapshot
is the name of a table, view, or snapshot from which data is selected.
dblink
is the complete or partial name for a database link to a remote database where the table, view, or snapshot is located. For more information on referring to database links, see the section "Referring to Objects in Remote Databases" . Note that this database need not be an Oracle7 database.
If you omit dblink, Oracle7 assumes that the table, view, or snapshot is on the local database.
subquery
is a subquery that is treated in the same manner as a view. For the syntax of subquery, see page 4 - 436. Oracle7 executes the subquery and then uses the resulting rows as a view in the FROM clause.
t_alias
provides a different name for the table, view, snapshot, or subquery for evaluating the query and is most often used in a correlated query. Other references to the table, view, or snapshot throughout the query must refer to the alias.
WHERE
restricts the rows selected to those for which the condition is TRUE. If you omit this clause, Oracle7 returns all rows from the tables, views, or snapshots in the FROM clause. See the syntax description of condition .
START WITH CONNECT BY
returns rows in a hierarchical order.
GROUP BY
groups the selected rows based on the value of expr for each row and returns a single row of summary information for each group.
HAVING
See the syntax description of expr and the syntax description of condition .
UNION UNION ALL INTERSECT MINUS
combines the rows returned by two SELECT statement using a set operation. To reference a column, you must use an alias to name the column. The FOR UPDATE clause cannot be used with these set operators.
ORDER BY
orders rows returned by the statement.
expr orders rows based on their value for expr. The expression is based on columns in the select list or columns in the tables, views, or snapshots in the FROM clause.
position orders rows based on their value for the expression in this position of the select list.
ASC DESC specifies either ascending or descending order. ASC is the default.
FOR UPDATE
OF Only lock the select rows for a particular table in a join.
NOWAIT
If two or more tables have some column names in common, you must qualify column names with names of tables. Otherwise, fully qualified column names are optional, although it is always better to explicitly qualify table and column references. Oracle7 often does less work with fully qualified table and column names.
You can use a column alias, c_alias, to label the preceding expression in the select list so that the column is displayed with a new heading. The alias effectively renames the select list item for the duration of the query. The alias can be used in the ORDER BY clause, but not other clauses in the query.
If you use the DISTINCT option to return only a single copy of duplicate rows, the total number of bytes in all select list expressions is limited to the size of a data block minus some overhead. This size is specified by the initialization parameter DB_BLOCK_SIZE.
You can use comments in a SELECT statement to pass instructions, or hints, to the Oracle7 optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, see Oracle7 Server Tuning.
Example I
The following statement selects rows from the employee table with the department number of 40:
SELECT *
FROM emp
WHERE deptno = 40
Example II
The following statement selects the name, job, salary and department number of all employees except salesmen from department number 30:
SELECT ename, job, sal, deptno
FROM emp
WHERE NOT (job = 'SALESMAN' AND deptno = 30)
Example III
The following statement selects from subqueries in the FROM clause and gives departments total employees and salaries as a percentage of all the departments:
SELECT a.deptno "Department", a.num_emp/b.total_count "%Employees", a.sal_sum/b.total_sal "%Salary" FROM (SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum FROM scott.emp GROUP BY deptno) a, (SELECT COUNT(*) total_count, SUM(sal) total_sal FROM scott.emp) b ;
START WITH
You can specify the root row(s) of the hierarchy using this clause.
CONNECT BY
You can specify the relationship between parent rows and child rows of the hierarchy using this clause.
WHERE
You can restrict the rows returned by the query without affecting other rows of the hierarchy using this clause.
Oracle7 uses the information from the above clause to form the hierarchy using the following steps:
PRIOR expr comparison_operator expr expr comparison_operator PRIOR expr
To find the children of a parent row, Oracle7 evaluates the PRIOR expression for the parent row and the other expression for each row in the table. Rows for which the condition is true are the children of the parent. The CONNECT BY clause can contain other conditions to further filter the rows selected by the query. The CONNECT BY clause cannot contain a subquery.
If the CONNECT BY clause results in a loop in the hierarchy, Oracle7 returns an error. A loop occurs if one row is both the parent (or grandparent or direct ancestor) and a child (or a grandchild or a direct descendent) of another row.
Example IV
CONNECT BY PRIOR empno = mgr
Example V
In the following CONNECT BY clause, the PRIOR operator applies only to the EMPNO value. To evaluate this condition, Oracle7 evaluates EMPNO values for the parent row and MGR, SAL, and COMM values for the child row:
CONNECT BY PRIOR empno = mgr AND sal > comm
To qualify as a child row, a row must have a MGR value equal to the EMPNO value of the parent row and it must have a SAL value greater than its COMM value.
The number of levels returned by a hierarchical query may be limited by available user memory.
Example VI
The following statement returns all employees in hierarchical order. The root row is defined to be the employee whose job is 'PRESIDENT'. The child rows of a parent row are defined to be those who have the employee number of the parent row as their manager number.
SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart, empno, mgr, job FROM emp START WITH job = 'PRESIDENT' CONNECT BY PRIOR empno = mgr ORG_CHART EMPNO MGR JOB ------------ ---------- ---------- --------- KING 7839 PRESIDENT JONES 7566 7839 MANAGER SCOTT 7788 7566 ANALYST ADAMS 7876 7788 CLERK FORD 7902 7566 ANALYST SMITH 7369 7902 CLERK BLAKE 7698 7839 MANAGER ALLEN 7499 7698 SALESMAN WARD 7521 7698 SALESMAN MARTIN 7654 7698 SALESMAN TURNER 7844 7698 SALESMAN JAMES 7900 7698 CLERK CLARK 7782 7839 MANAGER MILLER 7934 7782 CLERK
The following statement is similar to the previous one, except that it does not select employees with the job 'ANALYST'.
SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart, empno, mgr, job FROM emp WHERE job != 'ANALYST' START WITH job = 'PRESIDENT' CONNECT BY PRIOR empno = mgr ORG_CHART EMPNO MGR JOB ------------- ---------- ---------- --------- KING 7839 PRESIDENT JONES 7566 7839 MANAGER ADAMS 7876 7788 CLERK SMITH 7369 7902 CLERK BLAKE 7698 7839 MANAGER ALLEN 7499 7698 SALESMAN WARD 7521 7698 SALESMAN MARTIN 7654 7698 SALESMAN TURNER 7844 7698 SALESMAN JAMES 7900 7698 CLERK CLARK 7782 7839 MANAGER
Oracle7 does not return the analysts SCOTT and FORD, although it does return employees who are managed by SCOTT and FORD.
The following statement is similar to the first one, except that it uses the LEVEL pseudocolumn to select only the first two levels of the management hierarchy:
SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart, empno, mgr, job FROM emp START WITH job = 'PRESIDENT' CONNECT BY PRIOR empno = mgr AND LEVEL <= 2 ORG_CHART EMPNO MGR JOB ------------ ---------- ---------- --------- KING 7839 PRESIDENT JONES 7566 7839 MANAGER BLAKE 7698 7839 MANAGER CLARK 7782 7839 MANAGER
If a SELECT statement contains the GROUP BY clause, the select list can only contain the following types of expressions:
The total number of bytes in all expressions in the GROUP BY clause is limited to the size of a data block minus some overhead. This size is specified by the initialization parameter DB_BLOCK_SIZE.
Example VII
SELECT deptno, MIN(sal), MAX(sal) FROM emp GROUP BY deptno DEPTNO MIN(SAL) MAX(SAL) ---------- ---------- ---------- 10 10 5004 20 804 3004 30 954 2854
Example VIII
To return the minimum and maximum salaries for the clerks in each department, issue the following statement:
SELECT deptno, MIN(sal), MAX(sal) FROM emp WHERE job = 'CLERK' GROUP BY deptno DEPTNO MIN(SAL) MAX(SAL) ---------- ---------- ---------- 10 1304 1304 20 804 1104 30 954 954
Example IX
To return the minimum and maximum salaries for the clerks in each department whose lowest salary is below $1,000, issue the following statement:
SELECT deptno, MIN(sal), MAX(sal) FROM emp WHERE job = 'CLERK' GROUP BY deptno HAVING MIN(sal) < 1000 DEPTNO MIN(SAL) MAX(SAL) ---------- ---------- ---------- 20 804 1104 30 954 954
If more than two queries are combined with set operators, adjacent pairs of queries are evaluated from left to right. You can use parentheses to specify a different order of evaluation.
The total number of bytes in all select list expressions of a component query is limited to the size of a data block minus some overhead. The size of a data block is specified by the initialization parameter DB_BLOCK_SIZE.
You can specify multiple expressions in the ORDER BY clause. Oracle7 first sorts rows based on their values for the first expression. Rows with the same value for the first expression are then sorted based on their values for the second expression, and so on. Oracle7 sorts nulls following all others in ascending order and preceding all others in descending order.
Sorting by position is useful in the following cases:
The ORDER BY clause is subject to the following restrictions:
If you use the ORDER BY clause in a hierarchical query, Oracle7 uses the ORDER BY clause rather than the hierarchy to order the rows.
Example X
SELECT * FROM emp WHERE job = 'SALESMAN' ORDER BY comm DESC
Example XI
SELECT ename, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC
To select the same information as the previous SELECT and use the positional ORDER BY notation, issue the following statement:
SELECT ename, deptno, sal FROM emp ORDER BY 2 ASC, 3 DESC
The FOR UPDATE clause cannot be used with the following other constructs:
If a row selected for update is currently locked by another user, Oracle7 waits until the row is available, locks it, and then returns control to you. You can use the NOWAIT option to cause Oracle7 to terminate the statement without waiting if such a row is already locked.
Example XII
SELECT empno, sal, comm FROM emp, dept WHERE job = 'CLERK' AND emp.deptno = dept.deptno AND loc = 'NEW YORK' FOR UPDATE
Example XIII
SELECT empno, sal, comm FROM emp, dept WHERE job = 'CLERK' AND emp.deptno = dept.deptno AND loc = 'NEW YORK' FOR UPDATE OF emp
To execute a join of three or more tables, Oracle7 first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle7 continues this process until all tables are joined into the result. The optimizer determines the order in which Oracle7 joins tables based on the join conditions, indexes on the tables, and, in the case of the cost-based optimization approach, statistics for the tables.
In addition to join conditions, the WHERE clause of a join query can also contain other conditions that refer to columns of only one table. These conditions can further restrict the rows returned by the join query.
Example XIV
This equijoin returns the name and job of each employee and the number and name of the department in which the employee works:
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------- KING PRESIDENT 10 ACCOUNTING BLAKE MANAGER 30 SALES CLARK MANAGER 10 ACCOUNTING JONES MANAGER 20 RESEARCH FORD ANALYST 20 RESEARCH SMITH CLERK 20 RESEARCH ALLEN SALESMAN 30 SALES WARD SALESMAN 30 SALES MARTIN SALESMAN 30 SALES SCOTT ANALYST 20 RESEARCH TURNER SALESMAN 30 SALES ADAMS CLERK 20 RESEARCH JAMES CLERK 30 SALES MILLER CLERK 10 ACCOUNTING
You must use a join to return this data because employee names and jobs are stored in a different table than department names. Oracle7 combines rows of the two tables according to this join condition:
emp.deptno = dept.deptno
Example XV
The following equijoin returns the name, job, department number, and department name of all clerks:
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND job = 'CLERK' ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------- SMITH CLERK 20 RESEARCH ADAMS CLERK 20 RESEARCH JAMES CLERK 30 SALES MILLER CLERK 10 ACCOUNTING
This query is identical to Example XII except that it uses an additional WHERE clause condition to return only rows with a JOB value of 'CLERK':
job = 'CLERK'
Example XVI
This query uses a self join to returns the name of each employee along with the name of the employee's manager:
SELECT e1.ename||' works for '||e2.ename "Employees and their Managers" FROM emp e1, emp e2 WHERE e1.mgr = e2.empno Employees and their Managers ------------------------------- BLAKE works for KING CLARK works for KING JONES works for KING FORD works for JONES SMITH works for FORD ALLEN works for BLAKE WARD works for BLAKE MARTIN works for BLAKE SCOTT works for JONES TURNER works for BLAKE ADAMS works for SCOTT JAMES works for BLAKE MILLER works for CLARK
The join condition for this query uses the aliases E1 and E2 for the EMP table:
e1.mgr = e2.empno
This is the basic syntax of an outer join of two tables:
Outer join queries are subject to the following rules and restrictions:
In a query that performs outer joins of more than two pairs of tables, a single table can only be the NULL-generated table for one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C.
Example XVII
This query uses an outer join to extend the results of Example XII:
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------- CLARK MANAGER 10 ACCOUNTING KING PRESIDENT 10 ACCOUNTING MILLER CLERK 10 ACCOUNTING SMITH CLERK 20 RESEARCH ADAMS CLERK 20 RESEARCH FORD ANALYST 20 RESEARCH SCOTT ANALYST 20 RESEARCH JONES MANAGER 20 RESEARCH ALLEN SALESMAN 30 SALES BLAKE MANAGER 30 SALES MARTIN SALESMAN 30 SALES JAMES CLERK 30 SALES TURNER SALESMAN 30 SALES WARD SALESMAN 30 SALES 40 OPERATIONS
In this outer join, Oracle7 returns a row containing the OPERATIONS department even though no employees work in this department. Oracle7 returns NULL in the ENAME and JOB columns for this row. The join query in Example X only selects departments that have employees.
The following query uses an outer join to extend the results of Example XV:
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno AND job (+) = 'CLERK' ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------- MILLER CLERK 10 ACCOUNTING SMITH CLERK 20 RESEARCH ADAMS CLERK 20 RESEARCH JAMES CLERK 30 SALES 40 OPERATIONS
In this outer join, Oracle7 returns a row containing the OPERATIONS department even though no clerks work in this department. The (+) operator on the JOB column ensures that rows for which the JOB column is NULL are also returned. If this (+) were omitted, the row containing the OPERATIONS department would not be returned because its JOB value is not 'CLERK'.
Example XVIII
This example shows four outer join queries on the CUSTOMERS, ORDERS, LINEITEMS, and PARTS tables. These tables are shown here:
SELECT custno, custname FROM customers CUSTNO CUSTNAME ---------- -------------------- 1 Angelic Co. 2 Believable Co. 3 Cabels R Us
SELECT orderno, custno, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE" FROM orders ORDERNO CUSTNO ORDERDATE ---------- ---------- ----------- 9001 1 OCT-13-1993 9002 2 OCT-13-1993 9003 1 OCT-20-1993 9004 1 OCT-27-1993 9005 2 OCT-31-1993
SELECT orderno, lineno, partno, quantity FROM lineitems ORDERNO LINENO PARTNO QUANTITY ---------- ---------- ---------- ---------- 9001 1 101 15 9001 2 102 10 9002 1 101 25 9002 2 103 50 9003 1 101 15 9004 1 102 10 9004 2 103 20
SELECT partno, partname FROM parts PARTNO PARTNAME ------ -------- 101 X-Ray Screen 102 Yellow Bag 103 Zoot Suit
Note that the customer Cables R Us have placed no orders and that order number 9005 has no line items.
The following outer join returns all customers and the dates they placed orders. The (+) operator ensures that customers who placed no orders are also returned:
SELECT custname, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE"
FROM customers, orders
WHERE customers.custno = orders.custno (+)
CUSTNAME ORDERDATE
-------------------- --------------
Angelic Co. OCT-13-1993
Angelic Co. OCT-20-1993
Angelic Co. OCT-27-1993
Believable Co. OCT-13-1993
Believable Co. OCT-31-1993
Cables R Us
The following outer join builds on the result of the previous one by adding the LINEITEMS table to the FROM clause, columns from this table to the select list, and a join condition joining this table to the ORDERS table to the WHERE clause. This query joins the results of the previous query to the LINEITEMS table and returns all customers, the dates they placed orders, and the part number and quantity of each part they ordered. The first (+) operator serves the same purpose as in the previous query. The second (+) operator ensures that orders with no line items are also returned:
SELECT custname, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE", partno, quantity FROM customers, orders, lineitems WHERE customers.custno = orders.custno (+) AND orders.orderno = lineitems.orderno (+) CUSTNAME ORDERDATE PARTNO QUANTITY -------------------- -------------- ---------- ---------- Angelic Co. OCT-13-1993 101 15 Angelic Co. OCT-13-1993 102 10 Angelic Co. OCT-20-1993 101 15 Angelic Co. OCT-27-1993 102 10 Angelic Co. OCT-27-1993 103 20 Believable Co. OCT-13-1993 101 25 Believable Co. OCT-13-1993 103 50 Believable Co. OCT-31-1993 Cables R Us
The following outer join builds on the result of the previous one by adding the PARTS table to the FROM clause, the PARTNAME column from this table to the select list, and a join condition joining this table to the LINEITEMS table to the WHERE clause. This query joins the results of the previous query to the PARTS table to return all customers, the dates they placed orders, and the quantity and name of each part they ordered. The first two (+) operators serve the same purposes as in the previous query. The third (+) operator ensures that rows with NULL part numbers are also returned:
SELECT custname, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE", quantity, partname FROM customers, orders, lineitems, parts WHERE customers.custno = orders.custno (+) AND orders.orderno = lineitems.orderno (+) AND lineitems.partno = parts.partno (+) CUSTNAME ORDERDATE QUANTITY PARTNAME -------------------- -------------- ---------- ------------ Angelic Co. OCT-13-1993 15 X-Ray Screen Angelic Co. OCT-13-1993 10 Yellow Bag Angelic Co. OCT-20-1993 15 X-Ray Screen Angelic Co. OCT-27-1993 10 Yellow Bag Angelic Co. OCT-27-1993 20 Zoot Suit Believable Co. OCT-13-1993 25 X-Ray Screen Believable Co. OCT-13-1993 50 Zoot Suit Believable Co. OCT-31-1993 Cables R Us
This is the syntax for a subquery:
WITH READ ONLY
specifies that the subquery cannot be updated.
WITH CHECK OPTION
specifies that, if the subquery is used in place of a table in an INSERT, UPDATE, or DELETE statement, changes to that table that would produce rows excluded from the subquery are prohibited. In other words, the following statement: INSERT INTO (SELECT ename, deptno FROM Emp WHERE deptno < 10) VALUES ('Taylor', 20); would be legal, but INSERT INTO (SELECT ename, deptno FROM Emp WHERE deptno < 10 WITH CHECK OPTION) VALUES ('Taylor', 20); would be rejected.
Other keywords and parameters are as outlined after the SELECT syntax diagram in the beginning of this entry.
A subquery is evaluated once for the entire parent statement, in contrast to a correlated subquery which is evaluated once per row processed by the parent statement.
A subquery can itself contain a subquery. Oracle7 places no limit on the level of query nesting.
Example XIX
To determine who works in Taylor's department, issue the following statement:
SELECT ename, deptno FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'TAYLOR')
Example XX
To give all employees in the EMP table a ten percent raise if they have not already been issued a bonus (if they do not appear in the BONUS table), issue the following statement:
UPDATE emp SET sal = sal * 1.1 WHERE empno NOT IN (SELECT empno FROM bonus)
Example XXI
To create a duplicate of the DEPT table named NEWDEPT, issue the following statement:
CREATE TABLE newdept (deptno, dname, loc) AS SELECT deptno, dname, loc FROM dept
SELECT select_list FROM table1 t_alias1 WHERE expr operator (SELECT column_list FROM table2 t_alias2 WHERE t_alias1.column operator t_alias2.column) UPDATE table1 t_alias1 SET column = (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column = t_alias2.column) DELETE FROM table1 t_alias1 WHERE column operator (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column = t_alias2.column)
This discussion focuses on correlated subqueries in SELECT statements, although it also applies to correlated subqueries in UPDATE and DELETE statements.
You can use a correlated subquery to answer a multi-part question whose answer depends on the value in each row processed by the parent statement. For example, a correlated subquery can be used to determine which employees earn more than the average salaries for their departments. In this case, the correlated subquery specifically computes the average salary for each department.
Oracle7 performs a correlated subquery when the subquery references a column from a table from the parent statement.
Oracle7 resolves unqualified columns in the subquery by looking in the tables of the subquery, then in the tables of the parent statement, then in the tables of the next enclosing parent statement, and so on. Oracle7 resolves all unqualified columns in the subquery to the same table. If the tables in a subquery and parent query contain a column with the same name, a reference to the column of a table from the parent query must be prefixed by the table name or alias. To make your statements easier for you to read, always qualify the columns in a correlated subquery with the table, view, or snapshot name or alias.
In the case of an UPDATE statement, you can use a correlated subquery to update rows in one table based on rows from another table. For example, you could use a correlated subquery to roll up four quarterly sales tables into a yearly sales table.
In the case of a DELETE statement, you can use a correlated query to delete only those rows that also exist in another table.
Example XXII
The following statement returns data about employees whose salaries exceed the averages for their departments. The following statement assigns an alias to EMP, the table containing the salary information, and then uses the alias in a correlated subquery:
SELECT deptno, ename, sal
FROM emp x
WHERE sal > (SELECT AVG(sal)
FROM emp
WHERE x.deptno = deptno)
ORDER BY deptno
For each row of the EMP table, the parent query uses the correlated subquery to compute the average salary for members of the same department. The correlated subquery performs these steps for each row of the EMP table:
Example XXIII
The following statement returns the current date:
SELECT SYSDATE FROM DUAL
You could select SYSDATE from the EMP table, but Oracle7 would return 14 rows of the same SYSDATE, one for every row of the EMP table. Selecting from DUAL is more convenient.
Example XXIV
The following statement increments the ZSEQ sequence and returns the new value:
SELECT zseq.nextval
FROM dual
The following statement selects the current value of ZSEQ:
SELECT zseq.currval
FROM dual
Distributed queries are currently subject to this restriction all tables locked by a FOR UPDATE clause and all tables with LONG columns selected by the query must be located on the same database. For example, the following statement will cause an error:
SELECT emp_ny.* FROM emp_ny@ny, dept WHERE emp_ny.deptno = dept.deptno AND dept.dname = 'ACCOUNTING' FOR UPDATE OF emp_ny.sal
Also, you cannot issue the above statement because it selects LONG_COLUMN, a LONG value, from the EMP_REVIEW table on the NY database and locks the EMP table on the local database:
SELECT emp.empno, review.long_column, emp.sal FROM emp, emp_review@ny review WHERE emp.empno = emp_review.empno FOR UPDATE OF emp.sal
Example XXV
This example shows a query which joins the DEPT table on the local database with the EMP table on the HOUSTON database:
SELECT ename, dname
FROM emp@houston, dept
WHERE emp.deptno = dept.deptno
For you to select rows from the base tables of a view, the owner of the schema containing the view must have SELECT privilege on the base tables. Also, if the view is in a schema other than your own, you must have SELECT privilege on the view.
The SELECT ANY TABLE system privilege also allows you to select data from any table or any snapshot or any view's base table.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of each queried table, view, or snapshot or you must have READUP system privileges.
AT
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, the SELECT statement is issued to your default database.
select_list
identical to the non-embedded SELECT command except that host variables can be used in place of literals.
INTO
specifies output host variables and optional indicator variables to receive the data returned by the SELECT statement. Note that these variables must be either all scalars or all arrays, but arrays need not have the same size.
WHERE
restricts the rows returned to those for which the condition is TRUE. See the syntax description of condition . The condition can contain host variables, but cannot contain indicator variables. These host variables can be either scalars or arrays.
All other keywords and parameters are identical to the non-embedded SQL SELECT command.
You can use comments in a SELECT statement to pass instructions, or hints, to the Oracle7 optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, see Oracle7 Server Tuning.
Example I
This example illustrates the use of the embedded SQL SELECT command:
EXEC SQL SELECT ename, sal + 100, job
INTO :ename, :sal, :job
FROM emp
WHERE empno = :empno
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the label of roles granted to you.
role
password is the password for a role. If the role has a password, you must specify the password to enable the role.
ALL EXCEPT
enables all roles granted to you for the current session, except those listed in the EXCEPT clause. Roles listed in the EXCEPT clause must be roles granted directly to you; they cannot be roles granted to you through other roles. You cannot use this option to enable roles with passwords that have been granted directly to you.
If you list a role in the EXCEPT clause that has been granted to you both directly and through another role, the role is still enabled by virtue of your enabling the role to which it has been granted.
NONE
disables all roles for the current session.
You can use the SET ROLE command to enable or disable any of the following roles:
Your current privilege domain is also changed in the following cases:
You can determine which roles are in your current privilege domain at any time by examining the SESSION_ROLES data dictionary view.
To change your default roles, use the ALTER USER command.
Example I
SET ROLE gardener IDENTIFIED BY marigolds
Example II
To enable all roles granted to you for the current session, issue the following statement:
SET ROLE ALL
Example III
To enable all roles granted to you except BANKER, issue the following statement:
SET ROLE ALL EXCEPT banker
Example IV
To disable all roles granted to you for the current session, issue the following statement:
SET ROLE NONE
READ ONLY
establishes the current transaction as a read-only transaction.
READ WRITE
establishes the current transaction as a read-write transaction.
ISOLATION LEVEL
specifies how transactions containing database modifications are handled.
SERIALIZABLE
use the serializable transaction isolation mode as specified in SQL92. That is, if a serializable transaction attempts to execute a DML statement that updates any resource that may have been updated in an uncommitted transaction at the start of the serializable transaction, then the DML statement fails. The COMPATIBLE initialization parameter must be set to 7.3.0 or higher for SERIALIZABLE mode to work.
READ COMMITTED
use the default Oracle transaction behavior. Thus, if the transaction contains DML that require row locks held by another transaction, then the DML statement will wait until the row locks are released.
USE ROLLBACK SEGMENT
assigns the current transaction to the specified rollback segment. This option also establishes the transaction as a read-write transaction.
You cannot use the READ ONLY option and the USE ROLLBACK SEGMENT clause in a single SET TRANSACTION statement or in different statements in the same transaction. Read-only transactions do not generate rollback information and therefore are not assigned rollback segments.
You can establish transaction level read consistency by issuing a SET TRANSACTION statement with the READ ONLY option. After a transaction has been established as read-only, all subsequent queries in that transaction only see changes committed before the transaction began. Read-only transactions are very useful for reports that run multiple queries against one or more tables while other users update these same tables.
Only the following statements are permitted in a read-only transaction:
The read consistency that read-only transactions provide is implemented in the same way as statement-level read consistency. Every statement by default uses a consistent view of the data as of the time the statement is issued. Read-only transactions present a consistent view of the data as of the time that the SET TRANSACTION READ ONLY statement is issued. Read-only transactions provide read consistency is for all nodes accessed by distributed queries and local queries.
You cannot toggle between transaction level read consistency and statement level read consistency in the same transaction. A SET TRANSACTION statement can only be issued as the first statement of a transaction.
Example I
COMMIT
SET TRANSACTION READ ONLY
SELECT COUNT(*) FROM ship
SELECT COUNT(*) FROM container
COMMIT
The last COMMIT statement does not actually make permanent any changes to the database. It ends the read-only transaction.
SET TRANSACTION allows you to assign transactions of different types to rollback segments of different sizes:
The following statement assigns your current transaction to the rollback segment OLTP_5:
SET TRANSACTION USE ROLLBACK SEGMENT oltp_5
INITIAL
specifies the size in bytes of the object's first extent. Oracle7 allocates space for this extent when you create the object. You can also use K or M to specify this size in kilobytes or megabytes. The default value is the size of 5 data blocks. The minimum value is the size of 2 data blocks. The maximum value varies depending on your operating system. Oracle7 rounds values up to the next multiple of the data block size for values less than 5 data blocks. Oracle7 rounds values up to the next multiple of 5 data blocks.
NEXT
PCTINCREASE
You cannot specify PCTINCREASE for rollback segments. Rollback segments always have a PCTINCREASE value of 0.
Oracle7 rounds the calculated size of each new extent up to the next multiple of the data block size.
MINEXTENTS
If the MINEXTENTS value is greater than 1, then Oracle7 calculates the size of subsequent extents based on the values of the INITIAL, NEXT, and PCTINCREASE parameters.
MAXEXTENTS
specifies the total number of extents, including the first, that Oracle7 can allocate for the object. The minimum value is 1. The default and maximum values vary depending your data block size.
UNLIMITED specifies that extents should automatically be allocated as needed. You should not use this option for rollback segments.
FREELIST GROUPS
for objects other than tablespaces, specifies the number of groups of free lists for a table, cluster, or index. The default and minimum value for this parameter is 1. Only use this parameter if you are using Oracle7 with the Parallel Server option in parallel mode.
FREELISTS
You can only specify the FREELISTS parameter in CREATE TABLE, CREATE CLUSTER, and CREATE INDEX statements. You can only specify the FREELIST GROUPS parameter in CREATE TABLE and CREATE CLUSTER statements.
OPTIMAL
specifies an optimal size in bytes for a rollback segment. Not applicable to other kinds of objects. You can also use K or M to specify this size in kilobytes or megabytes. Oracle7 tries to maintain this size for the rollback segment by dynamically deallocating extents when their data is no longer needed for active transactions. Oracle7 deallocates as many extents as possible without reducing the total size of the rollback segment below the OPTIMAL value.
The value of this parameter cannot be less than the space initially allocated for the rollback segment specified by the MINEXTENTS, INITIAL, NEXT, and PCTINCREASE parameters. The maximum value varies depending on your operating system. Oracle7 rounds values to the next multiple of the data block size.
When you create a tablespace, you can specify values for the STORAGE parameters. These values serve as default STORAGE parameter values for segments allocated in the tablespace.
When you create a cluster, index, rollback segments, snapshot, snapshot log, or table, you can specify values for the STORAGE parameters for the segments allocated to these objects. If you omit any STORAGE parameter, Oracle7 uses the value of that parameter specified for the tablespace.
When you alter a cluster, index, rollback segment, snapshot, snapshot log, or table, you can change the values of STORAGE parameters. These new values only affect future extent allocations. For this reason, you cannot change the values of the INITIAL and MINEXTENTS parameter. If you change the value of the NEXT parameter, the next allocated extent will have the specified size, regardless of the size of the most-recently allocated extent and the value of the PCTINCREASE parameter. If you change the value of the PCTINCREASE parameter, Oracle7 calculates the size of the next extent using this new value and the size of the most recently allocated extent.
When you alter a tablespace, you can change the values of STORAGE parameters. These new values serve as default values only to subsequently allocated segments (or subsequently created objects).
A rollback segment created without specifying the storage option has the same storage options as the tablespace that the rollback segment is created in. Thus, if the tablespace is created with MAXEXTENT UNLIMITED, then the rollback segment would also have the same default.
Example I
The following statement creates a table and provides STORAGE parameter values:
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13) )
STORAGE (INITIAL 100K NEXT 50K
MINEXTENTS 1 MAXEXTENTS 50 PCTINCREASE 5 )
Oracle7 allocates space for the table based on the STORAGE parameter values for the following reasons:
The following statement creates a rollback segment and provides STORAGE parameter values:
CREATE ROLLBACK SEGMENT rsone
STORAGE ( INITIAL 10K NEXT 10K
MINEXTENTS 2 MAXEXTENTS 25
OPTIMAL 50K )
Oracle7 allocates space for the rollback segment based on the STORAGE parameter values:
If you are using Trusted Oracle, your DBMS label must match the creation label of the table or cluster or you must satisfy one of these criteria. If the creation label of the table or cluster is not comparable or higher than your DBMS label, you must have READUP system privilege.
TABLE
specifies the schema and name of the table to be truncated. If you omit schema, Oracle7 assumes the table is in your own schema. This table cannot be part of a cluster.
When you truncate a table, Oracle7 also automatically deletes all data in the table's indexes.
CLUSTER
specifies the schema and name of the cluster to be truncated. If you omit schema, Oracle7 assumes the cluster is in your own schema. You can only truncate an indexed cluster, not a hash cluster.
When you truncate a cluster, Oracle7 also automatically deletes all data in the cluster's tables' indexes.
DROP STORAGE
REUSE STORAGE
leaves the space from the deleted rows allocated to the table or cluster. STORAGE values are not reset to the values when the table or cluster was created. This space can be subsequently used only by new data in the table or cluster resulting from inserts or updates.
The DROP STORAGE or REUSE STORAGE option that you choose also applies to the space freed by the data deleted from associated indexes.
If you omit both the REUSE STORAGE and DROP STORAGE options, Oracle7 uses the DROP STORAGE option by default.
Deleting rows with the TRUNCATE command is also more convenient than dropping and recreating a table for the following reasons:
You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and recreate the table.
You cannot truncate the parent table of an enabled referential integrity constraint. You must disable the constraint before truncating the table.
If you truncate the master table of a snapshot, Oracle7 does not record the removed rows in the snapshot log. For this reason, a fast refresh does not remove the rows from the snapshot. Snapshots based on a truncated table must be refreshed completely for Oracle7 to remove their rows.
You cannot roll back a TRUNCATE statement.
Example I
TRUNCATE TABLE emp
The above statement also deletes all data from all indexes on EMP and returns the freed space to the tablespaces containing them.
Example II
The following statement deletes all rows from all tables in the CUST cluster, but leaves the freed space allocated to the tables:
TRUNCATE CLUSTER cust
REUSE STORAGE
The above statement also deletes all data from all indexes in the tables in CUST.
type
is the user-defined datatype to be equivalenced with an Oracle7 external datatype.
datatype
is an Oracle7 external datatype recognized by the Oracle Precompilers (not an Oracle7 internal datatype). The datatype may include a length, precision, or scale. This external datatype is equivalenced to the user-defined type and assigned to all host variables assigned the type. For a list of external datatypes, see Programmer's Guide to the Oracle Precompilers.
All Oracle Precompilers also support the embedded SQL VAR command for host variable equivalencing.
Example I
This example shows an embedded SQL TYPE statement in a Pro*C Precompiler program:
struct screen {short len; char buff[4002]; }; typedef struct screen graphics; EXEC SQL BEGIN DECLARE SECTION; EXEC SQL TYPE graphics IS VARRAW (4002); graphics crt; -- host variable of type graphics ... EXEC SQL END DECLARE SECTION;
Example II
This example shows an embedded SQL TYPE statement in a Pro*Pascal Precompiler program:
Type OraDate = Record Cent, Year, Month, Day, Hour, Min, Sec: Byte End; Var EXEC SQL BEGIN DECLARE SECTION; EXEC SQL TYPE OraDate IS DATE; Birthday: OraDate; -- host variable of type OraDate ... EXEC SQL END DECLARE SECTION;
For you to update values in the base table of a view, the owner of the schema containing the view must have UPDATE privilege on the base table. Also, if the view is in a schema other than your own, you must have UPDATE privilege on the view.
The UPDATE ANY TABLE system privilege also allows you to update values in any table or any view's base table.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the creation label of the table or view:
schema
is the schema containing the table or view. If you omit schema, Oracle7 assumes the table or view is in your own schema.
table view
is the name of the table to be updated. If you specify view, Oracle7 updates the view's base table.
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 the section, "Referring to Objects in Remote Databases," . You can only use a database link to update a remote table or view if you are using Oracle7 with the distributed option.
If you omit dblink, Oracle7 assumes the table or view is on the local database.
alias
provides a different name for the table, view, or subquery to be referenced elsewhere in the statement.
subquery_1
is a subquery that Oracle treats in the same manner as a view. For the syntax of subquery, see page 4 - 431.
column
is the name of a column of the table or view that is to be updated. If you omit a column of the table from the SET clause, that column's value remains unchanged.
expr
is the new value assigned to the corresponding column. This expression can contain host variables and optional indicator variables. See the syntax description of expr .
subquery_2
is a subquery that returns new values that are assigned to the corresponding columns. For the syntax of subquery, see page 4 - 436.
subquery_3
is a subquery that return a new value that is assigned to the corresponding column. For the syntax of subquery, see page 4 - 436.
WHERE
restricts the rows updated to those for which the specified condition is TRUE. If you omit this clause, Oracle7 updates all rows in the table or view. See the syntax description of condition .
The WHERE clause determines the rows in which values are updated. If the WHERE clause is not specified, all rows are updated. For each row that satisfies the WHERE clause, the columns to the left of the equals (=) operator in the SET clause are set to the values of the corresponding expressions on the right. The expressions are evaluated as the row is updated.
You can use comments in an UPDATE statement to pass instructions, or hints, to the Oracle7 optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information, see Oracle7 Server Tuning.
Issuing an UPDATE statement against a table fires any UPDATE triggers associated with the table.
You cannot update a view if the view's defining query contains one of the following constructs:
The SET clause may mix assignments of expressions and subqueries.
Potentially, each row evaluated by an UPDATE statement could be updated with a different value as determined by the correlated subquery. Normal UPDATE statements update each row with the same value.
Example I
The following statement gives null commissions to all employees with the job TRAINEE:
UPDATE emp
SET comm = NULL
WHERE job = 'TRAINEE'
Example II
The following statement promotes JONES to manager of Department 20 with a $1,000 raise (assuming there is only one JONES):
UPDATE emp
SET job = 'MANAGER', sal = sal + 1000, deptno = 20
WHERE ename = 'JONES'
Example III
The following statement increases the balance of bank account number 5001 in the ACCOUNTS table on a remote database accessible through the database link BOSTON:
UPDATE accounts@boston
SET balance = balance + 500
WHERE acc_no = 5001
Example IV
This example shows the following syntactic constructs of the UPDATE command:
UPDATE emp a
SET deptno =
(SELECT deptno
FROM dept
WHERE loc = 'BOSTON'),
(sal, comm) =
(SELECT 1.1*AVG(sal), 1.5*AVG(comm)
FROM emp b
WHERE a.deptno = b.deptno)
WHERE deptno IN
(SELECT deptno
FROM dept
WHERE loc = 'DALLAS'
OR loc = 'DETROIT')
The above UPDATE statement performs the following operations:
For you to update values in the base table of a view, the owner of the schema containing the view must have UPDATE privilege on the base table. Also, if the view is in a schema other than your own, you must have UPDATE privilege on the view.
The UPDATE ANY TABLE system privilege also allows you to update values in any table or any view's base table.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the creation label of the table or view:
AT
db_name is a database identifier declared in a previous DECLARE DATABASE statement.
:host_variable is a host variable whose value is a previously declared db_name.
If you omit this clause, the UPDATE statement is issued to your default database.
FOR :host_integer
limits the number of times the UPDATE statement is executed if the SET and WHERE clauses contain array host variables. If you omit this clause, Oracle7 executes the statement once for each component of the smallest array.
schema
is the schema containing the table or view. If you omit schema, Oracle7 assumes the table or view is in your own schema.
table view
is the name of the table to be updated. If you specify view, Oracle7 updates the view's base table.
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 the section "Referring to Objects in Remote Databases," . You can only use a database link to update a remote table or view if you are using Oracle7 with the distributed option.
subquery_1
is a subquery that Oracle treats in the same manner as a view. For the syntax of subquery, see page 4 - 431.
If you omit dblink, Oracle7 assumes the table or view is on the local database.
t_alias
is a name used to reference the table, view, or subquery elsewhere in the statement.
column
is the name of a column of the table or view that is to be updated. If you omit a column of the table from the SET clause, that column's value remains unchanged.
expr
is the new value assigned to the corresponding column. This expression can contain host variables and optional indicator variables. See the syntax description of expr .
subquery_2
is a subquery that returns new values that are assigned to the corresponding columns. For the syntax of subquery, see page 4 - 436.
subquery_3
is a subquery that return a new value that is assigned to the corresponding column. For the syntax of subquery, see page 4 - 436.
WHERE
specifies which rows of the table or view are updated:
condition updates only rows for which this condition is true. This condition can contain host variables and optional indicator variables. See the syntax description of condition .
CURRENT OF updates only the row most recently fetched by the cursor. The cursor cannot be associated with a SELECT statement that performs a join unless its FOR UPDATE clause explicitly locks only one table.
If you omit this clause entirely, Oracle7 updates all rows of the table or view.
Array host variables can have different sizes. In this case, the number of times Oracle7 executes the statement is determined by the smaller of the following values:
You can use comments in an UPDATE statement to pass instructions, or hints, to the Oracle7 optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, see Oracle7 Server Tuning.
For more information on this command, see Programmer's Guide to the Oracle Precompilers.
Examples
The following examples illustrate the use of the embedded SQL UPDATE command:
EXEC SQL UPDATE emp
SET sal = :sal, comm = :comm INDICATOR :comm_ind
WHERE ename = :ename;
EXEC SQL UPDATE emp
SET (sal, comm) =
(SELECT AVG(sal)*1.1, AVG(comm)*1.1
FROM emp)
WHERE ename = 'JONES';
host_variable Keywords and Parameters
is the host variable to be assigned an Oracle7 external datatype.
is an Oracle7 external datatype recognized by the Oracle Precompilers (not an Oracle7 internal datatype). The datatype may include a length, precision, or scale. This external datatype is assigned to the host_variable. For a list of external datatypes, see Programmer's Guide to the Oracle Precompilers.
Example
This example equivalences the host variable DEPT_NAME to the datatype STRING and the host variable BUFFER to the datatype RAW(2000):
EXEC SQL BEGIN DECLARE SECTION; ... dept_name CHARACTER(15); -- default datatype is CHAR EXEC SQL VAR dept_name IS STRING; -- reset to STRING ... buffer CHARACTER(200); -- default datatype is CHAR EXEC SQL VAR buffer IS RAW(200); -- refer to RAW EXEC SQL END DECLARE SECTION;
NOT FOUND
identifies any exception condition that results in a return code of +100 in SQLCODE, (or +1403 in Version 5 compatibility mode).
SQLERROR
identifies a condition that results in a negative return code.
SQLWARNING
identifies a non-fatal warning condition.
CONTINUE
indicates that the program should progress to the next statement.
GOTO
indicates that the program should branch to the statement named by label_name.
STOP
DO
indicates that the program should call a host language routine. The syntax of routine_call depends on your host language. See your language-specific Supplement to the Oracle Precompilers Guide.
The scope of a WHENEVER statement is positional, rather than logical. A single WHENEVER statement applies to all embedded SQL statements that physically follow it in the Precompiler source file, not in the flow of the program logic. A WHENEVER statement remains in effect until it is superseded by another WHENEVER statement checking for the same condition.
For more information on this command, see Programmer's Guide to the Oracle Precompilers.
Do not confuse the WHENEVER embedded SQL command with the WHENEVER SQL*Plus command.
Example
The example illustrates the use of the WHENEVER command in a Pro*C embedded SQL program:
EXEC SQL WHENEVER NOT FOUND; ... EXEC SQL WHENEVER SQLERROR GOTO sqlerror: ... sql_error: EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK RELEASE;
Prev Next |
Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |