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

& 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.

Manually configure Enterprise Manager Console

Run emca -config dbcontrol db and follow the instructions.

Redo log size & performance

We got a performance issue with our Oracle 10g. Suddenly the performance was going down and we was getting a significant increase on wait times. After sniffing around and asking a friend we found that:

1. All our performance pick were related with time when our Oracle was writing the archive log.
2. There is a direct relationship between log_buffer, redo log file size and performance.

So, here is a tip. Your redo log file size must be multiple by 2 of your log_buffer. What I mean is that if your log_buffer is 5 MB your redo log file size can be 10 MB (2 x 5 MB), 20 MB (4 x 5 MB), 30 MB (6 x 5 MB) and so on...

Show archive logfiles sequence

Quick SQL to see if your archive log had been applied:

 

SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Manually register new archive log

If you need to register archive log in Oracle by hand:

 

alter database register logfile '/some_direcotry/some_file.arc';