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 1…

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, …

How to kill SNIPED sessions dynamically using PL/SQL in Oracle

In Oracle, to kill sniped sessions dynamically using SQL, you can use EXECUTE IMMEDIATE inside a PL/SQL block. Understanding Sniped Sessions A sniped session is a session that has been marked as inactive (due to profile settings like IDLE_TIME in …

Load More
That is All