Oracle data all table keyword search.

SET SERVEROUTPUT ON SIZE 100000;


DECLARE
  match_count INTEGER;
-- Type the owner of the tables you are looking at
  v_owner VARCHAR2(255) :='SCOTT';

-- Type the data type you are look at (in CAPITAL) VARCHAR2, NUMBER, etc.
  v_data_type VARCHAR2(255) :='VARCHAR2';

-- Type the string you are looking at
  v_search_string VARCHAR2(4000) :='%RCT2011030328%';

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 lower('||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 );
  INSERT INTO
    XSTOCKS.trn_cds_checkdatastring a ( a.str_cds_place)
    VALUES (t.table_name ||' '||t.column_name||' '||match_count);


    END IF;

  END LOOP;
END;

Oracle Long operation monitoring

Lock session

select v$session.username,
           v$lock.sid,
               id1, id2,
           lmode,
               request, block, v$lock.type , v$session.osuser , v$session.machine , v$session.process , v$process.pid
    from v$lock, v$session , v$process
    where v$lock.sid = v$session.sid
    and v$lock.type = 'TX'
    and v$process.addr = v$session.paddr
  order by username

active long operation

select username||'('||sid||','||serial#||') ospid = '|| process ||
    ' program = ' || program username,
    to_char(LOGON_TIME,' Day HH24:MI') logon_time,
    to_char(sysdate,' Day HH24:MI') current_time,
    sql_address,
    sql_hash_value , a.osuser ,a.machine  ,a.last_call_et   
   from v$session a
   where status = 'ACTIVE'
   and rawtohex(sql_address) <> '00'
   and username is not null
   order by last_call_et desc

Long opseration details

 select  distinct x.sid , a.machine  , x.opname , a.program  , x.target , x.sofar ,x.totalwork , x.start_time , x.last_update_time ,x.time_remaining , x.elapsed_seconds , context , message,
           sql_text
  from v$session_longops x , gv$sqlarea y , v$session a
  where x.username = 'SYS'
  and x.sql_hash_value = y.HASH_VALUE
  and a.sid = x.sid
  and a.status = 'ACTIVE'
  order by TIME_REMAINING desc

oracle dbms schedulers

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'TEST_SHEDULE',
job_type => 'STORED_PROCEDURE',
job_action => 'DPG.SP_SHEDULE_TEST',
start_date => to_date('26/05/2011 13:38:00' , 'DD/MM/RRRR HH24:MI:SS' ),
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2;',
comments => 'My new job');
END;
/
DECLARE
BEGIN
DBMS_SCHEDULER.ENABLE('TEST_SHEDULE');
END;
-------------------------------------------------------------
EXEC dbms_scheduler.run_job('TEST_SHEDULE');
-------------------------------------------------------------
EXEC DBMS_SCHEDULER.DROP_JOB('my_java_job');
-------------------------------------------------------------
SELECT * FROM dba_scheduler_jobs  where job_creator = 'DPG' order by job_name

How to create a yum repository on RHEL/CentOS 5.x with CD/DVD or ISO images

Mount your CD/DVD or ISO images

DVD Disk or DVD ISO image

• If you have DVD disk, please mount dvd-rom first, and then create yum repository:

# mkdir /mnt/dvd/
# mount /dev/cdrom /mnt/dvd/

• If you use DVD iso, please copy it to the system, and then create yum repository:

# mkdir /mnt/dvd/
# mount -o loop /root/rhel5.1-dvd.iso /mnt/dvd

CD images

If you have multiple CD image files, you should mount all iso images and then create yum repository.

• Mount all iso images:

# mkdir -p /mnt/{1,2,3,4,5}
# mount -o loop rhel5.1-disc1.iso /mnt/1
# mount -o loop rhel5.1-disc2.iso /mnt/2
# mount -o loop rhel5.1-disc3.iso /mnt/3
# mount -o loop rhel5.1-disc4.iso /mnt/4
# mount -o loop rhel5.1-disc5.iso /mnt/5

Install necessary package

• Find and install 'createrepo' package in /mnt directory:

# find /mnt -iname 'createrepo*'
/mnt/dvd/Server/createrepo-0.4.11-3.el5.noarch.rpm

# rpm -ivh /mnt/dvd/Server/createrepo-0.4.11-3.el5.noarch.rpm

Create yum repository

Create metadata

• Create yum repository:

# cd /mnt/
# createrepo .

Define yum repository

Create yum repository define file /etc/yum.repos.d/dvdiso.repo:

[MailRepo]
name=MailRepo
baseurl=file:///mnt/
enabled=1
gpgcheck=0

Test it

# yum clean all
# yum list

If 'yum list' list all packages in DVD/CD disks or ISO images, it works. :)

Send Email using mysql trigger

CREATE TRIGGER dbo.whatever ON dbo.wherever
FOR INSERT AS BEGIN SET NOCOUNT ON;    

IF EXISTS (SELECT 1 FROM inserted WHERE speed > 100) BEGIN EXEC msdb.dbo.sp_send_dbmail @to = 'whoever@yourcompany.com',          
                                            @profile_name = 'default',          
                                            @subject = 'Someone was speeding',          
                                            @body = 'Yep, they sure were.';    
END END GO