SOME HANDY DBA SCRIPTS

------------------------------------
TO LIST JOBS CURRENTLY RUNNING:

SELECT a.job "Job",
               a.sid,
               a.failures "Failures",
              Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last Date",
              Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This Date"
FROM  dba_jobs_running a;

-------------------------------------
TO LIST ALL DB SESSIONS:

SELECT NVL(a.username, '(oracle)') AS username,
               a.osuser,
               a.sid,
               a.serial#,
               c.value AS,
               a.lockwait,
               a.status,
               a.module,
               a.machine,
               a.program,
               d.name,
               TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM    v$session a,
               v$sesstat c,
               v$statname d
WHERE a.sid = c.sid
     AND c.statistic# = d.statistic#
ORDER BY c.value DESC;

-------------------------------------
TO DISPLAY DB SESSIONS AS A HIERARCHY:

SELECT  LPAD(' ', (LEVEL - 1) * 2, ' ') || NVL (s.username, '(oracle)') AS username,
              s.osuser,
              s.sid,
              s.serial#,
              s.lockwait,
              s.status,
              s.module,
              s.machine,
              s.program,
              TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;
-----------------------------------
TO GRANT INSERT ON SCHEMA TABLES:

SPOOL temp.sql

SELECT 'GRANT INSERT ON "' ||u.table_name|| '" TO &1;'
FROM   user_tables u
WHERE NOT EXISTS (SELECT '1'
                                      FROM all_tab_privs a
                                     WHERE a.grantee = UPPER('&1') AND
                                                   a.privilege = 'INSERT' AND
                                                   a.table_name = u.table_name);

SPOOL OFF
-- Comment out following line to prevent immediate run
-- @temp.sql

-------------------------------------
TO GRANT EXECUTE ON SCHEMAS OBJECTS:

SPOOL temp.sql
SELECT 'GRANT EXECUTE ON "' ||u.object_name|| '" TO &1;'
FROM   user_objects u
WHERE u.object_type IN ('PACKAGE','PROCEDURE','FUNCTION')
    AND NOT EXISTS (SELECT '1'
                                     FROM all_tab_privs a
                                     WHERE a.grantee = UPPER('&1')
                                          AND a.privilege = 'EXECUTE'
                                          AND a.table_name = u.object_name);

SPOOL OFF
-- Comment out following line to prevent immediate run
-- @temp.sql

-------------------------------------
TO CREATE SYNONYMS IN CURRENT SCHEMA:

SPOOL temp.sql

SELECT 'CREATE SYNONYM "' ||a.object_name ||'" FOR "' ||a.owner|| '"."' ||a.object_name|| '";' FROM   all_objects a
WHERE a.object_type IN ('PACKAGE','PROCEDURE','FUNCTION')
     AND a.owner = UPPER('&1')
     AND NOT EXISTS (SELECT '1'
                                       FROM user_synonyms u
                                      WHERE u.synonym_name = a.object_name
                                           AND u.table_owner = UPPER('&1'));

SPOOL OFF
-- Comment out following line to prevent immediate run
-- @temp.sql

-------------------------------------
TO KNOW USER CONTEXT:

SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL;

-------------------------------------
TO SHOW INDEXES:

SELECT table_owner,
               table_name,
               owner AS index_owner,
               index_name,
               tablespace_name,
               num_rows,
               status,
               index_type
FROM   dba_indexes
WHERE table_owner = UPPER('&1')
     AND table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'))
ORDER BY table_owner, table_name, index_owner, index_name;

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

API FOR APPLICATION USER PASSWD RESET :

begin
      fnd_user_pkg.UpdateUser(
           x_user_name                             => <user login id>,
           x_owner                                    => null,
           x_unencrypted_password          => 'welcome1',
           x_start_date                              => to_date(sysdate,'MM/DD/YYYY'),
           x_end_date                               => fnd_user_pkg.null_date,
           x_last_logon_date                     => fnd_user_pkg.null_date,
           x_description                            => <user full name>,
           x_password_date                     => fnd_user_pkg.null_date,
           x_password_accesses_left        => fnd_user_pkg.null_number,
           x_password_lifespan_accesses => fnd_user_pkg.null_number,
           x_password_lifespan_days       => 180,
           x_employee_id                         => null,
           x_fax                                       => fnd_user_pkg.null_char,
           x_customer_id                         => fnd_user_pkg.null_number,
           x_supplier_id                           => fnd_user_pkg.null_number,
          x_email_address                      => <User Email Address>);
end;

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


API FOR ASSIGN SYSADMIN RESPONSIBILITY TO AN APPLICATION USER:

begin
    fnd_user_pkg.AddResp(
               username => <user login id>,
               resp_app => 'SYSADMIN',
               resp_key => 'SYSTEM_ADMINISTRATOR',
               security_group => 'STANDARD',
               description => 'System Administrator',
               start_date => sysdate,
               end_date => null);
end;

begin
    fnd_user_pkg.AddResp(
               username => <user login id>,
               resp_app => 'ICX',
               resp_key => 'SYSTEM_ADMINISTRATION',
               security_group => 'STANDARD',
               description => 'System Administration',
               start_date => sysdate,
               end_date => null);
end;

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

R12 COMPILE JAVA SERVER PAGES :

Go to $HTML_TOP or where the JSPs are located.
For example; to compile "abcdef_12.jsp"

$FND_TOP/patch/115/bin/ojspCompile.pl --compile -s 'abcdef_12.jsp' --flush

Check the log for any errors, correct them and compile.
Bounce the apache from the $ADMIN_SCRIPTS_HOME

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

R12 USERS WITH NO LOG ON ACTIVITY IN APPLICATIONS FOR LAST 60 DAYS:

SELECT USER_NAME,
               DESCRIPTION FULL_NAME,
               TO_CHAR(LAST_LOGON_DATE, 'DD-MON-YYYY') LastLogon
  FROM  FND_USER
WHERE LAST_LOGON_DATE < SYSDATE - 60 AND
               USER_ID > 1100 AND
               END_DATE IS NULL
ORDER BY last_logon_date ASC


------------------------------------
Some Useful Links
http://www.orafaq.com/wiki/Scripts
http://oracle-base.com/dba/scripts.php
http://dbakevlar.com/scripts/
http://rafioracledba.blogspot.com/2010/08/database-health-check.html
------------------------------------