Oracle 23ai – Database SOS

Oracle 23ai 表和视图的列最多支持到4096个

根据经验,oracle在以前常用版本中(包含oracle 19c),表和视图支持最大的列数量为1000,在oracle 23ai中允许支持最大列数量为4096,具体参见:23ai New Feature – Increased RDBMS Table/View Column Limit to 4096 (Doc ID 2947033.1),这里做了简单的试验,确认如果要支持4096列,需要设置max_columns=’EXTENDED’
准备测试表1000列、4096列和4097列

create table t_xff_col_1000(
col1 number,
col2 number,
col3 number,
col4 number,
……
col1000 number
);


create table t_xff_col_4096(
col1 number,
col2 number,
col3 number,
col4 number,
……
col4096 number
);

create table t_xff_col_4097(
col1 number,
col2 number,
col3 number,
col4 number,
……
col4097 number
);

[oracle@xifenfei ~]$ cat tab_col_4096.sql |grep col|grep -v xff|wc -l
4096
[oracle@xifenfei ~]$ cat tab_col_1000.sql |grep col|grep -v xff|wc -l
1000
[oracle@xifenfei ~]$ cat tab_col_4097.sql |grep col|grep -v xff|wc -l
4097

在max_columns为默认值的情况下(STANDARD)23ai版本中最多也只能支持1000列

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Aug 5 22:01:57 2024
Version 23.5.0.24.07

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


Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07

SQL> show parameter max_co;

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
max_columns                          string
STANDARD

SQL> @tab_col_1000.sql

Table created.

SQL> @tab_col_4096.sql
        col1001 number,
        *
ERROR at line 1002:
ORA-01792: maximum number of columns in a table or view is 1000

在max_columns为EXTENDED的情况下能够支持列4096

SQL> alter system set max_columns='EXTENDED';
alter system set max_columns='EXTENDED'
                                      *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option


SQL>  alter system set max_columns='EXTENDED' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.

ORACLE instance shut down.
SQL> SQL> startup 
ORACLE instance started.

Total System Global Area 2413360688 bytes
Fixed Size                  5363248 bytes
Variable Size             570425344 bytes
Database Buffers         1828716544 bytes
Redo Buffers                8855552 bytes
Database mounted.
Database opened.
SQL> show parameter max_co;

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
max_columns                          string
EXTENDED
SQL> @tab_col_4096.sql

Table created.

SQL> select table_name,count(1) from dba_tab_cols where table_name like 'T_XFF%' GROUP BY TABLE_NAME;

TABLE_NAME
--------------------------------------------------------------------------------
  COUNT(1)
----------
T_XFF_COL_4096
      4096

T_XFF_COL_1000
      1000

SQL> @tab_col_4097.sql
create table t_xff_col_4096(
*
ERROR at line 1:
ORA-01792: maximum number of columns in a table or view is 4096

Oracle 23ai True Cache搭建和基本测试

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 23ai True Cache搭建和基本测试

oracle 23ai推出的True Cache功能,提供一种比较完美的支持Oracle数据库语法,配合Oracle数据库使用的一种cache解决方案,配置也相对比较简单
主库和True Cache库说明
主库IP:192.168.222.8/主机名:xifenfei/db_unique_name:ora23ai/tns:ora23ai/sid:ora23ai
True Cache库IP:192.168.222.18/主机名:xifenfeidg/db_unique_name:ora23ai_tc/tns:ora23aitc/sid:ora23ai
主库参数文件

*.compatible='23.0.0'
*.control_files='/u01/app/oracle/oradata/ORA23AI/control01.ctl'
*.db_block_size=8192
*.db_name='ora23ai'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=14742m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora23aiXDB)'
*.enable_pluggable_database=true
*.local_listener='listener_ora23ai'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=ora23ai'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=764m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2292m
*.undo_tablespace='UNDOTBS1'

True Cache库参数文件

*.true_cache=true
*.db_name=ora23ai
*.db_unique_name=ora23ai_tc
*.compatible='23.0.0'
*.db_block_size=8192
*.diagnostic_dest='/u01/app/oracle'
*.enable_pluggable_database=true
*.local_listener=listener_ora23aitc
*.remote_listener=listener_ora23ai
*.sga_target=2292m
*._exadata_feature_on=true
*.fal_server=ora23ai
*.fal_client=ora23aitc
*.db_create_file_dest=/u01/app/oracle/oradata/ORA23AI

主库和True Cache库tnsnames.ora配置

ora23ai =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.222.8)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = ora23ai)
 )
 )


ora23aitc =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.222.18)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = ora23ai_tc)
 )
 )


listener_ora23ai=(DESCRIPTION =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.222.8)(PORT=1521)))
listener_ora23aitc=(DESCRIPTION =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.222.18)(PORT=1521)))

拷贝主库密码文件到True Cache库

[oracle@xifenfeidg dbs]$ ls -l orapwora23ai
-rw-r-----. 1 oracle oinstall 2048 Aug  2 19:59 orapwora23ai

主库启动归档模式,并开启force logging

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Wed Jul 31 05:24:29 2024
Version 23.5.0.24.07

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


Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07

SQL>  archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3
Current log sequence           2
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2413360688 bytes
Fixed Size                  5363248 bytes
Variable Size             553648128 bytes
Database Buffers         1845493760 bytes
Redo Buffers                8855552 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database force logging;

Database altered.

SQL> alter database open;

Database altered.

True Cache库启动到nomount

[oracle@xifenfeidg ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Aug 2 19:57:16 2024
Version 23.5.0.24.07

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

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/pfile';
ORACLE instance started.

Total System Global Area 2404873776 bytes
Fixed Size                  5363248 bytes
Variable Size             536870912 bytes
Database Buffers         1862270976 bytes
Redo Buffers                 368640 bytes

SQL> SELECT file_name FROM v$passwordfile_info;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/23ai/db_1/dbs/orapwora23ai

启动True Cache

SQL> CREATE TRUE CACHE;

True Cache created.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE                    OPEN_MODE
-------------------------------- ----------------------------------------
TRUE CACHE                       READ ONLY WITH APPLY

SQL> select name from v$datafile;

no rows selected

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA23AI/ORA23AI_TC/onlinelog/o1_mf_1_mbslm3p3_.log
/u01/app/oracle/oradata/ORA23AI/ORA23AI_TC/onlinelog/o1_mf_2_mbslm3x2_.log
/u01/app/oracle/oradata/ORA23AI/ORA23AI_TC/onlinelog/o1_mf_3_mbslm4bp_.log
/u01/app/oracle/oradata/ORA23AI/ORA23AI_TC/onlinelog/o1_mf_4_mbslm4tp_.log

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA23AI/S_TEMP_##TC##_ora23ai#tc_8192_1_3_201_1
/u01/app/oracle/oradata/ORA23AI/S_TEMP_##TC##_ora23ai#tc_8192_1_6_202_1
/u01/app/oracle/oradata/ORA23AI/S_TEMP_##TC##_ora23ai#tc_8192_2_3_203_1

SQL> select count(1) from obj$;

  COUNT(1)
----------
     70866

启动True Cache时主库alert日志提示
自动增加log_archive_dest_n记录,传输数据到True Cache库

2024-08-02T20:00:37.340496+08:00
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=MEMORY SID='ora23ai';
2024-08-02T20:00:37.341412+08:00
ALTER SYSTEM SET log_archive_dest_2='service=','"ora23aitc"','LGWR ASYNC NET_TIMEOUT=30 VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)
   DB_UNIQUE_NAME="ora23ai_tc" REOPEN=15 MAX_FAILURE=20 ROLE="TRUE_CACHE"' SCOPE=MEMORY SID='ora23ai';
2024-08-02T20:00:37.356979+08:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='ora23ai';
2024-08-02T20:00:38.734646+08:00
Thread 1 advanced to log sequence 9 (LGWR switch),  current SCN: 4168136
  Current log# 3 seq# 9 mem# 0: /u01/app/oracle/oradata/ORA23AI/redo03.log
2024-08-02T20:00:38.801849+08:00
ARC1 (PID:7534): Archived Log entry 6 added for B-1175412482.T-1.S-8 LOS:0x00000000003f839e NXS:0x00000000003f99c8 NAB:21445 ID 0x8fe90542 LAD:1 [krse.c:4872]
2024-08-02T20:00:39.456381+08:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='ora23ai';
2024-08-02T20:00:43.431813+08:00
*** 2024-08-02T20:00:43.431713+08:00
[kradcm.c:1217] kradcm_start_dsndr_from_primary: True Cache: RCVR primary data request receiver process (rmi PID:7854)
    for True Cache DGID:1938295904 started for DEST_ID:2 by FCH (PID:9392)
2024-08-02T20:00:43.448189+08:00
*** 2024-08-02T20:00:43.448096+08:00
[kradcm.c:2906] kradcm_dsndr_main: True Cache: DSNDR primary data block sender process (TT04 PID:7856) for True Cache DGID:1938295904 started for DEST_ID:2
*** 2024-08-02T20:00:43.450891+08:00
[kradcm.c:406] kradcm_connect_to_adc: True Cache DSNDR (PID:7856): Establishing connection to True Cache DGID:1938295904 CONNECTION:ora23aitc
2024-08-02T20:00:43.457328+08:00
*** 2024-08-02T20:00:43.457236+08:00
[kradcm.c:2906] kradcm_dsndr_main: True Cache: DSNDR primary data block sender process (TT06 PID:7858) for True Cache DGID:1938295904 started for DEST_ID:2
*** 2024-08-02T20:00:43.460061+08:00
[kradcm.c:406] kradcm_connect_to_adc: True Cache DSNDR (PID:7858): Establishing connection to True Cache DGID:1938295904 CONNECTION:ora23aitc
*** 2024-08-02T20:00:43.478444+08:00
[kradcm.c:483] kradcm_connect_to_adc: True Cache DSNDR (PID:7856): Successfully connected to True Cache DGID:1938295904 CONNECTION:ora23aitc
*** 2024-08-02T20:00:43.480139+08:00
[kradcm.c:3025] kradcm_dsndr_main: True Cache: DTS data block receiver process started on True Cache DGID:1938295904 PID:9396
*** 2024-08-02T20:00:43.484350+08:00
[kradcm.c:483] kradcm_connect_to_adc: True Cache DSNDR (PID:7858): Successfully connected to True Cache DGID:1938295904 CONNECTION:ora23aitc
*** 2024-08-02T20:00:43.485593+08:00
[kradcm.c:3025] kradcm_dsndr_main: True Cache: DTS data block receiver process started on True Cache DGID:1938295904 PID:9398

True Cache库alert日志信息
1.自动创建standby redo
2.自动创建tempfile
3.启动库到只读状态（非标准dg的只读）
4.启动日志同步(非标准dg的mrp同步)

2024-08-02T20:00:33.507464+08:00
CREATE TRUE CACHE
--ATTENTION--
Default temporary tablespace will be necessary for a locally managed database in future release.
--ATTENTION--
Default temporary tablespace will be necessary for a locally managed database in future release.
2024-08-02T20:00:35.688251+08:00
Control File SGA cache allocated 8388608 bytes.
        Address           : 0x6e03afb8
        Number of buckets : 256
        Number of pools   : 8
        Number of buffers : 1024
        Block size        : 8192
        Trace flags       : 0x0
*** 2024-08-02T20:00:35.702201+08:00
[kcvfdb.c:9694] kcfcmb: True Cache mounted.
Expanded controlfile section 32 from 31 to 128 records
Requested to grow by 97 records; added 5 blocks of records
2024-08-02T20:00:35.703624+08:00
.... (PID:9313): WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is [kcrr.c:2568]
not set to the value "AUTO".
This may cause recovery of the standby database to terminate
prior to applying all available redo data.
It may be necessary to use the ALTER DATABASE CREATE DATAFILE
command to add datafiles created on the primary database.
Lost write protection mode set to "auto"
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 1 SIZE 200M BLOCKSIZE 512
Completed: ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 1 SIZE 200M BLOCKSIZE 512
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 2 SIZE 200M BLOCKSIZE 512
Completed: ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 2 SIZE 200M BLOCKSIZE 512
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 3 SIZE 200M BLOCKSIZE 512
2024-08-02T20:00:36.854038+08:00
Completed: ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 3 SIZE 200M BLOCKSIZE 512
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 SIZE 200M BLOCKSIZE 512
Completed: ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 SIZE 200M BLOCKSIZE 512
alter database open
True Cache opening with primary at ora23ai.
Checkpoint for True Cache starts at scn 0x00000000003f99c4 Thread 1 RBA 0x000008.000053c5.0010
2024-08-02T20:00:37.334840+08:00
.... (PID:9313): Enable RFS client   [krsr.c:20527]
2024-08-02T20:00:37.335043+08:00
ALTER SYSTEM SET log_archive_config='DG_CONFIG=(ora23ai)' SCOPE=MEMORY;
2024-08-02T20:00:37.335498+08:00
ALTER SYSTEM SET log_archive_dest_1='' SCOPE=MEMORY;
2024-08-02T20:00:37.335878+08:00
ALTER SYSTEM SET log_archive_dest_1='location="/u01/app/oracle/oradata/ORA23AI" mandatory VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' SCOPE=MEMORY;
*** 2024-08-02T20:00:37.342697+08:00
[kcv.c:24912] kcvcrv_adc: New DB SCN 0x00000000003f99c3
.... (PID:9313): Starting Managed Recovery process for Physical Standby [krsm.c:1581]
2024-08-02T20:00:37.381794+08:00
.... (PID:9354): Background Managed Recovery process started [krsm.c:1986]
2024-08-02T20:00:39.457787+08:00
 rfs (PID:9369): krsr_rfs_atc: Identified database type as 'TRUE CACHE': Client is Foreground (PID:7528) [krsr.c:5976]
 rfs (PID:9369): Disable RFS client RFS LogMiner Client [kcrlc.c:1531]
2024-08-02T20:00:39.463332+08:00
 rfs (PID:9373): krsr_rfs_atc: Identified database type as 'TRUE CACHE': Client is ASYNC (PID:7841) [krsr.c:5976]
2024-08-02T20:00:39.467452+08:00
 rfs (PID:9373): Opened LNO:1 for DBID:2414386242 B-1175412482.T-1.S-8.C-0 [krsr.c:19076]
2024-08-02T20:00:39.868908+08:00
 rfs (PID:9377): krsr_rfs_atc: Identified database type as 'TRUE CACHE': Client is ASYNC (PID:7839) [krsr.c:5976]
2024-08-02T20:00:39.872888+08:00
 rfs (PID:9377): Opened LNO:2 for DBID:2414386242 B-1175412482.T-1.S-9.C-0 [krsr.c:19076]
2024-08-02T20:00:42.405225+08:00
 Started logmerger process
2024-08-02T20:00:42.417728+08:00
PR00 (PID:9380): Managed Recovery starting Real Time Apply [krsm.c:15931]
2024-08-02T20:00:42.463844+08:00
Parallel Media Recovery started with 4 slaves
2024-08-02T20:00:42.510016+08:00
Recovery of Standby Redo Log: Thread 1 Group 1 Seq 8 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/ORA23AI/ORA23AI_TC/onlinelog/o1_mf_1_mbslm3p3_.log
2024-08-02T20:00:42.562877+08:00
Recovery of Standby Redo Log: Thread 1 Group 2 Seq 9 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/ORA23AI/ORA23AI_TC/onlinelog/o1_mf_2_mbslm3x2_.log
2024-08-02T20:00:43.401465+08:00
*** 2024-08-02T20:00:43.401345+08:00
[kradcm.c:2391] kradcm_fch_main: True Cache: FCH data request sender process (TT02 PID:9392) started
*** 2024-08-02T20:00:43.403321+08:00
[kradcm.c:583] kradcm_fch_connect_primary: FCH (PID:9392): trying to establish connection to primary ora23ai
2024-08-02T20:00:43.414087+08:00
*** 2024-08-02T20:00:43.413868+08:00
[kradcm.c:2376] kradcm_fch_main: FCH process already started. Ignoring request.
[kradcm.c:626] kradcm_fch_connect_primary: FCH (PID:9392) connection established to primary ora23ai
*** 2024-08-02T20:00:43.428869+08:00
[kradcm.c:3279] kradcm_start_dsndr_from_adc: FCH (PID:9392): Message primary DGID:817860583 to start DSNDR for True Cache DGID:1938295904
*** 2024-08-02T20:00:43.432861+08:00
[kradcm.c:3356] kradcm_start_dsndr_from_adc: True Cache: DSNDR process successfully started in primary DGID:817860583 by RCVR PID:7854, initiated by FCH (PID:9392)
2024-08-02T20:00:43.479008+08:00
*** 2024-08-02T20:00:43.478916+08:00
[kradcm.c:1018] kradcm_callback: True Cache: DTS data block receiver process (rmi PID:9396) started for primary DGID:817860583 DSNDR PID:7856
2024-08-02T20:00:43.484358+08:00
*** 2024-08-02T20:00:43.484260+08:00
[kradcm.c:1018] kradcm_callback: True Cache: DTS data block receiver process (rmi PID:9398) started for primary DGID:817860583 DSNDR PID:7858
2024-08-02T20:00:44.152726+08:00
replication_dependency_tracking turned off (no async multimaster replication found)
No Resource Manager plan active
Physical standby database opened for read only access.
Completed: alter database open
Completed: CREATE TRUE CACHE

True Cache 同步测试

--主库创建用户和表
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XIFENFEI                       MOUNTED
SQL> alter session set container=xifenfei;

Session altered.

SQL> alter database open;

Database altered.

SQL> create user xff identified by oracle;

User created.

SQL> grant dba to xff;

Grant succeeded.

SQL> create table xff.t_xff as select * from dba_objects;

Table created.

SQL> select count(1) from xff.t_xff;

  COUNT(1)
----------
     70656

--True Cache库查询结果
SQL> alter session set container=xifenfei;

Session altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 XIFENFEI                       READ ONLY  NO
SQL> select count(1) from xff.t_xff;

  COUNT(1)
----------
     70656

True Cache DML重定向测试

---True Cache库
SQL> conn xff/oracle@192.168.222.18/xifenfei   
Connected.
SQL> select database_role from v$database;

DATABASE_ROLE
--------------------------------
TRUE CACHE

SQL> alter session enable ADG_REDIRECT_DML;

Session altered.

SQL> delete from t_xff;

70656 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(1) from t_xff;

  COUNT(1)
----------
         0

--主库
SQL> conn xff/oracle@192.168.222.8/xifenfei
Connected.
SQL> select count(1) from t_xff;

  COUNT(1)
----------
         0

True Cache库操作临时表

[oracle@xifenfeidg ~]$ sqlplus xff/oracle@192.168.222.18/xifenfei   

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Aug 2 20:47:43 2024
Version 23.5.0.24.07

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


Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE                    OPEN_MODE
-------------------------------- ----------------------------------------
TRUE CACHE                       READ ONLY WITH APPLY

SQL> create global temporary table t_temp as
  2  select * from t_xff;

Table created.

SQL> select count(1) from t_temp;

  COUNT(1)
----------
         0

SQL> insert into t_temp select * from dba_objects;

70663 rows created.

Oracle23ai新特性—sqlplus errordetails功能

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle23ai新特性—sqlplus errordetails功能

在oracle 23ai中如果sqlplus执行遇到ORA-错误,会有对应的Help: https://docs.oracle.com/error-help/db/ORA-XXXXX提示,由于以前的使用习惯或者是提示本身意义不大等原因,还是希望在sqlplus中关闭这类提示.通过分析确认该提示是由errordetails来控制的

[oracle@xifenfei admin]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Jul 29 22:44:23 2024
Version 23.5.0.24.07

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


Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07

SQL> select * from xff.xifenfei;
select * from xff.xifenfei
                  *
ERROR at line 1:
ORA-00942: table or view "XFF"."XIFENFEI" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/

SQL> show errordetails   <--该值默认为on
errordetails ON

设置errordetails off即可实现关闭Help: https://docs.oracle.com/error-help/db/ORA-XXXXX提示

SQL> set errordetails off
SQL>  select * from xff.xifenfei;
 select * from xff.xifenfei
                   *
ERROR at line 1:
ORA-00942: table or view "XFF"."XIFENFEI" does not exist

设置errordetails verbose可以实现更加详细的提示

SQL> set errordetails verbose
SQL> select * from xff.xifenfei;
select * from xff.xifenfei
                  *
ERROR at line 1:
ORA-00942: table or view "XFF"."XIFENFEI" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
Cause:     The specified table or view did not exist, or a synonym
           pointed to a table or view that did not exist.
           To find existing user tables and views, query the
           ALL_TABLES and ALL_VIEWS data dictionary views. Certain
           privileges may be required to access the table. If an
           application returned this message, then the table that the
           application tried to access did not exist in the database, or
           the application did not have access to it.
Action:    Check each of the following:
           - The spelling of the table or view name is correct.
           - The referenced table or view name does exist.
           - The synonym points to an existing table or view.

           If the table or view does exist, ensure that the correct access
           privileges are granted to the database user requiring access
           to the table. Otherwise, create the table.

           Also, if you are attempting to access a table or view in another
           schema, make sure that the correct schema is referenced and that
           access to the object is granted.
Params: 1) object_name: The table or view name specified as
                        SCHEMA.OBJECT_NAME, if one is provided.
                        Otherwise, it is blank.

如果要实现sqlplus启动即屏蔽该提示,可以在glogin.sql文件($ORACLE_HOME/sqlplus/admin目录中)设置

[oracle@xifenfei admin]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Jul 29 22:44:23 2024
Version 23.5.0.24.07

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


Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07

SQL> show errordetails;
errordetails OFF
SQL> select * from xifenfei.xff;
select * from xifenfei.xff
                       *
ERROR at line 1:
ORA-00942: table or view "XIFENFEI"."XFF" does not exist

Oracle23ai新特性—SCHEMA级授权( grant select any table on schema)

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle23ai新特性—SCHEMA级授权( grant select any table on schema)

创建测试用户a并在a中创建t1,t2两个测试表,另外创建用户b(只授权登录权限)

[oracle@xifenfei ~]$ ss

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Jul 29 22:14:11 2024
Version 23.5.0.24.07

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


Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 XIFENFEI                       READ WRITE NO

SQL> create user b identified by oracle;

User created.

SQL> grant create session  to b;

Grant succeeded.

SQL> grant unlimited tablespace to a;

Grant succeeded.

SQL> create table a.t1 as select * from dba_objects;

Table created.

SQL> c/t1/t2;
  1* create table a.t2 as select * from dba_objects
SQL> /

Table created.

SQL> select count(1) from a.t1;

  COUNT(1)
----------
     70638

SQL> select count(1) from a.t2;

  COUNT(1)
----------
     70639

SQL> create user b identified by oracle;

User created.

SQL> grant create session  to b;

Grant succeeded.

直接使用b用户登录并尝试查询a用户数据,结果是无法查询数据报ora-00942(意料之中因为b现在无权限访问a.t1表)

SQL> conn b/oracle@127.0.0.1/xifenfei
Connected.
SQL> select count(1) from a.t1;
select count(1) from a.t1
                       *
ERROR at line 1:
ORA-00942: table or view "A"."T1" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/

直接schema级别授权a用户下面的表给b用户查询权限

[oracle@xifenfei ~]$ ss

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Jul 29 22:07:14 2024
Version 23.5.0.24.07

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


Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07

SQL>  grant select any table on schema a to b;

Grant succeeded.

SQL>  conn b/oracle@127.0.0.1/xifenfei
Connected.
SQL> select count(1) from a.t1;

  COUNT(1)
----------
     70638

SQL> select count(1) from a.t2;

  COUNT(1)
----------
     70639

在a用户中新增加表,在b用户中可以直接查询(无需再次授权)

SQL> conn a/oracle@127.0.0.1/xifenfei
Connected.
SQL>  create table t3 as select * from tab;

Table created.

SQL> select count(1) from t3;

  COUNT(1)
----------
         3

SQL> conn b/oracle@127.0.0.1/xifenfei
Connected.
SQL> select count(1) from a.t3;

  COUNT(1)
----------
         3

Oracle 23ai中，Oracle引入了架构级（SCHEMA级别）授权。这允许你以更简单、更直观的方式对整个Schema进行授权。以前的版本中如果需要类似授权操作,需要对schema下面所有表进行grant select on user.table to user2形式授权工作量比较大而且user1中如果新增加表还需要额外授权

Oracle 23ai rm redo*.log恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 23ai rm redo*.log恢复

在oracle 23ai的pdb中创建用户和表,并且插入数据(不提交),在另外一个会话中abort库,并从os层面rm删除掉redo文件,模拟数据库当前redo丢失,数据库恢复
创建用户和表并插入数据

[oracle@xifenfei ~]$ sqlplus sys/oracle@free as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 3 15:40:55 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> 
SQL> 
SQL> show pdbs;

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

SQL> alter session set container=FREEPDB1;

Session altered.

SQL> create user xff identified by oracle;

User created.

SQL> grant dba to xff ;

Grant succeeded.

SQL> conn xff/oracle@FREEPDB1
Connected.
SQL> create table t1 as select * from dba_objects;

Table created.


SQL> insert into t1 select *from t1;

75877 rows created.

SQL> /

151754 rows created.

另外一个会话中abort库

[oracle@xifenfei ~]$ sqlplus sys/oracle@free as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 3 15:43:30 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> shutdown abort;
ORACLE instance shut down.
SQL> 
SQL> 
SQL> 
SQL> exit
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

操作系统层面rm -rf 删除redo

[oracle@xifenfei ~]$ cd $ORACLE_BASE/oradata
[oracle@xifenfei oradata]$ ls
FREE
[oracle@xifenfei oradata]$ cd FREE/
[oracle@192 FREE]$ ls
control01.ctl  FREEPDB1  redo01.log  redo03.log    system01.dbf  undotbs2.dbf
control02.ctl  pdbseed   redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
[oracle@xifenfei FREE]$ ls -ltr
total 2441036
drwxr-x---. 2 oracle     1000         85 May  1 16:49 pdbseed
-rw-r-----. 1 oracle oinstall   20979712 May  1 16:51 temp01.dbf
drwxr-x---. 2 oracle     1000        104 May  1 16:55 FREEPDB1
-rw-r-----. 1 oracle oinstall  209715712 May  3 15:23 redo01.log
-rw-r-----. 1 oracle oinstall  209715712 May  3 15:23 redo02.log
-rw-r-----. 1 oracle oinstall    7348224 May  3 15:23 users01.dbf
-rw-r-----. 1 oracle oinstall 1080041472 May  3 15:43 system01.dbf
-rw-r-----. 1 oracle oinstall  692068352 May  3 15:43 sysaux01.dbf
-rw-rw----. 1 oracle oinstall   52436992 May  3 15:43 undotbs2.dbf
-rw-r-----. 1 oracle oinstall  209715712 May  3 15:43 redo03.log
-rw-r-----. 1 oracle oinstall   18759680 May  3 15:43 control01.ctl
-rw-r-----. 1 oracle oinstall   18759680 May  3 15:43 control02.ctl
[oracle@xifenfei FREE]$ rm -rf redo0*
[oracle@192 FREE]$ ls -l redo*
ls: cannot access 'redo*': No such file or directory
[oracle@xifenfei FREE]$

尝试启动数据库，报ora-00313，ora-00312，ora-27037等错误

[oracle@xifenfei ~]$ sqlplus sys/oracle@free as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 3 15:44:17 2024
Version 23.4.0.24.05

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603726344 bytes
Fixed Size                  5360648 bytes
Variable Size             671088640 bytes
Database Buffers          922746880 bytes
Redo Buffers                4530176 bytes
Database mounted.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/FREE/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

由于所有redo均被删除(包含当前redo),因此只能强制resetlogs方式打开库,尝试打开数据库

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 1603726344 bytes
Fixed Size                  5360648 bytes
Variable Size             671088640 bytes
Database Buffers          922746880 bytes
Redo Buffers                4530176 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 4244588 generated at 05/03/2024 15:23:22 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/product/23ai/dbhomeFree/dbs/arch1_6_1167842962.dbf
ORA-00280: change 4244588 for thread 1 is in sequence #6


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/FREE/system01.dbf'


ORA-01112: media recovery not started

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by irrecoverable error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [],
[], [], [], [], [], [], []
Process ID: 5596
Session ID: 29 Serial number: 63204

数据库遇到常见的ORA-600 kcbzib_kcrsds_1错误,这类错误类似oracle在12c之前版本中的ORA-600 2662错误
ORA-600 kcbzib_kcrsds_1报错
 存储故障,强制拉库报ORA-600 kcbzib_kcrsds_1处理
 redo异常强制拉库报ORA-600 kcbzib_kcrsds_1修复
 ORA-00603 ORA-01092 ORA-600 kcbzib_kcrsds_1
这种一般就是scn问题,通过修改数据库scn,数据库启动报ORA-16433错误

[oracle@xifenfei ~]$ sqlplus sys/oracle@free as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 3 15:49:00 2024
Version 23.4.0.24.05

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

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/pfile';
ORACLE instance started.

Total System Global Area 1603726344 bytes
Fixed Size                  5360648 bytes
Variable Size             671088640 bytes
Database Buffers          922746880 bytes
Redo Buffers                4530176 bytes
SQL> alter database mount;

Database altered.

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database or pluggable database must be opened in read/write mode.


SQL> shutdown abort;
ORACLE instance shut down.

处理ctl,open数据库成功

SQL> startup nomount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 1603726344 bytes
Fixed Size                  5360648 bytes
Variable Size             671088640 bytes
Database Buffers          922746880 bytes
Redo Buffers                4530176 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "FREE" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 1024
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/opt/oracle/oradata/FREE/redo01.log'  SIZE 200M BLOCKSIZE 512,
  9    GROUP 2 '/opt/oracle/oradata/FREE/redo02.log'  SIZE 200M BLOCKSIZE 512,
 10    GROUP 3 '/opt/oracle/oradata/FREE/redo03.log'  SIZE 200M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/opt/oracle/oradata/FREE/system01.dbf',
 14    '/opt/oracle/oradata/FREE/pdbseed/system01.dbf',
 15    '/opt/oracle/oradata/FREE/sysaux01.dbf',
 16    '/opt/oracle/oradata/FREE/pdbseed/sysaux01.dbf',
 17    '/opt/oracle/oradata/FREE/users01.dbf',
 18    '/opt/oracle/oradata/FREE/pdbseed/undotbs01.dbf',
 19    '/opt/oracle/oradata/FREE/FREEPDB1/system01.dbf',
 20    '/opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf',
 21    '/opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf',
 22    '/opt/oracle/oradata/FREE/FREEPDB1/users01.dbf',
 23    '/opt/oracle/oradata/FREE/undotbs2.dbf'
 24  CHARACTER SET AL32UTF8
 25  ;

Control file created.

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> show pdbs;

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

至此当前数据库redo故障模拟和恢复基本完成

Oracle 发布计划—包含Oracle 23ai版本

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 发布计划—包含Oracle 23ai版本

根据oracle官方公布的Oracle 23ai的发布计划（当然free版本，exadata,cloud版本已经发布）,可以确定在2024年上半年即将发布Linux x86-64版本，Oracle Solaris SPARC/Linux on Arm/Microsoft Windows x64 (64-bit)版本将在下版本发布,Oracle Solaris x86-64平台不在发布Oracle 23ai版本数据库，IBM AIX on POWER Systems/IBM Linux on System z/HP-UX Itanium在发布计划中具体时间没有公布。具体参考：Release Schedule of Current Database Releases (Doc ID 742060.1)

Platform	23aiLong Term Release	21cInnovation Release	19cLong Term Release	18c	12.2.0.1	12.1.0.2	12.1.0.1	11.2.0.4
*Oracle Public Cloud Releases*
Exadata Express Cloud Service	Not Planned	Not Planned	Not Planned	Not Planned	18-Sep-2016	N/A
Base Database Service¹(Previously known as: Database Cloud Service)	19-Sep-2023	08-Dec-2020available on VM (RAC or single-instance) and Bare Metal (single-instance)	OCI DB System – VM: Jul-2019OCI DB System – Bare Metal: April 2020	1-Mar-2018	4-Nov-2016	Sep-2014	N/A	Sep-2014
Exadata Database Service on Dedicated Infrastructure¹(Previously known as: Exadata Cloud Service)	2-May-2024	N/A	19-Jun-2019	Jun-2018	4-Nov-2016	Oct-2015	N/A	Oct-2015
Gen 1 Exadata Cloud at Customer¹(Previously known as: Exadata Cloud at Customer)	N/A	N/A	17-Sep-2019	May-2018	2-May-2017	Dec-2016	N/A
Exadata Database Service on Cloud at Customer ¹	2-May-2024	N/A	17-Sep-2019	N/A	N/A	N/A	N/A
Autonomous Database on Dedicated Exadata Infrastructure ²	1H CY2024	N/A	26-Jun-2019	N/A	N/A	N/A	N/A
Autonomous Database on Exadata Cloud at Customer ²	1H CY2024	N/A	08-Jul-2020	N/A	N/A	N/A	N/A
Autonomous Database on Shared Exadata Infrastructure ²	1H CY2024	N/A	15-Mar-2020	18-Mar-2018	N/A	N/A	N/A
*On-Premises Engineered Systems*(Same software as released for other platforms but tested on Engineered Systems)
Oracle Database Appliance	CY2024	16-Sep-2021	16-Oct-2019	7-Mar-2018	17-Nov-2017	Apr 2014	N/A	Oct 2013
Exadata	1H CY2024	13-Aug-2021	13-Feb-2019	16-Feb-2018	10-Feb-2017	Oct 2014	Mar 2013	Nov 2013
Supercluster	TBA	N/A	26-Apr-2019	12-Mar-2018	10-Feb-2017	Nov 2014	Apr 2014	Dec 2013
**On-Premises Server Releases (includes client)(Download here)**
Linux x86	Not Planned	Not Planned	Not planned	Not planned	Not planned	Not planned	Not planned	28-Aug-2013
Linux x86-64	1H CY2024	13-Aug-2021	25-Apr-2019	23-Jul-2018	1-Mar-2017	22-Jul-2014	25-Jun-2013	27-Aug-2013
Linux on Arm	2H CY2024	Not Planned	28-June-2023	Not Planned	Not Planned	Not Planned	Not Planned	Not Planned
Oracle Solaris SPARC (64-bit)	2H CY2024	See DOC ID 2853097.1	26-Apr-2019	30-Jul-2018	1-Mar-2017	22-Jul-2014	25-Jun-2013	29-Aug-2013
Oracle Solaris x86-64 (64-bit)	Platform de-supported	Platform de-supported	07-Nov-2019Terminal Release	6-Aug-2018	1-Mar-2017	22-Jul-2014	25-Jun-2013	29-Aug-2013
Microsoft Windows x64 (64-bit)	2H CY2024	08-Oct-2021	08-Jun-2019	21-Aug-2018	16-Mar-2017	25-Sep-2014	9-Jul-2013	25-Oct-2013
OpenVMS Itanium	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Terminal Release
HP-UX Itanium	TBA	28-Sept-2021	28-May-2019	20-Nov -2018	13-Apr-2017	14-Nov-2014	9-Jan-2014	10-Oct-2013
HP-UX PA-RISC (64-bit)	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	Platform de-supported	2-Jan-2014
IBM AIX on POWER Systems	TBA	See DOC ID 2766930.1	28-May-2019	20-Nov -2018	13-Apr-2017	14-Nov-2014	9-Jan-2014	10-Oct-2013
IBM Linux on System z	TBA	See DOC ID 2766930.1	06-June-2019	20-Nov -2018	6-Jun-2017	14-Nov-2014	9-Jan-2014	9-Jan-2014
Microsoft Windows (32-bit)	Not Planned	Client Only – Oct 2021	Client only – June 2019	Not planned	Not planned	Not planned	Not planned	25-Oct-2013
Platform	23ai	21c	19	18	12.2.0.1	12.1.0.2	12.1.0.1	11.2.0.4
*Instant Client-Only Releases*
Apple macOS (Intel) download			10-Sept-2019	Mar-2018	15-Jan-2018	2016	Not planned	20-Apr-2014 Download
IBM Linux on POWER (Big Endian)						18-Dec-2015 (download)		4-Dec-2014
IBM Linux on POWER (Little Endian)download			Jul-2019download	31-Oct-2018 (download)	7-Jun-2017 (download)	18-Dec-2015 (download)
Linux on Arm		Not Planned	May-2021download
¹Oracle Base Database Service (formerly Oracle Database Cloud Service) and Exadata Database Service (formerly Exadata Cloud Service and Exadata Cloud@Customer) follow the same support life and error correction schedule as on-premises dates unless otherwise noted in Table 1 above (for example, 11.2.0.4 MDS). Also, Extended Support is bundled with both the license-included and BYOL versions of these services and does not require additional fees. These services will not be covered under Sustaining Support and Oracle makes no commitment that any cloud service instances will continue to run after the end of their support life (Premier, extended, error correction, or MDS). We will not disable any installed databases upon the expiration of support, but the underlying infrastructure will continue to be updated. The infrastructure updates may render the unpatched databases inoperable. We make no commitment as to how long any unpatched databases will continue to run.²Oracle’s current plan for Oracle Autonomous Database – Shared Exadata Infrastructure, Oracle Autonomous Database – Dedicated Exadata Infrastructure, Autonomous Database on Exadata Cloud@Customer services is to support Long Term Release versions for a similar period of time found for those major releases when deployed on-premises. The intent of this plan is to provide stability of service experience for the thousands of critical database deployments found in Oracle Cloud. Additionally, we will at times make available Innovation Releases as part of the Autonomous Database Cloud Services. Innovation Releases, when provided, will be supported for a shorter period of time as compared to a Long Term Release, similar to what is found for those releases when deployed on-premises. Oracle reserves the right to change this plan with changing business requirements. Any change will take into account that stability is of utmost importance to Oracle’s many thousands of mission critical deployments. *References for Client-Only:* https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle19c-linux-5462157.html https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle19c-windows-5539283.html *32-bit Linux & Windows Instant Clients, https://www.oracle.com/database/technologies/instant-client/linux-x86-32-downloads.html* *https://www.oracle.com/database/technologies/instant-client/microsoft-windows-32-downloads.html*

Oracle 23ai 变化之—-默认数据文件变为bigfile

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 23ai 变化之—-默认数据文件变为bigfile

oracle把23c变为23ai，引入了很多新特性,今天下载了free版本的虚拟机登录进去第一感觉就是system,sysaux,undo等文件以前版本默认的smallfile变为了bigfile

SQL> select BANNER_FULL from v$version;

BANNER_FULL
----------------------------------------------------------------------------------
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

SQL> select con_id,ts#,name,bigfile from v$tablespace order by 1,2;

    CON_ID        TS# NAME                           BIG
---------- ---------- ------------------------------ ---
         1          0 SYSTEM                         YES
         1          1 SYSAUX                         YES
         1          3 TEMP                           NO
         1          4 USERS                          YES
         1          5 UNDOTBS2                       YES
         2          0 SYSTEM                         YES
         2          1 SYSAUX                         YES
         2          2 UNDOTBS1                       YES
         2          3 TEMP                           NO
         3          0 SYSTEM                         YES
         3          1 SYSAUX                         YES
         3          2 UNDOTBS1                       YES
         3          3 TEMP                           NO
         3          6 USERS                          NO

14 rows selected.

SQL> select con_id,file#,ts#,rfile#,name from v$datafile;

    CON_ID      FILE#        TS#     RFILE# NAME
---------- ---------- ---------- ---------- ------------------------------------------------------
         1          1          0       1024 /opt/oracle/oradata/FREE/system01.dbf
         2          2          0       1024 /opt/oracle/oradata/FREE/pdbseed/system01.dbf
         1          3          1       1024 /opt/oracle/oradata/FREE/sysaux01.dbf
         2          4          1       1024 /opt/oracle/oradata/FREE/pdbseed/sysaux01.dbf
         1          7          4       1024 /opt/oracle/oradata/FREE/users01.dbf
         2          9          2       1024 /opt/oracle/oradata/FREE/pdbseed/undotbs01.dbf
         3         12          0       1024 /opt/oracle/oradata/FREE/FREEPDB1/system01.dbf
         3         13          1       1024 /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf
         3         14          2       1024 /opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf
         3         15          6         15 /opt/oracle/oradata/FREE/FREEPDB1/users01.dbf
         1         16          5       1024 /opt/oracle/oradata/FREE/undotbs2.dbf

11 rows selected.

分类目录归档：Oracle 23ai