Run SQL commands at login with SQL*Plus

When SQL*Plus starts up, it looks for a global login script called glogin.sql in the $ORACLE_HOME/sqlplus/admin directory. If found, this script will be executed.

Thereafter, sqlplus will try to find a local login script called login.sql in the directory where you start sqlplus from, alternatively the directories listed in the SQLPATH environment variable. When found, sqlplus will execute it.

NOTE: From Oracle 10g SQL*Plus will attempt to execute glogin.sql and login.sql after each successful connection. This is handy if you want to change the sqlprompt to include the current user. Here is an example (g)login.sql file:

prompt Loading login.sql file...
set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER SQL>"
define _editor=vi

A bit of history: when SQL*Plus was still called UFI, this file was called login.ufi (located in $ORACLE_HOME/demo).

Enabling SQL*Plus Help Utility

To enable HELP for SQl*Plus, run the supplied "helpins" script in $ORACLE_HOME/bin. The "helpins" command will prompt you for the SYSTEM password and load the help data into the SYSTEM schema.

Alternatively you can load the help facility manually like this:

cd $ORACLE_HOME/sqlplus/admin/help
sqlplus system/manager @helpdrop.sql        # Drop the HELP table
sqlplus system/manager @helpbld.sql         # Create the HELP table
sqlplus system/manager @helpus.sql          # Load data into the HELP table

If the HELP command is not supported on your operating system, you can access the help table with a simple script like this (let's call it help.sql):

select info
from   system.help
where  upper(topic)=upper('&1');

Whenever you need help, you can now run the help.sql script:

@help SELECT

Using the default editor (Notepad or VI) for SQL Commands

One can edit SQL scripts and the command buffer (the last command entered) with the EDIT (or ED) command. However, sometimes one needs to select a editor before using this command. Examples:

Use the Unix/Linux vi-editor:

DEFINE _EDITOR=vi

Use the Notepad on Windows:

DEFINE _EDITOR=notepad

TIP: Add this command in your login.sql or glogin.sql scripts so it executes every time you start sqlplus.

What are the basic SQL*Plus commands?

Here is a list of some of the most frequently used SQL*Plus commands:

• ACCEPT - Get input from the user
• DEFINE - Declare a variable (short: DEF)
• DESCRIBE - Lists the attributes of tables and other objects (short: DESC)
• EDIT - Places you in an editor so you can edit a SQL command (short: ED)
• EXIT or QUIT - Disconnect from the database and terminate SQL*Plus
• GET - Retrieves a SQL file and places it into the SQL buffer
• HOST - Issue an operating system command (short: !)
• LIST - Displays the last command executed/ command in the SQL buffer (short: L)
• PROMPT - Display a text string on the screen. Eg prompt Hello World!!!
• RUN - List and Run the command stored in the SQL buffer (short: /)
• SAVE - Saves command in the SQL buffer to a file. Eg "save x" will create a script file called x.sql
• SET - Modify the SQL*Plus environment eg. SET PAGESIZE 23
• SHOW - Show environment settings (short: SHO). Eg SHOW ALL, SHO PAGESIZE etc.
• SPOOL - Send output to a file. Eg "spool x" will save STDOUT to a file called x.lst
• START - Run a SQL script file (short: @)

Shrink Oracle LOB columns

Two commands are necessary to allow a LOB to shrink:
ALTER TABLE ... ENABLE ROW MOVEMENT;
ALTER TABLE ... MODIFY LOB (column_name) (SHRINK SPACE COMPACT);

Display all TABLE Owning users with an Oracle SID

Execute the following SQL as the SYS or SYSTEM user:

select distinct owner from all_tables order by owner asc;

Find the Characterset Encoding for an Oracle SID

Execute the following SQL as the SYS or SYSTEM user:

select * from nls_database_parameters WHERE parameter =
'NLS_NCHAR_CHARACTERSET';

The expected column and value would be:
NLS_NCHAR_CHARACTERSET = UTF8