首页 » AI, ORACLE 9i-23ai » 分分钟安装体验Oracle 23ai,SQL*Plus细致入微的创新

分分钟安装体验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 —

打赏

对不起,这篇文章暂时关闭评论。