In order to get an application running for testing purposes on our servers, my team and I needed some configuration data that was stored on the client's production database. After asking them for what we needed, they soon sent us back an Oracle DMP file. Shoving data from the DMP back into the database would be as simple as using Oracle's imp utility...or so I thought.

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

With the release of 10g, a database can be cloned from one location to another by use of the Oracle database Creation Assistant.  However, this feature along with many of the new 10g features is worrisome to the veteran database administration crowd.  The main reason for this worry is the lack of knowledge our junior associates are getting due to the new EM tool.  They are not able to function in a telnet environment and trying to get them to perform any tasks using SQL*Plus is nearly impossible.  Do not miss our point.  The new EM tool provided by Oracle is a great improvement over past enterprise manager versions, however every tool has its place and a DBA must know far more than a GUI.  The instruction set detailed below for the cloning of an oracle database is valid in multiple versions of Oracle from 8.1.5 to 10g and has the added benefit of being performed over a telnet or ssh connection.

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: