[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.我自己还有一个疑问,如果使用它连接低版本的数据库,这些特性还能用吗? --//上班在更新自己的机器工作环境,看看是否生效.估计不行.