to get trace stats for a toad SQL

to get trace stats for a toad SQL

alter session set statistics_level = ALL;    

SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

Is there a lock on any object/table ??

select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from gv$locked_object) a,
(select object_id, owner, object_name,object_type from sys.dba_objects) b
where a.object_id=b.object_id
and b.object_name = ''

Get the time remaining for a session

SQL to find time remaining for a session ( to be run in the same NODE where session is running)

select b.username,a.sid,b.opname,b.target,round(b.SOFAR*100 / b.TOTALWORK,0) || '%' as "%DONE",
b.TIME_REMAINING,to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') START_TIME
from V$SESSION_LONGOPS b,V$SESSION a
where a.sid=b.sid and a.sid= <>

with global tables

select b.username,a.sid,b.opname,b.target,round(b.SOFAR*100 / b.TOTALWORK,0) || '%' as "%DONE",
b.TIME_REMAINING,to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') START_TIME
from gV$SESSION_LONGOPS b,gV$SESSION a
where a.sid=b.sid and a.sid= <> 

send mail via DB/UNIX

UNIX

more <filename with path> | mailx -s "<subject>" sanegi


Oracle Database

 begin

   EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';

sys.UTL_MAIL.SEND ('sanegi@cisco.com', 'satyapsnegi@gmail.com', 'sanegi@cisco.com',
               'satyapsnegi@yahoo.co.in', 'text/plain','ha ha ha');
             
               end;
               /

We can SMTP utility also

CREATE OR REPLACE PROCEDURE  TEST_send_mail (msg_to         VARCHAR2,
                                       msg_subject    VARCHAR2,
                                       msg_text       VARCHAR2)
IS
   c          UTL_SMTP.connection;
   rc         INTEGER;
   msg_from   VARCHAR2 (50) := '<from_mail_alias>';
   mailhost   VARCHAR2 (30) := '<server_alias>';             -- local database host
BEGIN
   c := UTL_SMTP.open_connection (mailhost, 25);            -- SMTP on port 25
   UTL_SMTP.helo (c, mailhost);
   UTL_SMTP.mail (c, msg_from);
   UTL_SMTP.rcpt (c, msg_to);

   UTL_SMTP.data (
      c,
         'From: Oracle Database'
      || UTL_TCP.crlf
      || 'To: '
      || msg_to
      || UTL_TCP.crlf
      || 'Subject: '
      || msg_subject
      || UTL_TCP.crlf
      || msg_text);
   UTL_SMTP.quit (c);
EXCEPTION
   WHEN UTL_SMTP.invalid_operation
   THEN
      DBMS_OUTPUT.put_line (' Invalid Operation in Mail attempt
using UTL_SMTP.');
   WHEN UTL_SMTP.transient_error
   THEN
      DBMS_OUTPUT.put_line (' Temporary e-mail issue - try again');
   WHEN UTL_SMTP.permanent_error
   THEN
      DBMS_OUTPUT.put_line (' Permanent Error Encountered.');
END;
/

responsibility - concurrent program - request set

To identify under which responsibility request set is available.

SELECT 'REQUEST SET' TYPE
,fr.responsibility_name
,frg.request_group_name
,frs.user_request_set_name
FROM
applsys.fnd_request_group_units frgus
,applsys.fnd_request_sets_vl frs
,applsys.fnd_request_groups frg
,applsys.fnd_responsibility_vl fr
WHERE 1=1
AND fr.request_group_id = frg.request_group_id
AND frg.request_group_id = frgus.request_group_id
AND frgus.request_unit_type = 'S'
AND frgus.request_unit_id = frs.request_set_id
AND frs.user_request_set_name LIKE &user_request_set_name

To identify under which responsibility the concurrent program is available.

SELECT 'CONC PROG' TYPE
,fr.responsibility_name
,frg.request_group_name
,fcp.user_concurrent_program_name
FROM
applsys.fnd_request_group_units frgup
,applsys.fnd_concurrent_programs_vl fcp
,applsys.fnd_request_groups frg
,applsys.fnd_responsibility_vl fr
WHERE 1=1
AND fr.request_group_id = frg.request_group_id
AND frg.request_group_id = frgup.request_group_id
AND frgup.request_unit_type = 'P'
AND frgup.request_unit_id = fcp.concurrent_program_id
AND fcp.user_concurrent_program_name LIKE &user_concurrent_program_name

to get size of tablespace

to get size of tablespace

SYS.DBA_FREE_SPACE

select  tablespace_name,sum(bytes/1024/1024/1024) "in GB"
  from dba_free_space
  group by tablespace_name
  order by  1 desc


to get size of table

 select segment_name,sum(bytes/1024/1024/1024)
     from SYS.dba_segments
     where segment_name =:’TABLE_NAME’
      and segment_type='TABLE'
      group by segment_name


Table : sys.dba_data_files
--> to get the file size

to get table space size

SELECT   a.TABLESPACE_NAME,
           a.BYTES/1024/1024/1024 GB_used,
           b.BYTES/1024/1024/1024 GB_free,
           b.largest,
           ROUND ( ( (a.BYTES - b.BYTES) / a.BYTES) * 100, 2) percent_used
    FROM   (  SELECT   TABLESPACE_NAME, SUM (BYTES) BYTES
                FROM   dba_data_files
            GROUP BY   TABLESPACE_NAME) a,
           (  SELECT   TABLESPACE_NAME, SUM (BYTES) BYTES, MAX (BYTES) largest
                FROM   dba_free_space
            GROUP BY   TABLESPACE_NAME) b
   WHERE   a.TABLESPACE_NAME = b.TABLESPACE_NAME
ORDER BY   ( (a.BYTES - b.BYTES) / a.BYTES) DESC

to get GL Account definition details

To get the GL Account definition/meaning

select ffv.enabled_flag, ffv.start_date_active,ffv.end_date_active,ffvt.*
from applsys.fnd_flex_values_tl ffvt, applsys.fnd_flex_values ffv
where 1=1
and ffvt.language = 'US'
and ffvt.flex_value_id = ffv.flex_value_id
and ffv.flex_value = '27010'
and ffv.FLEX_VALUE_SET_ID in (select FLEX_VALUE_SET_ID from applsys.fnd_flex_value_sets
where 1=1
and FLEX_VALUE_SET_NAME = 'GL:Account')


To get all the child accounts related to a parent account


select ffvh.flex_value_set_id,ffvh.child_flex_value_low,ffvh.child_flex_value_high
from applsys.fnd_flex_value_hierarchies ffvh, applsys.fnd_flex_value_sets ffvs
where ffvs.flex_value_set_name = 'GL:Account'
and ffvh.flex_value_set_id = ffvs.flex_value_set_id
and ffvh.parent_flex_value = '40010'


To get Account details

select a.*, c.FLEX_VALUE_SET_NAME
from applsys.fnd_flex_values_tl a, applsys.fnd_flex_values b, applsys.fnd_flex_value_sets c
where 1=1
and a.flex_value_id =  b.flex_value_id
and b.flex_value = '40015'
and b.FLEX_VALUE_SET_ID = c.FLEX_VALUE_SET_ID
and a.language = 'US'

Assign responsibility via backend in Oracle

Query to get the details w.r.t. the responsibility

SELECT FAV.APPLICATION_SHORT_NAME, FAV.APPLICATION_NAME,FRV.RESPONSIBILITY_KEY, FRV.RESPONSIBILITY_NAME
FROM FND_APPLICATION_VL FAV, FND_RESPONSIBILITY_VL FRV
WHERE FRV.APPLICATION_ID=FAV.APPLICATION_ID
and FRV.RESPONSIBILITY_NAME = 'System Administrator'
ORDER BY FRV.RESPONSIBILITY_NAME ;




Run the below block in APPS schema to assign the responsibility

BEGIN
        fnd_user_pkg.addresp ('<user_name>', 'SYSADMIN' , 'SYSTEM_ADMINISTRATOR', 'STANDARD',
        'Add Responsibility to USER using pl/sql', SYSDATE, SYSDATE + 1000);
        COMMIT;
        DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
END;

to remove the assigned responsibility

BEGIN
        fnd_user_pkg.addresp ('<user_name>', 'SYSADMIN' , 'SYSTEM_ADMINISTRATOR', 'STANDARD');
        COMMIT;
        DBMS_OUTPUT.put_line ('Responsibility Removed Successfully');
END;