Monday, 24 August 2020

Difference between Direct Path Load and Conventional Load

Difference between Direct Path Load and Conventional Load

This article will help you quickly understand the concept of direct  path load and conventional load within Oracle Database. 

So to begin, SQL*Loader provides 2 methods for loading data,

  • Conventional Path Load (This is by default used)
  • Direct Path load

Sunday, 1 September 2019

Selecting data from table and Writing to file in Oracle PL/SQL using UTL_FILE (File handling)

Using Oracle supplied package UTL_FILE we can perform file operations like Reading a File, Writing a File, Deleting File & also coping files.
Before you start make sure to create DB directory & to provide required required grants to your user.

Wednesday, 28 August 2019

GET EXACT ERROR LINE NUMBER IN PL/SQL USING FORMAT_ERROR_BACKTRACE


DBMS_UTILITY package provides FORMAT_ERROR_BACKTRACE procedure which returns exact line no of error source along with the error source name.

Saturday, 25 May 2019

Oracle Procedure to Search for a Particular String/Character

Oracle Procedure to Search for a Particular String/Character - Full Schema Scan


The below mentioned Oracle procedure will search for any particular string/character in all the tables for a particular schema and will list the count of number of rows for every column where that string/character is found. The code can be altered as per the need.

DECLARE
  match_count INTEGER;
  v_owner VARCHAR2(255) :='ENTER_SCHEMA_HERE';
  v_data_type VARCHAR2(255) :='VARCHAR2';
-- Type the string you are looking at
  v_search_string VARCHAR2(4000) := '@';

BEGIN
  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP

    EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' like ||':1'
    INTO match_count
    USING v_search_string;

    IF match_count > 0 THEN
      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
    END IF;

  END LOOP;
END;
/

I hope this helps !!

Wednesday, 10 October 2018

libnjni12.so(), but a runtime definition of the symbol was not found

/oracle/app/oracle/product/12.1.0/dbhome_1/lib/libnjni12.so(), but a runtime definition of the symbol was not found.

libnjni12.so(), but a runtime definition of the symbol was not found

Tuesday, 18 September 2018

UNNAMED Datafile | ORA-01111: name for data file is unknown | Oracle Standby

ORA-01111 ORA-01110 ORA-01157 is Oracle Standby Alert Log | Datafile created with UNNAME#### in Oracle Standby after adding new datafile to Primary


UNNAME#### in Oracle Standby after adding new datafile to Primary

Wednesday, 29 August 2018

ORA-01113: file # needs media recovery | Oracle Database

ORA-01113: file # needs media recovery | ORA-01110: data file #: 'E:\Database\TESTDB\#####.DBF'


ORA-01113 Oracle error indicates inconsistent datafiles with respect to Controlfile SCN and hence requiring further recovery in order to be online again. How those datafiles got into this RECOVER state can be because of multiple reasons as we can assume the different possibilities (power failure, server crash etc). But how they can be recovered and brought back online is answerable and simple.