Find Password in Oracle E-Business Suite 11i and R12

This scripts allows to view the passwords which are stored encrypted in fnd_user table.

You can use different sqls to find apps password and application user passwords.

Create Package

CREATE OR REPLACE PACKAGE XXARTO_GET_PWD

AS

   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2;

END XXARTO_GET_PWD;

CREATE OR REPLACE PACKAGE BODY XXARTO_GET_PWD

AS

   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2

   AS

      LANGUAGE JAVA

      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt

(java.lang.String,java.lang.String) return java.lang.String';

END XXARTO_GET_PWD;

Find apps password in 11i

SELECT (SELECT XXARTO_GET_PWD.Decrypt (

                  UPPER( (SELECT UPPER (Fnd_Profile.VALUE ('Guest_User_Pwd'))

                            FROM DUAL)),

                  Usertable.Encrypted_Foundation_Password)

          FROM DUAL)

          AS Apps_Password

  FROM applsys.Fnd_User Usertable

 WHERE Usertable.User_Name LIKE

          UPPER( (SELECT SUBSTR (

                            Fnd_Profile.VALUE ('Guest_User_Pwd'),

                            1,

                            INSTR (Fnd_Profile.VALUE ('Guest_User_Pwd'), '/')

                            - 1)

                    FROM DUAL));
 

Find apps password in R12

 SELECT (SELECT XXARTO_GET_PWD.Decrypt (

                  Fnd_Web_Sec.Get_Guest_Username_Pwd,

                  Usertable.Encrypted_Foundation_Password)

          FROM DUAL)

          AS Apps_Password

  FROM applsys.Fnd_User Usertable

 WHERE Usertable.User_Name LIKE

          (SELECT SUBSTR (

                     Fnd_Web_Sec.Get_Guest_Username_Pwd,

                     1,

                     INSTR (Fnd_Web_Sec.Get_Guest_Username_Pwd, '/') - 1)

             FROM DUAL);
 

Find application user password (for exaample SYSADMIN) in 11i

SELECT Usertable.User_Name,

       (SELECT XXARTO_GET_PWD.Decrypt (

                  UPPER( (SELECT (SELECT XXARTO_GET_PWD.Decrypt (

                                            UPPER( (SELECT UPPER(Fnd_Profile.VALUE('Guest_User_Pwd'))

                                                      FROM DUAL)),

                                            Usertable.Encrypted_Foundation_Password)

                                    FROM DUAL)

                                    AS Apps_Password

                            FROM applsys.Fnd_User Usertable

                           WHERE Usertable.User_Name LIKE

                                    UPPER( (SELECT SUBSTR (

                                                      Fnd_Profile.VALUE (

                                                         'Guest_User_Pwd'),

                                                      1,

                                                      INSTR (

                                                         Fnd_Profile.VALUE (

                                                            'Guest_User_Pwd'),

                                                         '/')

                                                      - 1)

                                              FROM DUAL)))),

                  Usertable.Encrypted_User_Password)

          FROM DUAL)

          AS Encrypted_User_Password

  FROM Applsys.Fnd_User Usertable

 WHERE Usertable.User_Name LIKE UPPER ('&Username');
 

Find application user password (for exaample SYSADMIN) in R12

SELECT Usr.User_Name,

       Usr.Description,

       XXARTO_GET_PWD.Decrypt (

          (SELECT (SELECT XXARTO_GET_PWD.Decrypt (

                             Fnd_Web_Sec.Get_Guest_Username_Pwd,

                             Usertable.Encrypted_Foundation_Password)

                     FROM DUAL)

                     AS Apps_Password

             FROM applsys.Fnd_User Usertable

            WHERE Usertable.User_Name =

                     (SELECT SUBSTR (

                                Fnd_Web_Sec.Get_Guest_Username_Pwd,

                                1,

                                INSTR (Fnd_Web_Sec.Get_Guest_Username_Pwd,

                                       '/')

                                - 1)

                        FROM DUAL)),

          Usr.Encrypted_User_Password)

          Password

  FROM applsys.Fnd_User Usr

 WHERE Usr.User_Name = '&User_Name';

Resize or Shrink Temporary Tablespace

If temporary tablespace size very large because of some bad queries or some other unexpected sorting operations, as maintanance DBA should shrink and reclaim that extra space. First, try resize the tempfile.

SQL> alter database tempfile '/oracle/oradata/ORCL/test01.dbf' resize 500M;

and faced error ORA-03297: file contains used data beyond request RESIZE value.

There are several methods to reclaim unused space depend on Oracle versions.

Method I	Database Version < 9i
	Database Version >=9i and related tablespace is not default temporary tablespace
Method II	Database Version >=9i and and related tablespace is default temporary tablespace
Method III	Database Version >=9i
Method IV	Database Version >=11g

Note: It is advisable to realize this operations during off hours. Otherwise  If tempfile is used then could not drop and give error ORA-25152: TEMPFILE cannot be dropped at this time. In this situation there are 2 scripts depend on oracle versions to find who use the temporary  segments.

Version <9i
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text

  FROM v$session a, v$sort_usage b, v$sqlarea c
  WHERE b.tablespace = 'TEMP'
  and a.saddr = b.session_addr
  AND c.address= a.sql_address
  AND c.hash_value = a.sql_hash_value
  ORDER BY b.tablespace, b.blocks; 
Version >=9i   
   SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text
    FROM v$session a, v$tempseg_usage b, v$sqlarea c
    WHERE b.tablespace = 'TEMP'
     and a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    ORDER BY b.tablespace, b.blocks;

Method I : 

Drop and recreate temporary tablespace.

SQL> DROP TABLESPACE temp;

SQL> CREATE TEMPORARY TABLESPACE TEMP
2 TEMPFILE '/oracle/oradata/ORCL/temp01.dbf' SIZE 500M
3 AUTOEXTEND ON NEXT 100M MAXSIZE 2048M
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Method II : 

Drop and recreate temporary tablespace when TEMP is default temporary tablespace.

If TEMP is default temporary tablespace and try to drop this tablespace like Method I faced error ORA-12906: cannot drop default temporary tablespace

In this situation steps like below

1. Create another temporary tablespace(create TEMP2)

2. Make new temporary tablespace is default (TEMP2 default)

3. Drop old temporary tablespace (drop TEMP)

4. Create temporary tablespace with old name (create TEMP)

5. Make new created tablespace is default (TEMP default)

6. Drop tablespace which created in step 1 (drop TEMP2)

SQL> CREATE TEMPORARY TABLESPACE TEMP2

2 TEMPFILE '/oracle/oradata/ORCL/temp02.dbf' SIZE 100M

3 AUTOEXTEND ON NEXT 10M MAXSIZE 2048M

4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

SQL> CREATE TEMPORARY TABLESPACE TEMP 2 TEMPFILE '/oracle/oradata/ORCL/temp01.dbf' SIZE 500M 3 AUTOEXTEND ON NEXT 100M MAXSIZE 2048M 4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

SQL> DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;

Method III : 

This method drop a tempfile. Only valid for >=9i version.First find tempfiles from v$tempfile and then drop

SQL> ALTER DATABASE TEMPFILE '/oracle/oradata/ORCL/temp01.dbf' DROP INCLUDING DATAFILES;

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/ORCL/temp01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 2048M;

Method IV: 

Shrink temporary tablespace and/or tempfile. Only valid for >=11g

ALTER TABLEPSACE TEMP SHRINK SPACE
or
ALTER TABLESPACE TEMP SHRINK TEMPFILE '................' KEEP 250M;