Oracle登录后怎么将sql>修改成user@实例名>

2012-07-24

Oracle登录后如何将sql修改成user@实例名方法如下：在D:\oracle\product\10.2.0\db_1\sqlplus\admin路径

Oracle登录后如何将sql>修改成user@实例名>

方法如下：

在D:\oracle\product\10.2.0\db_1\sqlplus\admin路径下面的glogin.sql文件末尾添加如下代码：

set termout off

define loginname = idle

column global_name new_value loginname

SELECT user

?????? ||'@'

?????? ||substr(global_name, 1, decode(dot, 0,length(global_name), dot-1)) global_name

FROM

?????? (SELECT?????? global_name,

????????????? instr(global_name, '.') dot

?????? FROM global_name);

set sqlprompt '&loginname>'

set termout on

解决啦！！

另：

仔细看里面的代码，发现set termout off和set termout on，这两个参数是用来设置信息显示的，如：

GLOBAL_NAME

------------------------------------------

XIYUAN@OCP10G

会话已更改。

之类的信息，如果将其后面的set termout 设置为off，则这些信息都不显示。

还有一个参数：set feedback on/off，它指定的是遍历表之后返回的行数提示信息，如：

已选择 1 行。

所以可以在set termout on的前面设置各种参数，例如：

ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY-MM-DD’

ALTER SESSION SET NLS_DATE_LANGUAGE = ‘AMERICAN’

SET FEEDBACK ON/OFF

例如修改过后的glogin.sql的内容是：

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

-- Used by Trusted Oracle

COLUMN ROWLABEL FORMAT A15

-- Used for the SHOW ERRORS command

COLUMN LINE/COL FORMAT A8

COLUMN ERROR??? FORMAT A65? WORD_WRAPPED

-- Used for the SHOW SGA command

COLUMN name_col_plus_show_sga FORMAT a24

COLUMN units_col_plus_show_sga FORMAT a15

-- Defaults for SHOW PARAMETERS

COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME

COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

-- Defaults for SHOW RECYCLEBIN

COLUMN origname_plus_show_recyc?? FORMAT a16 HEADING 'ORIGINAL NAME'

COLUMN objectname_plus_show_recyc FORMAT a30 HEADING 'RECYCLEBIN NAME'

COLUMN objtype_plus_show_recyc??? FORMAT a12 HEADING 'OBJECT TYPE'

COLUMN droptime_plus_show_recyc?? FORMAT a19 HEADING 'DROP TIME'

-- Defaults for SET AUTOTRACE EXPLAIN report

-- These column definitions are only used when SQL*Plus

-- is connected to Oracle 9.2 or earlier.

COLUMN id_plus_exp FORMAT 990 HEADING i

COLUMN parent_id_plus_exp FORMAT 990 HEADING p

COLUMN plan_plus_exp FORMAT a60

COLUMN object_node_plus_exp FORMAT a8

COLUMN other_tag_plus_exp FORMAT a29

COLUMN other_plus_exp FORMAT a44

-- Default for XQUERY

COLUMN result_plus_xquery HEADING 'Result Sequence'