SQL Server Transactions

 Transaction Property
1-Atomicity

2- Consistency
3- Isolation

4- Durable

Transaction Modes-
1- AutoCommit Transaction
2- Implicit  

SET IMPLICIT_TRANSACTIONS ON

3- explicit 

BEGIN TRAN

ROLLBACK TRANSACTION

COMMIT TRAN

oRCLE HOW TO SERACH A TEXT IN ALL TABLES AND ALL COLUMN

SET SERVEROUTPUT ON SIZE 100000

DECLARE match_count INTEGER;

    v_owner all_tab_cols.owner%type  := 'NAUCRM';

    v_data_type all_tab_cols.data_type%type :='VARCHAR2';

    v_search_string VARCHAR2(4000) := 'Himanshu';

BEGIN

    FOR t IN (SELECT a.table_name, a.column_name

        FROM all_tab_cols a inner join total_tables_cba_COUNT_total b on a.table_name=b.Table_name

        WHERE  data_type in ('VARCHAR2') 

        and record_count<112000  and record_count>1) 

        LOOP

        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||t.table_name

            ||' WHERE '||t.column_name||' = :1'

       INTO match_count

       USING 'Tri-cycle Fleet';

       IF match_count > 0 THEN

       insert into SEARCH_TABLE values(t.table_name,t.column_name);

       END IF;

    END LOOP;

END;

select distinct table_name from SEARCH_TABLE

delete from SEARCH_TABLE

Oracle All tables and their record count script

 DECLARE 

v_owner varchar2(40); 

v_table_name varchar2(40); 

cursor get_tables is 

select distinct table_name

from total_tables

where is_insert = 1; 

begin 

open get_tables; 

loop

    fetch get_tables into v_table_name; 

    EXIT WHEN get_tables%NOTFOUND;

    execute immediate 'INSERT INTO table_count(TABLE_NAME,count_number) 

    SELECT ''' || v_table_name || ''' , COUNT(*)  FROM ' || v_table_name; 

end loop;

CLOSE get_tables; 

END; 

insert into total_tables_cba(table_name) 

 

 select table_name from all_tables where owner='LURLIVE'

CREATE TABLE total_tables_cba_COUNT_total (

  table_name   VARCHAR2(100) NOT NULL,

  record_count   NUMBER  NULL

);

 select * from total_tables_cba

 insert into total_tables_cba(table_name) 

 select * from total_tables_cba

 select * from  total_tables_cba_COUNT_total

 delete from total_tables_cba_COUNT_total

DECLARE 

v_owner varchar2(40); 

v_table_name varchar2(40); 

cursor get_tables is 

select table_name

from total_tables_cba

where is_insert = 2; 

begin 

open get_tables; 

loop

    fetch get_tables into v_table_name; 

    EXIT WHEN get_tables%NOTFOUND;

    execute immediate 'INSERT INTO total_tables_cba_COUNT_total(TABLE_NAME,record_count) 

    SELECT ''' || v_table_name || ''' , COUNT(*)  FROM ' || v_table_name; 

end loop;

CLOSE get_tables; 

END;