Oracle uses object statistics to estimate the amount of data that a query will probably return. This is used to find the best execution plan for the specific case. Having stale statistics is perhaps a major reason for bad execution plans. But sometimes the data changes so frequently that the generation of up to date statistics is not a feasible goal.Continue reading "Faking Oracle statistics for TIMESTAMP columns"
Oracle provides the
DBMS_METADATA package to extract an XML representation of database objects. The generated XML can either be an undocumented internal Oracle format or the so called Oracle SXML format. SXML is a SQL-like XML dialect for Oracle object metadata. The provided interface allow us to extract an object from the database in SXML format and also to convert the SXML format into a DDL statement to create the object again. Since it is basically XML we can go one step further and add an XSLT transformation to modify the object before we recreate it.
A co-worker tried to drop a user in an Oracle database and got a strange error:
ORA-00600: [ktssdrp_segment: NonNullDba]. Unfortunately My Oracle Support did not have any useful information on this error and so I started to find a solution for this myself. In the end the solution was easy but not obvious.