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