How to find The Turnaround Time (TAT) in Oracle SQL

The Turnaround Time (TAT) in Oracle SQL is typically calculated as: TAT = END_TIME - START_TIME It measures total time taken from initiation to completion of a task — not just the repair time. 🧾 Assumed Table Structure CREATE TABLE TASKS ( …

How to find MTTR (Mean Time To Repair) in Oracle

In Oracle SQL , to calculate MTTR (Mean Time To Repair) , you typically need a table that records fault start time and fault end time for each incident. 🧾 Assumptions: Table Structure Assume a table like: CREATE TABLE FAULTS ( FAULT_ID …

How to find the top 10 largest indexes in oracle

In Oracle 11g , the method to find the top 10 largest indexes    1. Using DBA_SEGMENTS (For DBAs) This query will return the top 10 largest indexes in the database: SELECT * FROM ( SELECT owner, segment_name AS index_name, …

How to find the total database size in Oracle

To find the total database size in Oracle , you can use a variety of queries, depending on the level of detail you need (e.g., total size of all tablespaces, individual tablespace sizes, or database overall). Here's a breakdown: 1. Using DBA_DATA…

Top 10 Largest Tables in Oracle 11g and 12c

For Oracle 11g , the FETCH FIRST 10 ROWS ONLY syntax doesn't work since it was introduced in Oracle 12c . Instead, you can use the ROWNUM approach to limit the results to the top 10 largest tables . Here’s how you can modify the query for Oracle…

How to find the size of a table in Oracle

To find the size of a table in Oracle , you can use several methods depending on the level of detail you need. Here's a summary of common approaches: 1. Using DBA_SEGMENTS (Shows table size) The DBA_SEGMENTS view provides detailed information ab…

CARDINALITY function in Oracle

In Oracle , the CARDINALITY function is primarily used in the context of the TABLE operator and collections , particularly when dealing with nested tables or VARRAYs in PL/SQL. It returns the number of elements in a collection. 🔹 Syntax: CARDIN…

BTAND bitwise AND operation between two integers in Oracle

The BITAND function in Oracle is used to perform a bitwise AND operation between two integer values. It returns the bitwise logical AND of the two numbers. 🔹 Syntax: BITAND(number1, number2) number1 , number2 must be integers (or expressions th…

How Convert binary number to decimal and vice versa

In Oracle, BIN_TO_NUM is a built-in bitwise utility function introduced in Oracle 11g (for bitmap operations). It is used to convert a series of binary flags (0s and 1s) into a single numeric value — basically converting a bit vector into its dec…

External Table with Multiple Files in Oracle

To create an external table in Oracle that reads from multiple files , you simply list all the files under the LOCATION clause of the ORGANIZATION EXTERNAL clause. ✅ Example: External Table with Multiple Files Assume the files: data1.csv data2.csv…

External Tables in Oracle

✅ External Tables in Oracle – Overview & Usage External tables in Oracle allow you to query data in flat files (CSV, pipe-delimited, etc.) as if they are regular database tables , without loading the data into the database. They're read-onl…

ADD_MONTHS Function in Oracle

In Oracle, ADD_MONTHS is a date function used to add or subtract a number of months to a given date. ✅ Syntax ADD_MONTHS(date, number_of_months) date : A valid DATE or TIMESTAMP value. number_of_months : Can be positive (add months) or neg…

Usage of %ROWTYPE in oracle

In Oracle, a ROWTYPE is a composite data type used in PL/SQL to declare a variable that represents a row of a table or row of a cursor . It allows you to easily manipulate and access an entire row of data without declaring each column individually.…

Scheduling Oracle jobs using DBMS_JOB

-- scheduling at 01:00 am declare jobno number; begin dbms_job.submit(jobno, '<procedurename>;',TRUNC(sysdate)+1/24,'TRUNC(sysdate+1)+1/24'); end; -- every 2 hours declare jobno number; begin dbms_job.submit(jobno, …

Load More
That is All