Tablespace Growth Query

https://dbissues.blogspot.com/2017/09/tablespace-growth-query.html

  SELECT  TO_CHAR(SYSDATE,'MON-YYYY') period , a.tablespace_name,
                  ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,
                            c.BYTES / 1024 / 1024 space_allocated,
                            ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,
                            ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free,
                            c.DATAFILES
                          FROM dba_tablespaces a,
                               (    SELECT   tablespace_name,
                                          SUM (BYTES) BYTES
                                   FROM   dba_free_space
                               GROUP BY   tablespace_name
                               ) b,
                              (    SELECT   COUNT (1) DATAFILES,
                                          SUM (BYTES) BYTES,
                                          tablespace_name
                                   FROM   dba_data_files
                               GROUP BY   tablespace_name
                            ) c
                          WHERE b.tablespace_name(+) = a.tablespace_name
                            AND c.tablespace_name(+) = a.tablespace_name

Delete Interface/Integrator/Component - Web ADI

--Delete an Interface/Integrator Details

SELECT biv.application_id

       ,biv.integrator_code

       ,biv.user_name

       ,bib.interface_code

   FROM bne_integrators_vl biv

       ,bne_interfaces_b   bib

  WHERE upper(user_name) like '%XXAK%'

    AND bib.integrator_code = biv.integrator_code ;

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

--Delete an Interface

DECLARE

   vn_number   NUMBER;

BEGIN

   vn_number := bne_integrator_utils.delete_interface

                (p_application_id => 20003,

        p_interface_code  => 'XXAKTESTUPADI_XINTG_INTF1');

                

   DBMS_OUTPUT.put_line ('ADI Interface Deleted '||vn_number);

   COMMIT;

   --

EXCEPTION  

   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line('Error: '||sqlerrm);

      ROLLBACK;

END;

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

--Delete an Intergrator

DECLARE

   vn_number number:=0;

BEGIN

   vn_number:= bne_integrator_utils.delete_integrator

               (p_application_id => 20003,

                p_integrator_code => 'XXAKTESTUPADI_XINTG');

               

   dbms_output.put_line(' ADI Deleted : '||vn_number);

   COMMIT;

   --

EXCEPTION  

   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line('Error: '||sqlerrm);

      ROLLBACK;

END;

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

--Delete a Component

DECLARE

   vn_number number:=0;

BEGIN

   vn_number:=BNE_INTEGRATOR_UTILS.DELETE_COMPONENT(p_application_id => 200,

                p_COMPONENT_CODE => 'HUBINV_XINTG_INTF1_C9_COMP');

              

   dbms_output.put_line(' ADI Deleted : '||vn_number);

   COMMIT;

   --

EXCEPTION 

   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line('Error: '||sqlerrm);

      ROLLBACK;

END;

How To Clear BNE Cache

https://dbissues.blogspot.com/2017/08/how-to-clear-bne-cache.html

To Clear BNE cache:

Using System Administrator responsibility:

Login to the application as a user with System Administrator Responsibility.

Select the System Administrator Responsibility.
Bring up the AdminServlet by changing the URL in the browser where you are logged into the applications keeping the appropriate hostname.domain and port number.

Release 11i:
http://hostname.domain:portnumber/oa_servlets/oracle.apps.bne.framework.BneAdminServlet
On the new web page that loads, scroll down and click the "clear-cache" link

Release 12:
http://hostname.domain:portnumber/OA_HTML/BneAdminServlet
On the new web page that loads, scroll down to the Cache Name section.
Clear the cache for the following by clicking the "(clear)" link:

Cache Name
============
Default Cache
Generic SQL Statements
Web ADI Repository Objects
Web ADI Parameter Lists
Web ADI Parameter Definitions


The BNE cache is now cleared.

PDF Output is being sent in .out format as mail attachment through Oracle

https://dbissues.blogspot.com/2017/08/pdf-output-is-being-sent-in-out-format.html

Attachment is being sent in .out format for PDF file

Currently, there is no such function in EBS to generate report output file extension in the mail as anything other than .out.

Workaround:

The delivery option is coming from xml publisher API , and if the report is generated by xml publisher, it will generate two output file, one is the output of the request, an .out file and the other one is generated by bi publisher, which may be a .pdf file or an .xls file. The API then attach  the correct file in the mail, but when the report is generated by oracle report, though it is a pdf file, it is an .out file in the file system and API attach it in the mail.

Simple is, If the report is generated by xml publisher , the pdf format output will have a output file as .pdf. In the case, the delivery API would send the pdf output in the email as the attachment with .pdf . So you may need to generate the report output from Oracle*Report by xml publisher.

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

Register Report in XML Publisher in .rtf format and make Default Output view & Preview Format to PDF.

In concurrent Program, select Output Format as XML and run the Report. Email will be sent in .pdf format

How to make a .tar file on Linux

https://dbissues.blogspot.com/2017/07/how-to-make-tar-file-on-linux.html

tar -czvf tt_new.tar tt

PLSQL Queries for User Sessions

http://dbissues.blogspot.com/2017/05/plsql-queries-for-user-sessions.html

1: Use this SQL statement  to check the query of current/Active users

=============================================================
select fnd.user_name, icx.responsibility_application_id, icx.responsibility_id, frt.responsibility_name,
icx.session_id, icx.first_connect,
icx.last_connect,
DECODE ((icx.disabled_flag),'N', 'ACTIVE', 'Y', 'INACTIVE') status
from
fnd_user fnd, icx_sessions icx, fnd_responsibility_tl frt
where
fnd.user_id = icx.user_id
and icx.responsibility_id = frt.responsibility_id
and icx.disabled_flag <> 'Y'
and trunc(icx.last_connect) = trunc(sysdate)
order by icx.last_connect;

2:  Use this SQL statement to count number of concurrent_users connected to Oracle apps:

===========================================================================

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)

3:  Use this SQL statement to count number of users connected to Oracle Apps in the past 1 hour.

=============================================================================

select count(distinct user_id) “users” from icx_sessions where last_connect > sysdate – 1/24 and user_id != ‘-1’;

4:  Use this SQL statement to get number of users connected to Oracle Apps in the past 1 day.

==========================================================================

select count(distinct user_id) “users” from icx_sessions where last_connect > sysdate – 1 and user_id != ‘-1’;

5:  Use this SQL statement to get number of users connected to Oracle Apps in the last 15 minutes.

============================================================================

select limit_time, limit_connects, to_char(last_connect, ‘DD-MON-RR HH:MI:SS’) “Last Connection time”, user_id, disabled_flag from icx_sessions where last_connect > sysdate – 1/96;

6: How do we know how many users are connected to Oracle Applications

==========================================================

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;

7: Count Number of concurrent_users in Oracle apps?

============================================

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);

8:How to find out which users are logged on to an Apps instance :-
====================================================

FND_USER table stores the details of all the end users. If we give this query:

select user_name,to_char(last_logon_date,'DD-MON-YYYY HH24:MI:SS')
from apps.fnd_user
where to_char(last_logon_date,'DD-MON-YYYY')=to_char(sysdate,'DD-MON-YYYY');

9: You can check the number of user session for the application using ICX_SESSIONS table. Use below query for checking the number of user sessions.

=================================================================================
select ((select sysdate from dual)),(select  ‘ user sessions : ‘ || count( distinct session_id) How_many_user_sessions
from icx_sessions icx
where disabled_flag != ‘Y’
and 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 counter < limit_connects) from dual

How to Check Database Character Set (PLSQL Query)

http://dbissues.blogspot.com/2016/11/how-to-check-character-set-query-in.html

select * from v$nls_parameters where parameter like '%CHARACTERSET%';

select * from NLS_DATABASE_PARAMETERS ;