×
Home
Jobs
Tools
Coding Ground
Current Affairs
UPSC Notes
Online Tutors
Whiteboard
Tutorix
Login
Categories
Java
JSP
iOS
HTML
Android
Python
C Programming
C++ Programming
C#
PHP
CSS
Javascript
jQuery
SAP
SAP HANA
Data Structure
RDBMS
MySQL
Mathematics
8085 Microprocessor
Operating System
Digital Electronics
Analysis of Algorithms
Mobile Development
Front End
Web Development
Selenium
MongoDB
Computer Network
General Topics
Q/A
Library
eBooks
Courses
Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Selected Reading
UPSC IAS Exams Notes
Developer's Best Practices
Questions and Answers
Effective Resume Writing
HR Interview Questions
Computer Glossary
Who is Who
Kiran P
has Published
125
Answers
How to generate JSON data and perform Schema Validation in Oracle?
Oracle
Software & Coding
Programming
Kiran P
Published on 05-Dec-2020 06:10:58
Problem:You want to generate JSON data and perform Schema Validation in Oracle.SolutionFunctions like JSON_OBJECT, JSON_ARRAYAGG, JSON_QUERY can be used to generate a complex json data using mutiple columns and tables.JSON_OBJECT:- Its an SQL/JSON function. JSON_OBJECT takes as its input one or more property key-value pairs. It returns a JSON object ...
Read More
How to use DATETIME functions in Oracle?
Oracle
Software & Coding
Programming
Kiran P
Published on 05-Dec-2020 06:08:36
Problem:You want to use datetime functions to extract or process datetimes.SolutionADD_MONTHS()ADD_MONTHS(x, y) returns the result of adding y months to x. If y is negative, then y months are subtracted from xExample-- add 12 months SELECT ADD_MONTHS('01-JAN-2020', 12) FROM dual;Output-- 01/JAN/21Example-- subtract 12 months SELECT ADD_MONTHS('01-JAN-2020', -12) FROM dual;Output-- 01/JAN/21We ...
Read More
How to insert and retrieve dates in Oracle ?
Oracle
Software & Coding
Programming
Kiran P
Published on 05-Dec-2020 06:03:24
Problem:You want to insert dates into a table and retrieve them.SolutionOracle, by default the uses the format DD-MON-YYYY to represent a date, whereDD represents a two-digit day.MON represents first three letters of the month, e.g., FEB.YYYY is a four-digit year, e.g., 1968Let us take a look at an example of ...
Read More
How to UNPIVOT results in Oracle?
Oracle
Software & Coding
Programming
Kiran P
Published on 05-Dec-2020 06:02:10
Problem:You want to UNPIVOT results in Oracle.SolutionThe UNPIVOT clause is new for Oracle Database 11g and enables you to flip the columns into rows in the output from a query, and, at the same time, allow you to run an aggregation function on the data.Consider a table called customer which ...
Read More
How to PIVOT results in Oracle?
Oracle
Software & Coding
Programming
Kiran P
Published on 05-Dec-2020 06:00:42
Problem:You want to PIVOT results in Oracle.SolutionThe PIVOT clause is new for Oracle Database 11g and enables you to flip the rows into columns in the output from a query, and, at the same time, allow you to run an aggregation function on the data.PIVOT is especially useful to look ...
Read More
How to monitor temporary tablespace usage in Oracle?
Oracle
Software & Coding
Programming
Kiran P
Published on 05-Dec-2020 05:58:55
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 ...
Read More
How to identify recent WAIT events in a Oracle database ?
Oracle
Software & Coding
Programming
Kiran P
Published on 05-Dec-2020 05:57:43
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 ...
Read More
How to identify blocked and blocking sessions in Oracle ?
Oracle
Software & Coding
Programming
Kiran P
Published on 05-Dec-2020 05:56:52
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 ...
Read More
How to use Grouping clause to handle NULLS in Oracle?
Oracle
Software & Coding
Programming
Kiran P
Published on 05-Dec-2020 05:55:47
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 ...
Read More
How to identify SQL queries with the most waits in Oracle?
Oracle
Software & Coding
Programming
Kiran P
Published on 05-Dec-2020 05:53:53
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
Previous
1
2
3
4
5
6
7
8
...
13
Next
Advertisements
Print
Add Notes
Bookmark this page
Report Error
Suggestions
Save
Close
Dashboard
Logout