Oracle 18c 新特性 (一）小特性

原创 Anbob 2018-03-04

919

oracle 18c当前很受关注的数据库，Oracle OOW上宣传的第一个自治数据库也是基于oracle 18c（实际oracle 18c不是自治数据库）。当前oracle 18c是只发布了在Oracle Cloud 和Oracle Exadata等Engineered Systems上版本，这也是之前我weibo（@weejar）调侃到以后的大版本第一版都是在云端和Engineered Systems首发，也就解决了出.1 版普遍不敢使用的尴尬^_^。
PS.因当前oracle 18c on-premise 还没正式发布，我只是用XD版简单测试几个小功能:
sys@cdb$root:anbob18c> SELECT BANNER FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Feedback sqlid

private temporary table

CANCEL SQL statement

SEQUENCE增强

Read-only Oracle Home (ROOH)

DBMS_SESSION.SLEEP

Inline External Table

1， Feedback sqlid

在SQL执行成功后，sqlplus 中返回SQL id.

sys@cdb$root:anbob18c> SET FEEDBACK ON SQL_ID
sys@cdb$root:anbob18c> select * from dual;
D
-
X
SQL_ID: a5ks9fhw2v9s1
复制

2, CANCEL SQL statement

18c 引入CANCEL SQL statement ，终止正在运行的SQL，而不是会话。

-- session 1
SQL> select count(*) from dba_objects,dba_objects,dba_objects;
running ....
-- session 2
sys@cdb$root:anbob18c> @usid 32
USERNAME                SID                 AUDSID OSUSER           MACHINE
----------------------- -------------- ----------- ---------------- ------------------
PROGRAM              SPID             OPID CPID                     SQL_ID         HASH_VALUE
-------------------- -------------- ------ ------------------------ ------------- -----------
  LASTCALL STATUS   SADDR            PADDR            TADDR            LOGON_TIME
---------- -------- ---------------- ---------------- ---------------- --------------------
SYS                      '32,52057'     4294967295 oracle           anbob
(TNS V1-V3)          5238               33 5237                     f5kskn9df2h2p  1524711509
         9 ACTIVE   000000006B2FC9E8 000000006CC99308                  03-MAR-2018 22:16:14

--语法：ALTER SYSTEM CANCEL SQL 'SID, SERIAL[, @INST_ID][, SQL_ID]';复制

sys@cdb$root:anbob18c> ALTER SYSTEM CANCEL SQL '32,52057,f5kskn9df2h2p'; 
System altered. 
-- session 1 
SQL> select count(*) from dba_objects,dba_objects,dba_objects; 
select count(*) from dba_objects,dba_objects,dba_objects 
* ERROR at line 1: 
ORA-01013: user requested cancel of current operation 
SQL> select distinct sid from v$mystat; 
SID 
---------- 
32复制

3, private temporary table

在内存中创建的会话级和事务级临时表，在会话或事务结束时会根据设置而丢弃。有点像SQL server Tsql存储过程中的的临时表。有别于global temporary table，私有临时表在其它会话对象都不存在,表名只能是指定的参数开头。

sys@cdb$root:anbob18c> show parameter prefix
PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ------------------
common_user_prefix                                           string      C##
os_authent_prefix                                            string      ops$
private_temp_table_prefix                                    string      ORA$PTT_
SQL> create private temporary table ORA$PTT_MY_TT ( x int ) ;
SQL> select count(*)  
from   all_objects  
where  object_name = 'ORA$PTT_MY_T1' 
-- NONE
复制

4, SEQUENCE增强

Sequence SCALE EXTEND ? 去年8月份就写过一篇不再描述可以移步这里
sequence 可以reset了，不用删了重建。

sys@cdb$root:anbob18c> create sequence seq_1 start with 100;
Sequence created.
sys@cdb$root:anbob18c> select seq_1.nextval from dual connect by rownum<=3; 
NEXTVAL 
---------- 
100 
101 
102 
sys@cdb$root:anbob18c> alter sequence seq_1 restart;
Sequence altered.
sys@cdb$root:anbob18c> select seq_1.nextval from dual;
   NEXTVAL
----------
         1
sys@cdb$root:anbob18c> alter sequence seq_1 restart start with 100;
Sequence altered.
sys@cdb$root:anbob18c> select seq_1.nextval from dual;
   NEXTVAL
----------
       100
复制

5, Read-only Oracle Home (ROOH)

只读ORACLE HOME是oracle 软件安装一大改进，ORACLE BASE不再允许和HOME同一个目录，同样也简化了安装。对于Docker容器类环境非常合适，把ORACLE HOME只读而把配置文件到放外面oraclebasehome下，更容易管理空间，通过克隆Oracle Home轻松部署。
缺省默认ORACLE_HOME是read-write 模式，如果要启动read-only OH, 需要在安装完软件而创建数据库前用roohctl 工具改变，否则会提示
[oracle@anbob oracle]$ which roohctl
/u01/app/oracle/bin/roohctl
[oracle@anbob oracle]$ roohctl -enable
Enabling Read-Only Oracle home.
Cannot enable Read-Only Oracle home in a configured Oracle home.
The Oracle Home is configured with databases 'anbob18c'.
虽然是roohctl 有一个force 选项(undocumented),但是转换后参数文件也不会转移到orabasehome下。roohctl工具在12.2时就已悄悄引入，只是到了18c -disable补去掉实际功能还存在。

[oracle@VM122 ~]$ roohctl -help
Usage:  roohctl [] [ ]
Following are the possible flags:
        -help
 
Following are the possible commands:
        -enable Enable Read-only Oracle Home
        -disable Disable Read-only Oracle Home
[oracle@anbob18c ~]$ roohctl -help
Usage:  roohctl [] []
Following are the possible flags: 
        -help 
Following are the possible commands: 
        -enable Enable Read-only Oracle Home
                [-nodeList List of nodes in a cluster environment]
复制

启动方法
[oracle@VM181 18c]$ roohctl -enable
Enabling Read-Only Oracle home.
Update orabasetab file to enable Read-Only Oracle home.
Orabasetab file has been updated successfully.
Create bootstrap directories for Read-Only Oracle home.
Bootstrap directories have been created successfully.
Bootstrap files have been processed successfully.
Read-Only Oracle home has been enabled successfully.
Check the log file /u01/app/oracle/cfgtoollogs/roohctl/roohctl-180217PM111551.log.
禁用方法
[oracle@VM181 18c]$ roohctl -disable
启动了Read Only Oracle Home后，配置文件会在ORACLEBASEHOME /dbs下和以前一样保证唯一性。只不过oraclebasehome 在只读模式下ORACLE_BASE,而读写模式下是ORACLE_HOME 路径。可以使用orabaseconfig查看orabasehome配置路径。同样可以查看orabasetab文件，如果最后一位是Y 说明是ROOH。

[oracle@anbob ~]$ cat $ORACLE_HOME/install/orabasetab
#orabasetab file is used to track Oracle Home associated with Oracle Base
/u01/app/oracle:/u01/orabase:OraDB18Home1:N:
The $ORACLE_HOME/install/orabasetab records the ORACLE_HOME, ORACLE_BASE and ORACLE_HOME_NAME:
复制

6， DBMS_SESSION.sleep

在oracle 18c中sleep 存储过程填加到DBMS_SESSION PACKAGE中，这样所有会话都可以直接使用，不需要再授权DBMS_LOCK PACKAGE。

sys@cdb$root:anbob18c> SET SERVEROUTPUT ON复制

sys@cdb$root:anbob18c> BEGIN
 DBMS_OUTPUT.put_line('Time 1: ' || TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF'));
 -- Pause for 1.5 second.
 DBMS_SESSION.sleep(1.5);
 DBMS_OUTPUT.put_line('Time 2: ' || TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF'));
END;
sys@cdb$root:anbob18c>
Time 1: 08:47:38.445748000
Time 2: 08:47:40.181196000
 PL/SQL procedure successfully completed.复制

7， Inline External Table

inline external table允许把外部表的定义写在SQL中，而不用先创建外部表对象。

[oracle@anbob ~]$ rm /tmp/et.txt
[oracle@anbob ~]$ rm /tmp/et1.txt
[oracle@anbob ~]$ for i in {1..5}; do echo $i',anbob'$i >> /tmp/et.txt; done; 
[oracle@anbob ~]$ cat /tmp/et.txt
1,anbob1
2,anbob2
3,anbob3
4,anbob4
5,anbob5
[oracle@anbob ~]$ for i in {6..10}; do echo $i',anbob'$i >> /tmp/et1.txt; done;  
[oracle@anbob ~]$ cat /tmp/et1.txt
6,anbob6
7,anbob7
8,anbob8
9,anbob9
10,anbob10
sys@cdb$root:anbob18c> CREATE OR REPLACE DIRECTORY tmp_dir1 AS '/tmp/';
Directory created.
sys@cdb$root:anbob18c>  SELECT *
  2  FROM   EXTERNAL (
  3           (
  4             id  number,
  5             name    VARCHAR2(128)
  6           )
  7           TYPE oracle_loader
  8           DEFAULT DIRECTORY tmp_dir1
  9           ACCESS PARAMETERS (
 10             RECORDS DELIMITED BY NEWLINE
 11             BADFILE tmp_dir1
 12             LOGFILE tmp_dir1:'inline_ext_tab_%a_%p.log'
 13             DISCARDFILE tmp_dir1
 14             FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 15             MISSING FIELD VALUES ARE NULL (
 16               id,
 17   name
 18             )
 19          )
 20          LOCATION ('et.txt', 'et1.txt')
 21          REJECT LIMIT UNLIMITED
 22        ) inline_ext_tab;
        ID NAME
---------- ---------------------------------------------
         1 anbob1
         2 anbob2
         3 anbob3
         4 anbob4
         5 anbob5
         6 anbob6
         7 anbob7
         8 anbob8
         9 anbob9
        10 anbob10
复制

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者