Those who visit SQL.ru often know what the title means. It’s a very simple yet powerful technique to group data which doesn’t seem appropriate for grouping at first sight.
I learned this very nice tip long time ago but often have to re-think before applying it to SQL or (more...)
DBMS_LOCK is a slightly obscure built-in package. It provides components which so we build our own locking schemes. Its obscurity stems from the default access on the package, which is restricted to its owner SYS and the other power user accounts. Because implementing your own locking strategy is a good (more...)
Time travel sucks, especially going back in time. Nobody takes a bath, there are no anaesthetics and you can't get a decent wi-fi signal anywhere. As for killing your own grandfather, forget about it.
The same is true for going back in database versions. In 2009 I had gone straight (more...)
The database on the Developer Days Database App VBox appliance doesn't have the SCOTT schema. This is fair enough, as the sample schemas aren't include by default any more (for security reasons, obviously). I know the official sample schemas
used in the documentation - HR, OE, and so on - (more...)
May 23, 2013 The following question recently came through an ERP mailing list (significantly rephrased): I would like to use the Microsoft Query tool in Microsoft Excel to extract records from the ERP database. I would like the list of parent records to be retrieved into Excel when all (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?
Usually an unique index grants the uniqueness of all rows in a specific table which have non-null values; But (more...)
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...)
May 1, 2013 I saw a decent SQL brain teaser this morning in the comp.databases.oracle.server Usenet group. The OP in the message thread is attempting to summarize data in one of his tables, with the summarizations broken down by month and then the week within that month. (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...)
April 25, 2013 Roughly 12 years ago I was attempting to analyze customer order changes that were received through electronic document interchange (EDI), specifically X12 830 documents that show order forecasted demand for specific part numbers. At the time, the EDI data was partially transformed and inserted into an (more...)
Yet another performance tuning story
, similar to one that happened about a month ago
. Just to keep it short:
- once upon a time there was a very time-consuming module.
- eventually we were forced to take much closer look - WHY is it so time-consuming
- we found in our own (more...)
Most of us here follow the good practice of executing our SQL statements directly against the database before using them within an SQLExec in PeopleCode, a view or an SQL actions in App Engines. Even after following this, we might get hit in some situations. Read on to find 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...)
Catastrophe! You’ve just accidentally dropped a table which contained really rather important data. What to do?
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...)
If you're reading this, you probably love SQLT and want to learn more about it, but sometimes, a SQLT Xtract isn't the best tool for the job. Here's an example, tuning Data guard. Using the right tool always makes the job a little easier.
The post XTRSBY – Tuning Oracle (more...)
There's nothing more annoying than having to struggle to do something because you don't have the right tools. That's why I love my swiss army knife and why when working for Oracle, I love SQLT. What is SQLT? Read on and find out.
The post What is SQLT? appeared first (more...)