Posts

Showing posts with the label sql scripts

Redis user permissions (ACL) and python connection

-
 Recently, I was working on setting up a redis cluster along with separate users for readwrite and readonly. I setup the readonly user using the below privileges (ACLs):   user redisreadonly on >mySuperSecretPassword ~* resetchannels -@all +@read +ping +asking   Once this is setup, I wrote a simple program in python to connect to the redis cluster using the readonly credentials and print the number of keys.   #!/usr/bin/env python3 """ pip3 install redis """ from redis.cluster import RedisCluster, ClusterNode # ─────── Cluster connection ─────── startup_nodes = [ ClusterNode("redis_host_1", 6379), ClusterNode("redis_host_2", 6379), ClusterNode("redis_host_3", 6379) ] rc = RedisCluster(startup_nodes=startup_nodes,decode_responses=True,username='redisreadonly',password='mySuperSecretPassw...
Post a Comment
Read more

Find Request Set Name, Conc Manager given a request ID

-
Given a request id, how does one find out the program name and the manager running the request? Most of the Metalink notes and blogs provide the answer to this question but for one aspect. What is that? Read on to find out. What the SQL queries available on the web and Metalink lack is that they provide the concurrent program name for a single request given the request id. What about for a request set? Do they provide the answer? No, they don't. I have tried here to cover this aspect too. So, here goes. Note : This query works for all request ids, not only for request ids for request sets. select request_id,fcpv.CONCURRENT_PROGRAM_NAME "PROGRAM_NAME", nvl (fcr.description,fcpv.user_concurrent_program_name) "USER_PROGRAM_NAME", decode(fcpv.REQUEST_SET_FLAG,'N','SINGLE_REQUEST','Y','REQUEST_SET') "PROGRAM_TYPE", fcqv.CONCURRENT_QUEUE_NAME, fcqv.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_requests fcr, fnd_concurrent_p...
Post a Comment
Read more

Find Tablespace Usage Percent

-
I have written a sql query which would output the usage percent of all the tablespaces in a given database. Although lots of tablespace usage queries are available, most of them, if not all, utilise dba_free_space view as the tool of measurement. It is well known that this view is inadequate when datafiles have autoextend feature turned on. I have tried to address this inadequacy in my query. Caveat : This query is not optimised for performance. Hence, you might observe slow performance. set linesize 100 set pagesize 10000 set feedback off verify off col tablespace_name format a18 col num_files format 999 col total_space format 9999999.99 col free_space format 9999999.99 col used_percent format 999.99 select /*+ parallel(tbs) */ tbs.tablespace_name, count(tbs.file_id) num_files, round(sum(TOTAL_SPACE),2) TOTAL_SPACE_MB, sum(FREE_SPACE) FREE_SPACE_MB, round(((sum(tbs.USED_SPACE)/sum(tbs.TOTAL_SPACE)) * 100),2) USED_PERCENT from (select ddf.file_id file_id, ddf.file_name, ddf.tablespace...
Post a Comment
Read more

Useful Oracle Apps (EBS) Patch Queries

-
1. Query to find the time taken for a patch to be applied. col name for a15 col orig_patch_name for a15 col runtime for a30 SELECT NAME, ORIG_PATCH_NAME, to_char(START_DATE,'DD-MON-YYYY::HH24:MI:SS') START_DATE, to_char(END_DATE,'DD-MON-YYYY::HH24:MI:SS') END_DATE, TO_CHAR(FLOOR((end_date - start_date) * 24)) || ' hrs ' || TO_CHAR(FLOOR((end_date - start_date) * (24 * 60)) - (FLOOR((end_date - start_date) * 24) * 60)) || ' min ' || TO_CHAR(FLOOR((end_date - start_date) * (24 * 60 * 60)) - (FLOOR((end_date - start_date) * 24 * 60) * 60)) || ' sec ' RUNTIME, SUCCESS_FLAG FROM applsys.ad_patch_runs JOIN applsys.ad_patch_drivers USING (patch_driver_id) JOIN applsys.ad_appl_tops USING (appl_top_id) where orig_patch_name like '%&patch_number%' ORDER BY ad_patch_runs.creation_date DESC; 2. Note 364439.1 argues that checking ad_bugs for applied patches might not always be the good idea to check whether a given ...
Post a Comment
Read more

Popular posts from this blog

EXCEPTION_ACCESS_VIOLATION in Jinitiator and IE crashes

-
The Environment Apps Version : 11.5.10.2 Jinitiator Version : 1.3.1.26 Internet Explorer Version : 6 The Issue One of our end users was receiving a very strange error while using jinitiator. The Internet explorer window would crash (close by itself) after creating a file HS_ERR_PID .log on the desktop, where #### stands for the Process ID of the f60webmx session. This error is intermittent and not easily reproducible. Moreover, the user would have to redo his/her work and hence this issue was turning out to be very vexing indeed. The error log is as below: An unexpected exception has been detected in native code outside the VM. Unexpected Signal : EXCEPTION_ACCESS_VIOLATION occurred at PC=0x6D043D1AFunction name=Java_sun_java2d_loops_DefaultComponent_IntIsomorphicCopy Library=C:\Program Files\Oracle\JInitiator 1.3.1.26\bin\awt.dll Current Java thread: at sun.java2d.loops.DefaultComponent.IntIsomorphicCopy(Native Method) at sun.java2d.loops.IntRgbToIntRgb.OpaqueBlit(Unknown Source) at ...
Read more

Version of mod_security with EBS 11.5.10.2

-
Though it was a bit tough to find out the version of Mod_security that is shipped with E-Business Suite 11.5.10.2, I finally managed to do it, thanks to Metalink Fora! The version that comes with eBS 11.5.10.2 is 1.8.4 . Below is how you find out the version of mod_security shipped with your version of eBS. $ strings $IAS_ORACLE_HOME/Apache/Apache/libexec/mod_security.so | grep mod_security/
Read more

Read a file and extract specific lines using Native Ansible Modules

-
I never realised that reading a file on a remote host and extracting specific lines from that file would be such an arduous task. As part of one of my projects, I had this requirement and without using "cat" or other shell utilities, it was quite cumbersome to read and extract the information that I was looking for. Read on to find out how it can be done using Ansible native commands. Note that I have suggested only one out of a possible many methods. Feel free to explore yourself and update the comments here! REMOTE FILE CONTENTS $ cat /tmp/IP_and_hostnames.txt $ cat /tmp/IP_and_hostnames.txt 192.168.230.165:example-host-1.example.com 192.168.230.166:example-host-2.example.com 192.168.230.167:example-host-3.example.com 192.168.230.175:example-host-1-vip.example.com 192.168.230.176:example-host-2-vip.example.com 192.168.230.177:example-host-3-vip.example.com 192.168.230.185:example-host-1-priv.example.com 192.168.230.186:e...
Read more