In anticipation of upgrading the databases on my project to Oracle 10g, I've been looking at Data Pump, a feature new to 10g that provides 'high speed data and metadata movement of Oracle database contents'.  Basically this is a replacement for Oracle's warhorse IMP and EXP utilities.  Oracle says they will continue to include and maintain those utilities with new database releases "forever" (a good thing too, since Data Pump can't read EXP dump files and would be useless in trying to migrate lower database releases (5.0 through 9i) into 10g. That being

There are times when it's useful for a DBA to login as a specific user temporarily in order to troubleshoot and diagnose problems.  Here's a way to do this without changing the password (at least as far as the user can see).  Since all Oracle user passwords are encrypted, the following technique can be used by a DBA who wants to take over a user account and then return it to it's original password:

-Query the DBA_USERS view to find the encrypted password for the user's account.

-Generate an ALTER USER command to return the account to the encrypted password by issuing the following command:

I came across the ORASTACK utility when I was getting "out of process memory" errors when rebuilding a large text index.  This error occurs when Oracle is trying to allocate memory for a session but no more is available (Oracle in Windows has a 3GB address space limit, but Oracle will issue a "process memory" error when available memory gets below 1MB).  Oracle's ORASTACK utility allows you to reduce the stack size for Oracle threads from 1MB down to a smaller number.  Typically, you would run the command against the listener and the Oracle kernel:

(From the DOS command prompt)

In a direct response to RW's January blog entry, Oracle released a new GUI development tool, SQL Developer (it used to be called Project Raptor), in March.  With SQL Developer, you can browse and modify database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements (it uses an enhanced version of  SQL Worksheet that includes an Explain Plan window, and various output windows ). You can  run any number of provided reports, or create your own. The tool also has a very good help interface, a snippets feature for inserting code fragments, and a data import/export function.   Since SQL Developer leverages the JDeveloper IDE,  you have the option of writing extensions. The tool also has a very good help interface and a snippets feature for inserting code fragments.   

I came across the following code that lists all tablespace objects with growth problems  (specifically, ones that will fail when they try to grab another extent).  It works for both Locally-Managed and Dictionary-Managed tablespaces (in the case of locally-managed that use AutoAllocate extent management, the script assumes that all the extent sizes are the same):

Set Linesize 130
SET Linesize 130
SET Trims on
COLUMN Tablespace             Format A30
COLUMN SegmentType            Format A12
COLUMN Owner                  Format A12
COLUMN Segment                Format A30
COLUMN "Required Extent(KB)"  Format 999,999,999.99
COLUMN "MaxAvail(KB)"         Format 999,999,999.99

Introduced in Oracle 9i, external tables are not really tables, but descriptions and locations of external text files that are stored in the database dictionary.  They allow the external tables to be read through SELECT statements.  One popular usage example is to create an external table to view the contents of the alert.log file via a simple SELECT statement .

create or replace directory bdump
as 'e:\oracle\admin\ora92\bdump';

create table alert_log ( text varchar2(400) )
organization external (
  type oracle_loader
  default directory BDUMP
  access parameters (
    records delimited by newline
    nobadfile
    nodiscardfile
    nologfile
   )
   location('alert.log')
)
reject limit unlimited
/
Querying the ALERT_LOG table will display the contents of the database's alert.log:

In version 9i, Oracle introduced a new feature called Flashback, that uses undo segments (rollback segments for old timers) to recover from a user error even though the transaction has been committed.   There are some initialization parameters that need to be set for it to work- UNDO_MANAGEMENT should be set at AUTO, UNDO_RETENTION should be set to the amount of time (in seconds) that committed undo information is retained for.  In addition, users who will be doing "flashback queries" need execute rights on the DBMS_FLASHBACK package and FLASHBACK privileges on the table(s) in need of recovery.

I'm experimenting with Oracle Multi-Master Replication (MMR).  This was the most powerful type of database replication that Oracle offered for 8i (my current production database) and it allows you, with some exceptions, to replicate the database in a higher release (say, 9i) and  pick and choose the objects you want to replicate.  I plan on using it to replicate my current production database to my new 9i database server, the beauty of it being the new server's data will be in sync with the current production data, making cutover immediate ( I almost used the word 'seamless').

Oracle has a very useful feature called Fine Grained Access Control (FGAC) for implementing database row level security.  I have used it on my project for, among other things, restricting access to certain documents based on the user's account and privileges.  In short, you create a policy that appends a predicate (such as a WHERE condition) to all SELECT (or INSERT, UPDATE and DELETE) statements involving the specified table,  regardless if the select is issued in the form of an interactive SQL statement or as the result of a data call from a front-end application.