Opciones del comando spool de SQL*PLUS

SQL> spool name_of_file
SQL> spool name_of_file off
SQL> spool name_of_file out
SQL> spool name_of_file create
SQL> spool name_of_file append
SQL> spool name_of_file replace

Los tres modificadores create, replace y append son mejoras del Oracle 10g.
Para suprimir la salida por consola cuando se utiliza el spool se utiliza el set echo off y set termout off .

Niveles de restricción:
sqlplus   -R <level>       Establece el modo de restricción de  SQL*Plus  para interactuar con el file syste.  El nivel puede ser  1, 2 o 3. El más restrictivo es  -R 3 el cual desactiva todos los comandos de usuario que interactúan con el file system
El comando spool no funciona si el nivel de restricciones es igual o mayor de 2

C:>sqlplus -r 2 rana/rana
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 17 21:10:39 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> spool  lululu
SP2-0738: Restricted command "spool" not available
SQL>

Añadir la fecha de ejecución al nombre del archivo en el spool

column date_column new_value today_var
select to_char(sysdate,'yyyy-mm-dd') date_column
from dual
/
spool file_of_&today_var
-- las instrucciones
spool off

Formatear la salida en SQL*Plus

Principales opciones:

COLUMN [opciones] Permite formatear la salida de cada columna
  CLEAR Limpia cualquier formato
  FORMAT formato Define la visualizacion de una columna
     An Establece el ancho de columna en número de caracteres
     9 Digitos con supresion de ceros
     0 Digitos dejando los ceros
     $ Simbolo de dolar $
     L Moneda local
     . Posicion del punto decimal
     , Separador de miles
     | Inserta un salto de linea
  HEADING texto Define la cebecera de la columna
  JUSTIFY alineación justifica LA CABECERA de la columna
  NOPRINT Oculta la columna
  PRINT muestra la columna
  TRUNCATE Trunca la cadena de caracteres al final de la primera linea
  WRAPPED Continua escribiendo en la linea siguiente

TTITLE posicion texto Especifica una cabecera al principio de cada página
BTITLE posicion texto Especifica una texto al pie de cada página
BREAK [ON elemento de informe] Suprime la visualización de duplicados.

Ejemplo:

SQL> column username format A10
SQL> column PROGRAM format A40
SQL> column MACHINE format A20
SQL> ttitle center "CABECERA DE LA PAGINA"
SQL> btitle right "FIN SELECT"
SQL>  select USERNAME, PROGRAM, MACHINE from  gv$session where  username is not null;

                              CABECERA DE LA PAGINA
USERNAME   PROGRAM                                  MACHINE
---------- ---------------------------------------- --------------------
SYS        racgimon@devn1 (TNS V1-V3)               devn1
SYS        sqlplus@devn1 (TNS V1-V3)                devn1
SYS        oracle@devn1 (PZ99)                      devn1
SYS        racgimon@devn1 (TNS V1-V3)               devn1
SYS        racgimon@devn1 (TNS V1-V3)               devn1
SYS        racgimon@devn2 (TNS V1-V3)               devn2
SYS        racgimon@devn2 (TNS V1-V3)               devn2
SYS        oracle@devn1(PZ99)                       devn1
SYS        racgimon@devn2 (TNS V1-V3)               devn2
                                                                      FIN SELECT

Más información:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch6.htm

Cómo ver los roles de un usuario

Para ver los roles del usuario conectado en ese momento:

select username, granted_role
   from user_role_privs;

La siguiente select muestra los roles asignados a un usuario en concreto:

select grantee, granted_role from dba_role_privs
  where grantee = upper ('&grantee')
  order by grantee;

Para ver los roles asignado a un rol:

select role, granted_role
 from role_role_privs;

Para ver los roles definidos en la base de datos:

select role from dba_roles;

Comandos básicos de SQL*Plus

Lista de los principales comandos utilizados en SQL*Plus:
  ACCEPT – Le pide una entrada al usuario
  DEFINE – Declara una variable (corto: DEF)
  DESCRIBE – Lista los atributos de las tablas y otros objetos (corto: DESC)
  EDIT – Te coloca en un editor para poder editar un comandos SQL (corto: ED)
  EXIT or QUIT – Desconecta de la base datos y termina la sesión de SQL*Plus
  GET – Carga un archivo SQL y lo coloca en le buffer de SQL
  HOST – Permite ejecutar un comando de sistema operativo (corto: !, en sistemas operativos Windows $)
       Las diferencias entre ! y HOST es que HOST soporta sustitución de varibales con los &, sin embargo ! no. Ejemplo:

     SQL> ! whoami
     oracle
     SQL> DEFINE cmd="whoami"
     SQL> HOST &&cmd
     oracle

  LIST – Despliega el ultimo comando ejecutado / comando en le SQL buffer (corto: L)
  PROMPT – Despliega un texto en pantalla. Eg prompt ¡Hola Mundo!
  RUN – Lista y ejecuta el comando almacenado en el buffer de SQL (corto: /)
  SAVE – Guarda los comandos del buffer de SQL. Eg “save x”  creará un fichero llamado x.sql
  SET – Modifica las variables de entorno de SQL*Plus eg. SET PAGESIZE 23
  SHOW – Muestra el valor de las variables de entorno (corto: SHO). Eg SHOW ALL, SHO PAGESIZE etc.
  SPOOL – Manda las salidas a un fichero. Eg “spool x”
  START – Ejecuta el  un script SQL (corto: @)

Como pasar parámetros del sistema operativo a SQL*Plus

La manera más sencilla es utilizar está sintaxis:

sqlplus username/password @cmdfile.sql var1 var2 var3

El parámetro var1 corresponderá la a la variable &1, el var2 a &2, etc. Como en este ejemplo:

sqlplus scott/tiger @ejemplo.sql  '"parametro text"' dual

ejemplo.sql:

select '&1' from &2;
exit 5;

Recordar que windows las variables se defines %Variable%