End users on our warehouse log in using their own, personal oracle account and have the ability to create objects in their own schema – so they can test out analysis ideas without harming anyone else. I had to tune one of the pieces of SQL a user was running the other day and it [...]
Archive of posts tagged code
TRUNCATE command marks previously UNUSABLE indexes as USABLE
Yes, I’m back…but on an adhoc basis as I still don’t have enough time for this really! Yeah – I know, you’re all in the same boat I came across this problem on the warehouse the other day and was slightly surprised by what was going on… We have an OWB mapping which does a [...]
Obtaining the value of an XML element in a VARCHAR2
We’ve got some XML data stored in the comments against tables on a system I’m working on – things like “Table Short Name (alias)”, “Legacy Key” and “SCD Type”, e.g. select table_name,commentsfrom dba_tab_commentswhere table_name=’BDM_T_CUSTOMER’; Table Name COMMENTS —————————— ———————BDM_T_CUSTOMER CUS We’re storing extra metadata in the comments for things we can’t easily store anywhere else. [...]
Hex to decimal conversion and vice versa
Here’s an interesting quick tip… I was building something earlier today and wanted to convert hex numbers to decimal and vice versa…figuring it must be easy I had a quick scan of the online manuals but couldn’t find anything of use so I resorted to the internet where I found several references to PL/SQL functions [...]
10g Recycle Bin
Much like holding the SHIFT key when you delete a file in Windows Explorer, if you use the PURGE keyword when dropping a Table you can ensure that it doesn’t go into the Recycle bin that Oracle 10g maintains. e.g. DROP TABLE emp PURGE; NOTE – You can’t roll back a PURGE statement…so be absolutely [...]