Tuesday, 27 November 2007

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


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


Anonymous said...

I used to be recommended this website by means of my cousin.
I am no longer certain whether or not this submit is
written through him as no one else know such targeted approximately my trouble.
You're wonderful! Thank you!
Look at my blog post my computer is slow

Anonymous said...

I don't even know how I ended up here, but I thought this post was good. I do not know who you are but certainly you are going to a famous blogger if you aren't already ;
) Cheers!
my site :: Work from home jobs

Anonymous said...

Thanks for a marvelous posting! I certainly enjoyed reading it, you happen to be a great
author.I will make sure to bookmark your blog and definitely will come
back down the road. I want to encourage yourself to continue
your great job, have a nice afternoon!
Also visit my web page ... how to download movies

Anonymous said...

Your style is very unique compared to other folks I have read stuff
from. Thanks for posting when you have the opportunity,
Guess I'll just bookmark this blog.
My blog :: http://www.yourtobaccosstore.com/15-erinmore

Anonymous said...

What's up every one, here every person is sharing such familiarity, so it's good
to read this web site, and I used to pay a quick visit this webpage daily.
Also visit my web-site ; golden virginia

Anonymous said...

Whats up this is kinda of off topic but I was wondering if blogs use WYSIWYG editors or if you
have to manually code with HTML. I'm starting a blog soon but have no coding skills so I wanted to get advice from someone with experience. Any help would be greatly appreciated!
Feel free to surf my site acne products

Anonymous said...

Good day! I could have sworn I've been to this blog before but after reading through some of the post I realized it's new to me.
Nonetheless, I'm definitely happy I found it and I'll
be bookmarking and checking back often!
Feel free to surf my web page ; http://akator.net