User Tools

Site Tools


dba:oracle:howtos:affordable_sqlplus

[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 <<EOF
for i in sqlplus rman asmcmd dgmgrl ttisql ; do
        alias \${i}="rlwrap \${i}"
done
EOF
chmod +x /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”.

Ver documentación oficial

Ejemplo

vim /u01/app/oracle/product/11.2.0/db/sqlplus/admin/glogin.sql

<code sql>

– NAME

-- glogin.sql

– DESCRIPTION

-- SQL*Plus global login "site profile" file

– Add any SQLPlus commands here that are to be executed when a – user starts SQLPlus, or uses the SQL*Plus CONNECT command. – – USAGE

-- This script is automatically run

– SET the SQLPROMPT to include the USER, _CONNECTIDENTIFIER – and DATE variables. SET SQLPROMPT “USER'@'CONNECTIDENTIFIER> ”

– 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 BEGININTERVALTIME FORMAT A50 COL BEGINTIME FORMAT A30 COL BUFINFO FORMAT A30 COL BUFSIZE FORMAT A30 COL CAPACITY FORMAT 999999999 COL CAPACITYGB FORMAT 999999999.99 COL CHARLENGTH FORMAT 9999999 COL CLIENTID FORMAT A30 COL CLIENTINFO FORMAT A40 COL COLUMNLENGTH FORMAT 9999999 COL COLUMNNAME FORMAT A30 COL COLUMNPOSITION FORMAT 9999999 COL COMPONENT FORMAT A30 COL COMPRESSFOR FORMAT A30 COL CONSECUTIVEOCCURRENCES FORMAT 9999999999 COL CONSTRAINTNAME FORMAT A50 COL CPURANK FORMAT 999 COL CRITICALOPERATOR FORMAT A16 COL CRITICALVALUE FORMAT A8 COL DATATYPE FORMAT A50 COL DATATYPESTRING FORMAT A20 COL DBLINK FORMAT A50 COL DEFLT FORMAT A30 COL DEPENDENCYTYPE FORMAT A20 COL DESCRIPTION FORMAT A120 COL DESTINATION FORMAT A30 COL DESTNAME FORMAT A20 COL DIRECTORYNAME FORMAT A40 COL DIRECTORYPATH FORMAT A180 COL DIRNAME FORMAT A120 COL DISKGROUP FORMAT A20 COL DISKNAME FORMAT A25 COL DISPLAYCHILD FORMAT A58 COL DISPLAYPARENT FORMAT A58 COL ENDINTERVALTIME FORMAT A50 COL ENDTIME FORMAT A30 COL EVENT FORMAT A20 WORDWRAPPED COL FAILGROUP FORMAT A30 COL FILENAME FORMAT A200 COL FILENAME FORMAT A80 COL FREEGB FORMAT 999999999.99 COL FREEMB FORMAT 999999999 COL GRANTOR FORMAT A33 COL HOST FORMAT A30 COL HOSTNAME FORMAT A40 WORDWRAPPED COL INDEXNAME FORMAT A30 COL INDEXOWNER FORMAT A30 COL INSTANCENAME FORMAT A15 COL INSTNACE FORMAT A35 COL JOBACTION FORMAT A30 COL JOBCREATOR FORMAT A30 COL JOBNAME FORMAT A40 COL JOBSTYLE FORMAT A30 COL JOBSUBNAME FORMAT A30 COL JOBTYPE FORMAT A30 COL KSPPDESC FORMAT A120 COL KSPPINM FORMAT A60 COL LIMIT FORMAT A30 COL LOCAL FORMAT A40 COL LOCKID1 FORMAT A10 COL LOCKID2 FORMAT A10 COL LOCKTYPE FORMAT A25 COL MACHINE FORMAT A40 COL MAXMB FORMAT 99999999999 COL MAXQUERYID FORMAT A20 COL METRICSNAME FORMAT A30 COL MODEHELD FORMAT A23 COL MODEREQUESTED FORMAT A23 COL NAME FORMAT A50 COL NAMESPACE FORMAT A15 COL OBJECT FORMAT A50 COL OBJECTTYPE FORMAT A25 COL OPERATIONPERIOD FORMAT A10 COL OSPID FORMAT A7 COL OSUSER FORMAT A33 COL OWNER FORMAT A30 COL OWNER FORMAT A33 COL P1TEXT FORMAT A20 WORDWRAPPED COL PARAMETER FORMAT A50 COL PATH FORMAT A100 COL PATH FORMAT A40 COL “PERCENT” FORMAT 999 COL PERCENT FORMAT 999.99 COL PERMISSIONNAME FORMAT A50 COL POSITION FORMAT 999 COL PROFILE FORMAT A30 COL PROGRAM FORMAT A50 COL PROGRAM FORMAT A70 WORDWRAPPED COL PROGRAMNAME FORMAT A35 COL PROGRAMOWNER FORMAT A20 COL QUERY HEADING 'SQL|QUERY' FORMAT A100 WORDWRAPPED COL RCONSTRAINTNAME FORMAT A50 COL RDMAENABLE FORMAT A10 COL REFERENCEDNAME FORMAT A33 COL REFERENCEDOWNER FORMAT A30 COL REFERENCEDTYPE FORMAT A30 COL REPEATINTERVAL FORMAT A70 COL RESOURCENAME FORMAT A50 COL ROLLNAME FORMAT A30 COL ROWNER FORMAT A30 COL SCHEDULE FORMAT A15 COL SCHEDULENAME FORMAT A30 COL SCHEDULEOWNER FORMAT A20 COL SEGMENTNAME FORMAT A35 COL SERIAL FORMAT 999999 COL SESSIONID FORMAT 999999 COL SESSIONID FORMAT 999999 COL SID FORMAT 99999 COL SIDSERIAL FORMAT A20 COL SQLID FORMAT A15 COL SQLID FORMAT A15 WORDWRAPPED COL SQLTEXT FORMAT A130 WORD WRAPPED COL STARTDATE FORMAT A50 COL STATUS FORMAT A15 COL SUBNAME FORMAT A40 COL SVRNAME FORMAT A40 COL SYSPATH FORMAT A80 COL TABLENAME FORMAT A30 COL TABLEOWNER FORMAT A30 COL TABLESPACE FORMAT A40 COL TABLESPACENAME FORMAT A35 COL TOTALCPUTIME FORMAT 999999999 COL TOTALMB FORMAT 99999990.9999 COL TOTALMB FORMAT 999999999 COL TYPE FORMAT A30 COL USERID FORMAT A40 COL USERNAME FORMAT A33 COL VALUESTRING FORMAT A100 COL WAITCLASS HEADING 'WAITING|CLASS' FORMAT A20 COL WAITINGSECS HEADING 'SECONDS|WAITING' FORMAT 99999 COL WARNINGOPERATOR FORMAT A16 COL WARNING_VALUE FORMAT A30

set feed off – DBMSMETADATA EXECUTE DBMSMETADATA.SETTRANSFORMPARAM(DBMSMETADATA.SESSIONTRANSFORM,'SQLTERMINATOR',TRUE); EXECUTE DBMSMETADATA.SETTRANSFORMPARAM(DBMSMETADATA.SESSION_TRANSFORM,'STORAGE',FALSE);

set feed on

– Enable timing –SET timing ON </code>

Links

dba/oracle/howtos/affordable_sqlplus.txt · Last modified: 2023/02/10 15:32 by dodger