Query to find runtime of a concurrent program

The following query finds total run-time in minutes for a concurrent program.

SELECT /*+ rule */
       rq.parent_request_id                   "Parent Req. ID",
       rq.request_id                          "Req. ID",
       tl.user_concurrent_program_name        "Program Name",
       rq.actual_start_date                   "Start Date",
       rq.actual_completion_date              "Completion Date",
       ROUND((rq.actual_completion_date -
           rq.actual_start_date) * 1440, 2)   "Runtime (in Minutes)"      
  FROM applsys.fnd_concurrent_programs_tl  tl,
       applsys.fnd_concurrent_requests     rq
 WHERE tl.application_id        = rq.program_application_id
   AND tl.concurrent_program_id = rq.concurrent_program_id
   AND tl.LANGUAGE              = USERENV('LANG')
   AND rq.actual_start_date IS NOT NULL
   AND rq.actual_completion_date IS NOT NULL
   AND tl.user_concurrent_program_name = 'PRC: Generate Draft Revenue for a Range of Projects'  -- <change it>
   -- AND TRUNC(rq.actual_start_date) = '&start_date'  -- uncomment this for a specific date
 ORDER BY rq.request_id DESC;

Kill all inactive sessions in a Database.

If you want to kill all inactive sessions in the database, it is hard to find and kill one by one.  Use below statement to create kill statements at a time and run them to kill all inactive session.

select 'alter system kill session '||sid||','||serial#|| ';' from v$session where status='INACTIVE';

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Issue Details:

When I'm trying to update a row in a table got the below error.

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Solution:

Use below query to find who is accessing the table.

SELECT a.object, a.type, a.sid,s.serial#, s.username,s.program, s.logon_time, s.osuser
FROM v$access a, v$session s
WHERE a.sid = s.sid
AND a.owner = '&OWNER'
AND a.object = '&Object_Name';

Check the session details and contact user to close his session or wait for the job complete.  If user requested to cancel his session, kill the session using below command.

SQL> alter system kill session '&SID,&Serial';

Then we tried our update statement, it works fine.

ORA-28007: the password cannot be reused

Issue:

SQL> alter user user1 identified by password1;
alter user user1 identified by password1
*
ERROR at line 1:
ORA-28007: the password cannot be reused


Solution:

1. Find the profile name:

SQL> SQL> select username,profile,account_status from dba_users where username='USER1';

USERNAME   PROFILE         ACCOUNT_STATUS
---------- --------------- ---------------
USER1     ADMINISTRATOR   OPEN

2. Find the profile values:

SQL> select * from dba_profiles where profile='ADMINISTRATOR';

PROFILE              RESOURCE_NAME                    RESOURCE LIMIT                COM
-------------------- -------------------------------- -------- -------------------- ---
ADMINISTRATOR        COMPOSITE_LIMIT                  KERNEL   DEFAULT              NO
ADMINISTRATOR        SESSIONS_PER_USER                KERNEL   DEFAULT              NO
ADMINISTRATOR        CPU_PER_SESSION                  KERNEL   DEFAULT              NO
ADMINISTRATOR        CPU_PER_CALL                     KERNEL   DEFAULT              NO
ADMINISTRATOR        LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT              NO
ADMINISTRATOR        LOGICAL_READS_PER_CALL           KERNEL   DEFAULT              NO
ADMINISTRATOR        IDLE_TIME                        KERNEL   DEFAULT              NO
ADMINISTRATOR        CONNECT_TIME                     KERNEL   DEFAULT              NO
ADMINISTRATOR        PRIVATE_SGA                      KERNEL   DEFAULT              NO
ADMINISTRATOR        FAILED_LOGIN_ATTEMPTS            PASSWORD 5                    NO
ADMINISTRATOR        PASSWORD_LIFE_TIME               PASSWORD 90                   NO
ADMINISTRATOR        PASSWORD_REUSE_TIME              PASSWORD 180                  NO
ADMINISTRATOR        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED            NO
ADMINISTRATOR        PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_FUNCTION_11G  NO
ADMINISTRATOR        PASSWORD_LOCK_TIME               PASSWORD 7                    NO
ADMINISTRATOR        PASSWORD_GRACE_TIME              PASSWORD 14                   NO

16 rows selected.

SQL>

3. Alter the profile values:

SQL> alter profile ADMINISTRATOR limit password_verify_function NULL;

Profile altered.

SQL> alter profile ADMINISTRATOR limit PASSWORD_REUSE_TIME UNLIMITED;

Profile altered.

4. Change the password:

SQL> alter user user1 identified by password1;

User altered.

SQL>


5. After changing the password restore the default/old settings:

SQL> alter profile ADMINISTRATOR  limit password_verify_function VERIFY_FUNCTION_11G;

Profile altered.

SQL> alter profile ADMINISTRATOR  limit PASSWORD_REUSE_TIME 180;

Profile altered.

SQL> 

ORA-28003: password verification for the specified password failed ORA-20009: Password must be 8 characters in length

Issue:

SQL> alter user USER1 identified by password1;
alter user USER1 identified by password1
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20009: Password must be 8 characters in length

Solution:

SQL> select profile from dba_users where username='USER1';

PROFILE
------------------------------
APPLICATION_PASSWORD_EXP

SQL> select RESOURCE_NAME,RESOURCE_TYPE,LIMIT FROM DBA_PROFILES WHERE PROFILE='APPLICATION_PASSWORD_EXP' AND RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION';

RESOURCE_NAME                    RESOURCE LIMIT
-------------------------------- -------- ----------------------------------------
PASSWORD_VERIFY_FUNCTION         PASSWORD PROD_PWD_LIMIT

SQL> ALTER PROFILE APPLICATION_PASSWORD_EXP LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Profile altered.

SQL> alter user USER1 identified by password1;

User altered.

SQL> ALTER PROFILE APPLICATION_PASSWORD_EXP LIMIT PASSWORD_VERIFY_FUNCTION PROD_PWD_LIMIT;

Profile altered.