Oracle '在模式上创建之前' 触发器显然在创建表之前没有触发

在使用字符集US7ASCII设置的Oracle 8.1.7实例中

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SQL> create table t1 (c1 varchar2(3));

Table created.

SQL> create table t2 (c1 varchar2(3));

Table created.

SQL>REM simulate legacy application inserting 8bit characters into these tables i.e. characters outwith the definition of the configured 7bit character set

SQL> insert into t1 values(chr(128)||chr(129)||chr(130));

1 row created.

SQL> insert into t2 values(chr(128)||chr(129)||chr(130));

1 row created.

运行表t1和t2的原始exp。


在目标数据库19C数据库 (用字符集AL32UTF8定义) 上运行原始imp，上面的导出文件取自源数据库-创建并填充数据的表和行，如图所示-应该在目标数据库上产生2个表，每一行包含3个3字节替换字符 (因此每行9个字节)，提供NLS_LENGTH_SEMANTICS设置为CHAR。

默认情况下，导入将在运行期间执行 “更改会话集NLS_LENGTH_SEMANTICS = byte”。

要尝试解决这个问题，请在执行 “alter” 的特权用户中创建一个架构级别触发器
会话集nls_length_ 语义 = CHAR'

create or replace trigger test_user_before_create
before create on test_user.schema 
begin
     execute immediate 'ALTER 会话集nls_length_ 语义 = CHAR';
end;
/

在19C上运行原始imp...

C:\dmp>set NLS_LANG=ENGLISH_UNITED KINGDOM.US7ASCII

C:\dmp>imp test_user@al32pdb file=test_user.00000 log=test_user_imp.log

Import: Release 19.0.0.0.0 - Production on Fri Nov 29 11:44:39 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

Export file created by EXPORT:V08.01.07 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export server uses US7ASCII NCHAR character set (possible ncharset conversion)
. importing TEST_USER's objects into TEST_USER
. importing TEST_USER's objects into TEST_USER
. . importing table                           "T1"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST_USER"."T1"."C1" (actual: 9, maximum: 3)
Column 1 ...          0 rows imported
. . importing table                           "T2"          1 rows imported
Import terminated successfully with warnings.

C:\dmp>sqlplus test_user@al32pdb

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 29 11:47:42 2019
Version 19.5.0.0.0

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

Enter password:
Last Successful login time: Fri Nov 29 2019 11:44:44 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 VARCHAR2(3)

SQL> desc t2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 VARCHAR2(3 CHAR)

SQL> select vsize(c1) from t2;

 VSIZE(C1)
----------
         9

SQL> select dump(c1,1016) from t2;

DUMP(C1,1016)
--------------------------------------------------------------------------------
Typ=1 Len=9 CharacterSet=AL32UTF8: ef,bf,bd,ef,bf,bd,ef,bf,bd

SQL>

如果触发器设置为更改字符集 “创建之前”，为什么t1没有在列名上获得CHAR资格？

当尝试使用具有多个表 (其中一些包含数千行) 的生产模式时，每个VARCHAR2类型的表列都被创建为CHAR-这被重复多次，结果相同，任何列都没有CHAR丢失。

似乎触发器存在一些同步/定时问题，当导出较小时可能会启动？

谢谢，

扳机正在发射。但是在解析你的陈述后，所以第一个语句用NLS_LENGTH_SEMANTICS = BYTE解析。并因此使用此设置执行。

所以，正如你所发现的，第一个语句是 “跳过”。

要确保您使用CHAR语义，请改用登录触发器:

create or replace trigger test_user_logon
after logon on test_user.schema 
begin
  execute immediate 'ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR';
end;
/