Ciberterminal Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== [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:
<code>
yum -y install rlwrap
</code>
==== Entorno ====
Para hacerlo "worldwide" avalaible:
<code bash>
cat > /etc/profile.d/sqlplus_wrapper.sh <<EOF
for i in sqlplus rman asmcmd dgmgrl ttisql ; do
        alias \${i}="rlwrap \${i}"
done
EOF
chmod +x /etc/profile.d/sqlplus_wrapper.sh
</code>
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:
<code>'sqlplus' '/ as sysdba'</code>

==== Desinstalar ====
Borrar el fichero:
<code>
rm -fv /etc/profile.d/sqlplus_wrapper.sh
</code>


====== 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 =====
<code>
vim /u01/app/oracle/product/11.2.0/db/sqlplus/admin/glogin.sql
</code>

<code 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
</code>

====== 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]]