分分钟安装体验Oracle 23ai，SQL*Plus细致入微的创新
最近有朋友在问Oracle 23ai 如何下载和体验，这篇简单的分享，在 Oracle 23ai 版本的安装过程中，可以体验到一系列令人振奋的新特性和功能。从下载安装到启动运行，整个过程简单而顺畅，展现了 Oracle 在技术创新和用户体验方面的不断进步。让我们一起探索 Oracle 23ai 版本带来的全新体验，感受其为开发者和企业带来的价值和便利。您网络如果没有瓶颈，几分钟就可以安装部署一套oracle 23ai, 小试一下sqlplus客户端的微创新。
Oralce free 23ai 安装体验.
1，安装oracle VirtualBox (100MB+)
下载链接 https://www.virtualbox.org/wiki/Downloads
2，下载oracle 23ai free Oracle VM VirtualBox镜像文件 Oracle_Database_23ai_Free_Developer.ova (5GB+)
下载链接 https://www.oracle.com/database/free/get-started 选择Oracle_Database_23ai_Free_Developer.ova文件
3，VBOX导入 oracle 23ai free虚拟机
自带Oracle Linux 8.9+ oracle 23ai free （Oracle Database 23ai Free Release 23.0.0.0.0 – Develop, Learn, and Run for Free Version 23.4.0.24.05）
4，配置虚拟机的网络，打开虚拟机就可以体验Oracle 23ai free了。
自动打开一个html，在桌面上的文件，显示了当前数据库的oracle home ,sid, 链接方式，监听端口等。
root, oracle及数据库内的sys,system密码全是oracle。
注意 默认创建了一个叫FREEPDB1的pdb.
SQLPLUS工具细致入微
1, sqlplus / as sysdba 无法直接登录？
[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - Production on Thu May 9 14:09:13 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. ERROR: ORA-01017: invalid credential or not authorized; logon denied Help: https://docs.oracle.com/error-help/db/ora-01017/
原因：
默认配置了TWO_TASK变量，之前我blog也记录过，算是直接登录PDB的小技巧。
[oracle@localhost ~]$ sqlplus sys/oracle as sysdba
SQL*Plus: Release 23.0.0.0.0 - Production on Thu May 9 14:12:52 2024
Version 23.4.0.24.05
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 FREEPDB1 READ WRITE NO
解决方法:
[oracle@localhost ~]$ unset TWO_TASK
-- 自带的环境变量配置文件在 ~/.bashrc
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - Production on Thu May 9 14:11:07 2024
Version 23.4.0.24.05
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FREEPDB1 READ WRITE NO
2， ora-1017错误message变了
在写《OB不建议模仿ORACLE的错误》那篇时，也有提到oracle在相同的ora-code ，但message后期的版本在逐渐变的更友好。
19c ora-1017
oracle@ora19c1:/home/oracle $oerr ora 1017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:
23ai ora-1017
[oracle@localhost ~]$ oerr ora 1017 01017, 00000, "invalid credential or not authorized; logon denied" // *Cause: An invalid credential was provided when accessing the Oracle // Database or you were not authorized to access this database. // *Action: Depending on your authentication method, ensure that the correct // credential is provided when logging in to Oracle Database. // Retry your credential after checking it. If your credential is // correct, you may not be authorized to access the database. // You will need to contact your database administrator or // identity management administrator to confirm that you are // authorized to access the database. SQL> oerr ora 1017 Message: "invalid credential or not authorized; logon denied" Help: https://docs.oracle.com/error-help/db/ora-01017/ Cause: An invalid credential was provided when accessing the Oracle Database or you were not authorized to access this database. Action: Depending on your authentication method, ensure that the correct credential is provided when logging in to Oracle Database. Retry your credential after checking it. If your credential is correct, you may not be authorized to access the database. You will need to contact your database administrator or identity management administrator to confirm that you are authorized to access the database. SQL> oerr ora-1017 Message: "invalid credential or not authorized; logon denied" Help: https://docs.oracle.com/error-help/db/ora-01017/ Cause: An invalid credential was provided when accessing the Oracle Database or you were not authorized to access this database. Action: Depending on your authentication method, ensure that the correct credential is provided when logging in to Oracle Database. Retry your credential after checking it. If your credential is correct, you may not be authorized to access the database. You will need to contact your database administrator or identity management administrator to confirm that you are authorized to access the database.
注意这里的message变化较大，另外注意，oerr可以在sqlplus 中直接调用。 实际使用help 命令也可以
SQL> help ora 1017
Message: "invalid credential or not authorized; logon denied"
Help: https://docs.oracle.com/error-help/db/ora-01017/
Cause: An invalid credential was provided when accessing the Oracle
Database or you were not authorized to access this database.
Action: Depending on your authentication method, ensure that the correct
credential is provided when logging in to Oracle Database.
Retry your credential after checking it. If your credential is
correct, you may not be authorized to access the database.
You will need to contact your database administrator or
identity management administrator to confirm that you are
authorized to access the database.
3, oracle error help 提示更加丰富
SQL> help ora 1
Message: "unique constraint (%s.%s) violated on table %s.%s columns (%s)"
Help: https://docs.oracle.com/error-help/db/ora-00001/
Cause: An UPDATE, INSERT or MERGE statement attempted to update or
create a record that duplicated values limited by a unique
constraint. A unique constraint can be implemented as an
explicit unique constraint, a unique index, or a primary key.
Consider the case where a table has a unique constraint on columns
FIRSTNAME and LASTNAME. Because of this constraint, it is not
possible to insert a row containing values of FIRSTNAME and
LASTNAME that are identical to the values of these columns in an
existing table row.
Action: Determine what type of unique constraint was violated (explicit
unique constraint, unique index, or primary key), and which table
columns are affected by this unique constraint.
Then choose whether to change the constraint to allow duplicate
values; modify the SQL statement to no longer create a duplicate
value; or drop the constraint, unique index, or primary key.
Params: 1) constraint_schema: The schema name where the constraint
resides.
2) constraint_name: The name of the constraint.
3) table_schema: The schema name for the table affected by this
constraint.
4) table_name: The name of the table affected by this
constraint.
5) column_names: The column names affected by this
constraint.
Info: **Note:** Further details about the violating column values are
provided with the parameter `ERROR_MESSAGE_DETAILS=ON`. This
parameter is `ON` by default. If it is currently `OFF`, you can
turn this parameter `ON` using the
`ALTER SESSION SET ERROR_MESSAGE_DETAILS=ON` statement.
The following examples describe how to identify the table name
and table columns of the violated unique constraint.
This query returns `CONSTRAINT` or `INDEX` to identify whether
an index or constraint generated the exception. For example:
```
SELECT 'CONSTRAINT' object_type
FROM all_constraints
WHERE owner = ''
AND constraint_name = ''
UNION
SELECT 'INDEX' object_type
FROM all_indexes
WHERE owner = ''
AND index_name = '';
```
If the constraint or index query returns `CONSTRAINT`, this
query finds the table columns affected by the violated unique
constraint. For example:
```
SELECT column_name, table_name
FROM all_cons_columns
WHERE owner = ''
AND constraint_name = '';
```
If the constraint or index query returns `INDEX`, this query
finds the table columns affected by the violated unique index
(or primary key). For example:
```
SELECT column_name, table_owner, table_name
FROM all_ind_columns
WHERE index_owner = ''
AND index_name = '';
```
For example:
```
ORA-00001: unique constraint (SCOTT.UNIQUE_VALUES) violated
```
The following query determines whether the violated constraint
is an explicit constraint or index. For example:
```
SELECT 'CONSTRAINT' object_type
FROM all_constraints
WHERE owner = 'SCOTT'
AND constraint_name = 'UNIQUE_VALUES'
UNION
SELECT 'INDEX' object_type
FROM all_indexes
WHERE owner = 'SCOTT'
AND index_name = 'UNIQUE_VALUES';
```
ora-1错误 在过去11g 就很简单的几行说明，现在变成了一篇样例，我还以为看错了。关闭sqlplus的errordetail功能可以使用SET ERRORDETAILS OFF。
4， sqlplus ping
[oracle@localhost ~]$ sqlplus --help
SQL*Plus: Release 23.0.0.0.0 - Production
Version 23.4.0.24.05
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus statements.
Usage 1: sqlplus -H | -V is: [-C ] [-F] [-L] [-M ""] [-NOLOGINTIME]
[-P ] [-R ] [-S]
-C Sets the compatibility of affected commands to the
version specified by . The version has
the form "x.y[.z]". For example, -C 10.2.0
...
-P Pings the listener associated with the provided
connect identifier and exits SQL*Plus.
...
注意sqlplus增加了tnsping的功能。
[oracle@localhost dbhomeFree]$ cd $ORACLE_HOME/network/admin
[oracle@localhost admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
FREE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREE)
)
)
LISTENER_FREE =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
FREEPDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREEPDB1)
)
)
anbob1= (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = anbob1)
)
)
[oracle@localhost admin]$ sqlplus -P freepdb1
Local Net Naming configuration file: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
Attempting to contact: (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREEPDB1)))
Ok (1.134 msec)
[oracle@localhost admin]$ sqlplus sys/oracle@freepdb1 as sysdba
SQL*Plus: Release 23.0.0.0.0 - Production on Thu May 9 14:29:43 2024
Version 23.4.0.24.05
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
SQL> ping
Ok (0.310 msec) --复用了tcp connection
SQL> ping anbob1
Local Net Naming configuration file: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
Attempting to contact: (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = anbob1)))
Ok (0.486 msec)
SQL>
5, SHOW CONNECTION
上面我使用了vi 打开tnsnames.ora，有没有更简单的呢？有，在sqlplus中使用show connection 就可以。
SQL> show conn nets
Local Net Naming configuration file: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
anbob1
FREE
LISTENER_FREE
FREEPDB1
SQL> show conn nets anbob1
Local Net Naming configuration file: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
anbob1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = anbob1)))
6, config
对于tnsnames.ora链接，还提供了一个新方式用于OCI Object storage和Azuer app
SQL> config
SP2-0306: Invalid option.
Help: https://docs.oracle.com/error-help/db/sp2-0306/
Usage: CONFIG EXPORT TNS [] [AZURE] [[FILE] ]
SQL> CONFIG EXPORT TNS
Generating config store JSON for Local Net Naming configuration file /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
{
"anbob1": {
"connect_descriptor": "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = anbob1)))"
},
"FREE": {
"connect_descriptor": "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREE)))"
},
"LISTENER_FREE": {
"connect_descriptor": "(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))"
},
"FREEPDB1": {
"connect_descriptor": "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREEPDB1)))"
}
}
7, 支持表、列注释annotations
Annotations提供了一种注释功能。比过去的comment更加灵活, more here
SQL> CREATE TABLE employee (
id NUMBER(5)
ANNOTATIONS(Identity, Display 'Employee ID', "Group" 'Emp_Info'),
ename VARCHAR2(50)
ANNOTATIONS(Display 'Employee Name', "Group" 'Emp_Info'),
sal NUMBER
ANNOTATIONS(Display 'Employee Salary', UI_Hidden)
) ANNOTATIONS (Display 'Employee Table');
Table created.
SQL> desc employee
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(5)
ENAME VARCHAR2(50)
SAL NUMBER
SQL> set describe annotation on
SQL> desc employee
Table Annotations (Key: Value)
------------------------------------------------------------------------------------------------------------------------
DISPLAY: Employee Table
Name Null? Type Annotations (Key: Value)
--------------------------------------- -------- ------------------------------ ---------------------------------------
ID NUMBER(5) IDENTITY:
DISPLAY: Employee ID
Group: Emp_Info
ENAME VARCHAR2(50) DISPLAY: Employee Name
Group: Emp_Info
SAL NUMBER DISPLAY: Employee Salary
UI_HIDDEN:
8, 创建vector向量表
当然要试一下当前最火的向量表。
SQL> create table anbob_vec_table (v64 vector(3, float64));
Table created.
SQL> desc anbob_vec_table
Name Null? Type
----------------------------------------- -------- ----------------------------
V64 VECTOR(3, FLOAT64)
SQL> insert into anbob_vec_table values('[1,2,3.1]');
1 row created.
SQL> select * from anbob_vec_table;
V64
--------------------------------------------------------------------------------
[1.0E+000,2.0E+000,3.1000000000000001E+000]
官方文档 同时支持 打包下载。从https://docs.oracle.com/en/database/oracle/oracle-database/index.html 可以找到 Download.
oracle 23ai 从安装流畅体验到资料文档和安装介质免费公开下载，仅客户端工具的功能丰富程度，国产库数据库厂家不知如何感想？
— enjoy —
微信扫一扫，打赏作者吧～
目前这篇文章还没有评论(Rss)