文章导航
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) "
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>
新特性体验以及与 MySQL 对比
目前对我来说比较实用的几个新特性体验如下：
-
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 类似的授权：
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 比较友好。
