Oracle internals, debugging and undocumented features

Category Archives: New Features

Querying multiple rows from dual

By Granville Bonyata

Sometimes it’s useful to have a query that always returns a certain number of rows, such as a report that returns one row for every day of the month. Using DUAL and CONNECT BY allows you to return as many rows as needed. For instance, this query returns one row for every day of the month:

SELECT TO_DATE(level||’-‘||TO_CHAR(SYSDATE,’MON-YYYY’)) each_day from dual connect by level <= TO_CHAR(LAST_DAY(SYSDATE),’DD’) ORDER BY 1;

SQL> /

EACH_DAY                                                                        ———                                                                       01-JAN-12                                                                       02-JAN-12                                                                       03-JAN-12                                                                       04-JAN-12                                                                       05-JAN-12                                                                       06-JAN-12                                                                       07-JAN-12                                                                       08-JAN-12                                                                       09-JAN-12                                                                       10-JAN-12                                                                       11-JAN-12                                                                       12-JAN-12                                                                       13-JAN-12                                                                       14-JAN-12                                                                       15-JAN-12                                                                       16-JAN-12                                                                       17-JAN-12                                                                       18-JAN-12                                                                       19-JAN-12                                                                       20-JAN-12                                                                       21-JAN-12                                                                       22-JAN-12                                                                       23-JAN-12                                                                       24-JAN-12                                                                       25-JAN-12                                                                       26-JAN-12                                                                       27-JAN-12                                                                       28-JAN-12                                                                       29-JAN-12                                                                       30-JAN-12                                                                       31-JAN-12

31 rows selected.


Mr Trace

Mr Trace  speaks for itself.

Cary Millsap and the and Method R created a great add-in to Oracle SQL Developer.

It’s called Method R Trace .When you run your PL/SQL code, it automatically turns tracing on and off at the right times, fetches the trace file for you, and presents it in a list right there on the screen.

You can find demo with Cary .

and more info at Cary’s blog:


Creating using DBCA

DBCA needs to include  sizes in G Bytes  for Fast Recovery Area.Mb are too small these days..

If you pick Automatic Memory Management   DBCA  will stop you from using more memory than it is  size of    tmpfs   /dev/shm.That is a good feature but

although I specified correct amount of memory database creation failed.After looking logs at cfgtools/dbca   I found that pfile was set to use for about few gigabyte higher. It seems that dbca will add  2 Gb extra.

Also  DBCA will not let you to create more than two controlfiles. I even tried using custom template.Same result.

After install I noticed that in  11.2 GSD is disabled by default and the service will show as target:offline, status:offline.

The crsctl command has cluster option so you  can stop / start all cluster using crsctl command  e.g.    crsctl stop cluster -all

Oracle Patch Set – New feature and change

Oracle Grid Infrastructure introduces new feature called Redundant Interconnect
allowing for Oracle Supplied redundancy for the cluster interconnect.Redundant Interconnect Usage
enables load-balancing and high availability across multiple (up to 4) private networks (also known as interconnects).Redundant Interconnect Usage creates a redundant interconnect when you identify more than one interface as private.

With this new feature, multicast network communication on the private interconnect network is utilized on bootstrap to establish communication with peer nodes in the cluster, once communication is established network communication is then switched to unicast.
This mulitcast communication utilizes the address (port 42424) on the private interconnect network.Therefore multicast on the private interconnect network must be enabled and properly
functioning on all cluster nodes for the mulitcast address (port 42424).
Should multicast communication fail, the end result will be the inability for the node to join the cluster.

Starting from onwards an I/O write error to a datafile will now crash the instance.

Before this fix I/O errors to datafiles not in the system tablespace
offline the respective datafiles when the database is in archivelog mode.

Oracle indroduced  new hidden parameter  _datafile_write_errors_crash_instance  to control instance crash on I/O error.

Default is true .

So if you decide to keep old setting set this parameter to false.