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

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 | Cost (%CPU)| Time     | […]

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

Do not upgrade to

If you do run @?/rdbms/admin/catupgrd for an Oracle Home on a, you may later realize some objects are missing (probably related to deferred segment creation). SQL>   delete from t1   2    where id in (   3      select ca.id from ca, p   4      where p.no_form like '%02.98'   5      and p.id = ca.prod_id   6    );   delete from t1               * ERROR at […]

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

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

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