Search a string/number/date value in your schema in oracle

Search a string/number/date value in your schema
-----------------------------------------------
------------------------------------------------

1. Search Proceduree
---------------------

CREATE OR REPLACE PROCEDURE search_db_test (
p_search VARCHAR,
p_type VARCHAR
)
IS
TYPE tab_name_arr IS VARRAY (10000) OF VARCHAR2 (256);

v_tab_arr1 tab_name_arr;
v_col_arr1 tab_name_arr;
v_amount_of_tables NUMBER (10);
v_amount_of_cols NUMBER (10);
v_search_result NUMBER (10);
v_result_string VARCHAR2 (254);
BEGIN
v_tab_arr1 := tab_name_arr ();
v_col_arr1 := tab_name_arr ();
v_col_arr1.EXTEND (1000);

SELECT COUNT (table_name)
INTO v_amount_of_tables
FROM user_tables;

v_tab_arr1.EXTEND (v_amount_of_tables);

FOR i IN 1 .. v_amount_of_tables
LOOP
SELECT table_name
INTO v_tab_arr1 (i)
FROM (SELECT ROWNUM a, table_name
FROM user_tables
ORDER BY table_name)
WHERE a = i;
END LOOP;

FOR i IN 1 .. v_amount_of_tables
LOOP
SELECT COUNT (*)
INTO v_amount_of_cols
FROM user_tab_columns
WHERE table_name = v_tab_arr1 (i) AND data_type = p_type;

IF v_amount_of_cols <> 0
THEN
FOR j IN 1 .. v_amount_of_cols
LOOP
SELECT column_name
INTO v_col_arr1 (j)
FROM (SELECT ROWNUM a, column_name
FROM user_tab_columns
WHERE table_name = v_tab_arr1 (i) AND data_type = p_type)
WHERE a = j;

IF p_type IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2')
THEN

EXECUTE IMMEDIATE 'select count(*) from '
|| v_tab_arr1 (i)
|| ' where lower('
|| v_col_arr1 (j)
|| ') like '
|| ''''
|| '%'
|| LOWER (p_search)
|| '%'
|| ''''
INTO v_search_result;
END IF;

IF p_type IN ('DATE')
THEN
EXECUTE IMMEDIATE 'select count(*) from '
|| v_tab_arr1 (i)
|| ' where '
|| v_col_arr1 (j)
|| ' = '
|| ''''
|| p_search
|| ''''
INTO v_search_result;
END IF;

IF p_type IN ('NUMBER', 'FLOAT')
THEN
EXECUTE IMMEDIATE 'select count(*) from '
|| v_tab_arr1 (i)
|| ' where '
|| v_col_arr1 (j)
|| ' = '
|| p_search
INTO v_search_result;
END IF;

IF v_search_result > 0
THEN
v_result_string := v_tab_arr1 (i) || '.' || v_col_arr1 (j);

EXECUTE IMMEDIATE 'insert into search_db_results values ('
|| ''''
|| v_result_string
|| ''''
|| ')';

COMMIT;
END IF;
END LOOP;
END IF;
END LOOP;
END;
/

2. need to create this table first
===================================

CREATE TABLE SEARCH_DB_RESULTS ( RESULT VARCHAR2(1024))

3. execute statements
=========================

exec search_db_test(999,'NUMBER')

exec search_db_test('iqbal','VARCHAR2')

exec search_db_test('halim','VARCHAR2')--string in lower case

exec search_db_test('12-Jan-10','DATE')

exec search_db_test(1000,'NUMBER')


4. find the output
========================

select * from SEARCH_DB_RESULTS


==========================More fast one is ============================
Author:- Tom kyte

CREATE OR REPLACE PROCEDURE basel2.find_string (p_str IN VARCHAR2)
AUTHID CURRENT_USER
AS
l_query LONG;
l_case LONG;
l_runquery BOOLEAN;
l_tname VARCHAR2 (2000);
l_cname VARCHAR2 (4000);

TYPE rc IS REF CURSOR;

l_cursor rc;
BEGIN
DBMS_APPLICATION_INFO.set_client_info ('%' || UPPER (p_str) || '%');

FOR x IN (SELECT *
FROM user_tables)
LOOP
l_query :=
'select distinct '''
|| x.table_name
|| ''', $$
from '
|| x.table_name
|| '
where ( 1=0 ';
l_runquery := FALSE;
l_case := NULL;

FOR y IN (SELECT *
FROM user_tab_columns
WHERE table_name = x.table_name
AND data_type IN ('VARCHAR2', 'CHAR')) ----you add here more datatype
LOOP
l_runquery := TRUE;
l_query :=
l_query
|| ' or upper('
|| y.column_name
|| ') like userenv(''client_info'') ';
l_case :=
l_case
|| '||'' ''|| case when upper('
|| y.column_name
|| ') like userenv(''client_info'') then '''
|| y.column_name
|| ''' else NULL end';
END LOOP;

IF (l_runquery)
THEN
l_query := REPLACE (l_query, '$$', SUBSTR (l_case, 8)) || ')';

BEGIN
OPEN l_cursor FOR l_query;

LOOP
FETCH l_cursor
INTO l_tname, l_cname;

EXIT WHEN l_cursor%NOTFOUND;
DBMS_OUTPUT.put_line ('Found in ' || l_tname || '.' || l_cname);
END LOOP;

CLOSE l_cursor;
END;
END IF;
END LOOP;
END;
/

--grant execute on find_string to public;


exec basel2.find_string ('halim');

How to fine User name of current session

User name of current session
===============================

SQL> SELECT USERNAME
from v$session
where audsid = sys_context('userenv','sessionid');

How to get Client ip address in oracle

How to get client ip address in oracle
=====================================

1. For Client IP Address

SELECT SYS_CONTEXT ('userenv', 'ip_address')
FROM DUAL

How to get server ip address in oracle

how to get server ip address in oracle
================================
1. For IP Address

SELECT UTL_INADDR.get_host_address
FROM DUAL

2. For Host Name

SELECT UTL_INADDR.get_host_name
FROM DUAL

3. For Domain and host name

SELECT sys_context('USERENV', 'HOST')
FROM dual;

4. For host name

SELECT sys_context('USERENV', 'SERVER_HOST')
FROM dual;

materialized view Refresh

About materialized view Refresh
=============================

As I find, Materialized View create and ("atomic_refresh => FALSE") refresh is faster and more logical Then create table ,truncate and direct insert.

about materialized view works
=================
1. create a MV with "complete refresh on demand" (means it refresh when we want, not automatic)
2. when we refresh that MV (ON DEMAND REFRESH MODE) then
• ON DEMAND – refreshes are initiated manually using one of the procedures in the DBMS_MVIEW package
• Can be used with all types of materialized views
• Manual Refresh Procedures
• DBMS_MVIEW.REFRESH(, )

• When refresh_option use ("atomic_refresh => TRUE") its means DELETE operation is used to empty the table.
( in this time if job is broken then rollback the transaction and MV remain useable)
• When refresh_option use ("atomic_refresh => FALSE") its means TRUNCATE operation is used to empty the table.
( in this time if job is broken then MV don't remain useable state before another refresh)

• AND we can reduce the LOG generation by creating MV with nologging mode.

Ultimately
================
Oracle performs the following operations when refreshing a materialized view. In the case of a complete refresh (using dbms_mview.refresh)
1. sys.snap$ and sys.mlog$ are updated to reflect the time of the refresh.
2. The materialized base view is truncated/delete.
3. All rows selected from the master table are inserted into the snapshot base table.
4. sys.slog$ is updated to reflect the time of the refresh.


• N.B :- I have attached a “trace file .txt” file. Just search in this file with MV_STTRAILB_038 and find the prob.