[toc]
Oracle Database 23c 已经 GA 了，Oracle Database 23c: The Next Long Term Support Release
We are pleased to announce that the new version of the world’s most powerful database, Oracle Database 23c, is now Generally Available on OCI Oracle Base Database Service.
安装部署 之前新版本发布之后，初步体验就是安装部署，捣鼓一堆，现在简单了，安装是最简单的了。
1、rpm 安装
2、docker 安装
3、官方提供的 Oracle VM VirtualBox ova 直接导入
rpm 和 ova 暂且不提，本次体验用 docker 安装三部曲。
rpm 在23c 预览版的时候安装过，这里截图如下：
安装 docker 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 [root@liups ~]# yum -y install docker-ce-18.06.3.ce Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile Resolving Dependencies --> Running transaction check ---> Package docker-ce.x86_64 0:18.06.3.ce-3.el7 will be installed --> Processing Dependency: container-selinux >= 2.9 for package: docker-ce-18.06.3.ce-3.el7.x86_64 --> Running transaction check ---> Package container-selinux.noarch 2:2.119.2-1.911c772.el7_8 will be installed --> Finished Dependency Resolution Dependencies Resolved ======================================================================================================================================================================================================= Package Arch Version Repository Size ======================================================================================================================================================================================================= Installing: docker-ce x86_64 18.06.3.ce-3.el7 docker-ce-stable 41 M Installing for dependencies: container-selinux noarch 2:2.119.2-1.911c772.el7_8 extras 40 k Transaction Summary ======================================================================================================================================================================================================= Install 1 Package (+1 Dependent package) Total download size: 41 M Installed size: 168 M Downloading packages: (1/2): container-selinux-2.119.2-1.911c772.el7_8.noarch.rpm | 40 kB 00:00:00 warning: /var/cache/yum/x86_64/7/docker-ce-stable/packages/docker-ce-18.06.3.ce-3.el7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID 621e9f35: NOKEY============-] 807 kB/s | 41 MB 00:00:00 ETA Public key for docker-ce-18.06.3.ce-3.el7.x86_64.rpm is not installed (2/2): docker-ce-18.06.3.ce-3.el7.x86_64.rpm | 41 MB 00:00:52 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 788 kB/s | 41 MB 00:00:52 Retrieving key from https://mirrors.aliyun.com/docker-ce/linux/centos/gpg Importing GPG key 0x621E9F35: Userid : "Docker Release (CE rpm) <docker@docker.com>" Fingerprint: 060a 61c5 1b55 8a7f 742b 77aa c52f eb6b 621e 9f35 From : https://mirrors.aliyun.com/docker-ce/linux/centos/gpg Running transaction check Running transaction test Transaction test succeeded Running transaction Warning: RPMDB altered outside of yum. Installing : 2:container-selinux-2.119.2-1.911c772.el7_8.noarch 1/2 setsebool: SELinux is disabled. Installing : docker-ce-18.06.3.ce-3.el7.x86_64 2/2 Verifying : docker-ce-18.06.3.ce-3.el7.x86_64 1/2 Verifying : 2:container-selinux-2.119.2-1.911c772.el7_8.noarch 2/2 Installed: docker-ce.x86_64 0:18.06.3.ce-3.el7 Dependency Installed: container-selinux.noarch 2:2.119.2-1.911c772.el7_8 Complete!
启动并检查 docker 的版本 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 [root@liups ~]# systemctl start docker [root@liups ~]# systemctl enable docker Created symlink from /etc/systemd/system/multi-user.target.wants/docker.service to /usr/lib/systemd/system/docker.service. [root@liups ~]# docker version Client: Version: 18.06.3-ce API version: 1.38 Go version: go1.10.3 Git commit: d7080c1 Built: Wed Feb 20 02:26:51 2019 OS/Arch: linux/amd64 Experimental: false Server: Engine: Version: 18.06.3-ce API version: 1.38 (minimum version 1.12) Go version: go1.10.3 Git commit: d7080c1 Built: Wed Feb 20 02:28:17 2019 OS/Arch: linux/amd64 Experimental: false
docker pull ORACLE 23c镜像 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 [root@liups ~]# docker pull container-registry.oracle.com/database/free Using default tag: latest latest: Pulling from database/free 089fdfcd47b7: Pull complete 43c899d88edc: Pull complete 47aa6f1886a1: Pull complete f8d07bb55995: Pull complete c31c8c658c1e: Pull complete b7d28faa08b4: Pull complete 1d0d5c628f6f: Pull complete db82a695dad3: Pull complete 25a185515793: Pull complete Digest: sha256:5ac0efa9896962f6e0e91c54e23c03ae8f140cf6ed43ca09ef4354268a942882 Status: Downloaded newer image for container-registry.oracle.com/database/free:latest [root@liups ~]# docker images REPOSITORY TAG IMAGE ID CREATED SIZE container-registry.oracle.com/database/free latest 39cabc8e6db0 2 weeks ago 9.16GB
可以看到速度还挺快。
docker 运行 ORACLE 23c 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 [root@liups ~]# docker run --name ora23c \ > -e ORACLE_PWD=Password23c \ > container-registry.oracle.com/database/free:latest Starting Oracle Net Listener. Oracle Net Listener started. Starting Oracle Database instance FREE. Oracle Database instance FREE started. The Oracle base remains unchanged with value /opt/oracle SQL*Plus: Release 23.0.0.0.0 - Production on Thu Sep 21 23:05:28 2023 Version 23.3.0.23.09 Copyright (c) 1982, 2023, Oracle. All rights reserved. Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09 SQL> User altered. SQL> User altered. SQL> Session altered. SQL> User altered. SQL> Disconnected from Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09 The Oracle base remains unchanged with value /opt/oracle # DATABASE IS READY TO USE! # The following output is now a tail of the alert.log:
可以看到非常快速的通过docker 拉起了一个 ORACLE 23c 数据库。登录测试下
登录 ORACLE 23c 5.1 本地 sys as sysdba 登录 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 [root@liups ~ ]# docker ps - a [root@liups ~ ]# docker ps - a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 4 fbe18dd538c container- registry.oracle.com/ database/ free :latest "/bin/bash -c $ORACL…" 26 seconds ago Up 25 seconds (health: starting) 0.0 .0 .0 :1521 - > 1521 / tcp ora23c[root@liups ~ ]# docker exec - it 4 fbe18dd538c bash bash-4.4 $ sqlplus / as sysdba SQL * Plus: Release 23.0 .0 .0 .0 - Production on Sat Sep 23 03 :37 :24 2023 Version 23.3 .0 .23 .09 Copyright (c) 1982 , 2023 , Oracle. All rights reserved. Connected to : Oracle Database 23 c Free Release 23.0 .0 .0 .0 - Develop, Learn, and Run for Free Version 23.3 .0 .23 .09 SQL > show parameter db_nameNAME TYPE VALUE db_name string FREE SQL > show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO 3 FREEPDB1 READ WRITE NO
5.2 远程登录 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 [oracle@liups ~ ]$ sqlplus sys/ Password23c@127 .0 .0 .1 :1521 / free as sysdba SQL * Plus: Release 12.1 .0 .2 .0 Production on Sat Sep 23 11 :48 :53 2023 Copyright (c) 1982 , 2014 , Oracle. All rights reserved. Connected to : Oracle Database 23 c Free Release 23.0 .0 .0 .0 - Develop, Learn, and Run for Free SQL > show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO 3 FREEPDB1 READ WRITE NO SQL >
新特性体验 目前对我来说比较实用的几个新特性体验如下：
Schema 级别的权限（Schema Level Privileges ）
不带 from 子句的查询（Select without FROM ）
通过别名进行 group/having （ Use column alias in GROUP BY and HAVING ）
DDL 语句的 if exists 判断(DDL statements using IF EXISTS and IF NOT EXIST(S?) )
下面分别进行测试
Schema 级别的权限 Schema 级别的权限（Schema Level Privileges ）
在 23c 之前，如果要给某个用户授予另一个用户的只读权限，另两种方式：
一种就是授予 select andy table 的权限，另一种就是挨个对每个表授予select 权限，当用户新增了表之后，还得重新新增权限。
这个场景会经常用到，就是所谓的只读用户。这个 MySQL 数据库我印象是从接触MySQL 就有这个特性的，比如 grant select on schema.* to user1;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 Oracle Database 23 c Free Release 23.0 .0 .0 .0 - Develop, Learn, and Run for Free SQL > exitDisconnected from Oracle Database 23 c Free Release 23.0 .0 .0 .0 - Develop, Learn, and Run for Free [oracle@liups admin]$ sqlplus sys/ Password23c@freepdb as sysdba SQL * Plus: Release 12.1 .0 .2 .0 Production on Sat Sep 23 12 :44 :43 2023 Copyright (c) 1982 , 2014 , Oracle. All rights reserved. Connected to : Oracle Database 23 c Free Release 23.0 .0 .0 .0 - Develop, Learn, and Run for Free SQL > show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED 3 FREEPDB1 READ WRITE NO SQL > SQL > SQL > create user hr identified by hr;User created.SQL > create user app identified by app;User created.SQL > grant connect ,resource to hr;Grant succeeded.SQL > grant connect ,resource to app;Grant succeeded.SQL > grant unlimited tablespace to hr;Grant succeeded.SQL > grant unlimited tablespace to app;Grant succeeded.SQL > conn app/ app@freepdb Connected. SQL > show user USER is "APP"SQL > create table city(name varchar2(20 ));Table created.SQL > insert into city values ('NewYork' );1 row created.SQL > create table people(name varchar2(20 ));Table created.SQL > insert into people values ('Joson' );1 row created.SQL > show user ;USER is "APP"SQL > grant select any table on schema app to hr;Grant succeeded.SQL > conn hr/ hr@freepdb Connected. SQL > select * from app.people;NAME Joson SQL > select * from app.city;NAME NewYork SQL > conn app/ app@freepdb ;Connected. SQL > create table country(name varchar2(20 ));Table created.SQL > insert into country values ('Canada' );1 row created.SQL > conn hr/ hr@freepdb Connected. SQL > select * from app.country;NAME Canada
MySQL 类似的授权：
1 grant select on db07.* to f007;
哈，是不是比 ORACLE 23c 简单多了吧。
不带 from 子句的查询 不带 from 子句的查询（Select without FROM ）
这个是 MySQL 也是很早就有了，但是 ORACLE 在23c之前，必须要有from，from dual 是搞 oracle 的同学经常常见的。
ORACLE 23c
运行数学运算
1 2 3 4 5 6 7 8 SQL > select 2 + 3 from dual; 2 + 3 5 SQL > select 2 + 3 ; 2 + 3 5
查询序列
1 2 3 4 5 6 7 8 9 10 SQL > create sequence liups_seq;Sequence created. SQL > select liups_seq.nextval from dual; NEXTVAL 1 SQL > select liups_seq.nextval ; NEXTVAL 2
查询日期
1 2 3 4 5 6 7 8 9 10 11 12 13 SQL > select current_date ;CURRENT_D 23 - SEP-23 SQL > select current_date from dual;CURRENT_D 23 - SEP-23 SQL >
MySQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 mysql> select 2 + 3 from dual; + | 2 + 3 | + | 5 | + 1 row in set (0.00 sec)mysql> select 2 + 3 ; + | 2 + 3 | + | 5 | + 1 row in set (0.00 sec)mysql> select current_date ; + | current_date | + | 2023 -09 -23 | + 1 row in set (0.00 sec)mysql> select current_date from dual; + | current_date | + | 2023 -09 -23 | + 1 row in set (0.00 sec)mysql> select @@version ; + | @@version | + | 5.7 .33 | + 1 row in set (0.00 sec)mysql>
可以看到 MySQL 这些也都早都实现了嘎。
通过别名进行 group/having 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 SQL > select OWNER o ,count (* ) from dba_objects group by o;O COUNT (* ) SYS 49636 PUBLIC 12837 OUTLN 10 SYSTEM 89 GSMADMIN_INTERNAL 293 DBSFWUSER 8 AUDSYS 56 GGSHAREDCAP 2 DBSNMP 29 APPQOSSYS 6 REMOTE_SCHEDULER_AGENT 13 XDB 1021 WMSYS 425 OJVMSYS 33 CTXSYS 419 OLAPSYS 25 MDSYS 4655 LBACSYS 239 DVF 22 DVSYS 417 APP 3 21 rows selected.SQL > select OWNER o ,count (* ) from dba_objects group by o having o= 'SYS' ;O COUNT (* ) SYS 49636 SQL >
可以看到 group by 和having 都可以使用别名了。
仍然看看MySQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 mysql> select user u,count (1 ) from user group by u; + | u | count (1 ) | + | aa | 1 | | aaa | 1 | | b | 1 | | comlps | 1 | | dd | 1 | | f007 | 1 | | liups.com | 2 | | mes | 3 | | pigpas_trade | 2 | | repl | 2 | | root | 2 | | xj | 1 | | xxc | 1 | + 13 rows in set (0.00 sec)mysql> select user u,count (1 ) from user group by u having u= 'aa' ; + | u | count (1 ) | + | aa | 1 | + 1 row in set (0.00 sec)mysql>
哈哈，MySQL 仍然可以。
DDL 语句的 if exists 判断 ORACLE 23c
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 SQL > col tname for a20SQL > select * from tab;TNAME TABTYPE CLUSTERID CITY TABLE PEOPLE TABLE COUNTRY TABLE SQL > create table city(t1 char (1 ));create table city(t1 char (1 )) * ERROR at line 1 : ORA-00955 : name is already used by an existing object SQL > create table if not exists city(t1 varchar2(10 ));Table created.SQL > desc city; Name Null ? Type NAME VARCHAR2(20 ) SQL >
注意：这里提示 Table created. 然后查看 表结果仍然是原来的，如果只有一个 Table created.可能会以为新的表创建成功了。那咱们看一下MySQL 的 if not exists.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> create table city(t1 char (1 )); Query OK, 0 rows affected (0.02 sec) mysql> create table city(t1 char (1 )); ERROR 1050 (42 S01): Table 'city' already exists mysql> create table if not exists city(t1 char (1 )); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; + | Level | Code | Message | + | Note | 1050 | Table 'city' already exists | + 1 row in set (0.00 sec)
可以看到 MySQL 这里有个 warnings 提示。比 ORACLE 23c 更友好。
测试下 exp 的导出，我们知道 MySQL 默认的 mysqldump 会自动添加 drop table if exists ，也就是表如果存在，先drop，然后在创建新的，并导入数据。现在咱们测试下 oracle 23c的 默认exp是不是呢。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 [oracle@liups ~]$ exp app/app@freepdb owner=app file=app.dmp Export: Release 12.1.0.2.0 - Production on Sat Sep 23 16:51:46 2023 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) . exporting pre-schema procedural objects and actions . exporting foreign function library names for user APP . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user APP About to export APP's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export APP' s tables via Conventional Path .... . exporting table CITY 1 rows exported EXP-00091: Exporting questionable statistics. . . exporting table COUNTRY 1 rows exported EXP-00091: Exporting questionable statistics. . . exporting table PEOPLE 1 rows exported EXP-00091: Exporting questionable statistics. . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs EXP-00056: ORACLE error 942 encountered ORA-00942: table or view does not exist EXP-00000: Export terminated unsuccessfully [oracle@liups ~]$ imp app/app@freepdb file=app.dmp full=y Import: Release 12.1.0.2.0 - Production on Sat Sep 23 16:52:21 2023 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Export file created by EXPORT:V12.01.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing APP's objects into APP IMP-00015: following statement failed because the object already exists: "CREATE TABLE "CITY" ("NAME" VARCHAR2(20)) PCTFREE 10 PCTUSED 40 INITRANS 1" " MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 F" "REELIST GROUPS 1 BUFFER_POOL DEFAULT) LOGGING NOCOMPRESS" IMP-00015: following statement failed because the object already exists: "CREATE TABLE "COUNTRY" ("NAME" VARCHAR2(20)) PCTFREE 10 PCTUSED 40 INITRAN" "S 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS " "1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) LOGGING NOCOMPR" "ESS" IMP-00015: following statement failed because the object already exists: "CREATE TABLE "PEOPLE" ("NAME" VARCHAR2(20)) PCTFREE 10 PCTUSED 40 INITRANS" " 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1" " FREELIST GROUPS 1 BUFFER_POOL DEFAULT) LOGGING NOCOMPRE" "SS" IMP-00009: abnormal end of export file Import terminated successfully with warnings.
可以看到 imp 导入的时候仍然报错了。但是MySQL 就友好的多了，默认添加了 drop if exists，能够直接导入成功。
总之，这些新特性对于 ORACLE 来说确实是个新特性，但是对于MySQL 来说，已经是早就实现的功能了。从以上测试个人觉得还是MySQL 比较友好。
原文作者:
liups.com
原文链接:
http://liups.cn/posts/aa3053f6/
许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议
