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

Display Oracle SID name using SQL

Execute the following SQL as a SYS or SYSTEM user:

select instance_name from v$instance;

List existing user accounts within an Oracle SID

Execute the following SQL as SYS or SYSTEM

select distinct username from dba_users order by username desc;

Find Oracle Server Version information using SQL

To find the version of the Oracle server using SQL:

SELECT * FROM v$version WHERE banner LIKE 'Oracle%';

Unlock an Oracle user account

To unlock a user account within oracle, requires SYS or SYSTEM access to the SID.

alter user sample_user account unlock;

Sample Oracle Create User SQL Script

A sample user creation script for Oracle

Drop user sample_user cascade;

CREATE USER sample_user IDENTIFIED BY sample_user_password
DEFAULT TABLESPACE USER_DATA
   TEMPORARY TABLESPACE user_temp
   PROFILE DEFAULT
   ACCOUNT UNLOCK;

GRANT "CONNECT" TO sample_user;
GRANT "RESOURCE" TO sample_user;
ALTER USER sample_user DEFAULT ROLE "CONNECT", "RESOURCE";
GRANT CREATE MATERIALIZED VIEW TO sample_user;
GRANT CREATE SEQUENCE TO sample_user;
GRANT CREATE SESSION TO sample_user;
GRANT CREATE SYNONYM TO sample_user;
GRANT CREATE TABLE TO sample_user;
GRANT CREATE TRIGGER TO sample_user;
GRANT CREATE VIEW TO sample_user;
GRANT UNLIMITED TABLESPACE TO sample_user;
PURGE RECYCLEBIN;

Oracle Create Tablespace Example

This is an example SQL Script to create an Oracle Tablespace.

DROP TABLESPACE user_data INCLUDING CONTENTS;
DROP TABLESPACE user_blob INCLUDING CONTENTS;
DROP TABLESPACE user_index INCLUDING CONTENTS;

CREATE TABLESPACE USER_DATA
   DATAFILE '/opt/oracle/product/10.2.0/oradata/sid1/user_data.dbf'
   SIZE 5M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
   EXTENT MANAGEMENT LOCAL AUTOALLOCATE
   LOGGING
   ONLINE
   SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE USER_BLOB
   DATAFILE '/opt/oracle/product/10.2.0/oradata/sid1/user_blob.dbf'
   SIZE 5M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
   EXTENT MANAGEMENT LOCAL AUTOALLOCATE
   LOGGING
   ONLINE
   SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE USER_INDEX
   DATAFILE '/opt/oracle/product/10.2.0/oradata/sid1/user_index.dbf'
   SIZE 5M AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
   EXTENT MANAGEMENT LOCAL AUTOALLOCATE
   LOGGING
   ONLINE
   SEGMENT SPACE MANAGEMENT AUTO;

CREATE TEMPORARY TABLESPACE USER_TEMP
    TEMPFILE '/opt/oracle/product/10.2.0/oradata/sid1/user_temp01.dbf'
    SIZE 50M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;