oracle 12 sqlplus 使用,oracle 12.2 sqlplus history
12.1開始使用sqlplus顯示用戶上次登錄時(shí)間，12.2提供了一個(gè)記錄歷史sql的功能，類似于操作系統(tǒng)的history，根目錄下的.history文件，不過功能比操作系統(tǒng)的history要強(qiáng)大，可以指定刪除/運(yùn)行/編輯歷史記錄里面的某一條SQL，比操作系統(tǒng)的上翻歷史命令都方便。
history默認(rèn)關(guān)閉，下面是打開關(guān)閉history功能，設(shè)置緩存多少條記錄：
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 指定運(yùn)行/編輯/刪除第N條記錄
hist clear/list 清除/列出歷史記錄
編輯需要設(shè)置編輯器define _editor = vi，且編輯后的記錄顯示在最后一條
hist默認(rèn)就是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，第一個(gè)是預(yù)讀取LOB字段多少字節(jié)，第二個(gè)是緩存語句到shared pool，不需要重新解析
詳細(xì)官方文檔：
https://docs.oracle.com/database/122/SQPUG/HISTORY.htm#SQPUG-GUID-CCF9B047-3122-4644-BA06-5FA4B5812E9F
「喜歡文章，快來給作者贊賞墨值吧」 贊賞
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容，轉(zhuǎn)載時(shí)必須標(biāo)注文章的來源(墨天輪)，文章鏈接，文章作者等基本信息，否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容，歡迎發(fā)送郵件至：contact@modb.pro進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，墨天輪將立刻刪除相關(guān)內(nèi)容。
評(píng)論
請(qǐng)登錄后發(fā)表評(píng)論
總結(jié)
以上是生活随笔為你收集整理的oracle 12 sqlplus 使用,oracle 12.2 sqlplus history的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 怎么才能点一下excel中的超链接就显示
- 下一篇： linux tcp cork,在此用例中