Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Problem:You want to monitor the usage of the temporary tablespace in Oracle.Solution:We can use the following query to find out the used and free space in a temporary tablespace.We will begin with identifying temporary table space names.ExampleSELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';OutputTEMPNext up we will use below SQL to identify the used and free space in temporary tablespace.ExampleSELECT * FROM (SELECT a.tablespace_name, SUM(a.bytes/1024/1024) allocated_mb FROM dba_temp_files a WHERE a.tablespace_name = 'TEMP' GROUP BY a.tablespace_name ) x, (SELECT SUM(b.bytes_used/1024/1024) used_mb, SUM(b.bytes_free /1024/1024) free_mb FROM v$temp_space_header b ... Read More
Problem:You want to find out the recent important waits in your database, as well as the users, SQL statements, and objects that are responsible for most of those waits.SolutionOracle provides us with V$ACTIVE_SESSION_HISTORY to get information about the most common wait events, and the SQL statements, database objects, and users responsible for those waits.SQL to find the wait eventsExampleSELECT event, SUM(wait_time + time_waited) total_wait_time FROM v$active_session_history GROUP BY event ORDER BY total_wait_time DESC;To find out the users who experienced the most waits we can use below SQL.ExampleSELECT s.sid, s.username, SUM(a.wait_time + a.time_waited) total_wait_time FROM v$active_session_history a, ... Read More
Problem:You’d like to identify the blocking and the blocked sessions in your database.SolutionWhen we see an enqueue wait event in an Oracle database, the chances are that there is some thing locking or holding up some sessions from executing their SQL statements. When a session waits on an “enqueue” wait event, that session is waiting for a lock that is held by a different session. We can issue the following command to view information about the blocked and the blocking sessions in ORacle.ExampleSELECT DECODE(request, 0, 'Holder: ', 'Waiter: ') || sid sess, id1, id2, lmode, request, ... Read More
Problem:You want to use GROUPING() with ROLLUP, CUBE and SETS functions.SolutionThe GROUPING() function accepts a column and returns 0 or 1. This function returns 1 when the column value is null and returns 0 when the column value is non null. However, GROUPING() is used only in queries that use ROLLUP or CUBE. GROUPING() is very useful when you want to display a value when a null would otherwise be returned.GROUPING() with a Single Column in a ROLLUPSQL to pass a column to ROLLUPThe last row in the below example will result total/sum of fees. This total is represented as ... Read More
Problem:You want to gather extended explain plan statistics for a specific query.SolutionWe can use the GATHER_PLAN_STATISTICS hint. This hint when placed within a query at runtime, will generate extended runtime statistics. It basically have two steps.Execute the query with the gather_plan_statistics hint.Use dbms_xplan.display_cursor to display the results.ExampleSELECT /*+ gather_plan_statistics */ city, round(avg(fees)) avg_fees, min(fees) min_fees, max(fees) max_fees FROM students e, departments d, locations l WHERE e.department_id = d.department_id AND l.location_id = d.location_id GROUP BY city;OutputSouthlake 5760 4200 9000 Seattle 10701 6900 ... Read More
Problem:You have a performance issue with a queryhaving JOIN conditions on multiple tables, and you wish to override the join type by placing the appropriate hint in the query.Solution:There are three possible types of joins: nested loops, hash, and sort merge.Nested Loops Join Hint: To invoke a nested loops join, use the USE_NL hintExampleEXPLAIN PLAN FOR SELECT /*+ use_nl(students, departments) */ first_name, department_name FROM students e, departments d WHERE e.department_id = d.department_id; SELECT * FROM table(DBMS_XPLAN.DISPLAY (FORMAT=>'BASIC'));Output---------------------------------------------------------- | Id | Operation | Name ... Read More
Problem:You have a performance issue with a queryhaving JOIN conditions on multiple tables, and the Oracle optimizer is not choosing the join order you wanted.Solution:Oracle has two hints the ORDERED hint, and the LEADING hint that can be used to influence the join order used within a query.ORDERED HintYou are running a SQL to join two tables, student and departments, as you want to get the department names for each student. By placing an ORDERED hint into the query, you can see how the hint alters the execution access path.ExampleEXPLAIN PLAN FOR SELECT first_name, department_name FROM students e, departments ... Read More
Problem:You wanted to know the difference between Union ALL and Insert ALL to generate small amounts of data.Solution:I have been recently generating small amounts of data for testing some functionality and came across a couple of options in Oracle. Union All and Insert All are two options commonly used for generating small amounts of data in Oracle.The most common set operators in Oracle are UNION and UNION ALL. These operators are used to combine sets of data, even if there are no relationships between these sets.UNION creates a distinct set, whereas UNION ALL allows for duplicates. Removing duplicates can have ... Read More
Problem:You need to determine if any given year is a leap year.Solution:There are numerous ways of solving this problem.These include calculations to determine whether February 29 exists in the year, or whether March 1 is the 61st or 62nd day of the year.ExampleSELECT to_number(to_char(sysdate, 'YYYY')) Year, CASE WHEN MOD(to_number(to_char(sysdate, 'YYYY')), 400) = 0 then 'Yes' WHEN MOD(to_number(to_char(sysdate, 'YYYY')), 100) = 0 then 'No' WHEN MOD(to_number(to_char(sysdate, 'YYYY')), 4) = 0 then 'Yes' ELSE 'No' END AS "Leap Year?" FROM dual;Output2020 YesObviously 2020 is a year to forget and it ... Read More
Problem:You want to find out some indroductory information about the database.Solution:Every Oracle programmer/ DBA has at one point or another during their career inherited a database someone else have already set up.You needs to find out some introductory information about the database to learn more about it.Idenitfy the host details and databsae versionExampleSELECT instance_name, host_name, version, startup_time FROM v$instanceOutputINSTANCE_NAME HOST_NAME VERSION STARTUP_TIME TESTDB ip-000-00-0-000 19.0.0.0.0 12/AUG/20Next, We will determine the ... Read More