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

Java ArrayList Example

import java.util.ArrayList;

public class ArrayListDemo {
   public static void main(String[] args) {
      
      // create an empty array list with an initial capacity
      ArrayList<Integer> arrlist = new ArrayList<Integer<(8);

      // use add() method to add elements in the list
      arrlist.add(20);
      arrlist.add(25);
      arrlist.add(10);
      arrlist.add(15);        
       
      // let us print all the elements available in list
      for (Integer number : arrlist) {
         System.out.println("Number = " + number);
       }

      // list contains element 10
      boolean retval = arrlist.contains(10); 
   
      if (retval == true) {
        System.out.println("element 10 is contained in the list");
       }
      else {
        System.out.println("element 10 is not contained in the list");
       }
  
      // list does not contain element 30
      boolean retval2 = arrlist.contains(30);
   
      if (retval2 == true) {
        System.out.println("element 30 is contained in the list");
       }
      else {
        System.out.println("element 30 is not contained in the list");    
       }
   }
}

Let us compile and run the above program, this will produce the following result:

Number = 20
Number = 25
Number = 10
Number = 15
element 10 is contained in the list
element 30 is not contained in the list

send e-mail configuration using Access list to Network Services in Oracle Database 11g

•acl - The name of the access control list XML file, generated relative to the "/sys/acls" directory in the XML DB Repository.
•description - A description of the ACL.
•principal - The first user account or role being granted or denied permissions. The text is case sensitive.
•is_grant - TRUE to grant, FALSE to deny the privilege.
•privilege - Use 'connect' for UTL_TCP, UTL_SMTP, UTL_MAIL and UTL_HTTP access. Use 'resolve' for UTL_INADDR name/IP resolution. The text is case sensitive.
•start_date - Default value NULL. When specified, the ACL will only be active on or after the specified date.
•end_date - An optional end date for the ACL.


CONN sys/password@db11g AS SYSDBA

CREATE USER test1 IDENTIFIED BY test1;
GRANT CONNECT TO test1;

CREATE USER test2 IDENTIFIED BY test2;
GRANT CONNECT TO test2;

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'test_acl_file.xml',
    description  => 'A test of the ACL functionality',
    principal    => 'TEST1',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  COMMIT;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.add_privilege (
    acl         => 'test_acl_file.xml',
    principal   => 'TEST2',
    is_grant    => FALSE,
    privilege   => 'connect',
    position    => NULL,
    start_date  => NULL,
    end_date    => NULL);

  COMMIT;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.delete_privilege (
    acl         => 'test_acl_file.xml',
    principal   => 'TEST2',
    is_grant    => FALSE,
    privilege   => 'connect');

  COMMIT;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.drop_acl (
    acl         => 'test_acl_file.xml');

  COMMIT;
END;
/


•acl - The name of the access control list XML file.
•host - The hostname, domain, IP address or subnet to be assigned. Hostnames are case sensitive, and wildcards are allowed for IP addresses and domains.
•lower_port - Defaults to NULL. Specifies the lower port range for the 'connect' privilege.
•upper_port - Defaults to NULL. If the lower_port is specified, and the upper_port is NULL, it is assumed the upper_port matches the lower_port.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'test_acl_file.xml',
    host        => '192.168.2.3',
    lower_port  => 80,
    upper_port  => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'test_acl_file.xml',
    host        => '10.1.10.*',
    lower_port  => NULL,
    upper_port  => NULL);

  COMMIT;
END;
/