Oracle 更改表T1联机移动-当我们有函数索引时，对象将失效

你好，

非常感谢你花时间回答这些问题。

关于如何使用ALTER TABLE..的任何想法。联机移动；使触发器失效（过程、同义词等? ）。下面的示例演示仅用于触发器失效。

致以问候，
巴德

演示；

SQL> set echo on
SQL> col object_name for a20
SQL> col banner_full for a80
SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0


SQL>
SQL> drop table t1 ;

Table T1 dropped.

SQL>
SQL> create table t1 (
2 c1 varchar2(10),
3 c2 varchar2(10)
4 --) segment creation deferred
5 ) segment creation immediate
6 ;

Table T1 created.

SQL>
SQL> /*
SQL>If I created the table with SEGMENT CREATION DEFERRED - and without inserting any rows
SQL>then the trigger will not get invalid. However if I insert rows; after creating the table
SQL>and later ONLINE move it; then the trigger will get invalid.
SQL>If I create the table with SEGMENT CREATION IMMEDIATE, even without any rows the
SQL>trigger will get INVALID.
SQL>*/
SQL>
SQL> insert into t1 (c1, c2) values ('AA','AA');

1 row inserted.

SQL> insert into t1 (c1, c2) values ('BB','BB');

1 row inserted.

SQL> commit;

Commit complete.

SQL>
SQL> create index t1_idx on t1 (c1, upper(c2));

Index T1_IDX created.

SQL> /*
SQL>Without the above index; the trigger will NOT get INVALID.
SQL>*/
SQL>
SQL> select distinct status from user_indexes;

STATUS
--------
VALID

SQL>
SQL> create or replace trigger t1_trg after insert or update or delete on t1
2 begin
3 null;
4 end;
5 /

Trigger T1_TRG compiled

SQL>
SQL> select object_name, object_type, status
2 from user_objects
3 where object_name in ('T1_TRG')
4 order by 1;

OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ----------------------- -------
T1_TRG TRIGGER VALID

SQL>
SQL> alter table t1 move online;

Table T1 altered.

SQL> --alter table t1 move;
SQL>
SQL> /*
SQL>If I use the ONLINE keyword; the trigger will get INVALID.
SQL>If I move the table without using the ONLINE option; then the trigger
SQL>will NOT get INVALID. However the index will become UNUSUABLE.
SQL>*/
SQL>
SQL> select distinct status from user_indexes;

STATUS
--------
VALID

SQL>
SQL> select object_name, object_type, status
2 from user_objects
3 where object_name in ('T1_TRG')
4 order by 1;

OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ----------------------- -------
T1_TRG TRIGGER INVALID

SQL>
SQL> /*
SQL>Why is the trigger getting invalid during an ONLINE move?
SQL>*/
SQL>
SQL> alter trigger t1_trg compile;

Trigger T1_TRG altered.

SQL>
SQL> select object_name, object_type, status
2 from user_objects
3 where object_name in ('T1_TRG')
4 order by 1;

OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ----------------------- -------
T1_TRG TRIGGER VALID
复制

我会把它归类为一个bug （我会记录一个bug ） ，因为如果我通过一个虚拟的隐形列来模拟同样的行为，那就没问题了

SQL> set echo on
SQL> col object_name for a20
SQL> col banner_full for a80
SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0


SQL> drop table t1 ;

Table dropped.

SQL> create table t1 (
  2   c1 varchar2(10),
  3   c2 varchar2(10)
  4  );

Table created.

SQL> insert into t1 (c1, c2) values ('aa','aa');

1 row created.

SQL> insert into t1 (c1, c2) values ('bb','bb');

1 row created.

SQL> commit;

Commit complete.

SQL> --create index t1_idx on t1 (c1, upper(c2));
SQL> alter table t1 add cc varchar2(10) generated always as ( upper(c2));

Table altered.

SQL> --create index t1_idx on t1 (c1, c2);
SQL> create index t1_idx on t1 (c1, cc);

Index created.

SQL> alter table t1 modify cc invisible;

Table altered.

SQL> select status from user_indexes where table_name = 'T1';

STATUS
--------
VALID

SQL> create or replace trigger t1_trg before insert or update or delete on t1
  2  begin
  3     null;
  4  end;
  5  /

Trigger created.

SQL> select object_name, object_type, status
  2  from user_objects
  3  where object_name in ('T1_TRG')
  4  order by 1;

OBJECT_NAME          OBJECT_TYPE             STATUS
-------------------- ----------------------- -------
T1_TRG               TRIGGER                 VALID

SQL>
SQL> alter table t1 move online;

Table altered.

SQL> --alter table t1 move;
SQL> select status from user_indexes where table_name = 'T1';

STATUS
--------
VALID

SQL> select object_name, object_type, status
  2  from user_objects
  3  where object_name in ('T1_TRG')
  4  order by 1;

OBJECT_NAME          OBJECT_TYPE             STATUS
-------------------- ----------------------- -------
T1_TRG               TRIGGER                 VALID

SQL> alter trigger t1_trg compile;

Trigger altered.

SQL> select object_name, object_type, status
  2  from user_objects
  3  where object_name in ('T1_TRG')
  4  order by 1;

OBJECT_NAME          OBJECT_TYPE             STATUS
-------------------- ----------------------- -------
T1_TRG               TRIGGER                 VALID

SQL>
复制