Oracle Database Utilities

Server Manager

The Server Manager (SVRMGR23.EXE in Oracle7 and SVRMGRL.EXE in Oracle8i) is a command database adminstration tool. This tool is generally used to perform the most basic of database administration functions.

Type svrmgrl help=y to get a help screen which looks something as shown below.

Oracle Server Manager Release 3.0.3.0.0 - Production

(c) Copyright 1997, Oracle Corporation.  All Rights Reserved.

Personal Oracle8 Release 8.0.3.0.0 - Production
With the Partitioning option
PL/SQL Release 8.0.3.0.0 - Production

The following are SIMPLIFIED syntax descriptions. For complete syntax
descriptions, please refer to the Oracle Server Manager User's Guide.

STARTUP      [DBA] [FORCE] [PFILE=filespec] [EXCLUSIVE | SHARED]
             [MOUNT dbname | OPEN dbname] [NOMOUNT]

SHUTDOWN     [NORMAL | IMMEDIATE | ABORT]

MONITOR      For graphical modes only, bring up a monitor

ARCHIVE LOG  [START] [STOP] [LIST] [NEXT] [] [ALL] ['destination']

RECOVER      { [DATABASE [MANUAL] ] | [TABLESPACE ts-name [,tsname]] }

CONNECT      [username [/password] ] [INTERNAL] ['@'instance-spec]
DISCONNECT

SET          options: INSTANCE, ECHO, TERMOUT, TIMING, NUMWIDTH, CHARWIDTH
SHOW         LONGWIDTH, DATEWIDTH, AUTOPRINT and for SHOW: ALL, SPOOL

EXIT
REM

Use of Line Mode and not the GUI Tool

Execute a script in the server manager in line mode from an operating system shell as shown below.

svrmgrl command=@script.sql OR svrmgrl @scipt.sql

Server Manager in Command Mode

Any SQL DDL, SQL DML and PL/SQL code can be executed from within the server manager program.

General Server Manager Commands

The @ Command

Execute a script stored as an operating system file. Use @@ to execute a script which calls other scripts.

The ARCHIVE LOG Command

Used to control automatic archiving or redo log files.

ARCHIVE LOG	START TO 'location'
		STOP
		NEXT
		ALL
		LIST
		integer

The CONNECT Command

The CONNECT command logs the user into a local or remote database.

CONNECT username/password
CONNECT username/password@database instance alias (SQL*Net)
CONNECT username/password AS SYSOPER or SYSDBA
CONNECT internal/<password>

The OSOPER role permits the following with the RESTRICTED SESSION privelage : STARTUP, SHUTDOWN, ALTER DATABASE, OPEN/MOUNT, ALTER DATABASE BACKUP, ARCHIVE LOG and RECOVER.

The OSDBA role has access to all system privelages with the ADMIN OPTION and the OSOPER role aswell.

The REMOTE_LOGIN_PASSWORDFILE must be set in the parameters file as follows.

The RECOVER Command

RECOVER performs database, tablespace or datafile media recovery. RECOVER is only accessible as SYSDBA, SYSOPER or INTERNAL. Also the RECOVER command cannot be used in a multi-threaded server database.

RECOVER [AUTOMATIC] [FROM 'location']
	  [DATABASE] [ [UNTIL CANCEL | UNTIL TIME date | UNTIL CHANGE n | USING BACKUP CONTROL FILE ] ... ]
	| [TABLESPACE 'tablespace' [, 'tablespace' ...]]
	| [DATABASE 'filename' [, 'filename' ...]]
	| [LOGFILE 'logfile']
	| [CONTINUE [DEFAULT]]
	| [CANCEL]
[PARALLEL parallel clause]
;

The SET Command

The SET command is used to alter the environment of a session.

SET	AUTORECOVERY ON/OFF
	CHARWIDTH integer
	COMPATIBILITY 6,7,8
	DATEWIDTH integer
	ECHO ON/OFF
	INSTANCE alias/local
	LOGSOURCE path
	LONGWIDTH integer
	MAXDATA integer
	NUMWIDTH integer
	RETRIES integer/infinite
	SERVEROUTPUT ON/OFF size
	STOPONERROR ON/OFF
	TERMOUT ON/OFF
	TIMING ON/OFF

The SHOW Command

Shows the current value of all SET commands plus ERRORS, PARAMETERS, SGA and SPOOL parameters.

SHOW	AUTORECOVERY
	CHARWIDTH
	COMPATIBILITY
	DATEWIDTH
	ECHO
	ERRORS previously compiled PL/SQL procedure, function or package errors
	INSTANCE
	LOGSOURCE
	LONGWIDTH
	MAXDATA
	NUMWIDTH
	PARAMETERS instance initialisation parameters
	RETRIES
	SERVEROUTPUT
	STOPONERROR
	TERMOUT
	TIMING
	ERRORS
	PARAMETERS
	SGA system global area for the current instance
	SPOOL

The STARTUP Command

STARTUP starts the Oracle instance, mounts and opens the database.

STARTUP		FORCE
		RESTRICT
		PFILE=filename
		MOUNT
		NOMOUNT
		OPEN
		RECOVER
		database_name
		EXCLUSIVE
		PARALLEL
		SHARED
		RETRY

The SHUTDOWN Command

Shutdown the database.

SQL*Plus

SQL*Plus is a command line direct SQL connection to the database. SQL commands can be executed from within SQL*Plus.

The SQL Buffer

The SQL Buffer stores the most recent SQL command or PL/SQL block. r, run or / will re-run the previous command. Type L or list to show the the contents of the buffer. The input to the SQL Buffer can be terminated using /, ; or pressing the Enter key twice. The - is the line continuation character.

SQL*Plus Commands

CommandDetails
@ Runs a command file.
@@ Runs command file nested inside another command file.
/ Executes.
accept Reads command line and stores into variable.
append Adds text to end of line.
break Format change of list of current break definition.
btitle Current btitle definition or places header at bottom of each report page.
change Changes current line buffer text.
clear Resets for specified option.
column Columns display format.
compute Compuational specification.
connect Connect username/password.
copy Copy data from query to table.
define Defines a variable.
del Deletes specified buffer lines.
describe Describe an object.
disconnect Commit and disconnect from database.
edit Operating system editor invocation.
execute Runs a PL/SQL statement.
exit Terminates SQL*Plus.
get Load operating system file into buffer.
help  .
host Executes operating system command from SQL*Plus.
input Adds specified number of lines after current line in the buffer.
list List specified number of buffer lines.
pause Waits for Enter key to be pressed.
print Display variable value.
prompt Sends message or specified line to terminal.
remark Comment line.
repfooter Report page footer format.
repheader Report page header format.
run Lists and runs current buffer contents.
save Save buffer to file.
set Change a system environment variable.
show Show local or environment variable value.
spool Stores query results to a file or sends to the printer.
sqlplus Executes SQL*Plus from the operating system.
start Runs a command file.
store Saves current SQL*Plus environment settings to a file.
timing Records timing data.
title Shows or sets report header foratting details.
undefine Removes variable definitions.
variable Declares PL/SQL referancable variable.
whenever oserror Exit SQL*Plus on operating system error.
whenever sqlerror Exit SQL*Plus on SQL or PL/SQL error.

The SET Command

SET		CHARWIDTH integer
		DATEWIDTH integer
		ECHO ON/OFF
		LONG integerWIDTH
		MAXDATA integer
		NUMWIDTH integer
		SERVEROUTPUT ON/OFF size
		STOPONERROR ON/OFF
		TERMOUT ON/OFF
		TIMING ON/OFF
		VERIFY ON/OFF
		AUTOTRACE ON/OFF EXPLAIN

PRODUCT_USER_PROFILE Table

The PRODUCT_USER_PROFILE table can be used to disable some SQL*Plus commands.

Command Line SQLPlus

sqlplus username/password@connect @file p1, p2, ..., pn. connect is a database connection alias through SQLNet. file is a SQL command file containing SQL commands. p1, p2, ..., pn is a sequence of parameters into SQL*Plus.

SQL*Loader

SQL*Loader loads data from files into a database. Two types of loading are available. Type sqlldr help=y to see the sqlldr help screen as shown below.

userid				ORACLE username/password
control				Control file name
log				Log file name
bad				Bad file name
data				Data file name
discard				Discard file name
discardmax			Number of discards to allow
skip				Number of logical records to skip
load				Number of logical records to load
errors				Number of errors to allow
rows				Number of rows in conventional path bind array or between direct path data saves
bindsize			Size of conventional path bind array in bytes
silent				Suppress messages during run (header,feedback,errors,discards,partitions)
direct				use direct path
_synchro			internal testing
parfile				parameter file: name of file that contains parameter specifications
parallel			do parallel load
file				File to allocate extents from
skip_unusable_indexes		disallow/allow unusable indexes or index partitions
skip_index_maintenance		do not maintain indexes, mark affected indexes as unusable
commit_discontinued		commit loaded rows when load is discontinued
_display_exitcode		Display exit code for SQL*Loader execution

Direct Path Load

Writes directly to datafiles bypassing database overhead of SQL insertion, redo logs and rollback provision.

Conventional Path Load

Uses SQL insert statements, tries to fill partially filled blocks and includes redo logs and rollback provision. The conventional path loading method is much slower than a direct path load.

SQL*Loader Files

The Control File

Data format, datafile location and table to be loaded into. The data to be loaded can be included into the control file. The Control File has parameters as detailed below.

SQL*Loader Command Line

Command line parameters will override those in the parameter file.

For example, sqlldr USERID=system/manager CONTROL=loadfile.ctl LOG=logfile.log.

... OR ...

sqlldr parfile=customers.par data='CUSTOMERS.DAT'

The CTL File

LOAD DATA
INTO TABLE SCOTT.CUSTOMERS TRUNCATE
WHEN NAME(1) = 'A' AND STATE = 'NY'

(
	NAME	POSITION(001:032) CHAR(32) TERMINATED BY WHITESPACE,
	ADDRESS	POSITION(033:160) CHAR(128) TERMINATED BY WHITESPACE,
	STATE	POSITION(161:162) CHAR(2) TERMINATED BY WHITESPACE,
	BALANCE	POSITION(163:173) INTEGER EXTERNAL
)

The PAR File

userid		= scott/tiger
control		= customers.ctl
log		= customers.log
bad		= customers.bad
discard		= customers.dsc
discardmax	= 2
errors		= 0
parallel	= TRUE
direct		= TRUE

Generating SQL*LDR Control Files Automatically See Automating SQL*LDR

Below is a stored procedure which can be used to generate SQL*Ldr control files from database meta-data.

PROCEDURE SQLLDR
(
	 pTABLE IN VARCHAR2 DEFAULT NULL
	,pTYPE IN VARCHAR2 DEFAULT 'TAB_SEPARATED'
	,pOPTION IN VARCHAR2 DEFAULT '' 		--INSERT is default, otherwise APPEND,REPLACE,TRUNCATE
	,pDATA IN VARCHAR2 DEFAULT '/u0/home/oracle/scripts/importDB/split/'
	,pCTL IN VARCHAR2 DEFAULT '/u0/home/oracle/scripts/importDB/ctl/'
)
AS

	TYPE cCURSOR IS REF CURSOR;
	lCURSOR cCURSOR;
	lTABLE_NAME VARCHAR2(30);
	lCOLUMN_NAME VARCHAR2(30);
	lDATA_TYPE VARCHAR2(106);
	lDATA_LENGTH NUMBER;
	lDATA_PRECISION NUMBER;
	lDATA_SCALE NUMBER;

	lPOSITION INTEGER DEFAULT 0;
	lBUFFER VARCHAR2(1024);
	lFIRST BOOLEAN DEFAULT TRUE;
	lFH UTL_FILE.FILE_TYPE;

BEGIN

	dbms_output.put_line(pTABLE||' '||pDATA||' '||pCTL);

	IF NOT pDATA IS NULL AND NOT pCTL IS NULL AND NOT pTABLE IS NULL THEN

		lFH := UTL_FILE.FOPEN (pCTL, pTABLE||'.ctl', 'w');

		IF UTL_FILE.IS_OPEN (lFH) = TRUE THEN

			UTL_FILE.PUT_LINE (lFH, 'LOAD DATA');
			UTL_FILE.PUT_LINE (lFH, 'INFILE '''||pDATA||pTABLE||'''');
			UTL_FILE.PUT_LINE (lFH, 'INTO TABLE '||pTABLE);
			IF pOPTION='INSERT' OR pOPTION='APPEND' OR pOPTION='REPLACE' OR pOPTION='TRUNCATE' THEN
				UTL_FILE.PUT_LINE (lFH, pOPTION);
			END IF;

			IF pTYPE = 'TAB_SEPARATED' THEN
				UTL_FILE.PUT_LINE (lFH,'FIELDS TERMINATED BY "	"');
			END IF;

			UTL_FILE.PUT_LINE (lFH, 'TRAILING NULLCOLS');

			UTL_FILE.PUT_LINE (lFH, '(');

			OPEN lCURSOR FOR 'SELECT table_name, column_name, data_type, data_length, data_precision, data_scale FROM SYS.DBA_TAB_COLUMNS WHERE table_name='''||pTABLE||''' ORDER BY COLUMN_ID';
			LOOP

				FETCH lCURSOR INTO lTABLE_NAME,lCOLUMN_NAME,lDATA_TYPE,lDATA_LENGTH,lDATA_PRECISION,lDATA_SCALE;
				EXIT WHEN lCURSOR%NOTFOUND;

				IF pTYPE = 'POSITIONAL' AND lDATA_TYPE = 'NUMBER' AND (lDATA_PRECISION IS NULL OR lDATA_SCALE IS NULL) THEN
					DBMS_OUTPUT.PUT_LINE ('All Oracle NUMBER must be declared with precision or precision,scale');
					RETURN;
				END IF;
					
				IF lFIRST THEN
					UTL_FILE.PUT (lFH, CHR(9)||' ');
					lFIRST := FALSE;
				ELSE
					UTL_FILE.PUT (lFH, CHR(9)||','); 
				END IF;

				IF pTYPE = 'POSITIONAL' THEN
	
					SELECT column_name||' POSITION('||TO_CHAR(lPOSITION+1)||':'||
	
					DECODE
					(
						 data_type
						,'NUMBER'
							,DECODE
							(
							 	data_scale
								,0
								,TO_CHAR(lPOSITION+data_precision)||') '
								,TO_CHAR(lPOSITION+data_precision+1)||') '
							)
						,'FLOAT',TO_CHAR(lPOSITION+data_precision)||') '
						,'DATE',TO_CHAR(lPOSITION+data_length+1)||') '
						,TO_CHAR(lPOSITION+data_length)||') '
					)|| 
		
					DECODE
					(
						 data_type
						,'VARCHAR2','CHAR('||data_length||')'
						,'CHAR','CHAR('||data_length||')'
						,'NUMBER',DECODE(NVL(data_scale,0),0,'INTEGER','FLOAT')
						,'FLOAT','FLOAT'
						,'DATE','DATE "YYYYMMDD"'
					)||
	
					DECODE
					(
						 data_type
						,'VARCHAR2',' TERMINATED BY WHITESPACE NULLIF '||column_name||'=BLANKS'
						,'CHAR',' TERMINATED BY WHITESPACE NULLIF '||column_name||'=BLANKS'
						,'NUMBER',' EXTERNAL DEFAULTIF '||column_name||'=BLANKS'
						,'FLOAT',' EXTERNAL DEFAULTIF '||column_name||'=BLANKS'
						,'DATE',' TERMINATED BY WHITESPACE "REPLACE(:'||column_name||', ''00000000'', ''19000101'')"'
					)

					into lBUFFER
					FROM SYS.DBA_TAB_COLUMNS
					WHERE TABLE_NAME = lTABLE_NAME AND COLUMN_NAME = lCOLUMN_NAME
					ORDER BY COLUMN_ID;

				ELSIF pTYPE = 'TAB_SEPARATED' THEN

					SELECT column_name||' '||
	
					DECODE
					(
						 data_type
						,'VARCHAR2','CHAR('||data_length||')'
						,'CHAR','CHAR('||data_length||')'
						,'NUMBER',DECODE(NVL(data_scale,0),0,'INTEGER','FLOAT')
						,'FLOAT','FLOAT'
						,'DATE','DATE "YYYY-MM-DD HH24:MI:SS"'
					)||

					DECODE
					(
						 data_type
						--,'VARCHAR2',	' NULLIF '||column_name||'=BLANKS'
						--,'CHAR',	' NULLIF '||column_name||'=BLANKS'
						,'NUMBER',	' EXTERNAL DEFAULTIF '||column_name||'=BLANKS'
						,'FLOAT',	' EXTERNAL DEFAULTIF '||column_name||'=BLANKS'
						--,'DATE',	' DEFAULT IF '||column_name||'=BLANKS'
					)

					into lBUFFER
					FROM SYS.DBA_TAB_COLUMNS
					WHERE TABLE_NAME = lTABLE_NAME AND COLUMN_NAME = lCOLUMN_NAME
					ORDER BY COLUMN_ID;

				END IF;

				UTL_FILE.PUT_LINE (lFH, lBUFFER);
				IF lDATA_TYPE = 'NUMBER' OR lDATA_TYPE = 'FLOAT' THEN
					lPOSITION := lPOSITION + lDATA_PRECISION;
					IF lDATA_TYPE = 'NUMBER' AND lDATA_SCALE != 0 THEN
						lPOSITION := lPOSITION + 1;
					END IF;
				ELSIF lDATA_TYPE = 'DATE' THEN
					lPOSITION := lPOSITION + lDATA_LENGTH + 1;
				ELSE
					lPOSITION := lPOSITION + lDATA_LENGTH;
				END IF;

			END LOOP;
			CLOSE lCURSOR;

			UTL_FILE.PUT_LINE (lFH, ')');

		END IF;
		UTL_FILE.FCLOSE (lFH);

	END IF;

EXCEPTION WHEN OTHERS THEN
	CLOSE lCURSOR;
	IF UTL_FILE.IS_OPEN (lFH) = TRUE THEN
		UTL_FILE.FCLOSE (lFH);
	END IF;
	DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
	RAISE;
END;