------------------------------------
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
------------------------------------
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
------------------------------------
