Oracle Enterprise Manager Administrator's Guide Release 1.5 A57694-01 |
|
This appendix describes the Server Manager DBA commands available in line mode. These commands can also be used in a SQL Worksheet, although some of these commands behave differently when used in a worksheet.
The DBA commands described in this appendix are:
If a reserved word is used as an object name in a DBA command, it must be enclosed in quotes. For example, to start up a database that is named with the reserved word V7, issue the command as follows:
STARTUP OPEN 'V7'Run scripts containing SQL, PL/SQL, and DBA commands.
You must have previously created the script and stored it as an operating system file.
@ command ::=
where:
If you omit the scriptname argument, you for are prompted for the script name.
This command enables you to run scripts of statements. To insert Comments in the script, use the REMARK command described in REMARK on page -17 . To spool the output, use the SPOOL command before running or within the script. The SPOOL command is described in SPOOL on page -30.
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.Start or stop automatic archiving of online redo log files, manually (explicitly) archive specified redo log files, or display information about redo log files.
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.
You must be connected to an open Oracle database as INTERNAL, SYSOPER, or SYSDBA.
ARCHIVE LOG command ::=
where:
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.
ARCH automatically starts on instance startup if the initialization parameter LOG_ARCHIVE_START is set to TRUE.
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.If an online redo log file group fills and none are available for reuse, database operation is suspended. The condition can be resolved by archiving a log file group.
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 to a database using the specified username.
Only valid username/password combinations can successfully connect. The AS clause allows users to connect to an instance with a default schema of SYS for database administration. Users connecting as SYSOPER or SYSDBA must have the necessary privileges to access the SYS schema.
CONNECT command ::=
where:
If you omit the password, you are prompted for one. If you omit both the username and password, you are prompted for both.
If you omit the username argument, the Connect dialog box displays.
If only the Oracle username is specified, the password is requested using the prompt "Password:". The entered password is not echoed.
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.
Non-Secure ConnectionConnecting as SYSOPER or SYSDBA over a
To connect to Oracle as a privileged user over a non-secure connection, you must satisfy the following conditions:
For information about creating a password file, see the Oracle7 Server Concepts and the Oracle7 Server Administrator's Guide, or Secure ConnectionConnecting as SYSOPER or SYSDBA over a Local
To connect to Oracle as a privileged user over a local or a secure connection, you must satisfy either of the following sets of conditions:
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
Using Server Manager you can connect as INTERNAL to a release 7.0 database. CONNECT INTERNAL is supported for backwards compatibility only. For information on how to use CONNECT INTERNAL, refer to your Oracle7 Server Administrator's Guide.
Describe a function, package, package body, procedure, table, or view.
You must be currently connected to a database.
DESCRIBE command ::=
where:
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 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); )
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.
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.
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 from an Oracle server.
You must be currently connected to a database.
DISCONNECT command ::=
Upon disconnection, line mode reverts to the current default host machine after closing all open cursors and committing any uncommitted transactions.
DISCONNECT
Executes a one-line PL/SQL statement.
You must be currently connected to a database. You must also have privileges to use any stored procedures, packages, package variables, and functions referenced with this command.
EXECUTE command ::=
You can execute only one line of PL/SQL code with the EXECUTE command. If you wish to execute a PL/SQL block with many lines, you must use the format:
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.
VARIABLE balance NUMBER
EXECUTE :balance := get_balance(34056) PRINT balance BALANCE ---------- 4678.24
Exits Server Manager line mode or closes a SQL Worksheet.
None.
EXIT command ::=
The EXIT command leaves line mode unconditionally, commits the current transaction, and returns to the operating system prompt.
The EXIT command commits the current transaction and closes the worksheet.
EXIT
Print the value of a variable defined with the VARIABLE command.
None.
PRINT command ::=
where:
Bind variables referenced with the print command do not need to be preceded by a colon. You can use the PRINT command to display variables defined only in the current line mode session. The SET CHARWIDTH and SET NUMWIDTH commands can affect the display of the PRINT command.
If no variable name is specified all currently defined variables are printed.
PRINT balance
BALANCE ---------- 4687.24 SET CHARWIDTH 10 PRINT ename ENAME ---------- SCOTT
Performs media recovery on one or more tablespaces, one or more datafiles, or the entire database.
You must be connected to the Oracle server as INTERNAL, SYSOPER, or SYSDBA. You cannot use the RECOVER command when connected via the multi-threaded server.
RECOVER command ::=
UNTIL clause ::=
PARALLEL clause ::=
where:
The PARALLEL keyword overrides the RECOVERY_PARALLELISM initialization parameter. The number specified with the PARALLEL keyword is the number of recovery processes used to apply redo entries to datafiles.
In a SQL Worksheet, if you issue the RECOVER command without arguments, Server Manager brings up the Recover dialog box.
To perform media recovery on an entire database (all tablespaces), the database must be mounted EXCLUSIVE and closed.
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.
RECOVER DATABASE
RECOVER DATABASE UNTIL TIME 30-AUG-90:04:32:00 RECOVER TABLESPACE ts_one, ts_two RECOVER DATAFILE 'data1.db'
Enter a Comment, typically in SQL script files.
None.
REMARK command ::=
Primarily for batch use of line mode. The Comment is ignored by line mode and by Oracle. REMARK can be shortened to REM.
REM must be the first non-blank character string in the line.
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 or change characteristics of the current command line mode session.
None.
SET command ::=
See next page for sytax diagram.
where:
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
OFF, the default option, requires that you enter the filenames manually or accept the suggested default filename given.
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.
In a SQL Worksheet the default is ECHO ON.
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.
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.
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 settings currently in effect.
None.
SHOW command ::=
where:
The SET CHARWIDTH command can be used to expand or truncate the display from the SHOW ERRORS command.
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.
SHOW with no arguments is the same as SHOW ALL.
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 --------------------------------------------------------------- ...
Shut down a currently running Oracle instance, optionally closing and dismounting a database.
You must be connected to a database as INTERNAL, SYSOPER, or SYSDBA. You cannot be connected via a multi-threaded server.
SHUTDOWN command ::=
where:
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.
SHUTDOWN
Database closed. Database dismounted. Oracle instance shut down.
Enable or disable spooling of output to a specified file.
None.
SPOOL command ::=
where:
If you do not specify a file, Server Manager prompts you for a filename.
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.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.
To create a file named NOV2.LOG, enter:
SPOOL NOV2
Start an Oracle instance with several options, including mounting and opening a database.
You must be connected to a database as INTERNAL, SYSOPER, or SYSDBA. You cannot be connected via a multi-threaded server.
STARTUP command ::=
MOUNT_OPTIONS clause ::=
where:
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. Refer to "RECOVER" for Usage Notes on page -16 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.
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.
Declare a bind variable for use in the current session with the EXECUTE or PRINT command, or for use with a PL/SQL block.
None.
VARIABLE command ::=
where:
Bind variables defined with the VARIABLE command exist until the end of the session. Variables defined in your session cannot be accessed or changed by a different session.
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.)
In V6 compatibility mode, a CHAR variable is variable length, rather than fixed.
VARIABLE balance NUMBER
VARIABLE ename CHAR(20)