cd /etc/ssh
vi sshd_config
Edit the
port which you want to set In my case i
have set 1993.
x
Restart sshd services.
Finished
Saturday, 23 June 2018
MONITORING TABLEPSACE SIZE
https://dbissues.blogspot.com/2018/06/monitoring-tablepsace-size.html
1:) To check total size of table space in "MB";
select tablespace_name,sum(bytes)/1024/1024 "total size in mb" from dba_data_files
group by tablespace_name;
2:) To check free space in table space in "MB";
select tablespace_name,sum(bytes)/1024/1024 "free space in mb" from dba_free_space
group by tablespace_name;
3:) You can check the single datafile full space by the following command;
select tablespace_name,file_id,file_name,sum(bytes)/1024/1024 "total size in mb" from dba_data_files
where file_id=6
group by tablespace_name,file_id,file_name;
4:) You can check the single datafile free space by the following command;
select tablespace_name,file_id,sum(bytes)/1024/1024 "size in MB" from dba_free_space
where file_id=6
group by tablespace_name,file_id;
5:)To add datafile in tables pace USERS;
alter table space users add datafile '/u01/app/oracle/oradata/orcl/users03' size 2m;
6:) To drop datafile from tablespace USERS;
alter tables pace users drop datafile '/u01/app/oracle/oradata/orcl/users02.dbf';
Thursday, 31 May 2018
Application DBA Queries
https://dbissues.blogspot.com/2018/05/application-dba-queries.html
QUERY TO GET all user assigned to GIVEN RESPONSIBILITY
SELECT UNIQUE u.user_id,
Substr(u.user_name, 1, 30) user_name,
Substr(r.responsibility_name, 1, 60) responsiblity,
Substr(a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
AND r.responsibility_name = ‘System Administrator’
–“Write Responsibility Name Here “
ORDER BY Substr(user_name, 1, 30),
Substr(a.application_name, 1, 50),
Substr(r.responsibility_name, 1, 60);
Query to get all Responsibility assigned to a user
SELECT UNIQUE u.user_id,
Substr (u.user_name, 1, 30) user_name,
Substr (r.responsibility_name, 1, 60) responsiblity,
Substr (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
AND u.user_name = ‘SYED.SALI’ –“Write Application User Name Here “
ORDER BY Substr (user_name, 1, 30),
Substr (a.application_name, 1, 50),
Substr (r.responsibility_name, 1, 60);
QUERY FOR Workflow Mailer Configurations
SELECT p.parameter_id,
p.parameter_name,
v.parameter_value value
FROM apps.fnd_svc_comp_param_vals_v v,
apps.fnd_svc_comp_params_b p,
apps.fnd_svc_components c
WHERE c.component_type = ‘WF_MAILER’
AND v.component_id = c.component_id
AND v.parameter_id = p.parameter_id
AND p.parameter_name IN ( ‘OUTBOUND_SERVER’, ‘INBOUND_SERVER’, ‘ACCOUNT’,
‘FROM’,
‘NODENAME’, ‘REPLYTO’, ‘DISCARD’, ‘PROCESS’,
‘INBOX’ )
ORDER BY p.parameter_name;
QUERY TO FIND WHO is connected when to which responsibility
QUERY TO FIND Number of user connected to EBS
SELECT Count (DISTINCT d.user_name)
FROM apps.fnd_logins a,
v$session b,
v$process c,
apps.fnd_user d
WHERE b.paddr = c.addr
AND a.pid = c.pid
AND a.spid = b.process
AND d.user_id = a.user_id
AND ( d.user_name = ‘USER_NAME’
OR 1 = 1 );
QUERY TO FIND E-BUSINESS SUITE PROFILE OPTIONS FOR ALL VALUES
SELECT p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
Decode(v.level_id, 10001, ‘Site’,
10002, ‘Application’,
10003, ‘Responsibility’,
10004, ‘User’,
10005, ‘Server’,
10006, ‘Org’,
10007, Decode(To_char(v.level_value2), ‘-1’,
‘Responsibility’
,
Decode(To_char(v.level_value), ‘-1’, ‘Server’,
‘Server+Resp’)),
‘UnDef’) LEVEL_SET,
Decode(To_char(v.level_id), ‘10001’, ”,
‘10002’, app.application_short_name,
‘10003’, rsp.responsibility_key,
‘10004’, usr.user_name,
‘10005’, svr.node_name,
‘10006’, org.name,
‘10007’,
Decode(To_char(v.level_value2), ‘-1’,
rsp.responsibility_key,
Decode(
To_char(v.level_value), ‘-1’, (SELECT
node_name
FROM
fnd_nodes
WHERE
node_id = v.level_value2),
(SELECT
node_name
FROM
fnd_nodes
WHERE
node_id = v.level_value2)
|| ‘-‘
||
rsp.responsibility_key)),
‘UnDef’) “CONTEXT”,
v.profile_option_value VALUE
FROM fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE p.profile_option_id = v.profile_option_id(+)
AND p.profile_option_name = n.profile_option_name
AND Upper(p.profile_option_name) IN
(SELECT profile_option_name
FROM fnd_profile_options_tl
WHERE Upper(user_profile_option_name) LIKE
Upper(‘%&user_profile_name%’))
AND usr.user_id(+) = v.level_value
AND rsp.application_id(+) = v.level_value_application_id
AND rsp.responsibility_id(+) = v.level_value
AND app.application_id(+) = v.level_value
AND svr.node_id(+) = v.level_value
AND org.organization_id(+) = v.level_value
ORDER BY short_name,
user_profile_option_name,
level_id,
level_set;
QUERY TO FIND APPLICATION USER DETAILS
SELECT user_name,
user_id,
Decode(employee_id, NULL, NULL,
‘E’) employee_flag,
description,
start_date,
end_date,
Decode(end_date, Least(end_date, SYSDATE), NULL,
‘+’) f,
To_char(last_logon_date, ‘mm/dd/yy hh24:mi:ss’) last_logon_time,
Decode(end_date, NULL, SYSDATE – last_logon_date) last_logon_days,
Decode(end_date, NULL, Decode(30, Greatest(30, Trunc(
SYSDATE – last_logon_date))
, NULL,
‘*’)) p,
password_lifespan_days,
password_date,
Decode(end_date, NULL, SYSDATE – password_date) password_change_days
FROM applsys.fnd_user u
WHERE user_name LIKE Upper(‘%&userid%’) –“Write Application User Name Here “
ORDER BY description,
user_name;
QUERY TO FIND COMPLETE DATABASE SIZE
SELECT ‘DATA_n_INDEX: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’ FULL_DATABASE_SIZE
FROM dba_data_files
UNION
SELECT ‘TEMP: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’
FROM dba_temp_files
UNION
SELECT ‘REDO LOGS: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’
FROM v$log
UNION
SELECT ‘CONTROLFILE: ‘
|| SUM(file_size_blks * block_size) / 1024 / 1024
|| ‘ MBytes’
FROM v$controlfile;
QUERY TO VERIFY THE WORKFLOW IMAP SETTING
SELECT c.component_id,
c.component_name,
p.parameter_id,
p.parameter_name,
v.parameter_value value
FROM fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
fnd_svc_components c
WHERE c.component_type = ‘WF_MAILER’
AND v.component_id = c.component_id
AND v.parameter_id = p.parameter_id
AND p.parameter_name IN ( ‘INBOUND_SERVER’, ‘ACCOUNT’, ‘REPLYTO’ )
ORDER BY c.component_id,
c.component_name,
p.parameter_name;
QUERY TO CHECK WEB SESSIONS
SELECT Count(*)
FROM v$process p,
gv$session s
WHERE p.addr = s.paddr
AND s.program = ‘JDBC Thin Client’;
QUERY TO FIND Global Locks
SELECT Count(*)
FROM gv$lock a
WHERE a.id1 IN (SELECT id1
FROM gv$lock
WHERE request <> 0);
QUERY to find out version of a PACKAGE
SELECT text
FROM dba_source
WHERE line = 2
AND name = ‘AP_IMPORT_INVOICES_PKG’; — “ENTER PACKAGE NAME HERE”
QUERY TO GET all user assigned to GIVEN RESPONSIBILITY
SELECT UNIQUE u.user_id,
Substr(u.user_name, 1, 30) user_name,
Substr(r.responsibility_name, 1, 60) responsiblity,
Substr(a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
AND r.responsibility_name = ‘System Administrator’
–“Write Responsibility Name Here “
ORDER BY Substr(user_name, 1, 30),
Substr(a.application_name, 1, 50),
Substr(r.responsibility_name, 1, 60);
Query to get all Responsibility assigned to a user
SELECT UNIQUE u.user_id,
Substr (u.user_name, 1, 30) user_name,
Substr (r.responsibility_name, 1, 60) responsiblity,
Substr (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
AND u.user_name = ‘SYED.SALI’ –“Write Application User Name Here “
ORDER BY Substr (user_name, 1, 30),
Substr (a.application_name, 1, 50),
Substr (r.responsibility_name, 1, 60);
QUERY FOR Workflow Mailer Configurations
SELECT p.parameter_id,
p.parameter_name,
v.parameter_value value
FROM apps.fnd_svc_comp_param_vals_v v,
apps.fnd_svc_comp_params_b p,
apps.fnd_svc_components c
WHERE c.component_type = ‘WF_MAILER’
AND v.component_id = c.component_id
AND v.parameter_id = p.parameter_id
AND p.parameter_name IN ( ‘OUTBOUND_SERVER’, ‘INBOUND_SERVER’, ‘ACCOUNT’,
‘FROM’,
‘NODENAME’, ‘REPLYTO’, ‘DISCARD’, ‘PROCESS’,
‘INBOX’ )
ORDER BY p.parameter_name;
QUERY TO FIND WHO is connected when to which responsibility
SELECT DISTINCT fu.user_name User_Name,
fr.responsibility_key Responsibility
FROM fnd_user fu,
fnd_responsibility fr,
icx_sessions ic
WHERE fu.user_id = ic.user_id
AND fr.responsibility_id = ic.responsibility_id
AND ic.disabled_flag = 'N'
AND ic.responsibility_id IS NOT NULL
AND ic.last_connect LIKE SYSDATE; -- "Enter the Date here"
QUERY TO FIND Number of user connected to EBS
SELECT Count (DISTINCT d.user_name)
FROM apps.fnd_logins a,
v$session b,
v$process c,
apps.fnd_user d
WHERE b.paddr = c.addr
AND a.pid = c.pid
AND a.spid = b.process
AND d.user_id = a.user_id
AND ( d.user_name = ‘USER_NAME’
OR 1 = 1 );
QUERY TO FIND E-BUSINESS SUITE PROFILE OPTIONS FOR ALL VALUES
SELECT p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
Decode(v.level_id, 10001, ‘Site’,
10002, ‘Application’,
10003, ‘Responsibility’,
10004, ‘User’,
10005, ‘Server’,
10006, ‘Org’,
10007, Decode(To_char(v.level_value2), ‘-1’,
‘Responsibility’
,
Decode(To_char(v.level_value), ‘-1’, ‘Server’,
‘Server+Resp’)),
‘UnDef’) LEVEL_SET,
Decode(To_char(v.level_id), ‘10001’, ”,
‘10002’, app.application_short_name,
‘10003’, rsp.responsibility_key,
‘10004’, usr.user_name,
‘10005’, svr.node_name,
‘10006’, org.name,
‘10007’,
Decode(To_char(v.level_value2), ‘-1’,
rsp.responsibility_key,
Decode(
To_char(v.level_value), ‘-1’, (SELECT
node_name
FROM
fnd_nodes
WHERE
node_id = v.level_value2),
(SELECT
node_name
FROM
fnd_nodes
WHERE
node_id = v.level_value2)
|| ‘-‘
||
rsp.responsibility_key)),
‘UnDef’) “CONTEXT”,
v.profile_option_value VALUE
FROM fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE p.profile_option_id = v.profile_option_id(+)
AND p.profile_option_name = n.profile_option_name
AND Upper(p.profile_option_name) IN
(SELECT profile_option_name
FROM fnd_profile_options_tl
WHERE Upper(user_profile_option_name) LIKE
Upper(‘%&user_profile_name%’))
AND usr.user_id(+) = v.level_value
AND rsp.application_id(+) = v.level_value_application_id
AND rsp.responsibility_id(+) = v.level_value
AND app.application_id(+) = v.level_value
AND svr.node_id(+) = v.level_value
AND org.organization_id(+) = v.level_value
ORDER BY short_name,
user_profile_option_name,
level_id,
level_set;
QUERY TO FIND APPLICATION USER DETAILS
SELECT user_name,
user_id,
Decode(employee_id, NULL, NULL,
‘E’) employee_flag,
description,
start_date,
end_date,
Decode(end_date, Least(end_date, SYSDATE), NULL,
‘+’) f,
To_char(last_logon_date, ‘mm/dd/yy hh24:mi:ss’) last_logon_time,
Decode(end_date, NULL, SYSDATE – last_logon_date) last_logon_days,
Decode(end_date, NULL, Decode(30, Greatest(30, Trunc(
SYSDATE – last_logon_date))
, NULL,
‘*’)) p,
password_lifespan_days,
password_date,
Decode(end_date, NULL, SYSDATE – password_date) password_change_days
FROM applsys.fnd_user u
WHERE user_name LIKE Upper(‘%&userid%’) –“Write Application User Name Here “
ORDER BY description,
user_name;
QUERY TO FIND COMPLETE DATABASE SIZE
SELECT ‘DATA_n_INDEX: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’ FULL_DATABASE_SIZE
FROM dba_data_files
UNION
SELECT ‘TEMP: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’
FROM dba_temp_files
UNION
SELECT ‘REDO LOGS: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’
FROM v$log
UNION
SELECT ‘CONTROLFILE: ‘
|| SUM(file_size_blks * block_size) / 1024 / 1024
|| ‘ MBytes’
FROM v$controlfile;
QUERY TO VERIFY THE WORKFLOW IMAP SETTING
SELECT c.component_id,
c.component_name,
p.parameter_id,
p.parameter_name,
v.parameter_value value
FROM fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
fnd_svc_components c
WHERE c.component_type = ‘WF_MAILER’
AND v.component_id = c.component_id
AND v.parameter_id = p.parameter_id
AND p.parameter_name IN ( ‘INBOUND_SERVER’, ‘ACCOUNT’, ‘REPLYTO’ )
ORDER BY c.component_id,
c.component_name,
p.parameter_name;
QUERY TO CHECK WEB SESSIONS
SELECT Count(*)
FROM v$process p,
gv$session s
WHERE p.addr = s.paddr
AND s.program = ‘JDBC Thin Client’;
QUERY TO FIND Global Locks
SELECT Count(*)
FROM gv$lock a
WHERE a.id1 IN (SELECT id1
FROM gv$lock
WHERE request <> 0);
QUERY to find out version of a PACKAGE
SELECT text
FROM dba_source
WHERE line = 2
AND name = ‘AP_IMPORT_INVOICES_PKG’; — “ENTER PACKAGE NAME HERE”
Friday, 27 April 2018
ORA-01476: divisor is equal to zero
https://dbissues.blogspot.com/2018/04/ora-01476-divisor-is-equal-to-zero.html
Please Copy Paste the Below URL in your Browser
https://easyoradba.com/2013/08/23/ora-01476-divisor-is-equal-to-zero/
Please Copy Paste the Below URL in your Browser
https://easyoradba.com/2013/08/23/ora-01476-divisor-is-equal-to-zero/
Sunday, 17 December 2017
Unsuccessful/Successful Logins with Status (Active/In Active/Locked) History from FND_USER
https://dbissues.blogspot.com/2017/12/unsuccessfulsuccessful-logins-with.html
Create Table
create table XXX_USER_STATUS_HIST
(
user_id number,
start_date date,
end_Date date,
Creation_date date,
encrypted_user_password varchar2(100)
)
---------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER XXX_USER_STATUS_HIST_TRG
BEFORE INSERT OR UPDATE ON "APPLSYS"."FND_USER#"
FOR EACH ROW
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
insert into XXX_USER_STATUS_HIST
values
(:new.user_id , :new.start_date ,:new.end_date , sysdate , :new.encrypted_user_password );
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (
num=> -20107,
msg=> 'Some Error Occurred.......');
END XXX_USER_STATUS_HIST_TRG;
------------------------------------------------------------------------------------------------
Query
select user_id, USER_NAME, DESCRIPTION,FAX,START_TIME, LOGIN ,
(
select max( case
when xx.ENCRYPTED_USER_PASSWORD = 'INVALID' then 'LOCKED'
when xx.end_date is null then 'ACTIVE'
when xx.end_date is not null and ABC.start_time < xx.end_date then 'ACTIVE'
else 'INACTIVE'
end )
from XXX_USER_STATUS_HIST XX
where XX.user_id = ABC.USER_ID
AND XX.creation_date = ( select max(creation_date) from XXX_USER_STATUS_HIST XX2 where XX2.user_id = ABC.USER_ID and xx2.creation_date <= ABC.start_time )
)
STATUS
/*,
(
select max( xx.ENCRYPTED_USER_PASSWORD )
from XXX_USER_STATUS_HIST XX
where XX.user_id = ABC.USER_ID
AND XX.creation_date = ( select max(creation_date) from XXX_USER_STATUS_HIST XX2 where XX2.user_id = ABC.USER_ID and xx2.creation_date <= ABC.start_time )
)
ENCRYPTED_USER_PASSWORD*/
from
(SELECT b.USER_ID , b.USER_NAME, b.DESCRIPTION, b.FAX, u.ATTEMPT_TIME START_TIME , 'UnSuccessful Login' LOGIN
,
/*( \*select *\
case when NVL(b.end_date, SYSDATE) >= SYSDATE
and b.encrypted_user_password <> 'INVALID' THEN 'Active'
when NVL (b.end_date, SYSDATE) < SYSDATE
and b.encrypted_user_password <> 'INVALID' THEN 'In Active'
when encrypted_user_password = 'INVALID' THEN 'Locked'
Else null
END --from FND_USER where user_id = U.user_id
)*/
--'N/A' STATUS,
b.ENCRYPTED_USER_PASSWORD
FROM FND_UNSUCCESSFUL_LOGINS U, FND_USER B
WHERE U.USER_ID = B.USER_ID
AND B.USER_NAME LIKE NVL(&USER_NAME, '%')
AND U.ATTEMPT_TIME
BETWEEN CASE
WHEN LENGTH(&START_TIME) in (14,15)
THEN TO_DATE(&START_TIME, 'DD-MON-RR HH24:MI')
WHEN LENGTH(&START_TIME) in (8,9)
THEN TO_DATE(&START_TIME, 'DD-MON-RR')
ELSE U.ATTEMPT_TIME
END
AND CASE
WHEN LENGTH(&END_TIME) in (14,15)
THEN TO_DATE(&END_TIME, 'DD-MON-RR HH24:MI')
WHEN LENGTH(&END_TIME) in (8,9)
THEN TO_DATE(&END_TIME, 'DD-MON-RR') + 1
ELSE U.ATTEMPT_TIME
END
union
select c.USER_ID , c.USER_NAME, c.DESCRIPTION, c.FAX, a.START_TIME START_TIME , 'Successful Login' LOGIN
,
/*( \*select*\
case when NVL(c.end_date, SYSDATE) >= SYSDATE
and c.encrypted_user_password <> 'INVALID' THEN 'Active'
when NVL (c.end_date, SYSDATE) < SYSDATE
and c.encrypted_user_password <> 'INVALID' THEN 'In Active'
when encrypted_user_password = 'INVALID' THEN 'Locked'
Else null
END --from FND_USER where user_id = a.user_id
)*/
--'N/A' STATUS
c.ENCRYPTED_USER_PASSWORD
from fnd_logins a, fnd_user c
where a.user_id = c.user_id
and c.USER_NAME LIKE NVL(&USER_NAME, '%')
and a.LOGIN_TYPE = 'FORM'
AND a.START_TIME
BETWEEN CASE
WHEN LENGTH(&START_TIME) in (14,15)
THEN TO_DATE(&START_TIME, 'DD-MON-RR HH24:MI')
WHEN LENGTH(&START_TIME) in (8,9)
THEN TO_DATE(&START_TIME, 'DD-MON-RR')
ELSE a.START_TIME
END
AND CASE
WHEN LENGTH(&END_TIME) in (14,15)
THEN TO_DATE(&END_TIME, 'DD-MON-RR HH24:MI')
WHEN LENGTH(&END_TIME) in (8,9)
THEN TO_DATE(&END_TIME, 'DD-MON-RR') + 1
ELSE a.START_TIME
END
) ABC
order by (START_TIME) desc
Create Table
create table XXX_USER_STATUS_HIST
(
user_id number,
start_date date,
end_Date date,
Creation_date date,
encrypted_user_password varchar2(100)
)
---------------------------------------------------------------------------------------
Trigger to Maintain History
CREATE OR REPLACE TRIGGER XXX_USER_STATUS_HIST_TRG
BEFORE INSERT OR UPDATE ON "APPLSYS"."FND_USER#"
FOR EACH ROW
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
insert into XXX_USER_STATUS_HIST
values
(:new.user_id , :new.start_date ,:new.end_date , sysdate , :new.encrypted_user_password );
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (
num=> -20107,
msg=> 'Some Error Occurred.......');
END XXX_USER_STATUS_HIST_TRG;
------------------------------------------------------------------------------------------------
Query
select user_id, USER_NAME, DESCRIPTION,FAX,START_TIME, LOGIN ,
(
select max( case
when xx.ENCRYPTED_USER_PASSWORD = 'INVALID' then 'LOCKED'
when xx.end_date is null then 'ACTIVE'
when xx.end_date is not null and ABC.start_time < xx.end_date then 'ACTIVE'
else 'INACTIVE'
end )
from XXX_USER_STATUS_HIST XX
where XX.user_id = ABC.USER_ID
AND XX.creation_date = ( select max(creation_date) from XXX_USER_STATUS_HIST XX2 where XX2.user_id = ABC.USER_ID and xx2.creation_date <= ABC.start_time )
)
STATUS
/*,
(
select max( xx.ENCRYPTED_USER_PASSWORD )
from XXX_USER_STATUS_HIST XX
where XX.user_id = ABC.USER_ID
AND XX.creation_date = ( select max(creation_date) from XXX_USER_STATUS_HIST XX2 where XX2.user_id = ABC.USER_ID and xx2.creation_date <= ABC.start_time )
)
ENCRYPTED_USER_PASSWORD*/
from
(SELECT b.USER_ID , b.USER_NAME, b.DESCRIPTION, b.FAX, u.ATTEMPT_TIME START_TIME , 'UnSuccessful Login' LOGIN
,
/*( \*select *\
case when NVL(b.end_date, SYSDATE) >= SYSDATE
and b.encrypted_user_password <> 'INVALID' THEN 'Active'
when NVL (b.end_date, SYSDATE) < SYSDATE
and b.encrypted_user_password <> 'INVALID' THEN 'In Active'
when encrypted_user_password = 'INVALID' THEN 'Locked'
Else null
END --from FND_USER where user_id = U.user_id
)*/
--'N/A' STATUS,
b.ENCRYPTED_USER_PASSWORD
FROM FND_UNSUCCESSFUL_LOGINS U, FND_USER B
WHERE U.USER_ID = B.USER_ID
AND B.USER_NAME LIKE NVL(&USER_NAME, '%')
AND U.ATTEMPT_TIME
BETWEEN CASE
WHEN LENGTH(&START_TIME) in (14,15)
THEN TO_DATE(&START_TIME, 'DD-MON-RR HH24:MI')
WHEN LENGTH(&START_TIME) in (8,9)
THEN TO_DATE(&START_TIME, 'DD-MON-RR')
ELSE U.ATTEMPT_TIME
END
AND CASE
WHEN LENGTH(&END_TIME) in (14,15)
THEN TO_DATE(&END_TIME, 'DD-MON-RR HH24:MI')
WHEN LENGTH(&END_TIME) in (8,9)
THEN TO_DATE(&END_TIME, 'DD-MON-RR') + 1
ELSE U.ATTEMPT_TIME
END
union
select c.USER_ID , c.USER_NAME, c.DESCRIPTION, c.FAX, a.START_TIME START_TIME , 'Successful Login' LOGIN
,
/*( \*select*\
case when NVL(c.end_date, SYSDATE) >= SYSDATE
and c.encrypted_user_password <> 'INVALID' THEN 'Active'
when NVL (c.end_date, SYSDATE) < SYSDATE
and c.encrypted_user_password <> 'INVALID' THEN 'In Active'
when encrypted_user_password = 'INVALID' THEN 'Locked'
Else null
END --from FND_USER where user_id = a.user_id
)*/
--'N/A' STATUS
c.ENCRYPTED_USER_PASSWORD
from fnd_logins a, fnd_user c
where a.user_id = c.user_id
and c.USER_NAME LIKE NVL(&USER_NAME, '%')
and a.LOGIN_TYPE = 'FORM'
AND a.START_TIME
BETWEEN CASE
WHEN LENGTH(&START_TIME) in (14,15)
THEN TO_DATE(&START_TIME, 'DD-MON-RR HH24:MI')
WHEN LENGTH(&START_TIME) in (8,9)
THEN TO_DATE(&START_TIME, 'DD-MON-RR')
ELSE a.START_TIME
END
AND CASE
WHEN LENGTH(&END_TIME) in (14,15)
THEN TO_DATE(&END_TIME, 'DD-MON-RR HH24:MI')
WHEN LENGTH(&END_TIME) in (8,9)
THEN TO_DATE(&END_TIME, 'DD-MON-RR') + 1
ELSE a.START_TIME
END
) ABC
order by (START_TIME) desc
Current Status Active/Inactive and Locked users from FND_USER
https://dbissues.blogspot.com/2017/12/activeinactive-and-locked-users-from.html
SELECT 'Active' STATUS, valid.*
FROM fnd_user valid
WHERE valid.user_id IN (SELECT user_id
FROM fnd_user
WHERE NVL (end_date, SYSDATE) >= SYSDATE)
and valid.encrypted_user_password <> 'INVALID'
UNION ALL
SELECT 'In Active' STATUS, invalid.*
FROM fnd_user invalid
WHERE invalid.user_id IN (SELECT user_id
FROM fnd_user
WHERE NVL (end_date, SYSDATE) < SYSDATE)
and invalid.encrypted_user_password <> 'INVALID'
UNION ALL
SELECT 'Locked' STATUS, Locked.*
FROM fnd_user Locked
WHERE Locked.encrypted_user_password = 'INVALID'
ORDER BY 2;
SELECT 'Active' STATUS, valid.*
FROM fnd_user valid
WHERE valid.user_id IN (SELECT user_id
FROM fnd_user
WHERE NVL (end_date, SYSDATE) >= SYSDATE)
and valid.encrypted_user_password <> 'INVALID'
UNION ALL
SELECT 'In Active' STATUS, invalid.*
FROM fnd_user invalid
WHERE invalid.user_id IN (SELECT user_id
FROM fnd_user
WHERE NVL (end_date, SYSDATE) < SYSDATE)
and invalid.encrypted_user_password <> 'INVALID'
UNION ALL
SELECT 'Locked' STATUS, Locked.*
FROM fnd_user Locked
WHERE Locked.encrypted_user_password = 'INVALID'
ORDER BY 2;
Friday, 15 December 2017
Successful/Unsuccessful user logins in Oracle from FND_USER
https://dbissues.blogspot.com/2017/12/successfulunsuccessful-user-logins-in.html
select USER_NAME, DESCRIPTION,FAX,START_TIME, STATUS
from
(SELECT b.USER_NAME, b.DESCRIPTION, b.FAX, u.ATTEMPT_TIME START_TIME , 'UnSuccessful Login' STATUS
FROM FND_UNSUCCESSFUL_LOGINS U, FND_USER B
WHERE U.USER_ID = B.USER_ID
AND B.USER_NAME LIKE NVL(&USER_NAME, '%')
AND U.ATTEMPT_TIME
BETWEEN CASE
WHEN LENGTH(&START_TIME) in (14,15)
THEN TO_DATE(&START_TIME, 'DD-MON-RR HH24:MI')
WHEN LENGTH(&START_TIME) in (8,9)
THEN TO_DATE(&START_TIME, 'DD-MON-RR')
ELSE U.ATTEMPT_TIME
END
AND CASE
WHEN LENGTH(&END_TIME) in (14,15)
THEN TO_DATE(&END_TIME, 'DD-MON-RR HH24:MI')
WHEN LENGTH(&END_TIME) in (8,9)
THEN TO_DATE(&END_TIME, 'DD-MON-RR') + 1
ELSE U.ATTEMPT_TIME
END
union
select c.USER_NAME, c.DESCRIPTION, c.FAX, a.START_TIME START_TIME , 'Successful Login' STATUS
from fnd_logins a, fnd_user c
where a.user_id = c.user_id
and c.USER_NAME LIKE NVL(&USER_NAME, '%')
and a.LOGIN_TYPE = 'FORM'
AND a.START_TIME
BETWEEN CASE
WHEN LENGTH(&START_TIME) in (14,15)
THEN TO_DATE(&START_TIME, 'DD-MON-RR HH24:MI')
WHEN LENGTH(&START_TIME) in (8,9)
THEN TO_DATE(&START_TIME, 'DD-MON-RR')
ELSE a.START_TIME
END
AND CASE
WHEN LENGTH(&END_TIME) in (14,15)
THEN TO_DATE(&END_TIME, 'DD-MON-RR HH24:MI')
WHEN LENGTH(&END_TIME) in (8,9)
THEN TO_DATE(&END_TIME, 'DD-MON-RR') + 1
ELSE a.START_TIME
END
)
order by (START_TIME) desc
select USER_NAME, DESCRIPTION,FAX,START_TIME, STATUS
from
(SELECT b.USER_NAME, b.DESCRIPTION, b.FAX, u.ATTEMPT_TIME START_TIME , 'UnSuccessful Login' STATUS
FROM FND_UNSUCCESSFUL_LOGINS U, FND_USER B
WHERE U.USER_ID = B.USER_ID
AND B.USER_NAME LIKE NVL(&USER_NAME, '%')
AND U.ATTEMPT_TIME
BETWEEN CASE
WHEN LENGTH(&START_TIME) in (14,15)
THEN TO_DATE(&START_TIME, 'DD-MON-RR HH24:MI')
WHEN LENGTH(&START_TIME) in (8,9)
THEN TO_DATE(&START_TIME, 'DD-MON-RR')
ELSE U.ATTEMPT_TIME
END
AND CASE
WHEN LENGTH(&END_TIME) in (14,15)
THEN TO_DATE(&END_TIME, 'DD-MON-RR HH24:MI')
WHEN LENGTH(&END_TIME) in (8,9)
THEN TO_DATE(&END_TIME, 'DD-MON-RR') + 1
ELSE U.ATTEMPT_TIME
END
union
select c.USER_NAME, c.DESCRIPTION, c.FAX, a.START_TIME START_TIME , 'Successful Login' STATUS
from fnd_logins a, fnd_user c
where a.user_id = c.user_id
and c.USER_NAME LIKE NVL(&USER_NAME, '%')
and a.LOGIN_TYPE = 'FORM'
AND a.START_TIME
BETWEEN CASE
WHEN LENGTH(&START_TIME) in (14,15)
THEN TO_DATE(&START_TIME, 'DD-MON-RR HH24:MI')
WHEN LENGTH(&START_TIME) in (8,9)
THEN TO_DATE(&START_TIME, 'DD-MON-RR')
ELSE a.START_TIME
END
AND CASE
WHEN LENGTH(&END_TIME) in (14,15)
THEN TO_DATE(&END_TIME, 'DD-MON-RR HH24:MI')
WHEN LENGTH(&END_TIME) in (8,9)
THEN TO_DATE(&END_TIME, 'DD-MON-RR') + 1
ELSE a.START_TIME
END
)
order by (START_TIME) desc
Subscribe to:
Posts (Atom)