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/

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)

)

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

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;

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

Code to get ASCII codes of any Database Character

https://dbissues.blogspot.com/2017/11/code-to-get-ascii-codes-of-any-database.html

select  

ascii(substr('PER MONTH',1,1)),

ascii(substr('PER MONTH',2,1)),

ascii(substr('PER MONTH',3,1)),

ascii(substr('PER MONTH',4,1)),

ascii(substr('PER MONTH',5,1)),

ascii(substr('PER MONTH',6,1)),

ascii(substr('PER MONTH',7,1)),

ascii(substr('PER MONTH',9,1))

from   dual

Code to get ASCII codes of character appearing in Vendor Name

select  ascii(substr(a.VENDOR_NAME,1,1)) from ap_suppliers a

where a.SEGMENT1 = 1

select  chr(83) from dual

Undo Related Queries

https://dbissues.blogspot.com/2017/11/undo-related-queries.html

List of All active Transactions

SELECT sid,
username,
t.used_ublk,
t.used_urec
FROM v$transaction t,
v$session s
WHERE t.addr = s.taddr;


To list all Datafile of UndoTablespace

SELECT tablespace_name,
file_name,
file_id,
autoextensible,
bytes / 1048576 Mbytes,
maxbytes / 1048576 maxMbytes
FROM dba_data_files
WHERE tablespace_name LIKE ‘%UNDO%’
OR tablespace_name LIKE ‘%RBS%’
ORDER BY tablespace_name,
file_name;


SELECT tablespace_name,
file_name,
file_id,
autoextensible,
bytes / 1048576 Mbytes,
maxbytes / 1048576 maxMbytes
FROM dba_data_files
WHERE tablespace_name LIKE ‘%UNDOTBS2%’
ORDER BY tablespace_name,
file_name;


col file_name FOR a40
set pagesize 100
SELECT tablespace_name,
file_name,
file_id,
autoextensible,
bytes / 1048576 Mbytes,
maxbytes / 1048576 maxMbytes
FROM dba_data_files
WHERE tablespace_name LIKE ‘%APPS_UNDOTS1%’
ORDER BY tablespace_name,
file_name;


SELECT file_name,
tablespace_name,
bytes / 1024 / 1024,
maxbytes / 1024 / 1024,
autoextensible
FROM dba_data_files
WHERE file_name LIKE ‘%undo%’
ORDER BY file_name;


To check when a table is last analysed
SELECT owner,
table_name,
tablespace_name,
status,
last_analyzed,
partitioned,
dependencies,
dropped
FROM dba_tables
WHERE table_name LIKE ‘MLC_PICK_LOCKS_DETAIL’;


SELECT owner,
table_name,
tablespace_name,
last_analyzed,
partitioned,
dependencies,
dropped
FROM dba_tables
WHERE table_name LIKE ‘APPS.XLA_AEL_GL_V’;


To list all Undo datafiles with status and size
show parameter undo
show parameter db_block_size
col tablespace_name FORM a20
col file_name FORM a60
set lines 120
SELECT tablespace_name,
file_name,
status,
bytes / 1024 / 1024
FROM dba_data_files
WHERE tablespace_name = (SELECT tablespace_name
FROM dba_tablespaces
WHERE CONTENTS = ‘UNDO’);

Total undo space

SELECT SUM(bytes) / 1024 / 1024 / 1024 GB
FROM dba_data_files
WHERE tablespace_name = ‘&Undo_TB_Name’;

Undo Tablespace

SELECT tablespace_name
FROM dba_tablespaces
WHERE tablespace_name LIKE ‘%UNDO%’;


To find MaxQueryLength from stats$undostat
SELECT Max(maxquerylen)
FROM stats$undostat;

SELECT Max(maxquerylen)
FROM v$undostat;

SELECT begin_date,
u.maxquerylen
FROM (SELECT To_char(begin_time, ‘DD-MON-YYYY:HH24-MI-SS’) begin_date,
maxquerylen
FROM v$undostat
ORDER BY maxquerylen DESC) u
WHERE ROWNUM < 11;

SELECT begin_date,
u.maxquerylen
FROM (SELECT maxquerylen,
To_char(begin_time, ‘DD-MON-YYYY:HH24-MI-SS’) begin_date
FROM v$undostat
ORDER BY maxquerylen DESC) u
WHERE ROWNUM < 26
ORDER BY begin_date ASC,
maxquerylen DESC;

SELECT begin_date,
u.maxquerylen
FROM (SELECT maxquerylen,
To_char(begin_time, ‘DD-MON-YYYY:HH24-MI-SS’) begin_date
FROM v$undostat
ORDER BY maxquerylen DESC) u
WHERE ROWNUM < 26
ORDER BY maxquerylen DESC;

SELECT SUM(u.maxquerylen) / 25 AvgUndoRetTime
FROM (SELECT maxquerylen
FROM v$undostat
ORDER BY maxquerylen DESC) u
WHERE ROWNUM < 26;

SELECT SUM(u.maxquerylen)
FROM (SELECT maxquerylen
FROM v$undostat
ORDER BY maxquerylen DESC) u
WHERE ROWNUM < 26;


DBA_UNDO_EXTENTS

set linesize 152
col tablespace_name FOR a20
col status FOR a10
SELECT tablespace_name,
status,
Count(extent_id) “Extent Count”,
SUM(blocks) “Total Blocks”,
SUM(bytes) / ( 1024 * 1024 * 1024 ) spaceInGB
FROM dba_undo_extents
GROUP BY tablespace_name,
status
ORDER BY tablespace_name;


Mapping Undo Segments to usernames

SELECT s.sid,
s.serial#,
username,
s.machine,
t.used_ublk,
t.used_urec,
( rs.rssize ) / 1024 / 1024 MB,
rn.name
FROM v$transaction t,
v$session s,
v$rollstat rs,
v$rollname rn
WHERE t.addr = s.taddr
AND rs.usn = rn.usn
AND rs.usn = t.xidusn
AND rs.xacts > 0;


Total Undo Statistics

ALTER SESSION SET nls_date_format=‘dd-mon-yy hh24:mi’;

set lines 120
set pages 2000
SELECT begin_time,
end_time,
undoblks,
txncount,
maxquerylen,
unxpstealcnt,
expstealcnt,
ssolderrcnt,
nospaceerrcnt
FROM v$undostat;


Total Undo Statistics since specified year

SELECT ‘TOTAL STATISTICS SINCE Jan 01, 2005 – STATSPACK’
FROM dual;

set head ON
set lines 152
column undotsn format 999 heading ‘Undo|TS#’;
column undob format 9, 999, 999, 999 heading ‘Undo|Blocks’;
column txcnt format 9, 999, 999, 999, 999 heading ‘Num|Trans’;
column maxq format 999, 999 heading ‘Max Qry|Len (s)’;
column maxc format 9, 999, 999 heading ‘Max Tx|Concurcy’;
column snol format 9, 999 heading ‘Snapshot|Too Old’;
column nosp format 9, 999 heading ‘Out of|Space’;
column blkst format a13 heading ‘uS/uR/uU/|eS/eR/eU’ wrap;
column unst format 9, 999 heading ‘Unexp|Stolen’ newline;
column unrl format 9, 999 heading ‘Unexp|Relesd’;
column unru format 9, 999 heading ‘Unexp|Reused’;
column exst format 9, 999 heading ‘Exp|Stolen’;
column exrl format 9, 999 heading ‘Exp|Releas’;
column exru format 9, 999 heading ‘Exp|Reused’;
SELECT undotsn,
SUM(undoblks) undob,
SUM(txncount) txcnt,
Max(maxquerylen) maxq,
Max(maxconcurrency) maxc,
SUM(ssolderrcnt) snol,
SUM(nospaceerrcnt) nosp,
SUM(unxpstealcnt)
||‘/’
|| SUM(unxpblkrelcnt)
||‘/’
|| SUM(unxpblkreucnt)
||‘/’
|| SUM(expstealcnt)
||‘/’
|| SUM(expblkrelcnt)
||‘/’
|| SUM(expblkreucnt) blkst
FROM stats$undostat
WHERE dbid IN (SELECT dbid
FROM v$database)
AND instance_number IN (SELECT instance_number
FROM v$instance)
AND end_time > To_date(‘01012005 00:00:00’, ‘DDMMYYYY HH24:MI:SS’)
AND begin_time < (SELECT SYSDATE
FROM dual)
GROUP BY undotsn;

SELECT ( SUM(undoblks) ) / SUM (( end_time – begin_time ) * 86400)
FROM v$undostat;

Active Users Session Query before taking Oracle Down

https://dbissues.blogspot.com/2017/11/active-user-sessions-query-before-going.html

SELECT DISTINCT icx.session_id,
                  icx.user_id,
                  fu.user_name,
                  fu.description
    FROM icx_sessions icx, fnd_user fu
   WHERE     disabled_flag != 'Y'
         AND icx.pseudo_flag = 'N'
         AND (last_connect +
              DECODE (fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'),
                      NULL, limit_time,
                      0   , limit_time,
                      fnd_profile.VALUE ('ICX_SESSION_TIMEOUT')/60) / 24) > SYSDATE
         AND icx.counter < limit_connects
         AND icx.user_id = fu.user_id;