How to change crontab's default editor

I wanted to update a cron job in one of our server. As usual I entered crontab -e command. I was not able to edit it. The editor was not VI. Then I figured out it was using emacs editor.

To make vi default editor for crontab set EDITOR parameter to vi.

$ echo $EDITOR
/usr/bin/emacs

$ export EDITOR=vi

$ echo $EDITOR
vi

If still crontab uses emacs editor, then set VISUAL parameter also to vi:

$ echo $VISUAL
/usr/bin/emacs

$ export VISUAL=vi


$ echo $VISUAL
vi

ADRCI Command

ADRCI is a command line interface tool to view or manage diagnostic information of a database. Alter log, listener log, incidents etc can be viewed using this command.

List all the homes

$ adrci
adrci> show homes
ADR Homes:
diag/tnslsnr/test1/listener
diag/rdbms/db11gr2/db11gr2
diag/rdbms/ora11g/ora11g

To set a home

adrci> set home diag/rdbms/ora11g/ora11g

To view alert log

adrci> show alert

To view 'tail' of alert log file

adrci> set home db11g
adrci> show alter -tail
adrci> show alter -tail -f

To search (grep) alter log for errors

adrci> set home db11g
adrci> show alter -P "MESSAGE_TEXT LIKE '%ORA-01578%'" -term

To unset home

adrci> show base
adrci> set base /u10/app/oracle

Running adrci command in Non-interactive mode
Without getting into adrci prompt we can run adrci command from Unix prompt.

$ adrci exec ="show homes; show incidents"
$ adrci exec ="set home db11g; show alter -term"
$ adrci exec ="set home db11g; show alter -term"|tail
$ adrci exec ="set home db11g; show alter -term"|grep ORA

To view incident and problem

adrci> show homes
adrci> set home diag/rdbms/ora11g/ora11g
adrci> show incident

ADR Home = /home/oracle/app/oracle/diag/rdbms/ora11g/ora11g:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                      CREATE_TIME                           
-------------------- --------------------- ----------------------------------------
12161                ORA 25319              2011-05-26 17:10:12.536000 +05:30    
12121                ORA 25319              2011-05-26 17:10:32.408000 +05:30    
12122                ORA 25319              2011-05-26 17:10:50.947000 +05:30    


adrci> show incident -mode detail -p "incident_id=2601"

adrci> show problem

To create incident packages

adrci> show homes
adrci> set home diag/rdbms/ora11g/ora11g
adrci> show incidents
adrci> ips create package incident 14553

It creates a logical package. In the above example 14553 is an incident ID. We can also add an incident to an existing package.

adrci> ips add incident 14554 package 1

Generating package

adrci> ips generate package 1 in /u01/app/oracle

It creates a physical package. Compressed file will be in /u01/app/oracle.

ACCEPT and PROMPT commands

Usage of ACCEPT and PROMPT commands in SQL script.

SQL> PROMPT 'Please enter your name:'

SQL> ACCEPT name CHAR FORMAT a20 

Here is a script which prompts for username and accepts it.

It also creates user with that username and a table

 UNDEF username

ACCEPT username PROMPT 'Enter username:'

--Create user
CREATE USER &username IDENTIFIED BY passwd;
GRANT CREATE SESSION TO &username;
GRANT CREATE TABLE TO &username;
ALTER USER &username QUOTA 5M ON users;

-- Create table
CREATE TABLE &username..test AS SELECT * FROM dual;

SQL> @create.sql
Enter username:harish
old   1: CREATE USER &username IDENTIFIED BY passwd
new   1: CREATE USER harish IDENTIFIED BY passwd

User created.

old   1: GRANT CREATE SESSION TO &username
new   1: GRANT CREATE SESSION TO harish

Grant succeeded.

old   1: GRANT CREATE TABLE TO &username
new   1: GRANT CREATE TABLE TO harish

Grant succeeded.

old   1: ALTER USER &username QUOTA 5M ON users
new   1: ALTER USER harish QUOTA 5M ON users

User altered.

old   1: CREATE TABLE &username..test AS SELECT * FROM dual
new   1: CREATE TABLE harish.test AS SELECT * FROM dual

Table created.

Please not that I have used two dots in create table script after schema name.