3

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?

HopelessN00b
  • 53,385
  • 32
  • 133
  • 208
Kamil Zadora
  • 217
  • 1
  • 3
  • 9

5 Answers5

6

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.

Community
  • 1
Leigh Riffel
  • 605
  • 2
  • 10
  • 23
3

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.

friol
  • 191
  • 1
  • 5
1

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

Mark Regensberg
  • 1,421
  • 12
  • 14
0

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/

MarlonRibunal
  • 283
  • 1
  • 3
  • 8
0

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.

Olaf
  • 908
  • 5
  • 7