set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
version_count, executions, hash_value,address
FROM V$SQLAREA
WHERE version_count > 20
ORDER BY version_count DESC)
WHERE rownum <=10
;
Archive for the ‘Uncategorized’ Category
Query for Top 10 by Version Count:
Posted by Sriram Sanka on September 13, 2022
Posted in Uncategorized | Leave a Comment »
Query for Top 10 by Sharable Memory:
Posted by Sriram Sanka on September 13, 2022
set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
sharable_mem, executions, hash_value,address
FROM V$SQLAREA
WHERE sharable_mem > 1048576
ORDER BY sharable_mem DESC)
WHERE rownum <=10
;
Posted in Uncategorized | Leave a Comment »
Query for the Top 10 by Parse Calls:
Posted by Sriram Sanka on September 13, 2022
set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
parse_calls, executions, hash_value,address
FROM V$SQLAREA
WHERE parse_calls > 1000
ORDER BY parse_calls DESC)
WHERE rownum <=10
;
Posted in Uncategorized | Leave a Comment »
Date Difference(datediff) Using Oracle SQL – By Thomas Kyte(ASKTOM)
Posted by Sriram Sanka on September 11, 2022
Its an Old Good Script from my Script Collection to find the number of minutes/Seconds/Hours between two dates- Original Author – TomKyte.
create or replace function datediff( p_what in varchar2,
p_d1 in date,
p_d2 in date ) return number
as
l_result number;
begin
select (p_d2-p_d1) *
decode( upper(p_what),
'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL )
into l_result from dual;
return l_result;
end;
/
WITH data
AS (SELECT To_date(SYSDATE - 2, 'dd-mon-yyyy hh24:mi:ss') d1,
To_date(SYSDATE, 'dd-mon-yyyy hh24:mi:ss') d2
FROM dual)
SELECT Datediff('ss', d1, d2) AS seconds,
Datediff('mi', d1, d2) AS minutes,
Datediff('hh', d1, d2) hours
FROM data
SQL> WITH data
2 AS (SELECT To_date(SYSDATE - 2, 'dd-mon-yyyy hh24:mi:ss') d1,
3 To_date(SYSDATE, 'dd-mon-yyyy hh24:mi:ss') d2
4 FROM dual)
5 SELECT Datediff('ss', d1, d2) AS seconds,
6 Datediff('mi', d1, d2) AS minutes,
7 Datediff('hh', d1, d2) hours
8 FROM data ;
SECONDS MINUTES HOURS
---------- ---------- ----------
172800 2880 48
SQL>
Posted in Functions In oracle, Uncategorized | Tagged: datediff, function, oracle, sql | Leave a Comment »
[FATAL] [DBT-06103] The port (5,500) is already in use.
Posted by Sriram Sanka on May 12, 2022
If you get this Port already in use Error While Installing Oracle in Linux., Make sure to have Proper Host Name Configured and Entry in /etc/hosts file.
Posted in Uncategorized | Leave a Comment »
Few More Additions to the List
Posted by Sriram Sanka on December 27, 2021
Posted in Uncategorized | Leave a Comment »
AWS- EC2 : Error while changing the IP Address in EC2 Security Groups
Posted by Sriram Sanka on June 12, 2021
Error while changing the IP Address in EC2 Security Groups. This happened ,as the user does not have access to change the IP in SG Group 2,after adding the below rule (Underlined Part) resolved this error.
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"ec2:RevokeSecurityGroupIngress",
"ec2:AuthorizeSecurityGroupEgress",
"ec2:AuthorizeSecurityGroupIngress",
"ec2:RevokeSecurityGroupEgress",
"ec2:DeleteSecurityGroup"
],
"Resource": [
"arn:aws:ec2:::security-group/sg-Group1",
"arn:aws:ec2:::security-group/sg-Group2"
]
},
{
"Sid": "VisualEditor1",
"Effect": "Allow",
"Action": [
"ec2:DescribeSecurityGroupReferences",
"ec2:DescribeVpcs",
"ec2:DescribeSecurityGroups",
"ec2:DescribeStaleSecurityGroups"
],
"Resource": "*"
}
]
}
Posted in Uncategorized | Tagged: AWS, EC2, Security Groups | Leave a Comment »
Script To Generate Alter Statements to Kill Locked Sessions in RDS
Posted by Sriram Sanka on May 28, 2021
Script to generate Kill Locked Sessions in Oracle :
In case of RDS ,
SELECT 'exec rdsadmin.rdsadmin_util.kill('||s.sid ||','|| s.serial# ||' );'
FROM v$session s ,
v$process p ,
v$lock l ,
dba_objects o
WHERE
s.paddr = p.addr
AND l.sid = s.sid
AND l.id1 = o.object_id
AND s.username = 'XXAPPS' --- Change as needed.
AND object_name like ('XXSC%'); --- Change as needed.
Posted in Uncategorized | Leave a Comment »
FRM-10102: Cannot attach PL/SQL library
Posted by Sriram Sanka on May 27, 2021
When you try to Open R12 Form template fmb files in Oracle forms, you may receive FRM-10102: Cannot attach PL/SQL library APPCORE .
Unable to open Template.fmb. Getting following error
FRM-10102: Cannot attach PL/SQL library APPCORE. This library attachment will be lost if the module is saved.
FRM-10102: Cannot attach PL/SQL library APPDAYPK. This library attachment will be lost if the module is saved.
FRM-10102: Cannot attach PL/SQL library GLOBE. This library attachment will be lost if the module is saved.
FRM-10102: Cannot attach PL/SQL library FNDSQF. This library attachment will be lost if the module is saved.
FRM-10102: Cannot attach PL/SQL library JA. This library attachment will be lost if the module is saved.
FRM-10102: Cannot attach PL/SQL library JE. This library attachment will be lost if the module is saved.
FRM-10102: Cannot attach PL/SQL library JL. This library attachment will be lost if the module is saved.
FRM-10102: Cannot attach PL/SQL library VERT. This library attachment will be lost if the module is saved.
FRM-10102: Cannot attach PL/SQL library GHR. This library attachment will be lost if the module is saved.
FRM-10102: Cannot attach PL/SQL library PQH_GEN. This library attachment will be lost if the module is saved.
FRM-10102: Cannot attach PL/SQL library PSAC. This library attachment will be lost if the module is saved.
Solution : Set the Path for FORMS_PATH with the location of all core libraries(pll files).
Posted in Uncategorized | Leave a Comment »
Apache – No space left on device / Failed to create proxy Mutex
Posted by Sriram Sanka on August 10, 2018
A Big Thanks to “https://help.directadmin.com/item.php?id=110”
Restarting Apache in one of the Servers failed with “No Space left on the Device ” Error and we have enough space in all available partitions.
This is something to deal with Server Kernel semaphores.
“On Linux, A semaphore is a System V IPC object that is used to control utilization of a particular process. Refer https://www.tldp.org/LDP/tlk/ipc/ipc.html
Semaphores are a shareable resource that take on a non-negative integer value. They are manipulated by the P (wait) and V (signal) functions, which decrement and increment the semaphore, respectively. When a process needs a resource, a “wait” is issued and the semaphore is decremented. When the semaphore contains a value of zero, the resources are not available and the calling process spins or blocks (as appropriate) until resources are available. When a process releases a resource controlled by a semaphore, it increments the semaphore and the waiting processes are notified.”
The system does n`t have actual resource available to serve the request, so either we need to configure the Kernel Semaphores or clear the Old entries to get it back.
kernel.sem = SEMMSL SEMMNS SEMOPM SEMMNI
SEMMSL maximum number of semaphores per array
SEMMNS maximum semaphores system-wide
SEMOPM maximum operations per semop call
SEMMNI maximum arrays
[root@r12 ~]# cat /etc/sysctl.conf |grep kernel.sem
kernel.sem = 256 32000 100 142
[root@r12 ~]#[root@r12 ~]# cat /proc/sys/kernel/sem
256 32000 100 142
[root@r12 ~]#
[root@r12 ~]# ipcs -l | awk ‘FNR>=7 && FNR<=15’
—— Semaphore Limits ——–
max number of arrays = 142
max semaphores per array = 256
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767—— Messages: Limits ——–
[root@r12 ~]#[root@r12 ~]# ipcs -ls
—— Semaphore Limits ——–
max number of arrays = 142
max semaphores per array = 256
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767
To Clear , we can execute the following
ipcs | grep apache | awk ‘{print $2}’ > sem.txt
for i in `cat sem.txt`; do { ipcrm -s $i; }; done;
| For Automating the removal of apache semaphores with ipcs/ipcrm, Please refer https://help.directadmin.com/item.php?id=572 |
Posted in Uncategorized | Tagged: Apache, no space left, Semaphores | Leave a Comment »
My Experiences with Databases & More 