To: Marin Komadina 

> Hi, Ari , 
> Please I have a problem this kind,
> 
> One table TABLEA in a database A , and second table TABLEB in a database B .
> Same table, same structure, everything was a same because it is created with
> export, import.
> Situation is not so simple because there is a constraint, FK, PK, Unique and
> so on.
> The first database is real production and second is test database. 
> Someone in a test database deleted all records, and I have to recreate all
> records again. This is static table. 
> I don't want to teach people to use export import, is there any metod to
> simply transfer records from one identical table to another, same shema in
> both.
> I created database links in both directions , it works,., next I know
> statement create table as select but what if I don't want to drop rekreate
> and soon. 
> Is there any solution.
> Marin. 
> 

The easiest solution is to teach them export and import.

While you can do "CREATE TABLE xxx AS SELECT * FROM xxx@remote_database;",
you will still have to create the indexes, constraints, synonyms, grants,
triggers, and so on.

If all the user did was delete records, but the table (and indexes, etc.)
are still there, then you can issue:

INSERT INTO xxx SELECT * FROM xxx@remote_database;

Keep in mind that any "BEFORE INSERT" triggers would fire for this table
if they exist.

Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

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

Back to Ari Kaplan's Home Page