I have an Oracle database (10g or 11g, doesn’t matter) with an empty schema. I am the only user on the system and I only have one session open.
I create a single table (FOO) and run a few simple SQL commands. Then, I run this, twice in a row (more...)
Matrix : What you must learn is that these rules are no different than rules of a computer system. Some of them can be bent, others can be broken. Understand?
Oracle’s advanced partitioning has some deficiencies. For example, partition info is missing in V$SESSION_LONGOPS for scan-operations ( full table scans, full index scans ). V$SESSION_LONGOPS.TARGET only shows OWNER.TABLE_NAME in these cases, even when the underlying table/index is partitioned, though the longop doesn’t refer to the whole segment but (more...)
Tom is not not a fan of public synonyms, here is why:
- public synonyms pollute the namespace.
- public synonyms can lead to security issues.
- public synonyms can lead to a maintenance headache.
- public synonyms are public – no one owns them.
So, instead of public synonyms…
create PRIVATE synonyms or (more...)
… the UTL_RAW package has a bunch of casting functions for RAW values (CAST_TO_BINARY_DOUBLE, CAST_TO_BINARY_FLOAT, CAST_TO_BINARY_INTEGER, CAST_TO_NUMBER, CAST_TO_NVARCHAR2, CAST_TO_VARCHAR2). Note the absence of a CAST_TO_DATE function.
Bertrand Drouvot also misses it, see Bind variable peeking: Retrieve peeked and passed values per execution in oracle 11.2
Here is (more...)
Tom Kyte’s print_table procedure, available on
seems to be very popular and there exist tricky variations on the theme, for example the following nice xml-trick by Sayan Malakshinov.
Please note that it is very easy to use the existing print_table-code to generate a pipelined version which (more...)
One thing you can do to recover the situation quickly (if you’re running 10g or later, that is) is to run the following command:
FLASHBACK TABLE MY_SCHEMA.MY_SUPER_IMPORTANT_TABLE TO BEFORE DROP;
If the table (more...)
I’ve previously written about manually rewriting an OR condition into a UNION ALL using LNNVL.
This is a description of a performance issue observed in the real world from the optimizer coming up with a CONCATENATION operation against many child operations including an INLIST operator and other children which then (more...)
I was playing the pl/sql challenge the other day and the question was to identify valid implementations for a “reverse_string” function taking a varchar2 as the input and returning another varchar2 with the characters in inverted order.
One of the possible answers was this:
FUNCTION reverse_string (in_string VARCHAR2) RETURN VARCHAR2 (more...)
- Which one is faster and why SELECT 1 FROM DUAL or SELECT ROWID FROM DUAL?
Yes, it's pretty basic question, but it's interesting enough to be covered. The answer is pretty simple - do not use ROWID (more...)