1) it is an internal function in sqlplus. there is a package DBMS_DESCRIBE that you can use to achieve the same.


2) read the view text out and compare them. ALL_OBJECTS has security builtin. DBA_OBJECTS -- shows everything.

ops$tkyte@ORA920> select 'all', count(*) from all_objects union all
2 select 'dba', count(*) from dba_objects;

'AL COUNT(*)
--- ----------
all 31622
dba 32121



they return different answers in all environments -- but the results are more pronounced in a procedure. Consider:

ops$tkyte@ORA920> begin
2 for x in ( select 'all' a , count(*) b from all_objects union all
3 select 'dba', count(*) from dba_objects )
4 loop
5 dbms_output.put_line( x.a || ' ' || x.b );
6 end loop;
7 end;
8 /
all 31623
dba 32122

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> create or replace procedure p
2 as
3 begin
4 for x in ( select 'all' a , count(*) b from all_objects union all
5 select 'dba', count(*) from dba_objects )
6 loop
7 dbms_output.put_line( x.a || ' ' || x.b );
8 end loop;
9 end;
10 /

Procedure created.

ops$tkyte@ORA920> exec p
all 29564
dba 32122

PL/SQL procedure successfully completed.



all_objects appears to have lots less rows in a procedure without roles -- but you can see this in plus as well;


ops$tkyte@ORA920> set role none
2 /

Role set.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select 'all' a , count(*) b from all_objects union all
2 select 'dba', count(*) from dba_objects
3 /

A B
--- ----------
all 29564
dba 32122