To WRAP (encrypt/obfuscate) a PL/SQL procedure in Oracle, you can use the Oracle Wrap Utility or the DBMS_DDL.WRAP function. Below are the correct and practical methods. 1. Using Oracle WRAP Utility (Command Line Method) This is the most common…
The error ORA-01950: no privileges on tablespace 'USERS' means that the user does not have quota on the USERS tablespace to create tables, indexes, or store data. This is very common when creating a new user in Oracle. ✅ How to Fix ORA-0…
In Oracle, there is NO difference between COUNT(*) and COUNT(1) in terms of performance or result . Both return the number of rows in the result set. ✅ 1. COUNT(*) Counts all rows , including rows with NULL values. It does not read column va…
This is a very common Oracle issue when handling mobile numbers stored in a numeric field . Let’s go step by step on why it happens and how to avoid ORA-06502 safely 👇 ⚠️ Why the Error Happens If a mobile number is stored or entered as a char…
Yes you can count distinct combinations of values across multiple columns in Oracle SQL. However, Oracle doesn’t allow COUNT(DISTINCT col1, col2) directly (unlike some other databases). You can achieve it using either of these three standard met…
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 ( …
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 …
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…
