Infrastructure at your Service

Category

Oracle

Oracle Team

Oracle multitenant dictionary: metadata links

By | Database management, Oracle | 2 Comments

By Franck Pachot . As in previous posts (1 and 2) I’m still playing with multitenant dictionary. You may wonder why I’m spending time to do unsupported things that we will never be able to use in real life. Of course, playing with internals is fun 😉 But it’s not only that. We will implement CDB databases (multi-tenant, and single-tenant as I think non-CDB will be deprecated in future versions). We will operate pluggable databases…

Read More
Oracle Team

Happy New Year 27, 104, 2015 and 2558

By | Database management, Oracle | No Comments

By Franck Pachot . calendar today tomorrow message —————– —— ——— ————– Arabic Hijrah 1436 1436 English Hijrah 1436 1436 Gregorian 2014 2015 Happy New Year Japanese Imperial 0026 0027 Happy New Year Persian 1393 1393 ROC Official 0103 0104 Happy New Year Thai Buddha 2557 2558 Happy New Year   According to Oracle calendars… SQL> l 1 with cal as ( 2 select ‘Arabic Hijrah’ name from dual union all select ‘English Hijrah’ from…

Read More
Oracle Team

Oracle multitenant dictionary: object links

By | Database management, Oracle | 3 Comments

By Franck Pachot . I’ve described Oracle 12c metadata and object links internals in a previous post. But before that, the first time I investigated on it, I made a wrong assumption because I was looking at AUDIT_ACTIONS which is not correctly implemented. That investigation came from a question on dba-village. And recently Ivica Arsov (@IvicaArsov) has made an interesting comment about AUDIT_ACTIONS object link table, so I’ll explain here what is special with it….

Read More
Oracle Team

Creating a schema synonym in Oracle – an unsupported feature

By | Database management, Oracle | 3 Comments

By Franck Pachot . 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…

Read More
Oracle Team

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

By | Database management, Oracle | No Comments

By Franck Pachot . 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…

Read More
Oracle Team

Oracle 12c: Can we disable logging for DML?

By | Database management, Oracle | No Comments

By Franck Pachot . 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:…

Read More
Oracle Team

SQL*Plus COPY Command is back as BRIDGE

By | Database management, Oracle | No Comments

By Franck Pachot . 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….

Read More
Oracle Team

Oracle 12c: comparing TTS with noncdb_to_pdb

By | Database management, Oracle | No Comments

By Franck Pachot . 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…

Read More
Oracle Team

When Oracle resets session statistics

By | Database management, Oracle | No Comments

By Franck Pachot . 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…

Read More
Oracle Team

Oracle 12c privilege analysis rocks

By | Database management, Oracle | 4 Comments

By Franck Pachot . 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…

Read More