DBA_SOURCE

Tested on an Oracle 9 database. Imagine you have a function call which returns a value as follows:

SQL> select sys.login_user from dual;

 

LOGIN_USER

--------------------------------------------------

ORACLE

 

SQL>
  

You can see its source code stored in DBA_SOURCE:

SQL> desc dba_source

Name                    Null?    Type

----------------------- -------- ----------------

OWNER                            VARCHAR2(30)

NAME                             VARCHAR2(30)

TYPE                             VARCHAR2(12)

LINE                             NUMBER

TEXT                             VARCHAR2(4000)

 

SQL> l

  1  select text from dba_source

  2  where owner = 'SYS'

  3  and name = 'LOGIN_USER'

  4* order by line

SQL> /
  

TEXT

--------------------------------------------------

function login_user return varchar2 is

begin

return dbms_standard.login_user;

end;

 

SQL>

The TYPE column shows what type of object the source came from. There are several possibilities:
  

SQL> select distinct type from dba_source;

 

TYPE

------------

FUNCTION

JAVA SOURCE

PACKAGE

PACKAGE BODY

PROCEDURE

TRIGGER

TYPE

TYPE BODY

 

8 rows selected.

 

SQL>
  

In the example above, the source came from a function, which you might have guessed as it returns a value to the user:
  

SQL> l

  1  select distinct type from dba_source

  2  where owner = 'SYS'

  3* and name = 'LOGIN_USER'

SQL> /

 

TYPE

------------

FUNCTION

 

SQL>

gather_schema_stats

You can gather statistics for a schema as follows:

ORACLE 9 > sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.7.0 - Production on Thu Sep 16 10:31:39 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning option

JServer Release 9.2.0.7.0 - Production

SQL> exec dbms_stats.gather_schema_stats(ownname=>'SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats(ownname=>'SYSTEM');

PL/SQL procedure successfully completed.

SQL>

You used to be told not to analyze tables owned by SYS. However, we found that this was required before exporting constraints etc. from a production database during an upgrade from Oracle 9 to 11. It reduced the time taken from 2 to 3 hours down to 5 minutes.

Oracle's Idea of Infinity

In version 10, Oracle introduced binary_double and binary_float datatypes. If you select 1c or 1e from dual, the number 1 is given an alias of C or E respectively:

SQL> select 1c from dual

  2  /

         C

----------

         1

SQL> select 1e from dual

  2  /

         E

----------

         1

SQL>

If you select 1d from dual, it is treated as a binary_double datatype:

SQL> select 1d from dual

  2  /

        1D

----------

  1.0E+000

SQL>

If you select 1f from dual, it is treated as a binary_float datatype:

SQL> select 1f from dual

  2  /

        1F

----------

  1.0E+000

SQL>

You cannot divide 1c or 1e by zero:

SQL> select 1c/0 from dual

  2  /

select 1c/0 from dual

         *
ERROR at line 1:

ORA-00923: FROM keyword not found where expected

SQL> select 1e/0 from dual

  2  /

select 1e/0 from dual

         *
ERROR at line 1:

ORA-00923: FROM keyword not found where expected

SQL>

But 1d or 1f divided by zero is infinite. This is not what I learnt at school. In those days, any number divided by zero was undefined.

SQL> select 1d/0 from dual

  2  /

      1D/0

----------

       Inf

SQL> select 1f/0 from dual

  2  /

      1F/0

----------

       Inf

SQL>

Oracle also has the concept of negative infinity:

SQL> select -1d/0 from dual

  2  /

     -1D/0

----------

      -Inf

SQL> select -1f/0 from dual

  2  /

     -1F/0

----------

      -Inf

And you can even test if a value is infinite:

SQL> select 'Yes' as Yes from dual

  2  where 1d/0 is infinite

  3  /

YES

---

Yes

SQL> select 'Yes' as Yes from dual

  2  where -1f/0 is infinite

  3  /

YES

---

Yes

SQL>