Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share
    
This should be community wiki –  Toro May 6 '09 at 14:49
comments disabled on deleted / locked posts

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.

5 Answers

up vote 6 down vote accepted

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.

share
add comment

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.

share
add comment

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...

share
add comment

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;

Reference: http://dbalink.wordpress.com/2008/06/08/find-active-sessions-in-oracle-database/

share
add comment

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.

share
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.