Skip navigation.

The Oracle Instructor

Syndicate content The Oracle Instructor
Explain, Exemplify, Empower
Updated: 9 hours 7 min ago

DOAG annual conference 2015 – great as always!

Fri, 2015-11-27 04:42

Another annual DOAG conference has passed, and I can only say the very best about it: Perfectly organized, large and modern location, impressive list of well known speakers and over 2100 attendees – wow!


My presentation Best of RMAN was scheduled at the first slot on the first day, so I was a bit concerned whether many people would attend that early. It turned out that the room got so full that I was asked by the organizers to deliver the same talk again next day – which I happily did, again with a packed room :-)

Apart from speaking myself, I enjoyed very much to see friends and colleagues again, as well as people I knew from social media before but never met in person yet. Thank you all for your good company guys – I may appear a bit quiet and distanced sometimes, but I really appreciate you and our little conversations. Personal highlight: The lady who approached me at the stairs, shaking hands and telling me that she knew me from an Oracle University class that she liked so much :-)

There were many interesting presentations, just to mention some of those I attended myself:

Frits Hoogland with Oracle Exadata and database memory


In his very detailed talk, he emphasized the importance of using Huge Pages on Exadata and that MEMORY_TARGET aka Automatic Memory Management is not an option here.

Jonathan Lewis presented Five Hints for Optimising SQL


I’m always amazed how much this man knows and how good he is able to explain it to us mere mortals :-)

Lothar Flatz was presenting Anatomie eines SQL Befehls (how to do Oracle Performance Tuning with a scientific approach)


During his very entertaining talk, he quoted no less than seven Oakies (including himself), so that was quite good advertising for the OakTable Network :-)

Frank Schneede delivered Eine Datenbank kommt selten allein (DB Cloning on Exadata using sparse diskgroups)


while Ulrike Schwinn presented Oracle DB Memory Techniken für mehr Performance (Overview about the meanwhile so many different ways to deal with memory in Oracle)


Couldn’t really catch her because she was running out of the picture all the time :-)

Martin Berger also did an interesting talk: Wie misst Oracle die Verwendung von Database Features? (How to make sense of DBA_FEATURE_USAGE STATISTICS)


I liked his presentation style with many live demonstrations very much!

My dear colleague Joel Goodman talked about Automatic Parallel Execution


Joel is what I call a ‘Standup Instructor’ – mention any database related topic and he will be able to deliver an excellent 30 minutes talk about it instantly :-)

A big  THANK YOU to the many people from DOAG who helped to make that event take place again in such an impressive way! Hope to be there again next year.

Tagged: #DOAG2015
Categories: DBA Blogs

Auto optimizer stats after CTAS or direct loads in #Oracle 12c

Thu, 2015-10-29 07:43

Less famous 12c New Feature: If you do Create Table As Select (CTAS) or a direct load into an empty table, we will automatically collect optimizer statistics:

SQL> select banner from v$version;

Oracle Database 12c Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE      Production
TNS for Linux: Version - Production
NLSRTL Version - Production

SQL> create table nu as select * from sales where rownum<=100000; 
Table created.
SQL> select num_rows,blocks from user_tables where table_name='NU';

---------- ----------
    100000        695

SQL> truncate table nu;
Table truncated.

SQL> insert /*+ append */ into nu  select * from sales where rownum<=200000; 
200000 rows created. 

SQL> commit;
Commit complete.

SQL> select num_rows,blocks from user_tables where table_name='NU';

---------- ----------
    200000       1379

If the table is not empty, the statistics are not updated:

SQL> insert /*+ append */ into nu  select * from sales where rownum<=100000; 
100000 rows created. 

SQL> commit;
Commit complete.

SQL> select num_rows,blocks from user_tables where table_name='NU';

---------- ----------
    200000       1379

You see that the previous statistics have not been changed here.
If it is a conventional insert:

SQL> truncate table nu; 
Table truncated. 
SQL> insert into nu select * from sales where rownum<=100000; 
100000 rows created. 
SQL> commit; 
Commit complete. 
SQL> select num_rows,blocks from user_tables where table_name='NU'; 
---------- ---------- 
    200000       1379

Again, the statistics have not been maintained automatically either. Can be done manually still, of course:

SQL> exec dbms_stats.gather_table_stats('ADAM','NU')

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from user_tables where table_name='NU';

---------- ----------
    100000        748

So knowing that, you may be able to skip some manual statistics collections that you may have done in the past after CTAS or bulk loads :-)

Tagged: 12c New Features
Categories: DBA Blogs

Four members of our team are in the Leadership Circle!

Wed, 2015-10-21 02:49

Oracle University Leadership Circle Q1FY16

The recipients of the Oracle University Leadership Circle Quarter 1 Fiscal Year 2016 have just been announced. That is a corporate award for the best instructors worldwide according to customer feedback. Not less than four (!) come from our small team of 14 instructors:

Leadership Circle Recipients Q1FY16

Yes, we have a great team – supported by a great manager: Thank you, Richard! Congratulations to everyone in the circle this time, I feel honored to be listed together with you!

By the way, I never understood why the fiscal year is ahead of the calendar year – suppose you need to study business economics for that ;-)

Categories: DBA Blogs

RMAN old feature: Restore datafile without backup

Tue, 2015-10-20 04:09

Say I have created a new tablespace recently and did not yet take a backup of the datafile. Now I lose that datafile. Dilemma? No, because I can do an ALTER DATABASE CREATE DATAFILE. Sounds complex? Well even if I wouldn’t be aware of that possibility, a simple RMAN restore will work – as if there were a backup:

RMAN> create table tablespace tbs1 as select * from adam.sales where rownum<=10000; 
Statement processed 
RMAN> alter system switch logfile;

Statement processed

RMAN> host 'echo kaputt > /u01/app/oracle/oradata/prima/tbs1.dbf';

host command complete

RMAN> select count(*) from;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 10/20/2015 11:50:12
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 5: '/u01/app/oracle/oradata/prima/tbs1.dbf'
ORA-27072: File I/O error
Additional information: 4
Additional information: 131

RMAN> alter database datafile 5 offline;

Statement processed

RMAN> restore datafile 5;

Starting restore at 2015-10-20 11:50:43
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=187 device type=DISK

creating datafile file number=5 name=/u01/app/oracle/oradata/prima/tbs1.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 2015-10-20 11:50:45

RMAN> recover datafile 5;

Starting recover at 2015-10-20 11:50:52
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 2015-10-20 11:50:53

RMAN> alter database datafile 5 online;

Statement processed

RMAN> select count(*) from;


Cool isn’t it? Requires that you have all archived logs available since the creation of the tablespace. And besides the SQL commands inside the RMAN shell, it is not a 12c new feature. It works that way since forever, as far as I recall. Don’t believe it, test it!  Maybe not on a production system ;-)

Tagged: Backup & Recovery, RMAN
Categories: DBA Blogs

#Oracle Learning Streams – Have a look!

Mon, 2015-09-07 02:06

Oracle University has introduced a new format that suits the way many learn today: Oracle Learning Streams

Oracle Learning Streams

Although I do not expect that this format will replace classroom training, I consider it a great enhancement – coming with a price like most good things…

As you can see, I’m happy to contribute to it in my area of expertise: You can find me here in the Database Stream.

My clips start with A Practical Guide because they all contain brief demonstrations that show how things can be done practically.

This is what I think viewers want to get instead of “Death by PowerPoint” :-)

It is an ongoing process (a stream of content, therefore the name), so I will keep on adding clips to the pool.

Especially if you like my kind of content, so have a look and get back to me to tell what you would like to see there, please!

Categories: DBA Blogs