SQL*Plus在启动时会读 $ORACLE_HOME/sqlplus/admin/glogin.sql 这个配置文件,并应用其文件中的配置在信息。我们可以把那些每次登录都要改的命令及设置,写到这个文件中。
快速编辑
[oracle@11204 ~]$ vi .bash_profile ### 增加如下内容 alias sqlplusad='vi $ORACLE_HOME/sqlplus/admin/glogin.sql' [oracle@11204 ~]$ . .bash_profile [oracle@11204 ~]$ sqlplusad -- Copyright (c) 1988, 2011, Oracle and/or its affiliates. -- 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
常用设置
define_editor=vi ==>设置SQL*Plus的默认编辑器为vi,即可用ed命令编辑上一条SQL语句;
set linesize 150 ==>设置SQL*Plus的显示宽度(让所有列都显示在一行吧!);
set pagesize 999 ==>设置SQL*Plus的显示每页的高度(让所有行都显示在一个列表中吧!);
set long 999999999 ==>设置显示单个字段最大长度;
set sqlprompt "_user'@'_connect_identifier> " ==>设置SQL*Plus提示符,默认SQL>,在此我加入了显示主机名和当前数据库用户;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; ==>修改显示的时间格式。
-- Copyright (c) 1988, 2011, Oracle and/or its affiliates. -- 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 -- define_editor=vi alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; set linesize 150 set pagesize 999 set long 999999999 set serveroutput on; set termout on set sqlprompt "_user'@'_connect_identifier> "
配置前效果
SQL> select username,account_status,profile,created from dba_users where rownum<=3; USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- PROFILE CREATED ------------------------------ --------- SYS OPEN DEFAULT 15-JUL-19 SYSTEM OPEN DEFAULT 15-JUL-19 OUTLN EXPIRED & LOCKED DEFAULT 15-JUL-19 SQL> ed Wrote file afiedt.buf 81 SQL> select dbms_metadata.get_ddl('TABLE','TEST1','SYS') from dual; DBMS_METADATA.GET_DDL('TABLE','TEST1','SYS') -------------------------------------------------------------------------------- CREATE TABLE "SYS"."TEST1" ( "T_ID" NUMBER, "T_NAME" VARCHAR2(15) )
配置后效果
[oracle@11204 ~]$ sql SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 15 18:16:56 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Session altered. SYS@test> select username,account_status,profile,created from dba_users where rownum<=3; USERNAME ACCOUNT_STATUS PROFILE CREATED ------------------------------ -------------------------------- ------------------------------ ------------------- SYS OPEN DEFAULT 2019-07-15 00:47:52 SYSTEM OPEN DEFAULT 2019-07-15 00:47:52 OUTLN EXPIRED & LOCKED DEFAULT 2019-07-15 00:47:53 SYS@test> select dbms_metadata.get_ddl('TABLE','TEST1','SYS') from dual; DBMS_METADATA.GET_DDL('TABLE','TEST1','SYS') -------------------------------------------------------------------------------- CREATE TABLE "SYS"."TEST1" ( "T_ID" NUMBER, "T_NAME" VARCHAR2(15) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM" SYS@test> ed Wrote file afiedt.buf 1* select dbms_metadata.get_ddl('TABLE','TEST1','SYS') from dual