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 […]

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; […]

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 […]

New environment for OCM 11g

For my readers who are preparing the ocm 11g exam, the environment just changed (From 13th May 2013 onwards) Instead of using OEM 10g, you will be using OEM 11g. The upgrade exam is still using OEM 10g and DB 11gR1 (!) but I did not care installing OEM 10g and I prepared with OEM […]

On deferred segment creation and truncate

One year ago, I wrote about a side effect of deferred segment creation and drop tablespace : on deferred segment creation Today I discoved one more side effect : In the good old days (I read once that you are old as soon as you start talking about the good old days) and according to […]

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 […]

_optimizer_random_plan parameter

I was trying to find a workaround for a bug in SELECT * FROM (SELECT 2 B FROM DUAL WHERE DUMMY = ‘Y’), (SELECT 3 C FROM DUAL WHERE DUMMY LIKE ‘%’) WHERE C = B(+); B C ———- ———- 2 3 ————————————————————————— | Id | Operation | Name | Rows | Bytes | […]

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 […]

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 […]

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 […]

On deferred segment creation

What’s deferred segment creation? It is a feature that saves lots of time by releases and lots of space if you have a -legacy- application with 10’000 tables and most of them are empty. When was it introduced ? Partly in and partly in, depending on the object type. What’s the opposite ? […]

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 […]

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 […]

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 […]

Return NULL if the column does not exist

It is a very common challenge for a dba to create scripts that work on every version. How do you return NULL if a column does not exists? Imagine I have a view that returns the table_name, column_name and retention_type of my LOBS. SQL> create table t1(c clob) lob(c) store as (retention); Table created. SQL> […]

RMAN duplicate does change your DB_NAME !

I had a very serious issue last Friday with errors as weird as ORA-00322: log name of thread num is not current copy. After a clone from Prod to Test, the prod crashed. Both databases are located on the same server (I am not a virtualization fanatic) and clone from prod to test have been […]

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 […]

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 […]

CONNECT BY and Recursive CTE

11gR2 introduced a new mechanism to build up hierarchies. I remembered a thread in that reveals the dubious implementation of nocycle in 10g. For the CONNECT BY ISLEAF, I have read the technique on Ok, here is my graph The 10g query with o as ( SELECT ‘A’ obj, ‘B’ link from dual […]

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