====== [HOWTO] Sqlplus Wrapper ======
===== Descripción =====
El readline wrapper implementa las funciones típicas de:
* History
* backward search
* fancy editing
Es decir, convierte SQL+ de un infierno a algo amigable.
También lo podemos aplicar a asmcmd, rman y dgmgrl!
===== Instalación =====
==== rpm ====
Desde la EPEL:
yum -y install rlwrap
==== Entorno ====
Para hacerlo "worldwide" avalaible:
cat > /etc/profile.d/sqlplus_wrapper.sh <
De esta forma cada vez que se inicia una sesión se aplica el alias.
Si se quiere usar el sqlplus sin eliminar el alias, basta con ejecutar:
'sqlplus' '/ as sysdba'
==== Desinstalar ====
Borrar el fichero:
rm -fv /etc/profile.d/sqlplus_wrapper.sh
====== Configuración de SQLPLUS ======
Para definir parámetros de sqlplus, es decir, ejecutar órdenes para configurarlo, editar el fichero "login.sql" o "glogin.sql".
[[http://docs.oracle.com/cd/B28359_01/server.111/b31189/ch2.htm|Ver documentación oficial]]
===== Ejemplo =====
vim /u01/app/oracle/product/11.2.0/db/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle. All Rights Reserved.
--
-- NAME
-- glogin.sql
--
-- DESCRIPTION
-- SQL*Plus global login "site profile" file
--
-- Add any SQL*Plus commands here that are to be executed when a
-- user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
-- This script is automatically run
--
-- SET the SQLPROMPT to include the _USER, _CONNECT_IDENTIFIER
-- and _DATE variables.
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "
-- To set the number of lines to display in a report page to 24.
SET PAGESIZE 90
-- To set the number of characters to display on each report line to 78.
SET LINESIZE 270
SET LONG 52000
SET LONGC 52000
-- Enable dbms_output messages
SET SERVEROUTPUT ON
-- To set the number format used in a report to $99,999.
-- SET NUMFORMAT $99,999
-- set the default editor
def_editor=vim
-- COLUMN DEFINITION
COL BEGIN_INTERVAL_TIME FORMAT A50
COL BEGIN_TIME FORMAT A30
COL BUFINFO FORMAT A30
COL BUFSIZE FORMAT A30
COL CAPACITY FORMAT 999999999
COL CAPACITY_GB FORMAT 999999999.99
COL CHAR_LENGTH FORMAT 9999999
COL CLIENT_ID FORMAT A30
COL CLIENT_INFO FORMAT A40
COL COLUMN_LENGTH FORMAT 9999999
COL COLUMN_NAME FORMAT A30
COL COLUMN_POSITION FORMAT 9999999
COL COMPONENT FORMAT A30
COL COMPRESS_FOR FORMAT A30
COL CONSECUTIVE_OCCURRENCES FORMAT 9999999999
COL CONSTRAINT_NAME FORMAT A50
COL CPU_RANK FORMAT 999
COL CRITICAL_OPERATOR FORMAT A16
COL CRITICAL_VALUE FORMAT A8
COL DATA_TYPE FORMAT A50
COL DATATYPE_STRING FORMAT A20
COL DB_LINK FORMAT A50
COL DEFLT FORMAT A30
COL DEPENDENCY_TYPE FORMAT A20
COL DESCRIPTION FORMAT A120
COL DESTINATION FORMAT A30
COL DEST_NAME FORMAT A20
COL DIRECTORY_NAME FORMAT A40
COL DIRECTORY_PATH FORMAT A180
COL DIRNAME FORMAT A120
COL DISKGROUP FORMAT A20
COL DISK_NAME FORMAT A25
COL DISPLAY_CHILD FORMAT A58
COL DISPLAY_PARENT FORMAT A58
COL END_INTERVAL_TIME FORMAT A50
COL END_TIME FORMAT A30
COL EVENT FORMAT A20 WORD_WRAPPED
COL FAILGROUP FORMAT A30
COL FILENAME FORMAT A200
COL FILE_NAME FORMAT A80
COL FREE_GB FORMAT 999999999.99
COL FREE_MB FORMAT 999999999
COL GRANTOR FORMAT A33
COL HOST FORMAT A30
COL HOSTNAME FORMAT A40 WORD_WRAPPED
COL INDEX_NAME FORMAT A30
COL INDEX_OWNER FORMAT A30
COL INSTANCE_NAME FORMAT A15
COL INSTNACE FORMAT A35
COL JOB_ACTION FORMAT A30
COL JOB_CREATOR FORMAT A30
COL JOB_NAME FORMAT A40
COL JOB_STYLE FORMAT A30
COL JOB_SUBNAME FORMAT A30
COL JOB_TYPE FORMAT A30
COL KSPPDESC FORMAT A120
COL KSPPINM FORMAT A60
COL LIMIT FORMAT A30
COL LOCAL FORMAT A40
COL LOCK_ID1 FORMAT A10
COL LOCK_ID2 FORMAT A10
COL LOCK_TYPE FORMAT A25
COL MACHINE FORMAT A40
COL MAX_MB FORMAT 99999999999
COL MAXQUERYID FORMAT A20
COL METRICS_NAME FORMAT A30
COL MODE_HELD FORMAT A23
COL MODE_REQUESTED FORMAT A23
COL NAME FORMAT A50
COL NAME_SPACE FORMAT A15
COL OBJECT FORMAT A50
COL OBJECT_TYPE FORMAT A25
COL OPERATION_PERIOD FORMAT A10
COL OSPID FORMAT A7
COL OSUSER FORMAT A33
COL OWNER FORMAT A30
COL OWNER FORMAT A33
COL P1TEXT FORMAT A20 WORD_WRAPPED
COL PARAMETER FORMAT A50
COL PATH FORMAT A100
COL PATH FORMAT A40
COL "PERCENT" FORMAT 999
COL PERCENT FORMAT 999.99
COL PERMISSION_NAME FORMAT A50
COL POSITION FORMAT 999
COL PROFILE FORMAT A30
COL PROGRAM FORMAT A50
COL PROGRAM FORMAT A70 WORD_WRAPPED
COL PROGRAM_NAME FORMAT A35
COL PROGRAM_OWNER FORMAT A20
COL QUERY HEADING 'SQL|QUERY' FORMAT A100 WORD_WRAPPED
COL R_CONSTRAINT_NAME FORMAT A50
COL RDMAENABLE FORMAT A10
COL REFERENCED_NAME FORMAT A33
COL REFERENCED_OWNER FORMAT A30
COL REFERENCED_TYPE FORMAT A30
COL REPEAT_INTERVAL FORMAT A70
COL RESOURCE_NAME FORMAT A50
COL ROLL_NAME FORMAT A30
COL R_OWNER FORMAT A30
COL SCHEDULE FORMAT A15
COL SCHEDULE_NAME FORMAT A30
COL SCHEDULE_OWNER FORMAT A20
COL SEGMENT_NAME FORMAT A35
COL SERIAL FORMAT 999999
COL SESSION_ID FORMAT 999999
COL SESSION_ID FORMAT 999999
COL SID FORMAT 99999
COL SID_SERIAL FORMAT A20
COL SQL_ID FORMAT A15
COL SQLID FORMAT A15 WORD_WRAPPED
COL SQL_TEXT FORMAT A130 WORD WRAPPED
COL START_DATE FORMAT A50
COL STATUS FORMAT A15
COL SUBNAME FORMAT A40
COL SVRNAME FORMAT A40
COL SYS_PATH FORMAT A80
COL TABLE_NAME FORMAT A30
COL TABLE_OWNER FORMAT A30
COL TABLESPACE FORMAT A40
COL TABLESPACE_NAME FORMAT A35
COL TOTAL_CPU_TIME FORMAT 999999999
COL TOTAL_MB FORMAT 99999990.9999
COL TOTAL_MB FORMAT 999999999
COL TYPE FORMAT A30
COL USERID FORMAT A40
COL USERNAME FORMAT A33
COL VALUE_STRING FORMAT A100
COL WAITCLASS HEADING 'WAITING|CLASS' FORMAT A20
COL WAITINGSECS HEADING 'SECONDS|WAITING' FORMAT 99999
COL WARNING_OPERATOR FORMAT A16
COL WARNING_VALUE FORMAT A30
set feed off
-- DBMS_METADATA
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
set feed on
-- Enable timing
--SET timing ON
====== Links ======
* [[http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_eight.htm#sthref949|Tuning SQL*Plus]]
* [[http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#SQPUG095|SQL*Plus SET reference]]