Oracle for Sysadmins

Set the 'EDITOR' variable for the user 'oracle'. Doing so will allow you to edit your sqlplus commands using a reasonable editor. After executing a piece of SQL in sqlplus, just type 'edit' or 'ed' and you'll be transported to your editor. In vi just type :q to get back to the sqlplus prompt.


Create an oracle environment file in your home directory and have Oracle execute it every time you start sqlplus. Oracle will execute a command file called login.sql if it finds one in the path defined by $SQLPATH. For DBA activity you can format commonly used columns to display in a more user friendly way e.g.

set linesize 200
col file_name for a40
col tablespace_name for a20

I store this file in $HOME/env/oracle and put this directory in $SQLPATH

Other things in the login.sql

• set timing on Automatically print the duration of a sqlplus command.
  

You can store your DBA scripts in the $SQLPATH too.

Supplementry packages

You'll want to add in these packages to allow you to monitor the database

• statspack - sample counters in the database then report on them. Very useful for performance analysis
  
• dbms_space
  

Statspack

Install the statspack tools

SQL>  @?/rdbms/admin/spcreate

Ensure timed statistics is set to true

SQL> show parameter timed_statistics

SQL> exec statspack.snap

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.57
SQL> exec statspack.snap

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.20
SQL> @$ORACLE_HOME/rdbms/admin/spreport

If you ever need to get rid of statspack (sometimes useful if the instll crashed part way through) just run the script

SQL>  @?/rdbms/admin/spdrop