版本影響當前使用者看到的物件

看一個簡單的例子：

-bash-3.2$ sqlplus test/test

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 15 21:04:58 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set pages 100 lines 120
SQL> create user u1 identified by u1 default tablespace users enable editions;

User created.

SQL> grant connect, resource, dba to u1;

Grant succeeded.

SQL> conn u1/u1
Connected.
SQL> select sys_context('USERENV', 'CURRENT_EDITION_NAME') from dual;

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
------------------------------------------------------------------------
ORA$BASE

SQL> create or replace procedure p1 as
  2  begin
  3  null;
  4  end;
  5  /

Procedure created.

SQL> create or replace procedure p2 as
  2  begin
  3  null;
  4  end;
  5  /

Procedure created.

SQL> create or replace procedure p3 as
  2  begin
  3  null;
  4  end;
  5  /

Procedure created.

SQL> create edition e2;

Edition created.

SQL> select count(*) from dba_objects;

  COUNT(*)
----------
     13565

SQL> select object_name, edition_name
  2  from dba_objects
  3  where wner = user
  4  and object_name like 'P_';

OBJECT_NAME                    EDITION_NAME
------------------------------ ------------------------------
P3                             ORA$BASE
P2                             ORA$BASE
P1                             ORA$BASE

SQL> alter session set edition = e2;

Session altered.

SQL> select count(*) from dba_objects;

  COUNT(*)
----------
     13565

SQL> drop procedure p2;

Procedure dropped.

SQL> create or replace procedure p1 as
  2  begin
  3  dbms_output.put_line('e2');
  4  end;
  5  /

Procedure created.

SQL> select count(*) from dba_objects;

  COUNT(*)
----------
     13564

SQL> select object_name, edition_name
  2  from dba_objects
  3  where wner = user
  4  and object_name like 'P_';

OBJECT_NAME                    EDITION_NAME
------------------------------ ------------------------------
P3                             ORA$BASE
P1                             E2

SQL> create or replace procedure p4 as
  2  begin
  3  null;
  4  end;
  5  /

Procedure created.

SQL> select count(*) from dba_objects;

  COUNT(*)
----------
     13565

SQL> select object_name, edition_name
  2  from dba_objects
  3  where wner = user
  4  and object_name like 'P_';

OBJECT_NAME                    EDITION_NAME
------------------------------ ------------------------------
P3                             ORA$BASE
P4                             E2
P1                             E2

SQL> alter session set edition = ora$base;

Session altered.

SQL> select count(*) from dba_objects;

  COUNT(*)
----------
     13565

SQL> select object_name, edition_name
  2  from dba_objects
  3  where wner = user
  4  and object_name like 'P_';

OBJECT_NAME                    EDITION_NAME
------------------------------ ------------------------------
P3                             ORA$BASE
P2                             ORA$BASE
P1                             ORA$BASE

SQL> conn test/test
Connected.
SQL> select count(*) from dba_objects;

  COUNT(*)
----------
     13565

SQL> select object_name, edition_name
  2  from dba_objects
  3  where wner = 'U1'
  4  and object_name like 'P_';

OBJECT_NAME                    EDITION_NAME
------------------------------ ------------------------------
P3                             ORA$BASE
P2                             ORA$BASE
P1                             ORA$BASE

可以看到，DBA_OBJECT檢視是版本化的檢視，即使是擁有DBA許可權的使用者在查詢這個檢視的時候也只能看到當前版本可見的檢視，因此DBA_OBJECTS檢視不在包括資料庫中所有的物件，如果想要獲取資料庫中各個版本的所有物件，查詢DBA_OBJECTS_AE檢視：

SQL> select object_name, edition_name
  2  from dba_objects_ae
  3  where wner = 'U1'
  4  and object_name like 'P_';

OBJECT_NAME                    EDITION_NAME
------------------------------ ------------------------------
P3                             ORA$BASE
P2                             ORA$BASE
P1                             ORA$BASE
P4                             E2
P2                             E2
P1                             E2

6 rows selected.