As my daily job circles around an Oracle DB I wonder are there any hidden features that could come in handy in day to day work with the DB, both as a developer and as a DBA?
locked by voretaq7 Feb 27 '12 at 22:38
This question exists because it has historical significance, but it is not considered a good, on-topic question for this site, so please do not use it as evidence that you can ask similar questions here. This question and its answers are frozen and cannot be changed. More info: help center.
Read more about locked posts here.
As an Oracle DBA I find that increased knowledge of the visible Oracle database features is far more useful than the undocumented and unpredictable hidden features. If you haven't already, start with the Concepts Guide (pdf) then read the Administrator's Guide (pdf). For further reading options see some stackoverflow questions here and here.
Don't know how "hidden" this is, but since version 9i, the Flashback Query mechanism was introduced.
With the Flashback Query, you can see contents and structure of Oracle objects at a certain point in the past (as long as those informations are still stored in the undo tablespaces), for example:
select * from mytable AS OF TIMESTAMP TO_TIMESTAMP ('2009-05-16 10:20:20', 'YYYY-MM-DD HH24:MI:SS'));
retrieves the records from "mytable" as they were 5 minutes ago.
Occasionally over the past few years I have been given undocumented parameters by Oracle tech support, but usually the reason they are undocumented (~= hidden) is because they really should only be used for troubleshooting and specific issues.
This page (by Donald Burleson) has a good breakdown on how to find undocumented packages and views, but there isn't much there I've had cause to use...
Find active sessions:
SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session WHERE Status=‘ACTIVE’ AND UserName IS NOT NULL;
If you have a RAC configuration:
SELECT Inst_Id, SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM GV_$Session WHERE Status=’ACTIVE’ AND UserName IS NOT NULL;
Given that hidden features might be the ones you don't expect, I'd vote for things you don't want exploited against yourself (just because you didn't know you had to disable them).
I remember a horror szenario from a talk about Oracle hacks (I believe it was this one - also as video). The most impressive feature was full database encryption, that seems to be available but not licensed. Thus, if you are under attack and somebody exploits SQL-injection with the required privileges, they might trigger the encryption of the database which you'll recognize with the next restart when it asks for the passphrase. As the encryption is "industry standard" you better have a nonencrypted backup or prepare to pay ransom if the attacker asks for it.
This isn't handy to use as developer or DBA, but worth knowing as security conscious DBA.
If I misquoted the talk and got the wrong one, let me know and I'll dig deeper.