Skip to main content

Oracle DBA Arena

Search This Blog

Home
Privacy Policy

Posts

Query to check the latest Materialized View refresh time and type

on July 23, 2022

last mivew refresh materialzied view mivew mview refresh date mview refresh type Oracle oracle database select query

+

Get link
Facebook
X
Pinterest
Email
Other Apps

How to fix ORA-28362: Master key not found tips

on June 29, 2022

encrypted tables ENCRYPTION WALLET encryption_wallet ewallet ewallet.p12 Oracle oracle 11g oracle database oracle encryption oracle tde oracle wallet v$encryption_wallet

+

Get link
Facebook
X
Pinterest
Email
Other Apps

Query to check the patches applied in current adop patch cycle R12.2

on June 22, 2022

adop patch adpatch Applied patches downtime Hotpatch oracle 12c oracle 19c oracle r12.2 patch cycle patch run time R12.2 Sql Query

+

Get link
Facebook
X
Pinterest
Email
Other Apps

Materialized view-Beginners Guide

on April 23, 2022

complete refresh fast refreshable force refresh materialzied view mview oracle database snapshot

+

Get link
Facebook
X
Pinterest
Email
Other Apps

Important Queries for cloning

on March 31, 2022

Clone oracle 12c oracle 19c oracle database Oracle EBS Oracle EBS Workflow Sql Query

+

Get link
Facebook
X
Pinterest
Email
Other Apps

Troubleshooting oracle long running request - Performance tuning

on August 08, 2021

concurrent program long running Long running request oracle 19c oracle database performance tuning troubleshooting

+

Get link
Facebook
X
Pinterest
Email
Other Apps

Oracle long running query Performance Tunning

on June 17, 2021

awr report analysis Oem oracle database oracle index performance tuning sql advisor sql tunning sqldb360

+

Get link
Facebook
X
Pinterest
Email
Other Apps

Oracle Standby Important queries

on May 17, 2021

Disaster recovery DR queries dr query oracle 11g oracle 12c oracle 19c oracle database oracle database dr Sql Query Standby standby query sync

+

Get link
Facebook
X
Pinterest
Email
Other Apps

Bug in oracle database- manipulating the last_ddl in oracle database

on February 09, 2021

bug in oracle database database last_ddl time manipulating oracle database Oracle oracle 11g oracle 12c oracle 19c sysdate

+

Get link
Facebook
X
Pinterest
Email
Other Apps

Blockchain table in 19c

on February 09, 2021

blockchain blockchain table crypto currency OCI oracle 19c oracle 21c Oracle Cloud oracle database

+

Get link
Facebook
X
Pinterest
Email
Other Apps

Oracle 19c grid installation and configuration

on February 01, 2021

19c asm asm add disk asm configuraiton asm rename disk database grid grid configuration installation Oracle oracle 19c grid Oracle 19c grid installation and configuration rename asm disk

+

Get link
Facebook
X
Pinterest
Email
Other Apps

Script to Start/Stop Oracle EBS Workflow Mailer From Backend

on January 08, 2021

11g 12.1 12.2 12c Automation Oracle Oracle EBS Oracle EBS Workflow oracle workflow workflow mailer

+

Get link
Facebook
X
Pinterest
Email
Other Apps

Popular Posts

Oracle long running query Performance Tunning

In this post, I'll be explaining the long-running concurrent program issue we have faced in one of our instance. The issue was with Oracle Remove Person Program ran for three and half hours to delete one person record. When the customer reported the issue, I checked the AWR report and found that one particular SQL was running for nearly three hours. Since the SQL dictates more than 74% of the runtime of the concurrent program I will be discussing how I tunned the query to make it run in 16 mins from nearly 3 hours. As a usual step, I have checked for the alternate hash plan using the queries in the link . But there are no other hash plans available in the database for the particular SQL id. So my next hope was on automatic SQL tuning advisor and even it didn't have any action plan for the particular sql_id. SQL ID : bxjnvubgufs6m SQL Text : UPDATE HZ_CUST_SITE_USES_ALL SET LOCATION=NVL2(LOCATION,PER_DRT_UDF.GENERATE_UNIQUE_STRING(ROWID,' ...

Script to run the Sql tuning advisor from backend

Most of the time we will be in situation to tune a long running query to increase the performance. One of easiest way to do that is to run the sql tuning advisor and implement the suggestion.Let us discus about the easiest way of running the sql tuning advisor in the back-end. You can find the sql tuning advisor script in the following directory. Copy $ORACLE_HOME/rdbms/admin/sqltrpt.sql Once you run the above sciprt it wil ask you for the sql_id for which you wanna run the tuning advisor.Provide the sql_id and wait sometime as it takes 5-10 minutes to create and run the tuning task.Most of the time suggestion would be either index or sql profile. Please find the example for sql profile suggestion. 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 99.96%) ------------------------------------------ - Consider ...

Oracle Standby Important queries

Sometimes we would be working in the Oracle DR environment and will be in a situation where we need to search different documents for each query. To avoid those problems. I have put all the required queries in one blog post. In case if have missed please add it in the comment section. To check whether archive shipping is enabled in Primary we can simply issue "show parameter log_archive_dest" but the problem here is that we won't be able to find whether the connection between primary and standby is valid. To check it use the below query to check whether the connection between Primary and Standby is Valid. Copy set pagesize 300 set lines 300 col DESTINATION for a45 col ERROR for a10 SELECT inst_id,DESTINATION, STATUS, ERROR FROM gV$ARCHIVE_DEST WHERE DEST_ID=2; Output: INST_ID DESTINATION STATUS ERROR ---------- --------------------------------------------- --------- ---------- 1 ORACLEDBAARENA ...

Important Queries for cloning

Let us see the important queries we might need during the clone. 1. Query to check the database status Copy set lines 333 col HOST_NAME for a33 SELECT instance_name, host_name, version, startup_time, status, archiver, database_status FROM v$instance; 2. Query to get the dba_directories Copy set linesize 150 set pagesize 999 column directory_name format a25 column directory_path format a99 select directory_name, directory_path from dba_directories; 3. Query to get the db_link details Copy set lines 333 col OWNER for a11 col DB_LINK for a33 col USERNAME for a11 col HOST for a11 select * from dba_db_links; 4. Query to get the dml statement of db links Copy SELECT 'create ' ||Decode(U.name, 'PUBLIC', 'public ') ||'database link ' ||Decode(U.name, 'PUBLIC', NULL, U.name ||'.') || L.name ||' connect t...

Troubleshooting oracle long running request - Performance tuning

The most common issue faced by all the oracle apps dba is a long-running concurrent program. Let us discuss the different approaches to troubleshoot the long-running oracle concurrent program. Step 0: Check the request Check how long the program has been running and also check the previous run of the same program to get an idea about how long it should be running. Use the Query from the Post ( Current running Concurrent Program ). The query shows the result of current running concurrent programs with the previous runtime. Step 1: Request id to SID The first step of troubleshooting a long-running concurrent program is to find the sid of the concurrent program. Please use the below query to find the sid of the concurrent program. Request id to SID query : Copy SELECT a.request_id, s.sid, s.serial#, s.osuser, s.process, c.spid FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, v$process c, v$session s...

Archive

January 20251
October 20243
August 20241
July 20231
July 20221
June 20222
April 20221
March 20221
August 20211
June 20211

May 20211
February 20213
January 20211
November 20203
October 20202
September 20201
August 20201
July 20204
March 20202
February 20202
December 20191
October 20192
September 20193

Show more Show less

Powered by Blogger