在Docker中快速体验Oracle 23c免费开发者版

AiDBA宝典

发布于 2023-04-27 13:59:28

1.3K0

发布于 2023-04-27 13:59:28

docker环境下载

docker rm -f lhroel87
docker run -itd --name lhroel87 -h lhroel87 \
  -p 1521:1521 -p 38389:3389 \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --privileged=true lhrbest/oracle23cfree:1.0 \
  /usr/sbin/init

 docker exec -it lhroel87 bash

启动数据库和监听

/etc/init.d/oracle-free-23c status
/etc/init.d/oracle-free-23c start

示例：

[root@lhroel87 /]# /etc/init.d/oracle-free-23c status
Status of the Oracle FREE 23c service:

LISTENER status: STOPPED
FREE Database status:   STOPPED
[root@lhroel87 /]# /etc/init.d/oracle-free-23c start 
Starting Oracle Net Listener.
Oracle Net Listener started.
Starting Oracle Database instance FREE.
Oracle Database instance FREE started.

[root@lhroel87 /]# /etc/init.d/oracle-free-23c status
Status of the Oracle FREE 23c service:

LISTENER status: RUNNING
FREE Database status:   RUNNING
[root@lhroel87 /]# su - oracle
Last login: Fri Apr  7 03:04:15 UTC 2023 on pts/1
[oracle@lhroel87 ~]$

测试新特性

[oracle@lhroel87 ~]$ sas

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Fri Apr 7 02:45:24 2023
Version 23.2.0.0.0

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ WRITE NO

(1) 不带FROM子句的SELECT查询

在Oracle 23c中，第一次实现了不带From子句的查询，也不需要dual，就是跟SQL Server，MySQL一样了

SQL> select 1;

         1
----------
         1

SQL>  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Session altered.

SQL>  SELECT SYSDATE;

SYSDATE
-------------------
2023-04-07 03:05:51

SQL>

(2) 单表支持4096列

Oracle 此前版本单表支持1000列。

在23c中，单表支持列数量扩展到4096列,启用这一个特性需要将兼容性参数设置为23.0.0，同时将 Max_columns设置为Extended。

SQL> show parameter MAX_COLUMNS;           
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_columns                          string      STANDARD

SQL> alter system set MAX_COLUMNS=EXTENDED scope=spfile;

-- 注：静态参数修改，需要重启数据库
SQL> shu immediate;
SQL> startup
SQL> show parameter MAX_COLUMNS; 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_columns                          string      EXTENDED

（3）SCHEMA级别的权限

在23c之前的版本，如果针对Schema对其他用户进行授权，需要通过系统权限或对象权限分别显式地授予，这对数据库带来了额外的安全风险或复杂性。

在Oracle 23中，可以对Schema进行授权，简化了之前的权限操作，

grant select any table on SCHMEA GSMUSER to DIP;

(4)Boolean数据类型

在Oracle Database 23c中，布尔数据类型被支持

create table test(name varchar2(100),flag BOOLEAN);
INSERT INTO test VALUES('dsss',True);
INSERT INTO test VALUES('hefei',1);
INSERT INTO test VALUES('abc',FALSE);

select name from test where flag;

SQL> select name from test where flag;
NAME
------------
dsss
hefei

(5)GROUP BY可以使用别名了

在Oracle Database 23c中,现在支持基于别名、位置的GROUP BY，这大大简化了SQL文本和编写

select extract(year FROM hire_date) as hire_year,count(*)
from employees
group by hire_year
having hire_year>1985;

(6)DDL的 IF EXISTS判断

在Oracle Database 23c中,DDL支持通过 IF [NOT] EXISTS 判断，从而规避执行过程中的错误、异常和中断

-- 创建表时指定：
CREATE TABLE IF NOT EXISTS Customers (ID NUMBER(10), Name VARCHAR2(100));

-- 删除表时指定：
DROP TABLE IF EXISTS Customers;


SQL> create table if not exists t1(id int);

Table created.

SQL> create table if not exists t1(id int);

Table created.

SQL> create table t1(id int);
create table t1(id int)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

表值函数增强

SQL> select * from (values (1,'zhang san'),  (2,'lisi'),(3,'wangerma') ) t(id,name);

        ID NAME
---------- ---------
         1 zhang san
         2 lisi
         3 wangerma

客户端使用

使用sqlplus远程连接：

C:\Users\lhr>sqlplus sys/lhr@192.18.0.14:1521/FREE as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Apr 7 12:16:16 2023
Version 21.3.0.0.0

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ WRITE NO
SQL>

C:\Users\lhr>sqlplus sys/lhr@172.18.0.14:1521/freepdb1 as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Apr 7 12:16:53 2023
Version 21.3.0.0.0

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 FREEPDB1                       READ WRITE NO
SQL>

使用PL/SQL Developer连接：

官方docker安装使用

参考：https://container-registry.oracle.com

-- 创建
docker pull container-registry.oracle.com/database/free:latest

docker rm -f oracle23cfree
docker run -d --name oracle23cfree -h oracle23cfree \
  -p 1621:1521 \
container-registry.oracle.com/database/free:latest

-- 日志
docker logs -f oracle23cfree