Tech Support & Travel

Here is a very useful production-style script to drop multiple tables automatically using a loop in Oracle. This is commonly used in data migration, backup cleanup, or refresh scripts . 1️⃣ Drop All Tables Matching a Pattern Example: Drop all backup…

PL/SQL block for dropping a table only if it exists. It safely ignores the error ORA-00942 (table or view does not exist) . Here is the clean version with proper formatting : BEGIN EXECUTE IMMEDIATE 'DROP TABLE CO_DATA_NZ_bkp PURGE'; EXCEPT…

Safe Drop Table in PL/SQL BEGIN EXECUTE IMMEDIATE 'DROP TABLE MAIN_SUB_LOC_NZ_BKP PURGE' ; EXCEPTION WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL ; -- table does not exist, ignore ELSE RAISE; …

Indian Financial Year (April–March) ending logic in Oracle SQL . 🇮🇳 Financial Year (April–March) Financial Year: Starts → 01-APR Ends → 31-MAR (next year) ✅ 1. Get Financial Year End Date for a Given Date Example: '15-DEC-2025' SELECT CASE …

In Oracle SQL, you can use the built-in LAST_DAY function. ✅ If you have a date like '01-DEC-2025' SELECT LAST_DAY(TO_DATE('01-DEC-2025','DD-MON-YYYY')) AS LAST_DATE FROM dual; ✅ Output 31-DEC-2025 🔹 If the column is already …

production-safe logic to ensure SMS is sent only between 10:00 AM and 8:00 PM . ✅ Simple & reliable Oracle logic (recommended) AND TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) BETWEEN 10 AND 19 ✔ Why 19 and not 20? HH24 = 20 means 8:00–8:59 PM “B…

To check whether a particular procedure is scheduled (used) in DBMS_SCHEDULER in Oracle, use the following reliable methods . ✅ 1️⃣ Check Scheduler Jobs Calling That Procedure (MOST COMMON) SELECT owner, job_name, job_type, job_a…

A DBA_2PC unhandled exception while purging usually means Oracle is unable to clean up an in-doubt distributed transaction , even after COMMIT FORCE / ROLLBACK FORCE . This is a known and tricky DBA issue. Problem Error while executing: EXEC DBMS_TR…

Issue ORA-01591: lock held by in-doubt distributed transaction Error Message ORA-01591: lock held by in-doubt distributed transaction 2.96.17311 When Executing SELECT * FROM COMPLAINT WHERE case_no = 'AP1706098703'; Error Description ORA-015…

Problem Statement SELECT * FROM UTILIZATION_REPORT_DATA WHERE AMOUNT_PAID_ONT>0  ORA-01722: invalid number Solution identify rows where AMOUNT_PAID_ONT contains non-numeric characters , which usually cause ORA-06502 / invalid number errors. Howe…

To enter multiple lines (new line) in the same Excel cell : ✅ While typing in a cell Double-click the cell (or press F2 ) Type your first line Press: Windows: ALT + ENTER Mac: CTRL + OPTION + RETURN Continue typing on the next line Press Enter to …

Here is the correct and simplest way to restore an Oracle PACKAGE / PACKAGE BODY using Flashback features . Oracle does not flashback individual objects directly — but you can restore them with the following supported methods. Restore Package f…

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…