Infrastructure at your Service

All posts by Franck Pachot

Franck Pachot
Technology Leader
Principal Consultant

OCM 12cFranck Pachot has 20 years of experience in Oracle databases. Through his expertise as a DBA, Oracle expert, data architect, and performance specialist, he is able to cover all database areas: architecture, data modeling, database design, tuning, operation, and training. Franck knows how to enable an efficient collaboration between the developers and the operational team when it comes to troubleshooting issues or performance tuning.

RSSOracle ACEOracle ACE Franck is Oracle Certified Master (OCM 12c), Oracle ACE Director and proud member of Oraworld-team and Oak Table.

Next public appearance:
C15LV
Las Vegas (US), Apr 25th

From Transportable Tablespaces to Pluggable Databases
12.2 Multitenant New Security Features to Clarify DevOps and DBA role separation
DOAGDB18
Düsseldorf (DE) , May 14th


12cR2/18c security features to clarify DBA role in DevOps
POUG
Sopot (PL), Sept. 7th

Join Methods: Nested Loop, Hash, Sort, Merge, Adaptive


Franck Pachot

Franck Pachot: Creating a schema synonym in Oracle – an unsupported feature

By | Database management, Oracle | 3 Comments

Ivica Arsov (@IvicaArsov) has made an interesting comment about AUDIT_ACTIONS object link table. I’ll blog about it soon, but in the meantime when checking its definition in cataudit.sql it came upon the following: /* SCHEMA SYNONYMS will be added in 12g */ — insert into audit_actions values (222, ‘CREATE SCHEMA SYNONYM’); — insert into audit_actions values (224, ‘DROP SCHEMA SYNONYM’); which caught my attention. So this blog post is not about mulitenant but about Schema…

 
Read More
Franck Pachot

Franck Pachot: Oracle lateral inline view, cursor expression and 12c implicit statement result

By | Database management, Oracle | No Comments

I’ll present here 3 ways to run a query for each result of another query. Let’s take an exemple: get all executions plan (select from dbms_xplan.display_cursor) for each of my queries (identified from v$sql). The 90’s way was to run the first query, which generates the second queries into a spool file, and execute that file. Here are easier ways, some of them coming from 12c new features lateral join and implicit statement result. Test…

 
Read More
Franck Pachot

Franck Pachot: Oracle 12c: Can we disable logging for DML?

By | Database management, Oracle | No Comments

If we don’t mind about loosing our changes, then can we disable logging for DML? This is a question I’ve heard a lot. Ok, you don’t need to recover your changes but Oracle may want to recover the consistency of its datafiles anyway. And that’s why datafiles blocks changed though the buffer cache always generate redo. But yes, in 12c you can do DML and generate only minimal redo. All DML: even updates and deletes….

 
Read More
Franck Pachot

SQL*Plus COPY Command is back as BRIDGE

By | Database management, Oracle | No Comments

Did you ever use the COPY command in sqlplus? It’s very old, and documentation says : The COPY command is not being enhanced to handle datatypes or features introduced with, or after Oracle8i. The COPY command is likely to be deprecated in a future release. Deprecated? But it is back, with a new name, in the new SQL Developer based SQL*Plus (currently called sdsql in beta) SQL*Plus COPY Documentation is here. Let’s show how to…

 
Read More
Franck Pachot

Oracle 12c: comparing TTS with noncdb_to_pdb

By | Database management, Oracle | No Comments

How to migrate from non-CDB to CDB? Of course all known migration methods works. But there is also another solution: upgrade to 12c if necessary and then convert the non-CDB to a PDB. This is done with the noncdb_to_pdb.sql which converts a non-CDB dictionary to a PDB one, with metadata and object links. But do you get a clean PDB after that ? I tested it and compared the result with same database migrated by…

 
Read More
Franck Pachot

When Oracle resets session statistics

By | Database management, Oracle | No Comments

During our Oracle 12c New Features workshop I had a very good question about whether the session statistics are reset or not when doing ALTER SESSION SET CONTAINER. My initial thought was that they were not reset because it’s the same session (same SID and SERIAL#). But when I’m not 100% sure about something, I test it. And once again, it proves that even the instructor can learn something new when giving a workshop, thanks…

 
Read More
Franck Pachot

Oracle 12c privilege analysis rocks

By | Database management, Oracle | 4 Comments

12c came with a very nice feature: privilege analysis. You don’t know which privileges are required? then just grant DBA, run your application, and check which minimal privileges are needed. And today, I’ve discovered how it is very powerful: you can even see privileges used internally, even when not done by SQL, and even not documented. It starts like that, with a question from Vladimir Sitnikov (who publishes very interesting stuff from his twitter account)…

 
Read More
Franck Pachot

Oracle locks: Identifying blocking sessions

By | Database management, Oracle | 5 Comments

When you have sessions blocked on locks, you probably have all information about the waiters (they call you and anyway their waiting session is visible in v$session our ASH). But you usually need to get enough information that help to identify the blocker. Here is a query I use to get that quickly, based on V$WAIT_CHAINS Here is the result I want to get: session wait event minutes USER PRO ———————– ———————————————– ——- —- —…

 
Read More
Franck Pachot

Franck Pachot: My planning for DOAG 2014

By | Database management, Oracle | No Comments

I’m quickly checking the planning for DOAG these 3 days and here is the list of sessions I would like to attend. There are still a lot of interesting ones even if my choice is limited by the language (I would like to understand German but I’m limited so sessions in English). And I’ve still some concurrency issues to solve because I cannot be at two places at the same time. There are some few…

 
Read More
Franck Pachot

12c SQL Plan Directive: state has changed between 12.1.0.1 and 12.1.0.2

By | Database management, Oracle | 9 Comments

SQL plan Directives have been introduced in 12c. If you have scripts that check their states (and I have that as I prefer to monitor closely the features that are new) you probably have seen a difference when going from 12.1.0.1 – the first release of 12c and the only one avilable yet in Standard Edition – and 12.1.0.2 – the first patchest. I’ll explain here what are the SQL Plan Directive states and how…

 
Read More