首页 » ORACLE [C]系列, ORACLE 9i-23c » Oracle 23c 几个开发相关新特性

Oracle 23c 几个开发相关新特性

Oracle 23c是19c后又一个长期支持版本（long term release），因为疫情的影响和Oracle版本策略调整，19c后一直未发部可本地部署的版本，23c 目前还是beta版仅ACE Direct和合作伙伴等部分人员下载测试，今天10月份William Hardie发部了申请beta的申请方式， 小测一下23c的几个新特性。初步体验是这个大综合体又开始兼容其它数据库的语法了。

[grid@db1 ~]$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [23.0.0.0.0]
[grid@db1 ~]$
[grid@db1 ~]$ exit
logout
[root@db1 ~]# su - oracle
[oracle@db1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Beta on Fri Dec 16 17:52:17 2022
Version 23.1.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to:
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Beta
Version 23.1.0.0.0

SQL> select count(*) from x$ksppi;
  COUNT(*)
----------
      6617

Note
数据库参数又上升了一个等级，12c时4400+， 19c时4900+, 21c时5500+， 23c已经6600+

SQL> create sequence seq1;
Sequence created.

SQL> select seq1.nextval;
   NEXTVAL
----------
         1

Note:
对于MySQL DBA有点眼熟，从23c支持select without from，和MySQL一样如select 1+1等计算。

SQL> create user u1 identified by u1;
User created.

SQL> create user anbob identified by anbob;
User created.

SQL> grant select any table ON SCHEMA anbob TO  u1;
Grant succeeded.

SQL> create table if not exists  anbob.t1(id int, isok boolean);
Table created.

SQL> exit
Disconnected from Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Beta
Version 23.1.0.0.0

Note:
有些应用出于安全控制，把owner与应用访问用户分开，对于每一个单独对象授权， 但是对于授权后创建的对象需要再次授权， 也有不少用户是把这个schema里的对象给某些权限给另一个用户，23c 终于等来grant xx ON SCHEMA to xx的授权。下面我们测试授权后新创建的对象是否直接有权限。

还有一个创建表时可以像mysql一样 指定if not exists,防止报错。

[oracle@db1 ~]$ export ORACLE_PDB_SID=ANBOBPDB1

[oracle@db1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Beta on Fri Dec 16 18:03:48 2022
Version 23.1.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Beta
Version 23.1.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ANBOBPDB1                       READ WRITE NO

SQL> grant DEVELOPER to anbob,u1;
Grant succeeded.

SQL> r
  1* select * from role_sys_privs where role='DEVELOPER'

ROLE                           PRIVILEGE                                ADM COM INH
------------------------------ ---------------------------------------- --- --- ---
DEVELOPER                      CREATE DOMAIN                            NO  YES YES
DEVELOPER                      CREATE MLE                               NO  YES YES
DEVELOPER                      CREATE ANALYTIC VIEW                     NO  YES YES
DEVELOPER                      CREATE HIERARCHY                         NO  YES YES
DEVELOPER                      CREATE ATTRIBUTE DIMENSION               NO  YES YES
DEVELOPER                      EXECUTE DYNAMIC MLE                      NO  YES YES
DEVELOPER                      CREATE CUBE BUILD PROCESS                NO  YES YES
DEVELOPER                      CREATE CUBE                              NO  YES YES
DEVELOPER                      CREATE CUBE DIMENSION                    NO  YES YES
DEVELOPER                      CREATE MINING MODEL                      NO  YES YES
DEVELOPER                      CREATE JOB                               NO  YES YES

ROLE                           PRIVILEGE                                ADM COM INH
------------------------------ ---------------------------------------- --- --- ---
DEVELOPER                      DEBUG CONNECT SESSION                    NO  YES YES
DEVELOPER                      ON COMMIT REFRESH                        NO  YES YES
DEVELOPER                      CREATE DIMENSION                         NO  YES YES
DEVELOPER                      CREATE TYPE                              NO  YES YES
DEVELOPER                      CREATE MATERIALIZED VIEW                 NO  YES YES
DEVELOPER                      CREATE TRIGGER                           NO  YES YES
DEVELOPER                      CREATE PROCEDURE                         NO  YES YES
DEVELOPER                      FORCE TRANSACTION                        NO  YES YES
DEVELOPER                      CREATE SEQUENCE                          NO  YES YES
DEVELOPER                      CREATE VIEW                              NO  YES YES
DEVELOPER                      CREATE SYNONYM                           NO  YES YES

ROLE                           PRIVILEGE                                ADM COM INH
------------------------------ ---------------------------------------- --- --- ---
DEVELOPER                      CREATE TABLE                             NO  YES YES
DEVELOPER                      CREATE SESSION                           NO  YES YES

24 rows selected.


[oracle@db1 ~]$ sqlplus u1/u1@172.20.22.166/ANBOBpdb1

SQL*Plus: Release 23.0.0.0.0 - Beta on Fri Dec 16 18:04:51 2022
Version 23.1.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Beta
Version 23.1.0.0.0

SQL> select * from anbob.t1;
no rows selected

SQL> desc anbob.t1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 ISOK                                               BOOLEAN

Note:
on schema授权后新创建对象无需再单独授权，这里看到23c 又引入了一个新特性一个叫DEVELOPER 的新ROLE。 还有 一个特性是从23c 开始支持Bollean 布尔类型。

SQL> with x(c1,c2) as(
values(1,true),(2,true),(3,false))
select * from x;  2    3

        C1 C2
---------- -----------
         1 TRUE
         2 TRUE
         3 FALSE

SQL> conn / as sysdba
Connected.

SQL> alter user anbob quota 10m on users;
User altered.

[oracle@db1 ~]$ sqlplus anbob/anbob@172.20.22.166/ANBOBpdb1

SQL*Plus: Release 23.0.0.0.0 - Beta on Fri Dec 16 18:23:27 2022
Version 23.1.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Fri Dec 16 2022 18:19:40 +08:00

Connected to:
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Beta
Version 23.1.0.0.0

SQL> insert into anbob.t2
with x(c1,c2) as(
VALUES(1,true),(2,true),(3,false))
select * from x;  2    3    4

3 rows created.

Note:
从23C开始像其它一些数据库一样，values 直接一组数据的写法，更甚至可以用在with中。

SQL> select * from t2;

        ID ISOK
---------- -----------
         1 TRUE
         2 TRUE
         3 FALSE

SQL> insert into t1 values(1,false);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from t1;

        ID ISOK
---------- -----------
         1 FALSE

SQL> update t1 set t1.isok=t2.isok from t2 where t1.id=t2.id;

1 row updated.

SQL> select * from t1;

        ID ISOK
---------- -----------
         1 TRUE

Note:
对于sql server DBA可能比较眼熟， oracle的update 表关连语法好多开发的都不太会写，或是不好理解，从ORACLE 23C开始支持像sql server一样的update语法新特性。

在postgresql中对于自定议数据类型可以使用domain， oracle之前本来就有type类型，来做为自定义数据类型，但是从23c还是引入了domain.
SQL> create domain addr varchar2(100);
create domain addr varchar2(100)
                   *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> create domain email as varchar2(100) constraint email_c
  2  check(regexp_like(email,'^(\S+)@(\S+)\.(\S+)$'));
Domain created.

SQL> create table t3( id int, inv_email email);
create table t3( id int, inv_email email)
                                   *
ERROR at line 1:
ORA-00902: invalid datatype

SQL> create table t3(id int, inv_email varchar2(500) domain email not null);
Table created.

SQL> insert into t3 values(1,'a');
insert into t3 values(1,'a')
*
ERROR at line 1:
ORA-02290: check constraint (SYS.SYS_C008297) violated

SQL> insert into t3 values(1,'a@a.com');
1 row created.

Note:
从23c开始开始支持domain，但是和postgresql有区别，增加了更多的功能，如约束。

[oracle@db1 ~]$ sqlplus u1/u1@172.20.22.166/ANBOBpdb12

SQL*Plus: Release 23.0.0.0.0 - Beta on Fri Dec 16 18:17:59 2022
Version 23.1.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.

ERROR:
ORA-12514: Cannot connect to database. Service ANBOBpdb12 is not registered with
the listener at host 172.20.22.166 port 1521.
(CONNECTION_ID=7/BIuJ2bjnzgU6YWFKzFUA==)

Note:
23c除了引入新功能外，对于报错信息也更友好。

整理感觉方向是敌无我有，敌有我优，一统江湖是大趋势。 但，一声叹息。

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：