[20181014]12cR2 sqlplus新特性.txt

[20181014]12cR2 sqlplus新特性.txt

--//前幾天講了SET FEEDBACK ONLY.在接著介紹另外幾個新特性.

1.環境:
SCOTT@test01p> @　ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.command history:
--//12cR2支援命令歷史.可以顯示過去執行的命令調入重複執行:
SCOTT@test01p> set history 10
SCOTT@test01p> select * from dept where deptno=10;

    DEPTNO DNAME                LOC
---------- -------------------- -------------
        10 ACCOUNTING           NEW YORK

SCOTT@test01p> select sysdate from dual ;
SYSDATE
-------------------
2018-10-14 20:28:06

SCOTT@test01p> history
  1  select * from dept where deptno=10;
  2  select sysdate from dual ;

SCOTT@test01p> history 1 run
    DEPTNO DNAME                LOC
---------- -------------------- -------------
        10 ACCOUNTING           NEW YORK
 
SCOTT@test01p> history 2 edit
--//編輯後放入第3位置.
SCOTT@test01p> history
  1  select * from dept where deptno=10;
  2  select sysdate from dual ;
  3  select user from dual ;

SCOTT@test01p> history 3 run
USER
--------------------
SCOTT

SCOTT@test01p> history  clear
SCOTT@test01p> history
SP2-1651: History list is empty.

3.12cR2支援變數定義以及直接賦值,前面版本不行:

SCOTT@test01p> variable a number ;
SCOTT@test01p> exec :a := 10;
PL/SQL procedure successfully completed.

--//12cR2可以直接賦值:
SCOTT@test01p> variable b number = 20;
--//注意這裡賦值沒有冒號在等號前面.orcle的語法太不統一.
SCOTT@test01p> print b
         B
----------
        20

SCOTT@test01p> print :b
         B
----------
        20        

SCOTT@test01p> select * from dept where deptno = :b ;
    DEPTNO DNAME                LOC
---------- -------------------- -------------
        20 RESEARCH             DALLAS

4.Statement caching:
--//Statement caching有點像軟軟解析.
SCOTT@test01p> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
       183       5778 5224:5288                DEDICATED 4932                      62         13 alter system kill session '183,5778' immediate;

variable b number = 20;
Select * from dept where deptno = :b ;
Select * from dept where deptno = :b ;

--//透過查詢執行計劃,確定sql_id=2b073tss4h1f3.
SCOTT@test01p> select sql_id,executions,parse_calls,sql_text from v$sql where sql_id='2b073tss4h1f3';
SQL_ID        EXECUTIONS PARSE_CALLS SQL_TEXT
------------- ---------- ----------- ------------------------------------------------------------
2b073tss4h1f3          2           2 Select * from dept where deptno = :b

--//執行2次,分析2次.
--//如果採用Statement caching
--//sesson 1:
set statementcache 100
variable c number = 10;
select * from dept where deptno = :c ;
select * from dept where deptno = :c ;
select sysdate from dual;
--//透過查詢執行計劃,確定sql_id='abzxwsyzmsu8h',另外開啟會話,登入cdb:

--//sesson 2:
SYS@test> select sql_id,executions,parse_calls,sql_text from v$sql where sql_id='abzxwsyzmsu8h';
SQL_ID        EXECUTIONS PARSE_CALLS SQL_TEXT
------------- ---------- ----------- ------------------------------------------------------------
abzxwsyzmsu8h          2           1 select * from dept where deptno = :c
--//執行2次,分析1次.
SYS@test> @ sharepool/shp4 abzxwsyzmsu8h 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 000007FF11BBEA90 000007FF00093CA0 select * from dept where deptno = :c              1          0          0 000007FF11B96238 000007FF13353C48       4032      16200       3190     23422      23422 3208407312 abzxwsyzmsu8h          0
父遊標控制程式碼地址 000007FF00093CA0 000007FF00093CA0 select * from dept where deptno = :c              1          0          0 000007FF132EB0B0 00                     4072          0          0      4072       4072 3208407312 abzxwsyzmsu8h      65535
--//KGLHDLMD=1.session 1最後執行的是select sysdate from dual;

--//session 1:
SCOTT@test01p> variable c number = 30;
SCOTT@test01p> select * from dept where deptno = :c ;
    DEPTNO DNAME                LOC
---------- -------------------- -------------
        30 SALES                CHICAGO

SCOTT@test01p> variable c number = 30;
SCOTT@test01p> select * from dept where deptno = :c ;
    DEPTNO DNAME                LOC
---------- -------------------- -------------
        30 SALES                CHICAGO

SYS@test> select sql_id,executions,parse_calls,sql_text from v$sql where sql_id='abzxwsyzmsu8h';
SQL_ID        EXECUTIONS PARSE_CALLS SQL_TEXT
------------- ---------- ----------- ------------------------------------------------------------
abzxwsyzmsu8h          3           1 select * from dept where deptno = :c
--//執行3次,分析1次.

5.支援CSV資料格式顯示:
SCOTT@test01p> set markup csv on
SCOTT@test01p> select * from dept;

"DEPTNO","DNAME","LOC"
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"

SCOTT@test01p> set markup csv on delimiter '|'
SCOTT@test01p> select * from dept;
"DEPTNO"|"DNAME"|"LOC"
10|"ACCOUNTING"|"NEW YORK"
20|"RESEARCH"|"DALLAS"
30|"SALES"|"CHICAGO"
40|"OPERATIONS"|"BOSTON"

SCOTT@test01p> set markup csv on delimiter '|' quote off
SCOTT@test01p> select * from dept;

DEPTNO|DNAME|LOC
10|ACCOUNTING|NEW YORK
20|RESEARCH|DALLAS
30|SALES|CHICAGO
40|OPERATIONS|BOSTON

SCOTT@test01p> set markup csv off
SCOTT@test01p> select * from dept;
    DEPTNO DNAME                LOC
---------- -------------------- -------------
        10 ACCOUNTING           NEW YORK
        20 RESEARCH             DALLAS
        30 SALES                CHICAGO
        40 OPERATIONS           BOSTON

6.我自己還有一個疑問,如果使用它連線低版本的資料庫,這些特性還能用嗎?
--//上班在更新自己的機器工作環境,看看是否生效.估計不行.