Find locks on object

Couple of different queries listed to find locks on objects / tables ...etc.

1. How to find any session is locked on a specific object:

Use case, one of the user trying to update a particular object, which is hanging to update.  Used below query to see who is accessing that object and cleared it.

Query:

select a.sid, s.serial#, s.status from v$access a, v$session s where a.owner='&Schema_Name' AND a.object='&Object_Name' and a.sid=s.sid;

Example:

select a.sid, s.serial#, s.status from v$access a, v$session s where a.owner='APPS' AND a.object='XXC_FS_RWP_BEE_PKG' and a.sid=s.sid;

2. Find Locks on Table Level:

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

Query to find Concurrent Requests submitted by a particular user

Query to find Concurrent Requests submitted by a particular User:

SELECT
    request_id,
    request_date,
    user_concurrent_program_name,
    responsibility_name,   
    argument_text,   
    phase_code,
    status_code,
    logfile_name,
    outfile_name,
    output_file_type
FROM
    fnd_concurrent_requests fcr,
    fnd_concurrent_programs_tl fcp,
    fnd_responsibility_tl fr,
    fnd_user fu
WHERE
    fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
    and fcr.responsibility_id = fr.responsibility_id
    and fcr.requested_by = fu.user_id
    and user_name = upper('&USERNAME')
ORDER BY REQUEST_DATE DESC;

Different Levels of SUDO Access.

As a root user, visudo and insert the following based on your requirement.

###############################################

# APPLMGRSU users can su to applmgr

User_Alias  APPLMGRSU = user1

APPLMGRSU ALL = NOPASSWD: /bin/su - applmgr

###############################################

# ORACLESU users can su to oracle

User_Alias  ORACLESU = user1

ORACLESU ALL = NOPASSWD: /bin/su - oracle

###############################################

# allow the user to issue commands as root without a password

user1 ALL=(root) NOPASSWD: ALL

###############################################

#Append the following entry to run ALL command without a password for a user named user1:

user1 ALL=(ALL) NOPASSWD:ALL

SUDO access to Developers to bounce some of services.

One of the scenario that I come across, some of developers wanted to bounce apache / opmn services after their development activity to see their changes got effected or not.  It is hard for DBAs every time to do such things.  To overcome that, we have granted sudoers access to some of key developers, so that they can bounce on their own on non-prod environments.  

As root, on application server:

visudo

Insert this into file:

###############################################

# DEVORACLE users can run the following commands as applmgr

User_Alias  DEVORACLE = user1, user2, user3

DEVORACLE ALL = (applmgr) NOPASSWD: /u01/scripts/stopopmn, /u01/scripts/startopmn, /u01/scripts/startapache, /u01/scripts/stopapache

As applmgr, on application server:

cp $ADMIN_SCRIPTS_HOME/adopmnctl.sh /u01/scripts/.

cp $ADMIN_SCRIPTS_HOME/adapcctl.sh /u01/scripts/.

Make easy commands to developers:

$ cd /u01/scripts

$ echo "/u01/scripts/adopmnctl.sh stopall" > stopopmn

$ echo "/u01/scripts/adopmnctl.sh startall" > startopmn

$ echo "/u01/scripts/adapcctl.sh stop" > stopapache

$ echo "/u01/scripts/adapcctl.sh start" > stopapache

Here are the files you see in /u01/scripts after all done:

adapcctl.sh

adopmnctl.sh

startapache

stopapache

startopmn

stopopmn

Change permissions to all files under /u01/scripts:

$ chmod 700 *

These are the commands that only the developers listed by name in the sudoers file (above) can run:

sudo -u applmgr /u01/scripts/stopopmn

sudo -u applmgr /u01/scripts/startopmn

sudo -u applmgr /u01/scripts/stopapache

sudo -u applmgr /u01/scripts/startapache

How to find database growth on a Monthly Basis

SQL query to find database growth month wise and year.  

select to_char(CREATION_TIME,'RRRR') Year, to_char(CREATION_TIME,'MM') Month, round(sum(bytes)/1024/1024/1024) GB

from   v$datafile

group by  to_char(CREATION_TIME,'RRRR'),   to_char(CREATION_TIME,'MM')

order by   1, 2;

Sample Output:

YEAR MO         GB

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

2019 02         96

2019 03         65

2019 04        239

2019 05         32

2019 06         64

2019 07         95

2019 09        315

2019 10         31

2019 11        375

2020 04        313

10 rows selected.