APPENDIX B. DBA Command Reference

Note: Server Manager Line Mode and the SQL Worksheet have not been implemented for the beta release.

DBA Commands

• @

• ARCHIVE LOG

• CONNECT

• DESCRIBE

• DISCONNECT

• EXECUTE

• EXIT

• PRINT

• RECOVER

• REMARK

• SET

• SHOW

• SHUTDOWN

• SPOOL

• STARTUP

• VARIABLE

STARTUP OPEN 'V7'

@ (at symbol)

Purpose

Prerequisites

Syntax

where:

scriptname	The name of an operating system file that contains a script of statements recognized by Server Manager.

Line Mode Behavior

Usage Notes

Do not use the @ command alone within a script unless you specify the full path name of the script.

To run other scripts from within a script, you can use the @@ command. In the original script include the line @@second_scriptname to run another script called second_scriptname. The @@ command indicates that the second script is in the same directory as the original script.

Additional Information: For information about the standard file selection dialog box for your system, see your operating system-specific documentation.

ARCHIVE LOG

Purpose

Note: This command applies only to the current instance. To specify archiving for a different instance or for all instances in a Parallel Server, use the SQL command ALTER SYSTEM.

Prerequisites

Syntax

where:

LIST	Requests a display that shows the range of redo log files to be archived, the current log file group's sequence number, and the current archive destination (specified by either the optional command text or by the initialization parameter LOG_ARCHIVE_DEST).
	If you are using both ARCHIVELOG mode and automatic archiving, the display might appear like:

				Database log mode		Archive Mode

				Automatic archival		Enabled

				Archive destination		DISK9:ARCH

				Oldest online log sequence	30 

				Next log sequence to archive	33 

				Current log sequence		33

	Because the log sequence number of the current log group and the next log group to archive are the same, automatic archival has archived all log groups up to the current one.
	If you are using ARCHIVELOG but have disabled automatic archiving, the last three lines might look like:

				Oldest online log sequence		30 

				Next log sequence to archive		30 

				Current log sequence			33

	If you are using NOARCHIVELOG mode, the "next log sequence to archive" line is suppressed.
	The log sequence increments every time LGWR begins to write to another redo log file group; it does not indicate the number of logs being used. Every time an online redo log file group is reused, the contents are assigned a new log sequence number.
STOP	Disables automatic archival. If the instance is still in ARCHIVELOG mode and all redo log file groups fill, database operation is suspended until a redo log file is archived (for example, until you enter the command ARCHIVE LOG NEXT or ARCHIVE LOG ALL).
START	Enables automatic archiving. Starts the background process ARCH, which performs automatic archiving as required. If ARCH is started and a filename is supplied, the filename becomes the new default archive destination.
	ARCH automatically starts on instance startup if the initialization parameter LOG_ARCHIVE_START is set to TRUE.
NEXT	Manually archives the next online redo log file group that has been filled but not yet archived.
ALL	Manually archives all filled, but not yet archived, online log file groups.
integer	Causes archival of the online redo log file group with log sequence number n. You can specify any redo log file group that is still online. An error occurs if the log file cannot be found online or the sequence number is not valid. This option can be used to re-archive a log file group.
`destination'	Specifies the destination device or directory in an operating system. Specification of archive destination devices is installation-specific; see your platform-specific Oracle documentation for examples of specifying archive destinations. On many operating systems, multiple log files can be spooled to the same tape.
	If not specified in the command line, the archive destination is derived from the initialization parameter LOG_ARCHIVE_DEST. The command ARCHIVE LOG START 'destination' causes the specified device or directory to become the new default archive destination for all future automatic or manual archives. A destination specified with any other option is a temporary destination that is in effect only for the current (manual) archive. It does not change the default archive destination for subsequent automatic archives.

Additional Information: For information about specifying archive destinations, see your platform-specific Oracle documentation.

Usage Notes

Examples

ARCHIVE LOG START

Starts up the archiver process and begins automatic archiving, using the archive destination specified in LOG_ARCHIVE_DEST.

ARCHIVE LOG STOP 

Stops automatic archiving.

ARCHIVE LOG 1001 'DISK9:[TEMPARCH]TEMP' 

Archives the log file group with the sequence number 1001 to the destination specified. 'TEMP' specifies the prefix of the filename on the destination device; the remainder of the filename is dependent on the initialization parameter LOG_ARCHIVE_FORMAT, which specifies the filename format for archived redo log files.

CONNECT

Purpose

Prerequisites

Syntax

where:

username	Any valid Oracle username for the current database. It can be a null string. If neither username nor INTERNAL is specified, you are prompted for a username and password.
password	The password corresponding to the specified username. Password can be a null string.
instance-path	A valid specification for an instance/database combination. If an instance is specified, it becomes the current instance for the duration of the connection. It does not become the default instance for subsequent connections.
SYSOPER/ SYSDBA	The AS clause allows privileged connections by users who have been granted SYSOPER or SYSDBA system privileges, if using a password file, or their operating system equivalents, if using OS authentication.
/	Uses operating system authentication.
INTERNAL	Connects as username SYS via keyword INTERNAL. Should be used rarely and only by the DBA for certain maintenance purposes; requires operating system privileges. CONNECT INTERNAL is supported for backwards compatibility only.

Line Mode Behavior

SQL Worksheet Behavior

Usage Notes

CONNECT can be used without a DISCONNECT to connect to another username.

The connect is always accompanied by the opening of a cursor.

You can specify an instance path for a remote instance when connecting. If you attempt a connection to a remote instance, authentication occurs on the remote node, so you must have the appropriate privileges on the remote node.

Connecting as SYSOPER or SYSDBA over a Non-Secure Connection

• The server to which you are connecting must have a password file.

• You must be granted the SYSOPER or SYSDBA system privilege.

• You must connect using a username and password.

Connecting as SYSOPER or SYSDBA over a Local or Secure Connection

• You can connect using a password file, provided you meet the criteria outlined above.

• If the server is not using a password file, or you have not been granted SYSOPER or SYSDBA and are therefore not in the password file, your operating system name must be authenticated by the operating system for a privileged connection. This form of authentication is system-specific.

Examples

To connect to an instance on the current default node, enter:

CONNECT 

To connect to an instance on the current node as username SCOTT with password TIGER, enter:

CONNECT scott/tiger 

To use a password file to connect to an instance on the current node as a privileged user named SCOTT with password TIGER, enter:

CONNECT scott/tiger AS SYSDBA

Note that your default schema is now SYS, not SCOTT.

To perform the same connection using OS authentication, enter:

CONNECT / AS SYSDBA

To connect to an instance on a different node as username SCOTT with password TIGER, enter:

CONNECT scott/tiger@instance-path 

Connecting as INTERNAL

DESCRIBE

Purpose

Prerequisites

Syntax

where:

name	The name of the object to describe.

Usage Notes

DESCRIBE emp

Server Manager describes the table or view if a table or view with the name EMP exists. If no table or view with that name exists in the user's schema, Server Manager will search for a PL/SQL function, procedure, or package with that name and describe it.

The shorthand DESC is now equivalent to the command DESCRIBE. This means that DESC has been added to the list of Server Manager reserved words. If you have a database object named DESC, you need to place quotes around the name to show that it is an object name. For example:

INSERT INTO 'DESC' VALUES('onetwothree', 123); )

Note: Some versions of PL/SQL allow the user to describe a package by giving the package name, and some versions require the user to specify an object in the package to describe. Server Manager supports package description for those versions of PL/SQL which support this functionality.

Example

A command like

DESCRIBE scott.addemp 

produces output similar to the following example:

PROCEDURE SCOTT.ADDEMP (EMPNO INTEGER, ENAME VARCHAR2, SAL NUMBER(9,2)) 

The use of the object type is no longer required. If you do not specify an object type, Server Manager attempts to determine the object type. For the statement

DESCRIBE emp

Server Manager describes the table or view if a table or view with the name EMP exists.

If no table or view with that name exists in the user's schema, Server Manager searches for a PL/SQL function, procedure, or package with that name and describes it. For the statement

DESCRIBE payroll.emp

Server Manager looks for a table or view in schema PAYROLL with the name EMP.

If there is no PAYROLL schema or no EMP table/view in the PAYROLL schema, Server Manager will search the current schema for a package PAYROLL containing a function or procedure EMP.

If both a table EMP in schema PAYROLL and a package PAYROLL with function/procedure EMP in the current schema exist, the statement

DESCRIBE FUNCTION payroll.emp

This allows you to specify the type of the object you want to describe.

Server Manager now has the capability to describe both functions and procedures contained in packages. Previously only functions and procedures not contained in packages could be described.

Note: Some versions of PL/SQL allow the user to describe a package by giving the package name, and some versions require the user to specify an object in the package to describe. Server Manager supports package description for those versions of PL/SQL which support this functionality.

DISCONNECT

Purpose

Prerequisites

Syntax

Usage Notes

Example

DISCONNECT

EXECUTE

Purpose

Prerequisites

Syntax

Usage Notes

BEGIN 

	[PL/SQL BLOCK] 

END; 

You can reference any command line mode bind variables in PL/SQL statements by preceding the variable name with a colon. The example below illustrates the use of bind variables with the VARIABLE, EXECUTE, and PRINT commands.

Example

VARIABLE balance NUMBER

EXECUTE :balance := get_balance(34056) 

PRINT balance 

BALANCE 

---------- 

4678.24 

EXIT

Purpose

Prerequisites

Syntax

Line Mode Behavior

SQL Worksheet Behavior

Example

EXIT

PRINT

Purpose

Prerequisites

Syntax

where:

variable	The name of a variable defined by the VARIABLE command.

Usage Notes

If no variable name is specified all currently defined variables are printed.

Examples

PRINT balance

BALANCE 

---------- 

4687.24 

SET CHARWIDTH 10 

PRINT ename 

ENAME 

---------- 

SCOTT 

RECOVER

Purpose

Prerequisites

Syntax

UNTIL clause ::=

PARALLEL clause ::=

where:

DATABASE	Specifies recovering the entire database.
USING BACKUP CONTROL FILE	Specifies that a backup of the control file is being used instead of the current control file.
TABLESPACE tablespace	Specifies recovering a particular tablespace. Tablespace is the name of a tablespace in the current database. You may recover up to 16 tablespaces in one statement.
DATAFILE filename	Specifies recovering a particular datafile. You can specify any number of datafiles.
UNTIL CANCEL	Specifies an incomplete, cancel-based recovery. Recovery proceeds by prompting you with the suggested filenames of archived redo log files, and recovery completes when you specify CANCEL instead of a filename.
UNTIL CHANGE integer	Specifies an incomplete, change-based recovery. The integer is the number of the SCN following the last change to which you wish to recover. For example, if you want to restore your database up to the transaction with an SCN of 9, you would specify UNTIL CHANGE 10.
UNTIL TIME date	Specifies an incomplete, time-based recovery. Use single quotes, and use the following format:

	'YYYY-MM-DD:HH24:MI:SS'. 

SQL Worksheet Behavior

Usage Notes

To perform media recovery on a tablespace, the database must be mounted and open, and the tablespace must be offline.

To perform media recovery on a datafile, the database can remain open and mounted with the damaged datafiles offline (unless the file is part of the SYSTEM tablespace).

Before using the RECOVER command you must have restored good copies of the damaged datafile(s) from a previous backup. Be sure you can access all archived and online redo log files dating back to when that backup was made.

When another log file is required during recovery, a prompt suggests the names of files that are needed. The name is derived from the values specified in the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT. You should restore copies of the archived redo log files needed for recovery to the destination specified in LOG_ARCHIVE_DEST, if necessary. You can override the initialization parameters by setting the LOGSOURCE variable.

During recovery you can accept the suggested log name by hitting return, cancel recovery by entering CANCEL instead of a log name, or enter AUTO for automatic file selection without further prompting.

If you have enabled autorecovery (that is, SET AUTORECOVERY ON), recovery proceeds without prompting you with filenames. Status messages are displayed when each log file is applied.

When normal media recovery is done, a completion status is returned.

For more information on recovery and the RECOVER command, see the Oracle7 Server Administrator's Guide.

Examples

RECOVER DATABASE

RECOVER DATABASE UNTIL TIME 30-AUG-90:04:32:00 

RECOVER TABLESPACE ts_one, ts_two 

RECOVER DATAFILE 'data1.db' 

REMARK

Purpose

Prerequisites

Syntax

Usage Notes

REM must be the first non-blank character string in the line.

Examples

Examples of valid comments embedded in a SQL file follow:

REM    This command file is used to create a 

REM    database. Edit it to fill in file names 

REM    and sizes, and invoke it from line mode. 

REM 

REM 

CREATE DATABASE dbname .... 

		/* This is a SQL comment */ ... 

		... 

		; 

REMARKs are recognized by SQL*Plus, as well as by line mode. They are used to put comments between SQL statements, while SQL comments (/*...*/) are used to place comments within statements.

SET

Purpose

Prerequisites

Syntax

where:

APPINFO	Registers the Server Manager application through the database's DBMS_APPLICATION_INFO package (Oracle 7.2 or later). By default, the APPINFO function is set to OFF.
Application registration allows DBAs to see what software is currently running to better monitor resource utilization for database tuning. When APPINFO is turned on, application registration proceeds normally. If a SQL script is not being run, the default registration text string is "Oracle SQL Worksheet." Optionally, you can create a customized registration text string. If a script is being run through Server manager, the script name is used as the registration text string.

• SET APPINFO ON registers Server Manager using the text string "Oracle SQL Worksheet" , a customized text string, if one has been defined, or a SQL script name. APPINFO is automatically turned OFF if a database or system error occurs.

• SET APPINFO OFF turns off the function.

• SET APPINFO usertext replaces the default "Oracle SQL Worksheet" registration string with a customized string (usertext). Note: usertext must be a contiguous string unless it is enclosed within single or double quotation marks.

Examples:
	Text String Registers as:
	SET APPINFO abc abc
	SET APPINFO abc def abc
	SET APPINFO "abc def" abc def
	SET APPINFO 'abc def' abc def
	SET APPINFO "abc def Error
	SET APPINFO 'abc def Error

• To set APPINFO so that it is turned ON whenever you start Server Manager, enter the following command string at the operating system prompt:

>svrmgrl command="SET APPINFO ON"

Note: This procedure applies to Server Manager Line Mode only and not the SQL Worksheet.

AUTO- RECOVERY	Specifying ON causes the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery. No interaction is needed when AUTORECOVERY is set to ON, provided the necessary files are in the expected locations with the expected names. The filenames used when AUTORECOVERY is ON are derived from the values of the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT.
	OFF, the default option, requires that you enter the filenames manually or accept the suggested default filename given.
	CHARWIDTH integer Sets the column display width for CHAR data. If entered with no argument, returns the setting to the default. The default is 80; the range of values is operating system-specific.

Note: CHARWIDTH now has an absolute value range from 1-2000. Previously, the range was 1-999999. The actual range of values is operating system-specific.

COMPATIBILITY	Sets compatibility mode to V6, V7, or NATIVE. The compatibility mode setting affects the specification of character columns, integrity constraints, and rollback segment storage parameters. NATIVE matches the version of the database.
	CHAR Columns: When creating tables in Version 6 compatibility mode, CHAR columns are variable length. In Oracle7, such column definitions are fixed length.
	Integrity Constraints: In Version 6 compatibility mode, the Version 6 syntax is still recognized, and the Oracle7 syntax is disabled. For V6 mode, table constraints on CREATE TABLE statements are specified with V6 syntax:
	CREATE TABLE {UNIQUE | PRIMARY KEY} CONSTRAINT ....
	and specified constraints are disabled by default. For V7 mode, table constraints are specified with Oracle7 syntax:
	CREATE TABLE CONSTRAINT .... {UNIQUE | PRIMARY KEY}
	and they are enabled.
	Rollback Segment Parameters: Version 6 compatibility mode allows PCTINCREASE and MAXEXTENTS to be specified for rollback segments, as well as for other segments. Although the specifications are ignored, the syntax is allowed. (Use of these parameters is not recommended. They exist only for backward compatibility.)
	Bind Variables: Bind variables of type VARCHAR2 are given type CHAR in Version 6 compatibility mode.
	DATEWIDTH integer Sets the column display width for DATE data. If entered with no argument, the setting returns to the default. The default is 9; the range of values is operating system-specific.
ECHO	ON enables echoing of commands entered from command files. OFF, the default, disables echoing of commands.
FETCHROWS integer	Limits the number of rows returned by a query. Useful with ordered queries for finding the "top ten" items in a category, for example. Also used with unordered queries to find the "first n" records that satisfy a given criteria. integer must be in the range 1-999,999.
	In a SQL Worksheet the default is ECHO ON.
INSTANCE instance-path	Changes the default instance for your session to the specified instance path. Does not connect to a database. The default instance is used for commands when no instance is specified.
	Any commands preceding the first use of SET INSTANCE communicate with the default instance.
	To reset the instance to the default value for your operating system, you can either enter SET INSTANCE with no instance-name or SET INSTANCE LOCAL. See your operating system-specific Oracle documentation for a description of how to set the initial default instance.
	This command may only be issued when SQL*Net is running. You can use any valid SQL*Net connect string as the specified instance path. See your operating system-specific Oracle documentation for a complete description of how your operating system specifies SQL*Net connect strings. The maximum length of the instance path is 64 characters.
LOGSOURCE pathname	Specifies the location from which archive logs are retrieved during recovery. The default value is set by the LOG_ARCHIVE_DEST initialization parameter. Issuing the SET LOGSOURCE command without a pathname restores the default location.
	LONGWIDTH integer Sets the column display width for LONG data. If entered with no argument, the setting returns to the default. The default is 80; the range of values is operating system-specific.

Note: LONGWIDTH now has an absolute value range from 1-2000. Previously, the range was 1-999999. The actual range of values is operating system-specific.

	MAXDATA integer Sets the maximum data size. Indicates the maximum data that can be received in a single fetch during a SELECT statement. The default is 20480 bytes (20K). The maximum is operating system-specific.
	NUMWIDTH integer Sets the column display width for NUMBER data. If entered with no argument, the setting returns to the default. The default is 10; the range of values is operating system-specific.

Note: NUMWIDTH now has an absolute value range from 1-2000. Previously, the range was 1-999999. The actual range of values is operating system-specific.

RETRIES integer | INFINITE	Sets the number of tries that are attempted when the RETRY option is used with the STARTUP command (see "STARTUP" ). INFINITE, the default, specifies an infinite number of retries.
SERVEROUTPUT	ON enables debugging output from stored procedures that use DBMS_OUTPUT PUT and PUT_LINE commands. OFF disables output.
	You can specify the size in bytes of the message buffer using the syntax SIZE n. That is the total number of bytes of all messages sent that can be accumulated at one time. The minimum is 2,000 bytes. If the buffer fills before calls to the get-message routines make room for additional message bytes, an error is returned to the message-sending program.
STOPONERROR	Specifying ON indicates that if a command file is running and an error occurs, execution should terminate. OFF disables STOPONERROR.
TERMOUT	ON, the default, enables terminal output for SQL commands. OFF disables terminal output. Useful for preventing output to terminal when spooling output to files. Note that nothing appears on the terminal until SET TERMOUT ON is used.
TIMING	ON displays parse, execute, and fetch times (CPU and elapsed) for each SQL statement executed. OFF, the default, does not display timing information.

Examples

SET INSTANCE D:DEV-PROD

SET TIMING ON 

SET LONGWIDTH 132 

SET NUMWIDTH 20 

SET CHARWIDTH 5 

Either of the following commands can be used to revert to the initial default host:

SET INSTANCE 

SET INSTANCE LOCAL 

SHOW

Purpose

Prerequisites

Syntax

where:

ALL	Shows all settings except for ERRORS, PARAMETERS, and SGA.
APPINFO	Shows the current status (ON/OFF) and the application registration text.
AUTORECOVERY	Shows whether or not autorecovery is enabled.
CHARWIDTH	Shows the column display width for CHAR data.
COMPATIBILITY	Shows the sessions compatibility mode: V6, V7, or NATIVE.
DATEWIDTH	Shows the column display width for DATE data.
ECHO	Shows whether or not commands from command files are echoed.
ERRORS	Shows the errors generated from the last compilation of a procedure, package, or function, if any. This option displays the line number, column number, and the error message generated. Use the SHOW ERRORS command if line mode error 72 is returned after compiling a package.
	The SET CHARWIDTH command can be used to expand or truncate the display from the SHOW ERRORS command.
FETCHROWS	Shows the numerical limit set for the number of rows returned by a query.
INSTANCE	Shows the connect string for the default instance. SHOW INSTANCE returns the value LOCAL if you have not used SET INSTANCE or if you have used the LOCAL option of the SET INSTANCE command.
LOGSOURCE	Shows the current setting for archive log location. Displays DEFAULT if the default setting is in effect, as specified by the LOG_ARCHIVE_DEST initialization parameter.
LONGWIDTH	Shows the column display width for LONG data.
MAXDATA	Shows the maximum data size.
NUMWIDTH	Shows column display width for NUMBER data.
PARAMETERS	Displays the current values for one or more initialization parameters. You can use a string after the command to see a subset of parameters whose names include that string. For example, if you enter:

				SHOW PARAMETERS COUNT 

	you would see:

				NAME   	                       TYPE  	   VALUE 

				-------------------            -------   ----- 

 				db_file_multiblock_read_count  integer    12 

 				spin_count                     integer    0 

	The SHOW PARAMETERS command, without any string following the command, displays all initialization parameters.
RETRIES	Displays the number of retries that will be attempted when restarting an instance in parallel mode. (See the RETRY option in "STARTUP" .)
SERVEROUTPUT	Displays ON if output from stored procedures and functions is enabled. Otherwise, displays OFF. SERVEROUTPUT is reset to OFF if you issue a CONNECT command.
SGA	Displays information about the current instance's System Global Area.
SPOOL	If spooling is enabled, displays the name of the output spool file. Otherwise, displays OFF.
STOPONERROR	Displays whether or not errors encountered during execution of command files should stop execution of the file.
TERMOUT	Shows if output to the terminal is enabled.
TIMING	Shows whether or not the parse, execute, and fetch times (CPU and elapsed) for each SQL statement executed are shown.

Usage Notes

Examples

SHOW TIMING

returns a display such as:

Timing             OFF

SHOW ALL 

returns a display like:

Instance           local 

Spool              OFF 

Timing             OFF 

Termout            ON 

Echo               OFF 

Stoponerror        OFF 

Autorecovery       OFF

Logsource          <default>

Maxdata            20480 

Numwidth           10 

Charwidth          80 

Longwidth          80 

Datewidth          9 

Labwidth           32

Compatibility      NATIVE

Retries            infinite

Server Output      OFF

SHOW SGA 

returns a display like:

Total Shared Global Area         4612820  bytes 

Fixed Size                         36376  bytes 

Variable Size                    4445372  bytes 

Database Buffers                  122880  bytes 

Redo Buffers                        8192  bytes 

SHOW ERRORS PACKAGE BODY name 

returns a display like:

ERRORS FOR PACKAGE BODY name: 

LINE/COL		ERRORS 

---------------------------------------------------------------

... 

SHUTDOWN

Purpose

Prerequisites

Syntax

where:

ABORT	Proceeds with the fastest possible shutdown. Does not wait for calls to complete or users to disconnect. Does not close or dismount the database, but does shut down the instance. Requires instance recovery on next startup. You must use this option if a background process terminates abnormally.
IMMEDIATE	Does not wait for current calls to complete, prohibits further connects, and closes and dismounts the database. Finally, shuts down the instance. Does not wait for connected users to disconnect. Does not require instance recovery on next startup.
NORMAL	Waits for currently connected users to disconnect from the database, prohibits further connects, and closes and dismounts the database. Finally, shuts down the instance. Does not require instance recovery on next startup. NORMAL is the default option.

Attention: In Server Manager, you can have several separate connections open at any time in multiple windows. If you have any connections open, remember to close them before performing a shutdown in normal mode. Otherwise, the shutdown will not complete.

Usage Notes

Example

SHUTDOWN

Database closed. 

Database dismounted. 

Oracle instance shut down. 

SPOOL

Purpose

Prerequisites

Syntax

where:

filename	Any valid name for a spool file. If not specified, the file type or file extension is LOG. If a file by the specified name exists, an error is generated.
OFF	Closes the previously opened spool file.

Usage Notes

Additional Information: The default filename is operating system specific. Refer to your operating system-specific Oracle documentation for the default filename on your operating system. To see whether you are currently spooling, enter SHOW SPOOL.

Note: When long lines of text (usually from a table query) are written to a spool file or to a terminal, newline codes are inserted in the text if the lines exceed the maximum line length for the platform.

Example

To create a file named NOV2.LOG, enter:

SPOOL NOV2 

STARTUP

Purpose

Prerequisites

Syntax

MOUNT_OPTIONS clause ::=

where:

FORCE	Shuts down the current Oracle instance (if it is running) with SHUTDOWN mode ABORT, before restarting it. If the current instance is running and FORCE is not specified, an error results. FORCE is useful while debugging and under abnormal circumstances. It should not normally be used.
RESTRICT	Only allows Oracle users with the RESTRICTED SESSION system privilege to connect to the database. Later, you can use the ALTER SYSTEM command to disable the restricted session feature.
PFILE=filename	Causes the specified parameter file to be used while starting up the instance.
MOUNT	Mounts a database but does not open it.
OPEN	Mounts and opens the specified database.
NOMOUNT	Causes the database not to be mounted upon instance startup. Cannot be used with SHARED, EXCLUSIVE, PARALLEL, MOUNT, or OPEN.
RECOVER	Specifies that media recovery should be performed, if necessary, before starting the instance. STARTUP RECOVER has the same effect as issuing the RECOVER DATABASE command and starting an instance. Only complete recovery is possible with the RECOVER option.
	Recovery proceeds, if necessary, as if AUTORECOVERY is set to ON, regardless of whether or not AUTORECOVERY is enabled. If a redo log file is not found in the expected location, recovery continues as if AUTORECOVERY is disabled, by prompting you with the suggested location and name of the subsequent log files that need to be applied. See "Usage Notes" in "RECOVER" for a description of how to proceed with recovery when AUTORECOVERY is disabled.
	If recovery fails using the RECOVER option, the database remains mounted and closed.
database	The name of the database to mount or open. If no database name is specified, the database name is taken from the initialization parameter DB_NAME.
EXCLUSIVE	Signifies that the database can only be mounted and opened by the current instance (it cannot be opened simultaneously by multiple instances). Cannot be used with SHARED, PARALLEL, or NOMOUNT. If no mounting option is specified, EXCLUSIVE is assigned by default.
PARALLEL	Must be specified if the database is to be mounted by multiple instances concurrently. Cannot be used with EXCLUSIVE or NOMOUNT. Invalid if the initialization parameter SINGLE_PROCESS is set to TRUE.
SHARED	Synonym for PARALLEL.
RETRY	Specifies that opening the database should be attempted every five seconds if the instance is busy being recovered by another instance. When an instance is being recovered by another instance, the down instance cannot open the database until recovery is complete. If the database cannot be opened for any other reason, RETRY does not attempt to open the database again. This option is only available for instances operating in PARALLEL mode. The number of times RETRY attempts to start the database can be set with the SET RETRIES command (see "RETRIES" in "SET" ).

Examples

To start an instance using the standard parameter file, mount the default database in exclusive mode, and open the database, enter:

STARTUP 

or enter:

STARTUP OPEN databasename EXCLUSIVE 

To start an instance using the standard parameter file, mount the default database in parallel mode, and open the database, enter:

STARTUP PARALLEL 

STARTUP OPEN databasename PARALLEL 

To restart an instance that went down in parallel mode and may not yet have been recovered by other instances, use the RETRY option:

STARTUP PARALLEL RETRY 

To shut down the current instance, immediately restart it without mounting or opening, and allow only database administrators to connect, enter:

STARTUP FORCE NOMOUNT RESTRICT 

To start an instance using the parameter file TESTPARM without mounting the database, enter:

STARTUP PFILE=testparm NOMOUNT 

To shut down a particular database, immediately restart and open it in parallel mode, allow access only to database administrators, and use the parameter file MYINIT.ORA. enter:

STARTUP OPEN databasename PFILE=myinit.ora FORCE SHARED RESTRICT 

To start up an instance and mount but not open a database, you can use the following sequence of commands (the system's response is also shown):

CONNECT INTERNAL 

Connected. 

STARTUP NOMOUNT 

Oracle instance started. 

ALTER DATABASE MOUNT

Statement processed.

At this point, you could run a maintenance command and then open the database, as shown in the following commands:

ALTER DATABASE ARCHIVELOG;

Statement processed. 

ALTER DATABASE OPEN; 

Statement processed. 

VARIABLE

Purpose

Prerequisites

Syntax

where:

name	The name of the bind variable you wish to define.
type	The datatype of the bind variable. The valid datatypes are NUMBER, CHAR, and VARCHAR2. You can specify a length from 1 to 255 for CHAR, and from 1 to 2,000 for VARCHAR2. If you do not specify a length, CHAR and VARCHAR2 default to the current setting of CHARWIDTH.

Usage Notes

Warning: CHAR variables are fixed length, padded with blanks. VARCHAR2 variables are variable length. You must use CHAR when passing a character variable to a PL/SQL procedure that defines a CHAR parameter. Otherwise, a conversion error results. A PL/SQL procedure that expects a VARCHAR2 variable, on the other hand, will automatically convert a CHAR parameter to the proper form. (In general, it is a good idea to avoid the use of CHAR variables in PL/SQL procedures unless blank-padding is an absolute requirement.)

Note: In V6 compatibility mode, a CHAR variable is variable length, rather than fixed.

Examples

VARIABLE balance NUMBER

VARIABLE ename CHAR(20)