Oracle 包过程中设置的行可见性立即执行 “更改会话集行存档可见性 = 活动”

嗨，汤姆，
我有一个表DAVE_ELOF设置为行存档 (alter table dave_elof行存档;)。
我希望用户可以通过包设置行可见性

过程setStudyVisibleYN(aStudyId编号，aVisible_YN varchar2)。

代码片段:

     -- make sure that we can update the wanted records
     EXECUTE IMMEDIATE 'ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL';
     -- commit;      
        update dave_elof 
          set ora_archive_state = dbms_ilm.archivestatename(1)
            where studyid=aStudyId;

         v_tot   := SQL%ROWCOUNT;
        commit;

检查它是否有效。这是:

        -- to be sure that the archived rows are no longer visible
        EXECUTE IMMEDIATE 'ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE'; 

        -- count the visible (un-archived) rows  
        EXECUTE IMMEDIATE 'create table tab1 as( select distinct count(1) as cnt,
                           ora_archive_state,studyid from dave_elof 
                           group by ora_archive_state,studyid)';

这是表的结果:

cnt    ora_archive_state studyid
230553       1            16244
13038       1            15128
13445       0            16953
99       1            99001
256085       0            17530

我希望只看到ora_archive_state = '0' 的记录。

如果我在SQLdeveloper中执行相同的操作，则可以正常工作。应该是。

       update dave_elof 
         set ora_archive_state ='1'
         where studyid=15128;
       commit;

   select distinct count(1) as cnt,ora_archive_state,studyid from dave_elof 
     group by ora_archive_state,studyid;

cnt    ora_archive_state studyid
13445       0            16953
256085       0            17530

整个想法是用户通过Java web应用程序调用包或存储过程以使记录可见或不可见。
在打包过程中，可以做些什么来归档记录。这种变化直接反映在表中？

亲切的问候
伊万·亨德里克斯

在你的第一个例子中，你有一个创建表...作为选择...

第二个是仅选择。

这会导致您看到的差异:

create table t as 
  select level x, mod(level, 11) y from dual
  connect by level <= 100;
  
alter table t row archival;

select count(*) from t;

alter session set row archival visibility = all;

update t
set    ora_archive_state = dbms_ilm.archivestatename(1)
where  x = 1;

select count(*) from t;

COUNT(*)   
       100 

alter session set row archival visibility = active;

create table t2 as 
  select * from t;
  
select count(*) from t2;

COUNT(*)   
       100 

select count(*) from t;

COUNT(*)   
        99 

explain plan for
 create table t2 as 
  select * from t;

select * 
from   table(dbms_xplan.display());

explain plan for
  select count(*) from t;

PLAN_TABLE_OUTPUT                         
Plan hash value: 1148579242               
                                          
---------------------------------------   
| Id  | Operation              | Name |   
---------------------------------------   
|   0 | CREATE TABLE STATEMENT |      |   
|   1 |  LOAD AS SELECT        | T2   |   
|   2 |   TABLE ACCESS FULL    | T    |   
---------------------------------------

select * 
from   table(dbms_xplan.display());

PLAN_TABLE_OUTPUT                                     
Plan hash value: 1071362934                           
                                                      
-----------------------------------                   
| Id  | Operation          | Name |                   
-----------------------------------                   
|   0 | SELECT STATEMENT   |      |                   
|   1 |  SORT AGGREGATE    |      |                   
|*  2 |   TABLE ACCESS FULL| T    |                   
-----------------------------------                   
                                                      
Predicate Information (identified by operation id):   
---------------------------------------------------   
                                                      
   2 - filter("T"."ORA_ARCHIVE_STATE"='0')

可悲的是，这在cta中没有应用。

请注意，“从t2中选择计数 (*)” 的计划具有ora_archive_state过滤器。但是cta计划没有。

为此提出了一个内部错误。

虽然我不明白为什么你需要在你的程序的另一个表中转储结果。你当然可以运行选择本身吗？