oracle 12 sqlplus 使用,oracle 12.2 sqlplus history

12.1开始使用sqlplus显示用户上次登录时间，12.2提供了一个记录历史sql的功能，类似于操作系统的history，根目录下的.history文件，不过功能比操作系统的history要强大，可以指定删除/运行/编辑历史记录里面的某一条SQL，比操作系统的上翻历史命令都方便。

history默认关闭，下面是打开关闭history功能，设置缓存多少条记录：

set hist[ory] on|off|n

set hist on

set hist 1000

set hist off

history命令如下：

HIST[ORY] [n RUN | EDIT | DEL[ETE]] | [CLEAR | LIST]

hist n run/edit/del 指定运行/编辑/删除第N条记录

hist clear/list 清除/列出历史记录

编辑需要设置编辑器define _editor = vi，且编辑后的记录显示在最后一条

hist默认就是hist list。

下面是简单的测试

[code][oracle@orasql2 ~]$ sqlplus system/oracle

SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 24 09:34:12 2016

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Last Successful login time: Mon Nov 21 2016 16:56:14 +08:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set history on

SQL> show history

history is ON and set to "100"

SQL> set history 10

SQL> history

SP2-1651: History list is empty.

SQL> select name from v$datafile;

NAME

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

/u02/app/oracle/oradata/orasql2/system01.dbf

/u02/app/oracle/oradata/orasql2/sysaux01.dbf

/u02/app/oracle/oradata/orasql2/undotbs01.dbf

/u02/app/oracle/oradata/orasql2/pdbseed/system01.dbf

/u02/app/oracle/oradata/orasql2/pdbseed/sysaux01.dbf

/u02/app/oracle/oradata/orasql2/users01.dbf

/u02/app/oracle/oradata/orasql2/pdbseed/undotbs01.dbf

/u02/app/oracle/oradata/orasql2/PDB1/system01.dbf

/u02/app/oracle/oradata/orasql2/PDB1/sysaux01.dbf

/u02/app/oracle/oradata/orasql2/PDB1/undotbs01.dbf

/u02/app/oracle/oradata/orasql2/PDB1/users01.dbf

...

SQL> history

1 select name from v$datafile;

SQL> hist

1 select name from v$datafile;

SQL> hist list

1 select name from v$datafile;

SQL> prompt Active session with sql text

Active session with sql text

SQL> column USERNAME format a14

SQL> set linesize 200

SQL> column EVENT format a30

SQL> select /*+rule */ distinct ses.SID, ses.sql_hash_value, ses.USERNAME, pro.SPID "OS PID", substr(stx.sql_text,1,200)

2 from V$SESSION ses

3 ,V$SQL stx

4 ,V$PROCESS pro

5 where ses.paddr = pro.addr

6 and ses.status = 'ACTIVE'

7 and stx.hash_value = ses.sql_hash_value ;

prompt Active session with wait

select /*+rule */ sw.event,sw.wait_time,s.username,s.sid,s.serial#,s.SQL_id from v$session s, v$session_wait sw where s.sid=sw.sid and s.USERNAME is not null and s.status = 'ACTIVE';

SID SQL_HASH_VALUE USERNAME OS PID

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

SUBSTR(STX.SQL_TEXT,1,200)

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

26 1497718616 SYSTEM 14593

select /*+rule */ distinct ses.SID, ses.sql_hash_value, ses.USERNAME, pro.SPID "OS PID", substr(stx.sql_text,1,200) from V$SESSION ses ,V$SQL stx ,V$PROCESS pro where ses.paddr = pro.addr and

SQL> Active session with wait

SQL>

EVENT WAIT_TIME USERNAME SID SERIAL# SQL_ID

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

OFS idle 0 SYS 6 2524

SQL*Net message to client -1 SYSTEM 26 23551 5rq1rg0tcjsyu

SQL>

SQL>

SQL>

SQL> hist

1 select name from v$datafile;

2 prompt Active session with sql text

3 column USERNAME format a14

4 set linesize 200

5 column EVENT format a30

6 select /*+rule */ distinct ses.SID, ses.sql_hash_value, ses.USERNAME, pro.SPID "OS PID", substr(stx.sql_text,1,200)

from V$SESSION ses

,V$SQL stx

,V$PROCESS pro

where ses.paddr = pro.addr

and ses.status = 'ACTIVE'

and stx.hash_value = ses.sql_hash_value ;

7 prompt Active session with wait

8 select /*+rule */ sw.event,sw.wait_time,s.username,s.sid,s.serial#,s.SQL_id from v$session s, v$session_wait sw where s.sid=sw.sid and s.USERNAME is not null and s.status = 'ACTIVE';

SQL> hist 8 run

EVENT WAIT_TIME USERNAME SID SERIAL# SQL_ID

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

OFS idle 0 SYS 6 2524

SQL*Net message to client -1 SYSTEM 26 23551 5rq1rg0tcjsyu

SQL>

SQL> hist 1 edit

select name,bytes/1024/1024 from v$datafile;

~

~

~

~

"history.buf" 1L, 45C written

SQL> hist

1 select name from v$datafile;

2 prompt Active session with sql text

3 column USERNAME format a14

4 set linesize 200

5 column EVENT format a30

6 select /*+rule */ distinct ses.SID, ses.sql_hash_value, ses.USERNAME, pro.SPID "OS PID", substr(stx.sql_text,1,200)

from V$SESSION ses

,V$SQL stx

,V$PROCESS pro

where ses.paddr = pro.addr

and ses.status = 'ACTIVE'

and stx.hash_value = ses.sql_hash_value ;

7 prompt Active session with wait

8 select /*+rule */ sw.event,sw.wait_time,s.username,s.sid,s.serial#,s.SQL_id from v$session s, v$session_wait sw where s.sid=sw.sid and s.USERNAME is not null and s.status = 'ACTIVE';

9 select name,bytes/1024/1024 from v$datafile;

SQL> col name for a100

SQL> hist 9 run

NAME BYTES/1024/1024

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

/u02/app/oracle/oradata/orasql2/system01.dbf 930

/u02/app/oracle/oradata/orasql2/sysaux01.dbf 830

/u02/app/oracle/oradata/orasql2/undotbs01.dbf 560

/u02/app/oracle/oradata/orasql2/pdbseed/system01.dbf 270

/u02/app/oracle/oradata/orasql2/pdbseed/sysaux01.dbf 560

/u02/app/oracle/oradata/orasql2/users01.dbf 5

/u02/app/oracle/oradata/orasql2/pdbseed/undotbs01.dbf 175

/u02/app/oracle/oradata/orasql2/PDB1/system01.dbf 280

/u02/app/oracle/oradata/orasql2/PDB1/sysaux01.dbf 610

/u02/app/oracle/oradata/orasql2/PDB1/undotbs01.dbf 175

/u02/app/oracle/oradata/orasql2/PDB1/users01.dbf 5

...

SQL> hist

1 column EVENT format a30

2 select /*+rule */ distinct ses.SID, ses.sql_hash_value, ses.USERNAME, pro.SPID "OS PID", substr(stx.sql_text,1,200)

from V$SESSION ses

,V$SQL stx

,V$PROCESS pro

where ses.paddr = pro.addr

and ses.status = 'ACTIVE'

and stx.hash_value = ses.sql_hash_value ;

3 prompt Active session with wait

4 select /*+rule */ sw.event,sw.wait_time,s.username,s.sid,s.serial#,s.SQL_id from v$session s, v$session_wait sw where s.sid=sw.sid and s.USERNAME is not null and s.status = 'ACTIVE';

5 select name,bytes/1024/1024 from v$datafile;

6 col name for a100

7 set pagesize 10000

8 /

9 col name for a120

10 /

SQL> hist 8 del

SQL> hist

1 column EVENT format a30

2 select /*+rule */ distinct ses.SID, ses.sql_hash_value, ses.USERNAME, pro.SPID "OS PID", substr(stx.sql_text,1,200)

from V$SESSION ses

,V$SQL stx

,V$PROCESS pro

where ses.paddr = pro.addr

and ses.status = 'ACTIVE'

and stx.hash_value = ses.sql_hash_value ;

3 prompt Active session with wait

4 select /*+rule */ sw.event,sw.wait_time,s.username,s.sid,s.serial#,s.SQL_id from v$session s, v$session_wait sw where s.sid=sw.sid and s.USERNAME is not null and s.status = 'ACTIVE';

5 select name,bytes/1024/1024 from v$datafile;

6 col name for a100

7 set pagesize 10000

8 col name for a120

9 /

SQL> hist 2 del

SQL> hist list

1 column EVENT format a30

2 prompt Active session with wait

3 select /*+rule */ sw.event,sw.wait_time,s.username,s.sid,s.serial#,s.SQL_id from v$session s, v$session_wait sw where s.sid=sw.sid and s.USERNAME is not null and s.status = 'ACTIVE';

4 select name,bytes/1024/1024 from v$datafile;

5 col name for a100

6 set pagesize 10000

7 col name for a120

8 /

SQL> hist clear

SQL> hist

SP2-1651: History list is empty.

SQL>

SQL> set hist off

SQL>[/code]

另外还增加了SET LOBPREFETCH、SET STATEMENTCACHE，第一个是预读取LOB字段多少字节，第二个是缓存语句到shared pool，不需要重新解析

详细官方文档：

https://docs.oracle.com/database/122/SQPUG/HISTORY.htm#SQPUG-GUID-CCF9B047-3122-4644-BA06-5FA4B5812E9F

「喜欢文章，快来给作者赞赏墨值吧」 赞赏

【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源(墨天轮)，文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

请登录后发表评论