Showing posts from June, 2025
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, …
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…
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…
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…
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…
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…
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…
Identify Exact Position and Code in a Column of a table If you want to see the position and ASCII code of non-printable characters: SELECT LEVEL AS position, SUBSTR(n2, LEVEL, 1) AS character, ASCII(SUBSTR(n2, LEVEL, 1)) AS ascii_v…
Here is a complete SQL script to create and populate a custom ASCII lookup table with character codes (0–127) and their corresponding descriptions/names . ✅ Step 1: Create the table CREATE TABLE ascii_lookup ( ascii_code NUMBER PRIMARY KEY, …
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 – 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…
Let's troubleshoot and ensure you're using EXCEPTIONS INTO correctly. Here’s a working example step-by-step , tested and compliant with Oracle behavior: ✅ 1. Create a table with duplicate data CREATE TABLE test_emp ( emp_id NUMBER, emp…
When you try to enforce a primary key on an existing table with duplicate values , Oracle will throw an error because primary keys must be unique and non-null . Here’s how you can identify and remove duplicates and then add the primary key constrai…
The ASCII function in Oracle returns the numeric ASCII code of the first character in a string. ✅ Syntax: ASCII(char) char can be a single character or a string — only the first character is considered. The return value is a NUMBER repre…
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…
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.…
When a column in an Oracle table stores only two distinct values like 'Yes' or 'No' , creating a standard B-tree index on it is not efficient , because: The column has low cardinality (few distinct values), B-tree indexes are most…
