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.

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

List number of file handles (open files) for each process

Here is a simple script that will show you a number of file handles (open files) used by each process on your Linux system:

ps -e|grep -v TTY|awk {'print "echo -n \"Process: "$4"\tPID: "$1"\tNumber of FH: \"; lsof -p "$1"|wc -l"'} > out; . ./out

& in Oracle insert or update statements

If you have & or % in your Oracle insert or update statements - you probably would like to do the following:

SQL> set define off

SQL> [your insert or update query containing & and %]

SQL> set define on

Export from Oracle table to CSV file

Sometimes you need to export result of the query to CSV file. Here is a nice example of how to do this kind of operations.

SQL> SET LINESIZE 500 FEEDBACK OFF TRIMSPOOL ON TERMOUT OFF HEAD OFF PAGESIZE 0 TERM OFF

SQL> spool outfile.csv

SQL> select '"'|| column_1 || '",' || column_2 || ',' || column_3 from mytable where [your where statement]

...

...

SQL> spool off

SQL> exit

In this case column_1 data will be surrounded by "" (you may need this if you have , character in field data).

Update: SET LINESIZE determines a maximum number of characters in the line. So if you expect output bigger than 500 lines you must set this to a bigger value.

Cent OS 5.1 + Oracle 10g

Apart of modifying /etc/redhat-release to "redhat-4", there are a couple of packages that you will need to install in order to make Oracle 10g work with Cent OS 5.1:

yum install \

compat-db-4.2.52-5.1 \

glibc-devel-2.5-18.el5_1.1 \

libX11-devel-1.0.3-8.0.1.el5 \

libXau-devel-1.0.1-3.1 \

libXdmcp-devel-1.0.1-2.1 \

libXmu-1.0.2-5 \

libXtst-devel-1.0.1-3.1 \

mesa-libGL-devel-6.5.1-7.5.el5 \

openmotif-2.3.0-0.3.el5 \

xorg-x11-proto-devel-7.1-9.el5.centos

Mac OS & Database development

Since switching to Mac I was looking for good DB modeling/editing tool that will support Oracle and MySQL databases. Finally I got one...

 

SQL4X Manager J from dbSuite

 

Seems to be a really nice tool.

Leopard terminal and Midnight Commander

If you use mc while connected to some linux server and you need to select some files - use Ctrl+T

Perl: CPAN or RPM packages?

Maybe this is just CentOS 5.1 related issue, but...

 

For those of you who will install MailScanner - just a small tip to avoid problems. Spend some time and install required perl modules from CPAN. This will save you some time on fixing SpamAssassin and some other perl modules.

Manually configure Enterprise Manager Console

Run emca -config dbcontrol db and follow the instructions.