dba topics

enq: TM - contention Wait Event , Cause, Related SQL queries, Possible Resolution

noreply@blogger.com (Shony) — Wed, 20 Sep 2023 09:07:00 +0530

The "enq: TM - contention" wait event in Oracle indicates contention for a table-level lock. This contention occurs when multiple sessions are attempting to acquire conflicting table-level locks concurrently, leading to performance issues as sessions have to wait for the locks to be released before they can proceed with their operations. While there are similarities in the resolution

enq: TX - index contention Wait Event , Cause, Related SQL queries, Possible Resolution

noreply@blogger.com (Shony) — Thu, 14 Sep 2023 08:44:00 +0530

The "enq: TX - index contention" wait event in Oracle occurs when multiple database sessions are contending for locks on the same index block. This contention can lead to performance issues as sessions have to wait for the locks to be released before they can proceed with their operations. Explanation: In Oracle, locks are used to control access to data structures, including index

enq: TX - row lock contention Wait Event , Cause, Related SQL queries, Possible Resolution

noreply@blogger.com (Shony) — Mon, 11 Sep 2023 15:33:00 +0530

The "enq: TX - row lock contention" wait event in Oracle occurs when multiple database sessions are contending for locks on the same row in a table. This contention can lead to performance issues, as sessions have to wait for the locks to be released before they can proceed with their operations. Explanation: In Oracle, locks are used to control access to data to maintain data consistency

Latch Free Wait Event , Cause, Related SQL queries, Possible Resolution

noreply@blogger.com (Shony) — Thu, 7 Sep 2023 08:55:00 +0530

wait event in Oracle occurs when a session is trying to acquire a latch but finds it unavailable, resulting in a wait condition. Latches are low-level synchronization mechanisms used by Oracle to protect shared data structures in the SGA (System Global Area) from concurrent access. Explanation: Latches are used to coordinate access to data structures like buffers in the buffer cache,

Log file sequential read Wait Event , Cause, Related SQL queries, Possible Resolution

noreply@blogger.com (Shony) — Mon, 4 Sep 2023 09:19:00 +0530

The "log file sequential read" wait event occurs when a session is waiting for a single redo log file block to be read from disk into the buffer cache in a sequential manner. This wait event is common during log file access operations, such as reading redo log records during recovery or archive log processing. Explanation: During database operations, especially during recovery

Fix runInstaller [INS-32042] The Installer has detected that the user (oracle) is not member of the central inventory group: wheel

noreply@blogger.com (Shony) — Thu, 31 Aug 2023 15:33:00 +0530

Problem DescriptionOracle installation or runinstaller failed with below error message. [INS-32042] The Installer has detected that the user (oracle) is not member of the central inventory group: wheelSolution DescriptionThis is the problem with the parameter value mentioned in the /etc/oraInst.loc. value for the inst_group parameter mentioned in the /etc/oraInst.loc should match with

log file parallel write Wait Event , Cause, Related SQL queries, Possible Resolution

noreply@blogger.com (Shony) — Wed, 30 Aug 2023 09:01:00 +0530

The "log file parallel write" wait event occurs when Oracle is writing redo log information to multiple redo log files in parallel. This wait event is related to the process of committing transactions and ensuring that redo log records are safely stored on disk. Explanation: During a transaction commit, the changes (redo log records) made in the transaction need to be

log file sync Wait Event , Cause, Related SQL queries, Possible Resolution

noreply@blogger.com (Shony) — Mon, 28 Aug 2023 09:23:00 +0530

The "log file sync" wait event occurs when a session is waiting for a commit's redo records to be written from the redo log buffer to the redo log file on disk. This wait event is crucial for ensuring data durability and is typically associated with transaction commits. Explanation: When a user or application commits a transaction, the changes (redo records) are first

db file scattered read - Wait Event , Cause, Related SQL queries, Possible Resolution

noreply@blogger.com (Shony) — Wed, 23 Aug 2023 09:04:00 +0530

The "db file scattered read" wait event occurs when a session is waiting for multiple data blocks to be read from disk into the buffer cache in a scattered manner. This wait event is common when a query or operation requires access to multiple non-adjacent data blocks that are not currently in memory. Explanation: This wait event indicates that a session is waiting for I/O operations to

db file sequential read - Wait Event , Cause, Related SQL queries, Possible Resolution

noreply@blogger.com (Shony) — Thu, 17 Aug 2023 10:51:00 +0530

The "db file sequential read" wait event occurs when a session is waiting for a single data block to be read from disk into the buffer cache in a sequential manner. This wait event is common when a query or operation requires access to a specific data block that is not currently in memory. Explanation: This wait event indicates that a session is waiting for the I/O operation to complete,

How to Get Bind Variable Value in Oracle Database

noreply@blogger.com (Shony) — Wed, 16 Aug 2023 10:02:00 +0530

To retrieve the value of a bind variable in Oracle, you can use the V$SQL_BIND_CAPTURE view or the DBMS_APPLICATION_INFO package, depending on your use case. Here's how you can do it: Using V$SQL_BIND_CAPTURE View: The V$SQL_BIND_CAPTURE view provides information about captured bind values for SQL statements in the library cache. You can query this view to retrieve the values of bind variables

How to Get or Display Explain Plan of SQL Baseline - DBMS_XPLAN.display_sql_plan_baseline

noreply@blogger.com (Shony) — Mon, 14 Aug 2023 11:37:00 +0530

you can use the DBMS_XPLAN. DISPLAY_SQL_PLAN_BASELINE procedure to display the execution plan associated with a specific SQL Baseline. This procedure is part of the DBMS_XPLAN package and is designed to show the execution plan for a SQL statement using its SQL ID or SQL handle, including those stored in SQL Plan Baselines. Here's an example of how to use

Fix Error: ORA-13541: system moving window baseline size (691200) greater than retention (259200)

noreply@blogger.com (Shony) — Fri, 11 Aug 2023 17:48:00 +0530

Problem Description:When I trying to change the AWR snapshot setting I am getting below error. Error: ORA-13541: system moving window baseline size (691200) greater than retention (259200)SQL> get afiedt.buf 1 DECLARE 2 retention_num NUMBER; 3 interval_num NUMBER; 4 BEGIN 5 -- Set the desired snapshot retention period (in days) 6 retention_num := 3*24*60; 7

DBMS_XPLAN - Script To get The Explain Plan from cursor

noreply@blogger.com (Shony) — Fri, 11 Aug 2023 10:11:00 +0530

To retrieve the execution plan (explain plan) for a specific cursor in an Oracle database, you can use the following SQL*Plus script. This script uses the DBMS_XPLAN package to display the execution plan for a given SQL statement: An execution plan (explain plan) provides insights into how Oracle's query optimizer intends to execute a given SQL statement. It shows the sequence of operations and

How to Fix Oracle VM - failed to open a session for the virtual machine - After upgrading windows

noreply@blogger.com (Shony) — Fri, 11 Aug 2023 09:45:00 +0530

Problem Description: I was facing an issue with Oracle Virtual Machine after upgrading windows 10 to 11. I had to reimage my system with latest windows 11 OS. After that I installed the Oracle VM of version 6 which is an old version. I tried to load an linux image to the same which was already available with me and it was perfectly working with windows 10 and Oracle VM 6. Post upgrade of

Kite Fest - Google Pay - Gpay - How to Get sharing Link ?Get up to Rs. 100 cashback once you fill the sky with 200 kites

noreply@blogger.com (Shony) — Thu, 10 Aug 2023 11:10:00 +0530

First time when I tried to share the link of Gpay kite fest with friends, I couldn't do that. It was not showing the whatsapp or copy link option. For that you need to update your google pay app to get the latest version and you can try below optionSample Link1. Open google pay app2. Click on Fly Kites together -> Let's go button3. Click on Fly kites with friends

How to Get Oracle Redo Log Files, Members, Size, Name and Status - Query

noreply@blogger.com (Shony) — Thu, 10 Aug 2023 10:17:00 +0530

To retrieve redo log information from an Oracle database, you can use the following SQL*Plus script. This script queries the V$LOG view to provide details about the redo log files in the database. Redo logs are a critical component of Oracle databases. They record all changes made to the database, allowing for recovery and transaction consistency. The redo log files are divided into groups, and

How to Find / Retrieve / Get ACL details in Oracle Database

noreply@blogger.com (Shony) — Thu, 10 Aug 2023 09:14:00 +0530

To retrieve the ACL (Access Control List) details in an Oracle database, you can use the following SQL script. This script queries the DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES views to get information about the ACLs and their privileges: An Access Control List (ACL) in Oracle is a list of network privileges for a particular host and a specific principal (user or role). ACLs control

How to Find out Oracle Database Uptime in Hours or Days - Script

noreply@blogger.com (Shony) — Wed, 9 Aug 2023 09:50:00 +0530

To find out the database uptime in Oracle, you can use the following SQL script. This script calculates the database uptime by subtracting the startup time from the current time Database uptime refers to the duration of time that a database has been running since its startup. Monitoring database uptime is important for ensuring the availability and reliability of the database. It helps

How to Revert / Convert USB Drive into 64GB (Actual Size) From Windows Bootable Image of Size 32GB?

noreply@blogger.com (Shony) — Wed, 9 Aug 2023 09:39:00 +0530

Reverting a USB drive to its actual size involves removing any partitions, formatting the drive, and potentially restoring its original firmware. If you have a 64GB USB drive that has been modified to appear as a 32GB drive with a Windows bootable image, you can follow these steps to revert it back to its actual size:Important Note: These steps involve low-level operations on the USB drive, which

How to Enable or Disable All Triggers in a Oracle Schema - Script

noreply@blogger.com (Shony) — Wed, 9 Aug 2023 08:49:00 +0530

To disable all triggers in an Oracle schema, you can use the following SQL script. This script generates the necessary SQL statements to disable all triggers and then executes them. Disabling triggers can be useful in scenarios where you need to temporarily suspend their execution, such as during data maintenance, bulk loading, or troubleshooting activities. Disabling triggers can prevent them

Script to Find out Row Count of All the Tables in a schema

noreply@blogger.com (Shony) — Mon, 7 Aug 2023 09:48:00 +0530

To retrieve the row count of all the tables in a schema, you can use the following SQL script. This script queries the DBA_TABLES view to get the row count for each table in the specified schemacol table_name for a40 SELECT table_name, num_rows FROM all_tables WHERE owner = 'YOUR_SCHEMA_NAME'; advertisements (adsbygoogle =

Script to Find and Cleanup Orphaned Datapump Jobs

noreply@blogger.com (Shony) — Mon, 7 Aug 2023 09:33:00 +0530

Orphaned Data Pump jobs are jobs that have been left behind due to various reasons, such as an interruption or error during the execution of the job. These orphaned jobs are not actively running but are still present in the database. To clean up these orphaned Data Pump jobs, you can use the following scriptDECLARE job_state VARCHAR2(30); BEGIN FOR rec IN (SELECT job_name, state FROM

SQL Query to Get / Find out Character Set Information of an Oracle Database

noreply@blogger.com (Shony) — Thu, 3 Aug 2023 16:31:00 +0530

The script queries the NLS_DATABASE_PARAMETERS view to retrieve character set-related information of the database. Here's what the script does: PARAMETER: This column represents the name of the parameter related to character sets. VALUE: This column contains the value of the character set parameter. The WHERE clause filters the results to include only rows where the parameter name contains the

SQL Queries to View, Modify Awr Snapshot Interval Setting

noreply@blogger.com (Shony) — Wed, 2 Aug 2023 15:37:00 +0530

To view the current settings of AWR (Automatic Workload Repository) snapshot retention and interval, you can query the DBA_HIST_WR_CONTROL view. Here's a SQL query along with a brief explanation and example outputcol SNAP_INTERVAL for a40 col RETENTION for a40 SELECT SNAP_INTERVAL, RETENTION FROM DBA_HIST_WR_CONTROL; advertisements (