[20181014]12cR2 sqlplus新特性.txt

最新推荐文章于 2024-10-17 18:58:27 发布

cuixun7780

最新推荐文章于 2024-10-17 18:58:27 发布

阅读量110

点赞数

文章标签：数据库

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