Wednesday, June 25, 2008

Creating a copy of the disk (vmdk) in VMware

When you need to have the a copy of the disk from one VM on the other:

  • Power off  source VM, copy your .vmdk files to destination VM (probably you would like to change disk name and flat name - just rename your files and edit smaller .vmdk file to set correct name for flat file).
  • Set new UUID for your copy of your original .vmdk file
/usr/sbin/vmkfstools -J setuuid path_to_destination_VM/filename.vmdk
  • Add new disk to your destination VM (using "Use an existing virtual disk" option)
  • Start your VMs (source and destination)
  • Mount new drive on your destination machine
We used this solution to make a quick copy of data files disks from primary Oracle server to standby Oracle server.

Monday, June 16, 2008

Oracle analog of MySQL GROUP_CONCAT function

I was looking for Oracle analog of MySQL GROUP_CONCAT function. There is a solution on Ask Tom site. So here it is:


create or replace type string_agg_type as object (
  total varchar2(4000),

  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type )
  return number,

  member function ODCIAggregateIterate(self IN OUT string_agg_type, value IN varchar2 )
  return number,

  member function ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT varchar2, flags IN number)
  return number,

  member function ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
  return number
);
/

create or replace type body string_agg_type is
  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
  return number
  is
  begin
    sctx := string_agg_type( null );
    return ODCIConst.Success;
  end;

  member function ODCIAggregateIterate(self IN OUT string_agg_type, value IN varchar2 )
  return number
  is
  begin
    self.total := self.total || ',' || value;
    return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT varchar2, flags IN number)
  return number
  is
  begin
    returnValue := ltrim(self.total,',');
    return ODCIConst.Success;
  end;

  member function ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
  return number
  is
  begin
    self.total := self.total || ctx2.total;
    return ODCIConst.Success;
  end;
end;
/

create or replace function stragg(input varchar2)
return varchar2
parallel_enable aggregate using string_agg_type;
/

After this you can use this function like this:
select t1.id, stragg(t2.name) as names from table1 t1, table2 t2 where t1.id=t2.id group by t1.id;

It will produce comma separated list of the names - something like this:
ID NAMES
-- ------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD