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