to R1 or to R2

In the past, most of my customers skipped R1 releases. That is, 8.1.7 -> 9.2 -> 10.2 -> 11.2. SAP does the same. For the very first time SAP plans to go to + some PSU in spring 2015. But only to avoid running out of support and without any fancy feature like Multitenant… Continue reading to R1 or to R2

distinct listagg

One limitation in listagg, you cannot use DISTINCT. Okay, there are plenty of distinct-capable listagg workarounds, but to get the real listagg working, it is a bit of an headache With one listagg SELECT DEPTNO, LISTAGG (JOB, ‘,’) WITHIN GROUP (ORDER BY JOB) JOBS FROM ( SELECT DISTINCT DEPTNO, JOB FROM EMP) GROUP BY DEPTNO;… Continue reading distinct listagg

Rman backup compression

Did you know you can make your backup at least twice faster with a single line ? Demo : RMAN> backup as compressed backupset database; Starting backup at 2013-06-05_13:08:01 … Finished backup at 2013-06-05_13:13:59 6 minutes for a compressed backup on a NAS with 24 Channels and 100Gb of raw data. Not bad. But look… Continue reading Rman backup compression

TNSNAMES and Active Directory

It is highly probable you already have MS AD in your company. Probably you use a local tnsnames.ora. Apart from setting a Oracle Internet Directory or Oracle Virtual Directory, there is one more option that you may want to consider : AD. Ok, here is a bit of a road map : – Schema Extension… Continue reading TNSNAMES and Active Directory


Just installed Oracle 11gR2 patchset 2 on Solaris Sparc 64bit, it works like a charm, waiting for the AIX patchset impatiently

how to run UTL_TCP, UTL_SMTP and the like in 11g

After we upgrade a db to 11g someone complained about an ORA-24248: XML DB extensible security not installed I thought, it should be easy to revert to 10g mechanism. Probably wrong after reading Marco : The default behavior for access control to network utility packages has been changed to disallow network operations to all nonprivileged… Continue reading how to run UTL_TCP, UTL_SMTP and the like in 11g

Reduce the number of commits

“Oftentimes, a database administrator (DBA) simply looks at the symptoms and immediately starts changing the system to fix those symptoms” Op. Cit. Oracle Database Performance Tuning Guide 11g Release 2 (11.2) Ok, let’s do this 🙂 Finding Waits on event “log file sync” while performing COMMIT and ROLLBACK operations were consuming significant database time. Action… Continue reading Reduce the number of commits

Categorized as 11gR2, dba, sql

nothing in user_segments

I wrote on deferred segment creation recently. Today I was looking for specific storage attributes that I used to find in user_segments. They are no longer here. Where are they then? test case : create table t(x clob) store (x) as securefile x (retention max storage(maxsize 8192000000)); Where do I find the retention max max_size… Continue reading nothing in user_segments

my first ADR package

You got an internal error and want to create a zip of all relevant files. First, let’s generate an internal error. I found a quick way to generate an ora-600 or an ora-700 (which is a harmless ora-600 in 11g, read 737878.1) on oradeblog SQL> oradebug unit_test dbke_test dde_flow_kge_soft foo bar baz Statement processed. Now… Continue reading my first ADR package

How does random=random evaluates?

I had fun answering a question about random on the technical forums. What is in your opinion the boolean value of DBMS_RANDOM.VALUE=DBMS_RANDOM.VALUE? Or, how many rows would select * from dual where dbms_random.value=dbms_random.value; return? It is wrong to assume the function will be evaluated twice. The short answer would be : do not rely on… Continue reading How does random=random evaluates?

EZCONNECT and HOSTNAME resolution methods

EZCONNECT is the easy connect protocol, available in 10g, whenever you want to connect to a database without tnsnames and without ldap. $ grep -iw directory_path $TNS_ADMIN/sqlnet.ora names.directory_path=EZCONNECT $ sqlplus scott/tiger@//srv01:1521/db01 connect to server srv01 on port 1521 for service db01 HOSTNAME was the old-fashion way to connect to a database, where hostname = sid… Continue reading EZCONNECT and HOSTNAME resolution methods

SPARC Supercluster

Oracle buys Sun was an exciting accouncement 20 months ago. What did change in the Solaris/Oracle Database world? First, Oracle delivered Exadata on Sun Hardware (x86_64). Second, they delivered Exadata on Sun Solaris Operating System (x86_64). But now, they announced a combination of software and hardware that will run Oracle database faster than anything ever… Continue reading SPARC Supercluster

connect by and recursive with (part 2)

According to the doc The subquery_factoring_clause now supports recursive subquery factoring (recursive WITH), which lets you query hierarchical data. This feature is more powerful than CONNECT BY in that it provides depth-first search and breadth-first search, and supports multiple recursive branches. A new search_clause and cycle_clause let you specify an ordering for the rows and… Continue reading connect by and recursive with (part 2)

number series

Patrick Wolf wrote about the newest Apex release, which contains a 11.2 db engine, so I had to play with recursive queries 😉 with t(x) as (select 1 from dual union all select x+1 from t where x

11.2 solaris x86_64

Released as announced for 2009Q4… database, 11.2 for Solaris x86_64 Still Oracle is suffering with European Union Commission who is still blocking the Sun deal. google news about the deal

11.2 Sparc

Good news, go there 🙂

stragg in 11gR2

This will be a killer in the Oracle forums 😉 LISTAGG (measure_expr [, ‘delimiter_expr’]) WITHIN GROUP (order_by_clause) [OVER query_partition_clause] for instance SELECT LISTAGG(ename,’,’) WITHIN GROUP (ORDER BY ename) FROM emp; ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD

database 11gR2

Good news today, you can download Oracle Database 11g Release 2 🙂