How to keep Source code creation History

How to keep Source code creation History
==========================================

CREATE TABLE halim.SOURCE_HIST
(
CHANGE_DATE DATE NULL,
NAME VARCHAR2(30 BYTE) NULL,
TYPE VARCHAR2(12 BYTE) NULL,
LINE NUMBER NULL,
TEXT VARCHAR2(4000 BYTE) NULL
)


CREATE OR REPLACE TRIGGER change_hist
AFTER CREATE ON halim.SCHEMA
DECLARE
BEGIN
IF ora_dict_obj_type IN
('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TYPE')
THEN
INSERT INTO source_hist
SELECT SYSDATE, user_source.*
FROM user_source
WHERE TYPE = ora_dict_obj_type AND NAME = ora_dict_obj_name;
END IF;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20000, SQLERRM);
END;
/


SELECT DISTINCT change_date, NAME, TYPE
FROM source_hist
ORDER BY change_date DESC

who are waiting for same record?

who are waiting for same record?
====================================
Solutions:
=========
1.

SELECT * FROM V$SESSION
WHERE trim(row_wait_obj#||row_wait_file#||row_wait_block#||row_wait_row#) =
(SELECT trim(row_wait_obj#||row_wait_file#||row_wait_block#||row_wait_row#)
FROM (
-------------------------------------------
select sid,d.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) rowidd
from v$session s, dba_objects d
where
s.ROW_WAIT_OBJ# = d.OBJECT_ID
and owner='STLBAS'
and OBJECT_TYPE='TABLE'
and sid=:SID
-------------------------------------------
))


select * from test ---object_name
where rowid='AAARJHAAGAACd13AAA'


2. Any rows returned are direct evidence of the problem
=======================================================

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request


3. can also give interesting information about session waiting for lock.
=======================================================================

$ORACLE_HOME/rdbms/admin/utllockt.sql



4. Script to find locks in the database
=======================================

select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

efficiant way to check the table is empty

----very efficiant way to check the table is empty
--or
-----checking the first rows value
=====================================================
---empno is primary_key column
--or
---empno is index column

select /*+ FIRST_ROWS */ empno from emp where rownum = 1;

use Ping utility from plsql function

CREATE OR REPLACE FUNCTION ping (
p_host_name VARCHAR2,
p_port NUMBER DEFAULT 1000
)
RETURN VARCHAR2
IS
tcpconnection UTL_TCP.connection;
c_ping_ok CONSTANT VARCHAR2 (10) := 'OK';
c_ping_error CONSTANT VARCHAR2 (10) := 'ERROR';
BEGIN
tcpconnection :=
UTL_TCP.open_connection (remote_host => p_host_name,
remote_port => p_port
);
UTL_TCP.close_connection (tcpconnection);
RETURN c_ping_ok;
EXCEPTION
WHEN UTL_TCP.network_error
THEN
IF (UPPER (SQLERRM) LIKE '%HOST%')
THEN
RETURN c_ping_error;
ELSIF (UPPER (SQLERRM) LIKE '%LISTENER%')
THEN
RETURN c_ping_ok;
ELSE
RAISE;
END IF;
END ping;




SELECT ping ('10.11.1.254', 1000)
FROM DUAL

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');