| Oracle7 Server SQL Reference Manual |  Library |
 Product |
 Contents |
 Index |
| 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.
![[*]](jump.gif)
and ROLLBACK .
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
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
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
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
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.
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.
. Note that auditing options established by the AUDIT command (Schema Objects) apply to current sessions as well as to subsequent sessions.
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
and the ALTER TABLE command .
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 .
table_constraint ::=
column_constraint ::=
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
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.
. The condition of a CHECK constraint can refer to any column in the table, but it cannot refer to columns of other tables. CHECK constraint conditions cannot contain the following constructs:
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
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:
. As with the columns of any index, the order of the columns in the cluster key affects the structure of the cluster index. 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
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
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:
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
INSERT command
LOCK TABLE command
SELECT command on 4 - 405
UPDATE command
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
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
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:
, the ALTER TABLE command , the ENABLE clause , and the DISABLE clause .
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
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:
. Note the following caveats:
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
ALTER INDEX command