> 
> Hi, I want to archive data by passing table name and date column as parameter
> to 
> new instance from production database  on the same Hard drive with the same 
> tables and column names and then delete the archived data from production 
> database. Oracle 7.3 on nt 4.0.How do i create new instance + any help will be
> appreciatable. 
> 
> email:rkreddy@hotmail.com 
>           rkreddy@aol.com 
> 
> Thanks in advance 
> 
You will first need to set up your second database. Change your ORACLE_SID
and use the "CREATE DATABASE" command, along with all other create statements
to create a second "archive" database.

Make sure that you have your listener configured for both databases. Check
the $ORACLE_HOME/network/admin files for correctness.

You can use dynamic SQL to create a function to INSERT data into the archive
database and DELETE from the production database. You can also do this manually
in SQL. For example, do the following SQL to create the table and insert data
into archive:

CREATE TABLE table1@archive_database AS
SELECT * FROM table1@production_database
WHERE date_column < TO_DATE('01-MAR-95');

Then, you can delete it from production:

DELETE FROM table1@production_database
WHERE date_column < TO_DATE('01-MAR-95');

Best of luck!


-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 160+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page htm"> Back to Ari Kaplan's Home Page