Détail des commandes SQL Oracle
COMMANDS
The commands are listed in the following order: SQL*Plus commands,
PL/SQL commands, and then SQL commands.
SQL*PLUS COMMANDS
@ @@ /
ACCEPT APPEND BREAK
BTITLE CHANGE CLEAR
COLUMN COMPUTE CONNECT
COPY DEFINE DEL
DESCRIBE DISCONNECT EDIT
EXECUTE EXIT GET
HELP HOST INPUT
LIST PAUSE PRINT
PROMPT REMARK RUN
RUNFORM SAVE SET
SHOW SPOOL SQLPLUS
START TIMING TITLE
UNDEFINE VARIABLE WHENEVER OSERROR
WHENEVER SQLERROR
PL/SQL COMMANDS (Statements)
CLOSE Statement EXIT Statement FETCH Statement
GOTO Statement IF Statement LOOP Statement
NULL Statement OPEN Statement RAISE Statement
RETURN Statement
SQL COMMANDS
The SQL commands are divided into these categories:
* Data Definition Language commands
* Data Manipulation Language commands
* Transaction Control commands
* Session Control commands
* System Control commands
SQL Data Definition Language commands include the following:
ALTER CLUSTER ALTER SEQUENCE COMMENT
ALTER DATABASE ALTER SNAPSHOT CREATE CLUSTER
ALTER FUNCTION ALTER SNAPSHOT LOG CREATE CONTROLFILE
ALTER INDEX ALTER TABLE CREATE DATABASE
ALTER PACKAGE ALTER TABLESPACE CREATE DATABASE LINK
ALTER PROCEDURE ALTER TRIGGER CREATE FUNCTION
ALTER PROFILE ALTER USER CREATE INDEX
ALTER RESOURCE COST ALTER VIEW CREATE PACKAGE
ALTER ROLE ANALYZE CREATE PACKAGE BODY
ALTER ROLLBACK SEGMENT AUDIT CREATE PROCEDURE
CREATE PROFILE DROP ROLE
CREATE ROLE DROP ROLLBACK SEGMENT
CREATE ROLLBACK SEGMENT DROP SEQUENCE
CREATE SCHEMA DROP SNAPSHOT
CREATE SEQUENCE DROP SNAPSHOT LOG
CREATE SNAPSHOT DROP SYNONYM
CREATE SNAPSHOT LOG DROP TABLE
CREATE SYNONYM DROP TABLESPACE
CREATE TABLE DROP TRIGGER
CREATE TABLESPACE DROP USER
CREATE TRIGGER DROP VIEW
CREATE USER GRANT
CREATE VIEW NOAUDIT
DROP CLUSTER RENAME
DROP DATABASE LINK REVOKE
DROP FUNCTION TRUNCATE
DROP INDEX UPDATE
DROP PROCEDURE
DROP PROFILE
SQL Data Manipulation Language commands include the following:
DELETE
EXPLAIN PLAN
INSERT
LOCK TABLE
SELECT
SQL Transaction Control commands include the following:
COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTION
SQL Session Control commands include the following:
ALTER SESSION
SET ROLE
SQL System Control command (only one command):
ALTER SYSTEM
ABS
ABS
SYNTAX:
ABS(n)
Purpose:
Returns the absolute value of n.
Example:
SELECT ABS(-15) "Absolute"
FROM DUAL
Absolute
--------
15
SEE:
SIGN
ADD_MONTHS
ADD_MONTHS
SYNTAX:
ADD_MONTHS(d,n)
PURPOSE:
Returns the date d plus n months. The argument n can be any
integer. If d is the last day of the month or if the resulting
month has fewer days than the day component of d, then the result is
the last day of the resulting month. Otherwise, the result has the
same day component as d.
EXAMPLES:
SELECT TO_CHAR(ADD_MONTHS(hiredate,1),
'DD-MON-YYYY') "Next month"
FROM emp
WHERE ename = 'SMITH'
Next month
-----------
17-JAN-1981
SEE:
MONTHS_BETWEEN
ALTER CLUSTER
ALTER CLUSTER command
PURPOSE:
To redefine future storage allocations or to allocate an extent for
a cluster.
SYNTAX:
ALTER CLUSTER [schema.]cluster
[PCTUSED integer] [PCTFREE integer]
[SIZE integer [K|M] ]
[INITRANS integer] [MAXTRANS integer]
[STORAGE storage_clause]
[ PARALLEL ( [ DEGREE { integer | DEFAULT } ]
[ INSTANCES { integer | DEFAULT } ]
)
| NOPARALLEL ]
[ CACHE | NOCACHE ]
[ALLOCATE EXTENT [( [SIZE integer [K|M] ]
[DATAFILE 'filename']
[INSTANCE integer] )] ]
where:
schema
is the schema containing the cluster. If you omit schema, Oracle
assumes the cluster is in your own schema.
cluster
is the name of the cluster to be altered.
SIZE
determines how many cluster keys will be stored in data blocks
allocated to the cluster. You can only change the SIZE parameter
for an indexed cluster, not for a hash cluster. For a description
of the SIZE parameter, see the CREATE CLUSTER command.
PCTUSED
PCTFREE
INITRANS
MAXTRANS
changes the values of these parameters for the cluster. See the
PCTUSED, PCTFREE, INITRANS, and MAXTRANS parameters of the CREATE
TABLE command.
STORAGE
changes the storage characteristics for the cluster. See the
STORAGE clause.
ALLOCATE EXTENT
explicitly allocates a new extent for the cluster.
SIZE
specifies the size of the extent in bytes. You can
use K or M to specify the extent size in kilobytes or
megabytes. If you omit this parameter, Oracle
determines the size based on the values of the
cluster's STORAGE parameters.
DATAFILE
specifies one of the data files in the cluster's
tablespace to contain the new extent. If you omit
this parameter, Oracle chooses the data file.
INSTANCE
makes the new extent available to the specified
instance. An instance is identified by the value of
its initialization parameter INSTANCE_NUMBER. If you
omit this parameter, the extent is available to all
instances. Only use this parameter if you are using
Oracle with the Parallel Server option in parallel
mode.
Explicitly allocating an extent with this clause does not cause
Oracle 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.
PARALLEL
DEGREE specifies the number of query server processes that can scan
the cluster in parallel. Either specify a positive integer or DEFAULT
which signifies to use the initialization parameter
INSTANCES specifies the minimum number of instances that need to be
of a Parallel Server. A positive integer specifies the number of
caches.
NOPARALLEL
specifies that queries on this cluster are not performed in parallel
by default. A hint in the query still causes the query to be
performed in parallel.
CACHE
specifies that blocks of this cluster are placed on the most recently
is performed.
This option is useful for small lookup tables.
NOCACHE
specifies that blocks of the cluster in the buffer cache follow the
standard LRU algorithm when a full table scan is performed.
PREREQUISITES:
The cluster must be in your own schema or you must have ALTER ANY
CLUSTER system privilege.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must match the cluster's creation label or you must satisfy one of
these criteria:
* If the cluster's creation label is higher than your DBMS label,
you must have READUP and WRITEUP system privileges.
* If the cluster's creation label is lower than your DBMS label, you
must have WRITEDOWN system privilege.
* If the cluster's creation label and your DBMS label are
noncomparable, you must have READUP, WRITEUP, and WRITEDOWN system
privileges.
SEE:
CREATE CLUSTER, CREATE TABLE, STORAGE
ALTER DATABASE
ALTER DATABASE command
PURPOSE:
To alter an existing database in one of these ways:
* mount the database
* convert an Oracle Version 6 data dictionary when migrating to
Oracle7
* open the database
* choose archivelog or noarchivelog mode for redo log file groups
* perform media recovery
* add or drop a redo log file group or a member of a redo log file
group
* clear and initialize an online redo log file
* rename a redo log file member or a data file
* backup the current control file
* backup SQL commands (that can be used to re-create the database)
to the database's trace file
* create a new data file in place of an old one for recovery
purposes
* take a data file online or offline
* enable or disable a thread of redo log file groups
* change the database's global name
* change the MAC mode
* equate the predefined label DBHIGH or DBLOW with an operating
system label
* resize one or more datafiles
* create a new datafile in place of an old one for recovery purposes
* enable or disable autoextend size of datafiles
SYNTAX:
ALTER DATABASE [database]
{ MOUNT [EXCLUSIVE | PARALLEL]
| CONVERT
| OPEN [RESETLOGS | NORESETLOGS]
| ARCHIVELOG
| NOARCHIVELOG
| RECOVER recover_clause
| ADD LOGFILE [THREAD integer] [GROUP integer] filespec
[, [GROUP integer] filespec] ...
| ADD LOGFILE MEMBER 'filename' [REUSE] [, 'filename' [REUSE]] ...
TO { GROUP integer
| ('filename' [,'filename'] ...)
| 'filename' }
[, 'filename' [REUSE] [, 'filename' [REUSE]] ...
TO { GROUP integer
| ('filename' [, 'filename'] ...)
| 'filename' } ] ...
| DROP LOGFILE { GROUP integer
| ('filename' [, 'filename'] ...)
| 'filename' }
[, { GROUP integer
| ('filename' [,'filename'] ...)
| 'filename' } ] ...
| DROP LOGFILE MEMBER 'filename' [, 'filename'] ...
| CLEAR [UNARCHIVED] LOGFILE { GROUP integer
| ('filename' [, 'filename'] ...)
| 'filename' }
[, { GROUP integer
| ('filename' [,'filename'] ...)
| 'filename' } ] ...
| RENAME FILE 'filename' [, 'filename'] ...
TO 'filename' [, 'filename'] ...
| BACKUP CONTROLFILE TO 'filename' [REUSE]
| BACKUP CONTROLFILE TO TRACE [ RESETLOGS | NORESETLOGS ]
| CREATE DATAFILE 'filename' [, filename] ...
[AS filespec [, filespec] ...
| DATAFILE 'filename' { ONLINE | OFFLINE [DROP] }
| ENABLE [PUBLIC] THREAD integer
| DISABLE THREAD integer
| RENAME GLOBAL_NAME TO database[.domain]...
| RESET COMPATIBILITY
| SET { DBMAC {ON | OFF}
| DBHIGH = 'text'
| DBLOW = 'text' }
| ENABLE [PUBLIC] THREAD integer
| DISABLE THREAD integer
| CREATE DATAFILE 'filename' [, 'filename'] ...
{ ONLINE
| OFFLINE [DROP]
| RESIZE integer [K | M]
| AUTOEXTEND { OFF
| ON [NEXT integer [K|M] ]
[MAXSIZE { UNLIMITED | integer [K|M] }
| END BACKUP }
where:
database
identifies the database to be altered. If you omit database, Oracle
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
mounts the database.
EXCLUSIVE
mounts the database in exclusive mode. This mode
allows the database to be mounted by only one
instance at a time. You cannot use this option if
another instance has already mounted the database.
PARALLEL
mounts the database in parallel mode. This mode
allows the database to be mounted by multiple
instances concurrently. You can only use this option
if you are using Oracle with the Parallel Server
option. You cannot use this option if another option
has mounted the database in exclusive mode.
The default is EXCLUSIVE.
CONVERT
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 the Oracle7 Server Migration Guide.
You can only use the following option when your instance has the
database mounted, but not open:
OPEN
opens the database, making it available for normal use. You must
mount the database before you can open it.
RESETLOGS
resets the current log sequence number to 1 and
invalidates all redo entries in the online and
archived redo log files. You must use this option to
open the database after performing media recovery
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 these options after performing incomplete
media recovery. In any other case, Oracle uses the NORESETLOGS
automatically.
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.
You can only use the following option when your instance has the
database mounted in exclusive mode:
RECOVER
performs media recovery. You only recover the entire database when
the database is closed. You can recover tablespaces or data files
when the database is open or closed, provided the tablespaces or
data files to be recovered are not being used. You cannot perform
media recovery if you are connected to Oracle through the multi-
threaded server architecture. You can also perform media recovery
with the RECOVER SQL*DBA command.
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 Oracle with the Parallel Server option in
parallel mode.
Each filespec specifies a redo log file group containing one or more
members, or copies.
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, Oracle
generates its value automatically. You can examine the GROUP value
for a redo log file group through the dynamic performance table.
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, Oracle 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 manners as the ADD LOGFILE MEMBER clause.
You cannot drop a redo log file group if all of its members have
been lost through media failure.
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
reinitializes an online redo log and optionally not archive the
redo log. CLEAR LOGFILE is similar to adding and dropping a redo
redo log except that the command may be issued even if there are
are only two logs for the thread and also may be issued for the
current redo log of a closed thread.
CLEAR LOGFILE cannot be used to clear a log needed for media
recovery. If it is necessary to 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 LOG command is interrupted by a system or instance
failure, then the database may hang. If so, the command must be
be re-issued 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 unuseable 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 data files 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.
BACKUP CONTROLFILE TO 'filename'
backs up the current control file to the specified 'filename'. If
the backup file already exists, you must specify the REUSE option.
BACKUP CONTROLFILE 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 files).
RESETLOGS
the SQL statements written to the trace file for starting the
database is ALTER DATABASE OPEN RESETLOGS.
NORESETLOGS
the SQL statements 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
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.
PUBLIC
makes the enabled thread available to any instance
that does not explicitly request a specific thread
with the initialization parameter THREAD.
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.
SET
changes one of the following for your database:
DBMAC
changes the mode in which Trusted Oracle is
configured:
ON
configures Trusted Oracle in DBMS MAC
mode.
OFF
configures Trusted Oracle in OS MAC
mode.
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'.
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 Oracle.
RESET COMPATIBILITY
Issue the ALTER DATABASE RESET COMPATIBILITY command when restarting
the database with the COMPATIBLE initialization parameter set to an
earlier release.
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 recreate a datafile that was lost with no backup.
The 'filename' must indentify 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 ssize 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
on the new file to return it to the state of the old file at the
time it was lost.
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.
RESIZE
attempts to change the size of the datafile to the specified
absolute size in bytes. You can also use K or M to specify this
size in kilobytes or megabytes. There is no default, so you must
specify a size.
AUTOEXTEND
enables or disables the automatic extension of a datafile.
OFF disables 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.
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 onb allocating disk space to the datafile.
END BACKUP avoid media recovery on database startup after an
online tablespace backup was interrupted by a system or
instance failure or SHUTDOWN abort.
PREREQUISITES:
You must have ALTER DATABASE system privilege.
SEE:
CREATE DATABASE, RECOVER
ALTER FUNCTION
ALTER FUNCTION command
PURPOSE:
To recompile a stand-alone stored function.
SYNTAX:
ALTER FUNCTION [schema.]function
COMPILE
where:
schema
is the schema containing the function. If you omit schema, Oracle
assumes the function is in your own schema.
function
is the name of the function to be recompiled.
COMPILE
causes Oracle to recompile the function. The COMPILE keyword is
required.
PREREQUISITES:
The function must be in your own schema or you must have ALTER ANY
PROCEDURE system privilege.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must match the function's creation label or you must satisfy one of
these criteria:
* If the function's creation label is higher than your DBMS label,
you must have READUP and WRITEUP system privileges.
* If the function's creation label is lower than your DBMS label,
you must have WRITEDOWN system privilege.
* If the function's creation label and your DBMS label are
noncomparable, you must have READUP, WRITEUP, and WRITEDOWN system
privileges.
SEE:
ALTER PROCEDURE, CREATE FUNCTION
ALTER INDEX
ALTER INDEX command
PURPOSE:
To change future storage allocation for data blocks in an index.
SYNTAX:
ALTER INDEX [schema.]index
[INITRANS integer] [MAXTRANS integer]
[STORAGE storage_clause]
where:
schema
is the schema containing the index. If you omit schema, Oracle
assumes the index is in your own schema.
index
is the name of the index to be altered.
INITRANS
MAXTRANS
changes the values of these parameters for the index. See the
INITRANS and MAXTRANS parameters of the CREATE TABLE command.
STORAGE
changes the storage parameters for the index. See the STORAGE
clause.
PREREQUISITES:
The index must be in your own schema or you must have ALTER ANY
INDEX system privilege.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must match the index's creation label or you must satisfy one of
these criteria:
* If the index's creation label is higher than your DBMS label, you
must have READUP and WRITEUP system privileges.
* If the index's creation label is lower than your DBMS label, you
must have WRITEDOWN system privilege.
* If the index's creation label and your DBMS label are
noncomparable, you must have READUP, WRITEUP, and WRITEDOWN system
privileges.
SEE:
CREATE INDEX, CREATE TABLE, STORAGE
ALTER PACKAGE
ALTER PACKAGE command
PURPOSE:
To recompile a stored package.
SYNTAX:
ALTER PACKAGE [schema.]package
COMPILE [PACKAGE | BODY]
where:
schema
is the schema containing the package. If you omit schema, Oracle
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.
PREREQUISITES:
The package must be in your own schema or you must have ALTER ANY
PROCEDURE system privilege.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must match the package's creation label or you must satisfy one of
these criteria:
* If the package's creation label is higher than your DBMS label,
you must have READUP and WRITEUP system privileges.
* If the package's creation label is lower than your DBMS label, you
must have WRITEDOWN system privilege.
* If the package's creation label and your DBMS label are
noncomparable, you must have READUP, WRITEUP, and WRITEDOWN system
privileges.
SEE:
CREATE PACKAGE, CREATE PACKAGE BODY
ALTER PROCEDURE
ALTER PROCEDURE command
PURPOSE:
To recompile a stand-alone stored procedure.
SYNTAX:
ALTER PROCEDURE [schema.]procedure
COMPILE
where:
schema
is the schema containing the procedure. If you omit schema, Oracle
assumes the procedure is in your own schema.
procedure
is the name of the procedure to be recompiled.
COMPILE
causes Oracle to recompile the procedure. The COMPILE keyword is
required.
PREREQUISITES:
The procedure must be in your own schema or you must have ALTER ANY
PROCEDURE system privilege.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must match the procedure's creation label or you must satisfy one of
these criteria:
* If the procedure's creation label is higher than your DBMS label,
you must have READUP and WRITEUP system privileges.
* If the procedure's creation label is lower than your DBMS label,
you must have WRITEDOWN system privilege.
* If the procedure's creation label and your DBMS label are
noncomparable, you must have READUP, WRITEUP, and WRITEDOWN system
privileges.
SEE:
ALTER FUNCTION, ALTER PACKAGE, CREATE PROCEDURE
ALTER PROFILE
ALTER PROFILE command
PURPOSE:
To add, modify, or remove a resource limit in a profile.
SYNTAX:
ALTER PROFILE profile
LIMIT [SESSIONS_PER_USER {integer | UNLIMITED | DEFAULT}]
[CPU_PER_SESSION {integer | UNLIMITED | DEFAULT}]
[CPU_PER_CALL {integer | UNLIMITED | DEFAULT}]
[CONNECT_TIME {integer | UNLIMITED | DEFAULT}]
[IDLE_TIME {integer | UNLIMITED | DEFAULT}]
[LOGICAL_READS_PER_SESSION {integer | UNLIMITED | DEFAULT}]
[LOGICAL_READS_PER_CALL {integer | UNLIMITED | DEFAULT}]
[COMPOSITE_LIMIT {integer | UNLIMITED | DEFAULT}]
[PRIVATE_SGA {integer [K|M] | UNLIMITED | DEFAULT}]
where:
profile
is the name of the profile to be altered.
integer
defines a new limit for a resource in this profile. For information
on resource limits, see the CREATE PROFILE command.
UNLIMITED
specifies that this profile allows unlimited use of the resource.
DEFAULT
removes a resource limit from the profile. Any user assigned the
profile is subject to the limit on the resource defined in the
DEFAULT profile in their subsequent sessions.
PREREQUISITES:
You must have ALTER PROFILE system privilege.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must match the profile's creation label or you must satisfy one of
these criteria:
* If the profile's creation label is higher than your DBMS label,
you must have READUP and WRITEUP system privileges.
* If the profile's creation label is lower than your DBMS label, you
must have WRITEDOWN system privilege.
* If the profile's creation label and your DBMS label are
noncomparable, you must have READUP, WRITEUP, and WRITEDOWN system
privileges.
SEE:
CREATE PROFILE
ALTER RESOURCE COST
ALTER RESOURCE COST command
PURPOSE:
To specify a formula to calculate the total resource cost used in a
session. For any session, this cost is limited by the value of the
COMPOSITE_LIMIT parameter in the user's profile.
SYNTAX:
ALTER RESOURCE
COST [CPU_PER_SESSION integer]
[CONNECT_TIME integer]
[LOGICAL_READS_PER_SESSION integer]
[PRIVATE_SGA integer]
where:
integer
is the weight of each resource.
CPU_PER_SESSION
is the amount of CPU time used by a session measured in hundredths
of seconds.
CONNECT_TIME
is the elapsed time of a session measured in minutes.
LOGICAL_READS_PER_SESSION
is the number of data blocks read during a session, including blocks
read from both memory and disk.
PRIVATE_SGA
is 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.
PREREQUISITES:
You must have ALTER RESOURCE COST system privilege.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must match DBLOW or you must have WRITEDOWN system privileges.
SEE:
CREATE PROFILE
ALTER ROLE
ALTER ROLE command
PURPOSE:
To change the authorization needed to enable a role.
SYNTAX:
ALTER ROLE role
{ NOT IDENTIFIED
| IDENTIFIED {BY password | EXTERNALLY }
where:
role
is the name of the role to be created. Oracle Corporation
recommends that the role contain at least one single-byte
character regardless of whether the database character set also
contains multi-byte characters.
NOT IDENTIFIED
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 Oracle 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.
EXTERNALLY
The operating system verifies the user enabling to
the role. Depending on the operating system, the
user may have to specify a password to the operating
system when enabling the role.
If you omit both the NOT IDENTIFIED option and the IDENTIFIED
clause, the role defaults to NOT IDENTIFIED.
PREREQUISITES:
You must either have been granted the role with the ADMIN OPTION or
have ALTER ANY ROLE system privilege.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must match the role's creation label or you must satisfy one of
these criteria:
* If the role's creation label is higher than your DBMS label, you
must have READUP and WRITEUP system privileges.
* If the role's creation label is lower than your DBMS label, you
must have WRITEDOWN system privilege.
* If the role's creation label and your DBMS label are
noncomparable, you must have READUP, WRITEUP, and WRITEDOWN system
privileges.
SEE:
CREATE ROLE, SET ROLE
ALTER ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT command
PURPOSE:
To alter a rollback segment in one of these ways:
* by bringing it online
* by taking it offline
* by changing its storage characteristics
* by shrinking it to an optimal or given size
SYNTAX:
ALTER ROLLBACK SEGMENT rollback_segment
{ ONLINE
| OFFLINE
| STORAGE storage_clause
| OPTIMAL [ TO integer [K|M] | NULL ]
| SHRINK [ TO integer [K|M] ]
where:
rollback_segment
specifies the name of an existing rollback segment.
ONLINE
brings the rollback segment online.
OFFLINE
takes the rollback segment offline.
STORAGE
changes the rollback segment's storage characteristics.
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.
NULL specifies no optimal size for the rollback segment, meaning that
Oracle7 never deallocates the rollback segment's extents. This is
the default behavior.
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.
SHRINK
attempts to shrink the rollback segment to an optimal or given size.
PREREQUISITES:
You must have ALTER ROLLBACK SEGMENT system privilege.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must match the rollback segment's creation label or you must satisfy
one of these criteria:
* If the rollback segment's creation label is higher than your DBMS
label, you must have READUP and WRITEUP system privileges.
* If the rollback segment's creation label is lower than your DBMS
label, you must have WRITEDOWN system privilege.
* If the rollback segment's creation label and your DBMS label are
noncomparable, you must have READUP, WRITEUP, and WRITEDOWN system
privileges.
SEE:
CREATE ROLLBACK SEGMENT, CREATE TABLESPACE, STORAGE
ALTER SEQUENCE
ALTER SEQUENCE command
PURPOSE:
To change the sequence in one of these ways:
* changing the increment between future sequence values
* setting or eliminating the minimum or maximum value
* changing the number of cached sequence numbers
* specifying whether or not sequence numbers must be ordered
SYNTAX:
ALTER SEQUENCE [schema.]sequence
[INCREMENT BY integer]
[MAXVALUE integer | NOMAXVALUE]
[MINVALUE integer | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE integer | NOCACHE]
[ORDER | NOORDER]
where:
schema
is the schema to contain the sequence. If you omit schema, Oracle
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 value can be
any positive or negative Oracle integer, but it cannot be 0. 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
specifies the sequence's minimum value.
NOMINVALUE
specifies a minimum value of 1 for an ascending sequence or -10
for a descending sequence.
The default is NOMINVALUE.
MAXVALUE
specifies the maximum value the sequence can generate.
NOMAXVALUE
specifies a maximum value of 10
for a descending sequence.
The default is NOMAXVALUE.
START WITH
specifies the first sequence number to be generated. You can use
this option to start an ascending sequence at a value greater than
its minimum or to start a descending sequence at a value less than
its maximum. For ascending sequences, the default value is the
sequence's minimum value. For descending sequences, the default
value is the sequence's maximum value.
CYCLE
specifies that the sequence continues to generate values after
reaching either its maximum or minimum value. After an ascending
sequence reaches its maximum value, it generates its minimum value.
After a descending sequence reaches its minimum, it generates its
maximum.
NOCYCLE
specifies that the sequence cannot generate more values after
reaching its maximum or minimum value.
The default is NOCYCLE.
CACHE
specifies how many values of the sequence Oracle preallocates and
keeps in memory for faster access. The minimum value for this
parameter is 2. For sequences that cycle, this value must be less
than the number of values in the cycle.
NOCACHE
specifies that values of the sequence are not preallocated.
If you omit both the CACHE parameter and the NOCACHE option, Oracle
caches 20 sequence numbers by default. However, if you are using
Oracle 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
guarantees that sequence numbers are generated in order of request.
You may want to use this option if you are using the sequence
numbers as timestamps. Guaranteeing order is usually not important
for sequences used to generate primary keys.
NOORDER
does not guarantee sequence numbers are generated in order of
request.
If you omit both the ORDER and NOORDER options, Oracle chooses
NOORDER by default. Note that the ORDER option is only necessary to
guarantee ordered generation if you are using Oracle with the
Parallel Server option in parallel mode. If you are using exclusive
mode, sequence numbers are always generated in order.
PREREQUISITES:
The sequence must be in your own schema or you must have ALTER
privilege on the sequence or you must have ALTER ANY SEQUENCE system
privilege.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must match the sequence's creation label or you must satisfy one of
these criteria:
* If the sequence's creation label is higher than your DBMS label,
you must have READUP and WRITEUP system privileges.
* If the sequence's creation label is lower than your DBMS label,
you must have WRITEDOWN system privilege.
* If the sequence's creation label and your DBMS label are
noncomparable, you must have READUP, WRITEUP, and WRITEDOWN system
privileges.
SEE:
CREATE SEQUENCE, DROP SEQUENCE
ALTER SESSION
ALTER SESSION command
PURPOSE:
To alter your current session in one of these ways:
* to enable or disable the SQL trace facility
* to change the values of NLS parameters
* to change your DBMS session label in Trusted Oracle
* to change the default label format for your session
* to close a database link
* to send advice to remote databases for forcing an in-doubt
distributed transaction
* to permit or prohibit procedures and stored functions from issuing
COMMIT and ROLLBACK statements
* to change the goal of the cost-based optimization approach
* in a parallel server, to indicate that the session must access
database files as if the session was connected to another instance
SYNTAX:
ALTER SESSION
{ SET { SQL_TRACE = { TRUE | FALSE }
| GLOBAL_NAMES = { TRUE | FALSE }
| NLS_LANGUAGE = language
| NLS_TERRITORY = territory
| NLS_DATE_FORMAT = 'fmt'
| NLS_DATE_LANGUAGE = language
| NLS_NUMERIC_CHARACTERS = 'text'
| NLS_ISO_CURRENCY = territory
| NLS_CURRENCY = 'text'
| NLS_SORT = { sort | BINARY }
| LABEL = {'text' | DBHIGH | DBLOW | OSLABEL }
| MLS_LABEL_FORMAT = 'fmt'
| FLAGGER = { ENTRY | INTERMEDIATE | FULL | OFF }
| SESSION_CACHED_CURSORS = integer
| CLOSE_CACHED_OPEN_CURSORS = { TRUE | FALSE }
| INSTANCE = integer
} ...
| CLOSE DATABASE LINK dblink
| ADVISE {COMMIT | ROLLBACK | NOTHING}
| {ENABLE | DISABLE} COMMIT IN PROCEDURE }
where:
SQL_TRACE
controls the SQL trace facility for your session:
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 Oracle returns errors and other
messages. This parameter also implicitly specifies new values for
these items:
* language for day and month names and abbreviations and
spelled values of other date format elements
* sort sequence
NLS_TERRITORY
implicitly specifies new values for these items:
* default date format
* decimal character and group separator
* local currency symbol
* ISO currency symbol
* first day of the week for D date format element
NLS_DATE_FORMAT
explicitly specifies a new default date format.
NLS_DATE_LANGUAGE
explicitly changes the language for day and month names and
abbreviations and spelled values of other date format elements.
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 different and
can only be single-byte characters.
NLS_ISO_CURRENCY
explicitly specifies the territory whose ISO currency symbol should
be used.
NLS_CURRENCY
explicitly specifies a new local currency symbol.
NLS_SORT
changes the sequence into which Oracle sorts character values.
sort
specifies the name of a linguistic sort sequence.
BINARY
specifies a binary sort.
NLS_CALENDAR
explicitly specify a new calendar type.
LABEL
changes your DBMS session label to either:
* the label specified by 'text' in your session's default
label format
* the label equivalent to DBHIGH
* the label equivalent to DBLOW
* your operating system label using OSLABEL
MLS_LABEL_FORMAT
changes the default label format for your session.
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
specifies that non-SQL92 compliant syntax should be flagged for the
session. Oracle flags any non-standard constructs as errors and
displays the violating syntax.
Currently there is no difference between entry, intermediate, and
full level flagging. These options will become significant as
Oracle conforms to SQL92 intermediate and full level standards.
OFF disables FIPS 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
in the cache.
CLOSE_CACHED_OPEN_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.
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
sends advice for forcing a distributed transaction to a remote
database. This advice appears on the remote database in the ADVICE
column of the DBA_2PC_PENDING data dictionary view in the event the
distributed transaction becomes in-doubt. The following are advice
options:
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.
PREREQUISITES:
To enable and disable the SQL trace facility or to change the
default label format, you must have ALTER SESSION system privilege.
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.
SEE:
Tuning SQL Statements and Appendix B of the Oracle Server
Application Developer's Guide.
ALTER SNAPSHOT
ALTER SNAPSHOT command
PURPOSE:
To alter a snapshot in one of these ways:
* changing its storage characteristics
* changing its automatic refresh mode and times
SYNTAX:
ALTER SNAPSHOT [schema.]snapshot
[ PCTFREE integer | PCTUSED integer
| INITRANS integer | MAXTRANS integer
| STORAGE storage_clause ] ...
[ USING INDEX [ INITTRANS integer | MAXTRANS integer
| STORAGE storage_clause] ...
[REFRESH [FAST | COMPLETE | FORCE] [START WITH date] [NEXT date]]
where:
schema
is the schema containing the snapshot. If you omit schema, Oracle
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
Oracle uses to maintain the snapshot's data. See the PCTFREE,
PCTUSED, INITRANS, and MAXTRANS parameters of the CREATE TABLE
command.
STORAGE
changes the storage characteristics of the internal table that
Oracle uses to maintain the snapshot's data.
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.
FORCE
specifies a fast refresh if one is possible or
complete refresh if a fast refresh is not possible.
Oracle decides whether a fast refresh is possible at
refresh time.
If you omit the FAST, COMPLETE, and FORCE options,
Oracle 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.
USING INDEX
alters the storage characteristics for the index on a simple
snapshot.
PREREQUISITES:
The snapshot must be in your own schema or you must have ALTER ANY
SNAPSHOT system privilege.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must match the snapshot's creation label or you must satisfy one of
these criteria:
* If the snapshot's creation label is higher than your DBMS label,
you must have READUP and WRITEUP system privileges.
* If the snapshot's creation label is lower than your DBMS label,
you must have WRITEDOWN system privilege.
* If the snapshot's creation label and your DBMS label are
noncomparable, you must have READUP, WRITEUP, and WRITEDOWN system
privileges.
To change the storage characteristics of the internal table that
Oracle uses to maintain the snapshot's data, you must also have the
privileges to alter that table. For information on these
privileges, see the ALTER TABLE command.
SEE:
CREATE SNAPSHOT, DROP SNAPSHOT
ALTER SNAPSHOT LOG
ALTER SNAPSHOT LOG command
PURPOSE:
Changes the storage characteristics of a snapshot log.
SYNTAX:
ALTER SNAPSHOT LOG ON [schema.]table
[PCTFREE integer] [PCTUSED integer]
[INITRANS integer] [MAXTRANS integer]
[STORAGE storage_clause]
where:
schema
is the schema containing the snapshot log and its master table. If
you omit schema, Oracle 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.
PCTFR