Ciberterminal Wiki

User Tools

Site Tools

Trace:
dba:oracle:howtos:affordable_sqlplus

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
dba:oracle:howtos:affordable_sqlplus [2023/01/31 08:28] – removed - external edit (Unknown date) 127.0.0.1dba:oracle:howtos:affordable_sqlplus [2023/02/10 15:32] (current) – dodger
Line 1: Line 1:
 +====== [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]]
 +
 +