Problem:You want to identify the SQL statements responsible for the most waits in your database.SolutionWe can use below SQL statement to identify SQL causing problem.The below query will rank SQL statements that ran during the past 30 minutes and display them as per the total time waited by each query.ExampleSELECT ... Read More

You want to use Oracle aggregate function XMLAGG for string aggregation.?Solution:You would like to include calculations based on preceding and following rows in the result set.Oracle supports the LAG and LEAD analytical functions to provide access to multiple rows in a table, utilizing preceding or following logic and you won’t ... Read More

Problem:You wanted to generate a data model from data dictionary tables in OracleSolution:The Oracle data dictionary is a collection of tables and related views that enable us to view the structure of the Oracle database. By querying these tables and views, we can obtain information about every object and every ... Read More

Problem:You want to identify the SQL statements consuming more resources in Oracle.Solution“V$SQLSTATS" view displays performance statistics for SQL statements that have recently executed. You can also use "V$SQL” and “V$SQLAREA" to report on SQL resource usage. "V$SQLSTATS” is faster and retains information for a longer period of time, but contains only ... Read More

Problem:You want to view where Oracle SQL is taking time within a SQL execution plan.SolutionWith Oracle 11g version, we can view SQL execution plan progress while the SQL is running. The “V$SQL_PLAN_MONITOR” view contains a row for each step of a SQL statement’s execution plan. Below SQL will help to ... Read More

Problem:You want to know how much longer a long running SQL might ake to finish.SolutionWe can use “V$SESSION_LONGOPS" view to know the approximate time of a query left to execute. "V$SESSION_LONGOPS” view displays the status of various database operations that have been running for longer than six seconds. Please note ... Read More

Problem:You want to monitor currently executing SQL statistics in Oracle.SolutionIf your database is Oracle Database 11g, you can use the following query to select from the “V$SQL_MONITOR” to monitor the near real time resource consumption of SQL queries.The statistics in “V$SQL_MONITOR” are updated every second. This helps us to view ... Read More

Problem:You want to limit the amount of resources a user can consume in your database.SolutionTo limit the resources, we can follow below steps.We can use below SQL statement to view the current setting of RESOURCE_LIMIT in our database.select name, value from v$parameter where name='resource_limit';Create a profile to limit the resources ... Read More

Problem:You want to use Primary Key Constraints and Foreign Key Constraints to enforce database integrity in Oracle.SolutionAny DML statement (an INSERT, UPDATE, or DELETE) executed on tables defined with integrity, helps to ensures that the rows in the tables maintain their integrity.Let’s look at some examples that show the enforcement ... Read More

Problem:You want to store the results of a SQL temporarily.SolutionWe can use the CREATE GLOBAL TEMPORARY TABLE statement to create a table that stores data temporarily for a session. Further, you can specify whether to retain temporary table data for a session or until a transaction commits. We can further ... Read More