Re: Data archiving

Hi,

>
>Using a simple select statement and spooling out means that the spool file
>will contain column headers, which makes the process of loading back the
>data staright from the spool file difficult, since you'll have to strip off
>the headers. If there a way of spolling without the headers??

SET HEAD OFF
SET PAGESIZE 0
>Another thing is, the data archiving program should be able to pick up data
>from all tables (or tables pre-defined in a special table setup for this
>purpose), instead of specifying the tables to archive one by one
>interactively or in an SQL script.

Setting this up is tediuous but not terribly difficult.

1. Create a SQL script such as:

    SELECT '-- TABLE NAME IS - '||table_name, 'SELECT * FROM ' table_name||';'  FROM all_tables WHERE OWNER = 'ME';

(If I specified this correctly you should wind up with a script with one line per table that will create a table name and dump all of the contents to the spool file.)

2. Run the script (but embed it in the necessary SQL*Plus commands to turn off the
headings, set pagesize to zero and turn spooling on and off).

3. Copy the spool file to your backup media using an OS utility.

Regards

Jerry

Jerry Gitomer Since I know how to spell DBA, I became one. Received on Fri May 08 1998 - 00:00:00 CDT