Square DBA – Oracle DBA Talk

Old MetaLink link to be Retired

The old MetaLink URL will be retired. You can no longer access MetaLink via metalink.oracle.com.

Oracle Global Customer Support is pleased to announce that later this year My Oracle Support will be upgraded to enable even more Oracle customers to use Oracle’s next generation support platform, and Classic MetaLink will be retired.

My Oracle Support
1) Click the “Settings” link in upper right hand corner
2) Select “Account & Privileges”
3) Click the pencil icon next to Username/E-mail

Customize your SQL*Plus Environment

The setup for SQL*Plus is amazingly easy. In fact, it should already be done. Every client software installation has it, and every server installation has it too. On windows, there are two versions of SQL*Plus: a GUI one (the sqlplusw.exe program) and a character based one (the sqlplus.exe program). The character-mode SQL*Plus is 100% compatible with SQL*Plus on every other platform on which Oracle is delivered. The GUI SQL*Plus, which offers no real functional benefit over the character mode – after all, it is a character-mode tool running in a window – is different enough to be confusing and isn’t flexible as the command-line version. Additionally, it is already officially deprecated in the next release of Oracle, so it won’t be around for long.

Store Settings for SQL*PLUS (login.sql and glogin.sql)

glogin.sql

Whenever SQL*PLUS starts up, it looks for a file named glogin.sql under the directory $ORACLE_HOME/sqlplus/admin. If such a file is found, it is read and the containing statements executed. This allows to store settings (such as linesize) accross SQL*PLUS sessions. New in Oracle 10g: Oracle also reads glogin.sql and login.sql at a connect in sql*plus.

login.sql

Additionally, after reading glogin.sql, sql*plus also looks for a file named login.sql in the directory from where SQL*PLUS was started and in the directory that the environment variable SQLPATH points to and reads it and executes it. Settings from the login.sql take precedence over settings from glogin.sql.

In addition to the standard items in my glogin.sql file I added the following:

-- Define a custom prompt

define gname=idle
column global_name new_value gname
select lower(user) || '@' ||
       substr( global_name, 1, decode( dot,
                                       0, length(global_name),
                                          dot-1) ) global_name
  from (select global_name, instr(global_name,'.') dot
          from global_name );
set sqlprompt '&gname> ' 

This give me a SQL*Plus prompt that show me the database name I am connected to as well as what user I am connected as. This helps eliminate the confusion of dealing with multiple users on multiple instances. The other piece of code I have added is:

-- Start logging all scripts

define logname=date
column clogname new_value logname
select substr(global_name,1,
 decode(dot,0,length(global_name),dot-1))||'-'||
 to_char(sysdate, 'yyyymmddhh24miss')||'-'||
round(dbms_random.value(10,99),0) clogname
from (select global_name, instr(global_name, '.')
dot from global_name);
spool c:\sqlpluslogs\&logname..txt

This starts a log file for my entire session. The spool filename is generated randomly. The log file name is generated with the date, database name, and for uniqueness a random number. The random number is not really needed since I am using the date down to the seconds, but I added it just to be safe.

Generating random numbers and strings in Oracle

Do you know how to auto generate random numbers or strings in Oracle? Generating random numbers is required when there is a need to create a lot of data for testing purposes, or when we simply need to use a number to temporarily tag a process. It may also be necessary to generate random password strings of a fixed size. If you need random numbers or string then DBMS_RANDOM is your friend.

DBMS_RANDOM package

The DBMS_RANDOM package will generate random data in character, numeric or alphanumeric formats. The size and the range from which to pickup the random values can also be specified. This package is created by the script dbmsrand.sql available in the /rdbms/admin directory.

The following functions present in the package can be used to serve the purpose of generating random numbers and strings. RANDOM – generate random numbers.

VALUE – generate random numbers from the range provided. The range will be taken as 0-1 if none is provided.

STRING – generate strings in upper case, lower case or alphanumeric format.

The first parameter takes the string type to be generated, the following values can be provided in upper or lower case.
U – Upper case
L – Lower case
A – Alphanumeric
X – Alphanumeric with upper case alphabets.
P – Printable characters only.
Providing any other character will return the output in upper case only.

The size of the string should also be provided as the second parameter.

Oracle documentation says that it is necessary to initialize the package before using the random number generator. Oracle by default initializes the package with the seed value as the current user name, current time down to the second and the current session id.

INITIALIZE – Initialize the package to proceed with the number generation.

Provide a number (seed) as input to the routine.

SEED – Used to change the seed value. It is used in the internal algorithm to generate values. Setting this will generate the random numbers in an order that will be similar in multiple sessions. Refer to the example below.

TERMINATE – Close the process of random number generation. More »

DNS lookup from Oracle

Today we needed a was to performa DNS query from inside an Oracle database.  Low and behold Oracle already has a function for that.  The utility is called UTL_INADDR. Beautiful little utility. Here are the examples I sent to the people that needed them.

SELECT utl_inaddr.get_host_name(‘68.180.206.184′) from dual;
SELECT utl_inaddr.get_host_address(‘yahoo.com’) FROM dual;