[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

[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) <[email protected]>"
 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 的版本

[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镜像

[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

[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 登录

[root@liups ~]# docker ps -a
[root@liups ~]# docker ps -a
CONTAINER ID        IMAGE                                                COMMAND                  CREATED             STATUS                             PORTS                    NAMES
4fbe18dd538c        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 4fbe18dd538c 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 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09

SQL>  show parameter db_name

NAME				     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 远程登录

[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 23c 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;

Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

SQL> exit
Disconnected from Oracle Database 23c 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 23c 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

运行数学运算

SQL>  select 2+3 from dual;
       2+3
----------
	 5
SQL>  select 2+3;
       2+3
----------
	 5

查询序列

SQL>  create sequence liups_seq;
Sequence created.
SQL> select liups_seq.nextval from dual;
   NEXTVAL
----------
	 1
SQL> select liups_seq.nextval ;
   NEXTVAL
----------
	 2

查询日期

SQL>  select current_date;

CURRENT_D
---------
23-SEP-23

SQL> select current_date from dual;

CURRENT_D
---------
23-SEP-23

SQL>

MySQL


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

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

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

SQL> col tname for a20
SQL> 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.

mysql>  create table city(t1 char(1));
Query OK, 0 rows affected (0.02 sec)

mysql>  create table city(t1 char(1));
ERROR 1050 (42S01): 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是不是呢。

[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://www.liups.com/posts/aa3053f6/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议

ORACLE 23c 初体验

文章目录