June 18, 2009
Today I have encountered a surprising fact:
Scenario is 10.2.0.1 Express Edition on Win32.
I have a table called job_history in the HR scheme which has a composit primary key : and there is an index for the primary key:
SQL> select index_name from user_indexes where table_name=’JOB_HISTORY’;
INDEX_NAME
Does the following info help to explain?
——————————
JHIST_EMP_ID_ST_DATE_PK
JHIST_JOB_IX
JHIST_EMPLOYEE_IX
JHIST_DEPARTMENT_IX
This is my constraint in the data dictionary:
SQL> select column_name , position
2 from user_cons_columns
3 where constraint_name=’JHIST_EMP_ID_ST_DATE_PK’;
COLUMN_NAME POSITION
———————– ———————————
EMPLOYEE_ID 1
START_DATE 2

Now I drop the primary key constraint
SQL> alter table job_history drop constraint
2 JHIST_EMP_ID_ST_DATE_PK including indexes;
Table altered.
And here is the surprise:
SQL> select index_name from user_indexes where table_name=’JOB_HISTORY’;
INDEX_NAME
——————————
JHIST_EMP_ID_ST_DATE_PK
JHIST_JOB_IX
JHIST_EMPLOYEE_IX
JHIST_DEPARTMENT_IX
I do not see the point here. Why is the index still there?
Evern though I have explicitly written INCLUDING INDEXES.
BUG or Feature?!
Can anyone explain?
11 Comments |
Uncategorized |
Permalink
Posted by Lutz Hartmann
March 31, 2009
Yesterday in my course I ran into a strange BUG in Oracle Express (XE) on WIN32 and Linux 32 bit, in fact one of my students, Ralf Spiwoks, found it:
I have tried to run a top level anonymous block in which assigned a value to a BIND variable and got kicked out of my session with an ORA-03113.
SQL> VAR lname VARCHAR2(4)
SQL> <<my_lable>>
BEGIN
:lname:=’LUTZ’;
end;
/
ERROR:
ORA-03114: not connected to ORACLE
<<my_lable>>
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
THIS IS A BUG which exists in 10gR2.
Only top level blocks are affected by this BUG.
After I wrapped the block into another block it worked.
Here is the workaround:
BEGIN
<<my_lable>>
BEGIN
:lname:=’LUTZ’;
END;
END;
/
PL/SQL procedure successfully completed.
I have tested only WIN 32 and LINUX32 => XE and Enterprise Edition on Win32. It has been fixed in the meentime, but I am not sure with which patch.
In EE 11gR1 it works.
2 Comments |
Uncategorized |
Permalink
Posted by Lutz Hartmann
April 14, 2008
Today my blog has reached to the 100′000 hits landmark.
I am very happy that this has become such a successfull forum here.
Thanks for reading
I’ll keep on posting in the future!
3 Comments |
Uncategorized |
Permalink
Posted by Lutz Hartmann
March 27, 2008
Today I have delivered two presentation @ the
yearly conference of the Swiss Oracle Usergroup.
The topics were :
- Real Application Testing with Oracle 11g
REPLAY DATABASE and
SQL PERFORMANCE ADVISOR
- SQL Plan Management and Automatic SQL Tuning in 11g
I have used my new courseware which
I have produced in the last few weeks for 11g New Features.
I offer to to deliver special courses for topics such as
- High Availability with Flashback and other 10g and 11g features
- Backup 10g & 11g
- Memory Management 10g & 11g
- Upgrade Issues 10g & 11g
- 11g New Features Overview
If you are interested in these topics please contact me by email:
info@sysdba.ch
Leave a Comment » |
Uncategorized |
Permalink
Posted by Lutz Hartmann
March 18, 2008
At the moment I am in Cairo delivering a number of 11g New Features trainings.
I have a completely packed class with 21 students.
Most of the students are Oracle Trainers, Support Agents and Consultants.
1 Comment |
Uncategorized |
Permalink
Posted by Lutz Hartmann
March 14, 2008
After arriving in Cairo I immediately went to see if the my friend Muhammd Ali is still in living in his place as before 13 years. I had been living in his place for a while in the 90s.
Muhammad is an internationally recognized artist and has been painting since more than 25 years now. He had exhibitions in Paris, Hamburg, Berlin, Zurich and many other places.
He lives in a very poor area near the great basar Khan Al-Khalili.
In April he will have an exhibition in Talaat Harb Streeet in Downtown Cairo and I hope
that he will sell a great number of his beautiful naive paintings again.
Leave a Comment » |
Uncategorized |
Permalink
Posted by Lutz Hartmann
March 13, 2008
Today I have arrived in Cairo, the Mother of the Earth.
I will deliver a number of Oracle 11g trainings here.
This is the first time I am in Egypt after 12 years. 
In the middel of the ninetees I had been living here for a couple of longer times.
I am very interested in the developments in Egyptian society since then.
Hopefully I will meet a lot of interesting people.
1 Comment |
Uncategorized |
Permalink
Posted by Lutz Hartmann
March 8, 2008

Yesterday I had organized a little dinner with Steven Feuerstein, my former collegue Christoph Burandt and his lovely wife Silvia as well as my dear friend Jenny.
Steven had delivered a two day seminar for Oracle University in Munich. Although he was very sick in the last days, he had caught a chest cold and swollowed an enormaouse amount of remedies in order to be able to deliver anyway, he preferred not to hang around in his room alone and come for our little dinner.
Thanks for coming Steven! Hope you’re feeling better already.
We talked about everything but PL/SQL and had a lot of fun.
Christoph is the one who had introduced me to PL/SQL in 2000 and I was very happy to meet him once again.
I think that he is one of the best instructors in this field you can get in Germany.
His wife is from Peru and Steven adressed her in Spanish.
Everybody could speak Spanish except me!
=;-)
1 Comment |
Uncategorized |
Permalink
Posted by Lutz Hartmann
March 1, 2008
I
I am just downloading the latest patchset for RDBMS Server 10.2.0.4 for Linux x86.
It is documented in Metalink NOTE: 316900.1
Components of This Patch Set
The following are the updated components of this patch set:
- Up to January 2008 CPU is included in this patch set.
- Platform-specific bug fixes as listed in the “Fixed Platform-Specific Bugs” section
- Generic bug fixes as listed in the List of Bugs fixed document
- Oracle Configuration Manager 10.2.7.1
I am very curiose about the possibility to capture a workload in 10g and run for Real Application Testing in 11g.
It should be included in this patchset.
I will try it and post about it here ASAP.
=;-)
7 Comments |
Uncategorized |
Permalink
Posted by Lutz Hartmann
February 29, 2008
It worked!
All you need to do is threaten them!
They have removed the links to my content.
We’re Sorry – We Were Unable to Locate The Article You Requested.It’s possible that this article is no longer available, has been reported as a bad link or is currently being reviewed/edited by its author.
We apologize for the inconvenience! Please use the menu system or the search boxes to locate other items that may be of interest.
If you feel there has been an error, please contact us and let us know about the issue and we’ll get on it immediately!
Leave a Comment » |
Uncategorized |
Permalink
Posted by Lutz Hartmann