SQL

Environmental consistency .. is that even a real phrase?

Whether it is or isn’t doesn’t really matter… what I mean by environmental consistency is basically just making your life easier by installing, configuring, testing, developing, etc, the same way. This is one of the reasons why I love Response Files in Oracle. Another thing we do pretty well around here is configuring our environment variables the same.

I have a .kshrc and a .profile that I copy to every Oracle DB Zone and the only parameter I edit is the ORACLE_SID. I’m sure there is still room for improvement here — off the topic of my head, I guess I could grep the /var/opt/oracle/oratab to find the SID and put that in for ORACLE_SID — but here is the way we set things up.

[oracle: /oracle] cat .profile
stty istrip
stty erase

# Display user, SID, and current working directory as the command prompt.
export PS1=’[$LOGNAME: $PWD] ‘
. ./.kshrc

And we do the same thing with our .kshrc. We try to references environment variables whenever possible. The less we hardcode, the less we have to change when we setup a new machine!

[oracle: /oracle] cat .kshrc
export EDITOR=vi
alias l=’ls -ltr’
alias bdump=’cd /oracle/$ORACLE_SID/oratrace/bdump’
alias arch=’cd /oracle/$ORACLE_SID/oraarch’
alias udump=’cd /oracle/$ORACLE_SID/oratrace/udump’
alias scrp=’cd /group/oracle/sh’
alias sql=’cd /group/oracle/sql’
alias par=’cd $HOME/scripts/parms’
alias out=’cd /RMAN/scripts/logs/$ORACLE_SID’
alias dbs=’cd $ORACLE_HOME/dbs’
alias net=’cd $ORACLE_HOME/network/admin’
alias alert=’vi /oracle/$ORACLE_SID/oratrace/bdump/alert_$ORACLE_SID.log’
alias oraenv=’. $ORACLE_HOME/bin/oraenv’
alias ohome=’cd $ORACLE_HOME’
alias tail_alert=’tail -f /oracle/$ORACLE_SID/oratrace/bdump/alert_${ORACLE_SID}.log’
alias alert500=’tail -500 /oracle/$ORACLE_SID/oratrace/bdump/alert_${ORACLE_SID}.log | more’
alias rdbms=’cd $ORACLE_HOME/rdbms/admin’
alias sw=’cd /group/software/oracle/stage/102_64′


My favorite part out of this whole mess is the SQLPATH variable. This is a great variable that lets you execute SQL scripts from a different location by simply using “@” as if it were in your current working directory.

According to the SQL*Plus User’s Guide and Reference, SQLPATH is:

Environment variable or Windows registry entry to specify the location of SQL scripts. SQL*Plus searches for SQL scripts, including login.sql, in the current directory and then in the directories specified by SQLPATH, and in the subdirectories of SQLPATH directories. SQLPATH is a colon separated list of directories. There is no default value set in UNIX installations.

In Windows, SQLPATH is defined in a registry entry during installation. For more information about the SQLPATH registry entry, see SQLPATH Registry Entry.

What I do is put my most-commonly used scripts into the directory defined in SQLPATH and then I can call it from anywhere. Obviously this has to be a directory that is accessible across zones (servers)

Keep in mind Oracle will FIRST look at your current working directory and then query the SQLPATH. So if you have a script by the same name in each directory you’ll drive yourself nuts when it isn’t working as you’d expect! I speak from experience.

So here is an example of how I check current sessions:

[oracle: /oracle] ls -al get_session.sql
get_session.sql: No such file or directory
[oracle: /oracle] sqlplus / as sysdba

SQL> @get_session
Sessions on database AD1
PID SID SER# BOX USERNAME OS_USER PROGRAM SQL_ADDRESS SQL_HASH_VALUE
——– ——– ——– ————- ————— ———— ———————————– —————- ————–
12140 196 5604 oraad1 SYSMAN oracle emagent@oraad1 (TNS V1-V3) 00 0
12261 182 12004 omsapp DBSNMP OMS 00 0
12726 424 9074 oraad1 DBSNMP oracle emagent@oraad1 (TNS V1-V3) 00 0
13101 431 54 oraad1 DBSNMP oracle emagent@oraad1 (TNS V1-V3) 00 0
16456 396 29816 oraad1 SYS oracle rman@oraad1 (TNS V1-V3) 00 0


I’m sorry, the output isn’t quite as pretty as I’d like but I think it conveys my point. Using a couple quick variables and commands I can maintain a consistent environment.

The get_session.sql script is something I’ve “acquired” from years of DBA’ing via Google

Here it is:

SQL> l
1 select substr(a.spid,1,9) pid
2 , substr(b.sid,1,5) sid
3 , substr(b.serial#,1,5) ser#
4 , substr(b.machine,1,9) box
5 , substr b.username,1,10) username
6 , substr(b.osuser,1,9) os_user
7 , substr(b.program,1,30) program
8 , b.sql_address sql_address
9 , b.sql_hash_value sql_hash_value
10 from v$session b, v$process a –, v$sqlarea c
11 where b.paddr = a.addr
12 and type=’USER’
13* order by spid


I do the same thing with a user.sql script:

[oracle: /oracle] ls -al user.sql
user.sql: No such file or directory
[oracle: /oracle] sqlplus / as sysdba
SQL> @user.sql
Enter value for 1:
old 3: where username like upper(’%&1%’)
new 3: where username like upper(’%%’)

SQL> l
1 select username, profile, account_status, created
2 from dba_users
3* where username like upper(’%&1%’)


What I like about this script is if you do not enter a username then it’ll display all of the users. The results are as narrow or as broad as you want. I have more columns in this script, like password hash, but that’s not information I care to give out. I think you understand

I hope this gives you an idea of what you can do with such a simple variable. The more simple and cleaner you can make your environment the better off you’ll be.

What do you do to simplify your environment? Are there variables and aliases that you setup that I’ve missed? I’d like to hear about them!

Piggybacking on VI’s importance…

As a guy who uses Solaris for 99% of his administration I’ve gone from fearing vi to loving and respecting it and seriously considering buying it a valentine’s day present every year. That’s probably best reserved for a different blog all together:-)

This is a good write-up on why VI is such a powerful too even today.
Why, oh WHY, do those #?@! nutheads use vi?

I’m by no means an expert. I have learned enough vi to make my daily life so much easier though. As a lazy computer guy, VI is your best friend!!

Some examples of how I use vi on a regular basis.. creating bulk insert statements, generating quick shell scripts to execute similar commands repeatedly, and other countless items…

Yesterday, I was playing with NLS_SORT and I wanted to generate a table with a lot of first & last names. This would take forever if I did it by hand. Thank god there is the Internet!! I downloaded a random .CSV with distinct first name and last name columns, removed the extranious data, and then pasted that data into vi.


[oracle: /oracle] more insert_first_last_names.sql
Ralph Lauren
Piper Laurie
D.H. Lawrence
Martin Lawrence
Steve Lawrence
Thomas Lawrence
Vickie Lawrence
Bill Lee


A quick table creation…

create table rich_test (first_name varchar2(30), last_name varchar2(40));

And now comes the vi fun! Since I know I have only first and last names at this point, I can assume the space between them delineates first from last.

## This guy will take the space and replace it with tick comma tick…
:%s/ /','/g

Ralph’,’Lauren
Piper’,’Laurie
D.H.’,’Lawrence
Martin’,’Lawrence
Steve’,’Lawrence
Thomas’,’Lawrence
Vickie’,’Lawrence
Bill’,’Lee


Now let’s get the insert statement taken care of…

## make sure you don’t forget the tick at the beginning of the first name!

:%s/^/insert into rich_test values(’/g

insert into rich_test values(’Ralph’,’Lauren
insert into rich_test values(’Piper’,’Laurie
insert into rich_test values(’D.H.’,’Lawrence
insert into rich_test values(’Martin’,’Lawrence
insert into rich_test values(’Steve’,’Lawrence
insert into rich_test values(’Thomas’,’Lawrence
insert into rich_test values(’Vickie’,’Lawrence
insert into rich_test values(’Bill’,’Lee


Last, we can finish it off by closing out the last_name tick, the parenthesis and do NOT forget the semicolon! Otherwise you’ll be left with a bunch of insert statements that are waiting for you to send them!

:%s/$/’);/g

insert into rich_test values(’Ralph’,’Lauren’);
insert into rich_test values(’Piper’,’Laurie’);
insert into rich_test values(’D.H.’,’Lawrence’);
insert into rich_test values(’Martin’,’Lawrence’);
insert into rich_test values(’Steve’,’Lawrence’);
insert into rich_test values(’Thomas’,’Lawrence’);
insert into rich_test values(’Vickie’,’Lawrence’);
insert into rich_test values(’Bill’,’Lee’);

Now it’s as simple as running our SQL statement and then committing the data!

SQL> @”insert_first_last_names.sql”

1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.

I’ve only given you an example with a few entries, but this is beautiful if you wanted to generate a table with a few thousand, tens of thousands, or hundreds of thousands of rows. It’s really just that quick and easy.

Until you’ve memorized the commands, you should keep a cheat sheet close at hand. This one looks pretty good! vi Editor “Cheat Sheet”

And one final note, keep your hands on the home keys. The sooner you learn to use h, j,k, & l to navigate the better off you’ll be.