Re: Exporting large tables!!!

Hi Vinnie,

        I have two proposed solutions and am happy with neither of them. The first one requires locking the table from inserts and updates for as long as it takes to copy the data and truncate it. The second will probably run very slowly.

        Can you schedule the job to run in the wee hours and then lock the table for a short time? If so, look into using something like the following (I am not sure whether or not you need the second LOCK TABLE):

	LOCK TABLE your_table IN EXCLUSIVE MODE;
	CREATE TABLE temp_table AS SELECT * FROM your_table;
	LOCK TABLE your_table IN EXCLUSIVE MODE;
	TRUNCATE TABLE your_table;
	

Then run the export against temp_table.

        If you can't lock the table for the duration of the CREATE and TRUNCATE commands you might consider the following -- which is MUCH slower:

	CREATE TABLE temp_table AS SELECT * FROM your_table;
	DELETE FROM your_table 
	WHERE your_table.<primary_key> = temp_table.<primary_key>;

Then run the export against temp_table.

regards

Jerry

Vinnie wrote:
>
> I am running ORACLE 7.3 on Soloris. I have one table which generates
> enormous amounts of records( 700K in a few days). I am trying to figure
> out a way to export the data then delete the same data!. I am using
> export & TRUNCATE. How can I make sure that no new records are inserted
> while I am doing this? Basically I want to export the same data I
> delete & not loose any new records in between!
>
> HELP!
>
> Vinnie Salerno
> Litton/PRC

--

Jerry Gitomer         	ICT Group
jgitomer_at_ictgroup.com	Langhorne PA
jgitomer_at_yahoo.com 	Opinions are mine not those of ICT Group