Tuesday, August 30, 2011

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>

Monday, August 29, 2011

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.

Sunday, August 14, 2011

DBA_USERS_WITH_DEFPWD

Here is an Oracle 10 database, which does not have a DBA_USERS_WITH_DEFPWD view:

ORACLE 10 > sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Aug 11 18:03:27 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> desc dba_users_with_defpwd
ERROR:
ORA-04043: object dba_users_with_defpwd does not exist

SQL>

And here is an Oracle 11 database, which does:

ORACLE 11 > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 11 18:31:20 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc dba_users_with_defpwd
Name                    Null?    Type
----------------------- -------- ----------------
USERNAME                NOT NULL VARCHAR2(30)

SQL> select password from sys.user$
  2  where name = 'SYSTEM';

PASSWORD
------------------------------
D4DF7931AB130E37

SQL>

So, although I have not checked, it looks to me as if this view was new in Oracle 11. The SELECT statement at the end shows that the PASSWORD column in SYS.USER$ has the old Oracle 10 encrypted value.

The dba_users_with_defpwd view shows users with default passwords:

SQL> alter user system identified by manager
  2  /

User altered.

SQL> select * from dba_users_with_defpwd
  2  /

USERNAME
------------------------------                   
SYSTEM
XS$NULL

2 rows selected.

SQL> alter user system identified by highly_secret
  2  /

User altered.

SQL> select * from dba_users_with_defpwd
  2  /

USERNAME
------------------------------
XS$NULL

1 row selected.

SQL>

It even shows ordinary users:

SQL> create user scott identified by tiger
  2  /

User created.

SQL> select * from dba_users_with_defpwd
  2  /

USERNAME
------------------------------
XS$NULL
SCOTT

2 rows selected.

SQL> alter user scott identified by lion
  2  /

User altered.

SQL> select * from dba_users_with_defpwd
  2  /

USERNAME
------------------------------
XS$NULL

1 row selected.

SQL>

This is the text of the view. The comparison is done on the old Oracle 10 encrypted value:

SQL> select text from dba_views
  2  where view_name = 'DBA_USERS_WITH_DEFPWD'
  3  /

TEXT
-------------------------------------------------
SELECT DISTINCT u.name
    FROM SYS.user$ u, SYS.default_pwd$ dp
   WHERE
     (u.type# = 1
      AND bitand(u.astatus, 16) = 16
     ) OR 
     (u.type# = 1 
     AND u.password = dp.pwd_verifier
     AND u.name     = dp.user_name
     AND dp.pv_type = 0)

1 row selected.

SQL>

So both upper and lower case versions of the default passwords
will be detected:

SQL> alter user system identified by manager
  2  /

User altered.

SQL> select * from dba_users_with_defpwd
  2  /

USERNAME
------------------------------
SYSTEM
XS$NULL

2 rows selected.

SQL>

Wednesday, August 10, 2011

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>