Re: creating database from dmp files

1. create correspondent user on secondary (repository) database.
2. Import data from dumps. imp73.exe User/passwd file=file001.dmp ignore=y imp73.exe User/passwd file=file002.dmp ignore=y ....... The negative of that approach is a duplication of data in repository, or, if a primary key exist, impossibility to transfer updated rows. They simply will not be inserted.

If you plan to use imp/exp just for transfering purposes (not for the backup) - consider copying via database link. On AIX:
sqlplus User/Passwd
create snapshot log on Table1;

On repository:
Create AIXDB alias in tnsnames.ora file. sqlplus User/Passwd
SQL>create database link AIXDB connect to User identified by Passwd using 'AIXDB';
SQL>create procedure Append_to_Table1 as begin
  insert /*+append*/ into Table1
  select t.* from Table1_at_AIXDB t, mlog$_Table1_at_AIXDB l   where t.rowid=l.row_id;
  delete mlog$_Table1_at_AIXDB;
  commit;
end;
/

You could create a simple snapshot. But, if you cleanup old data on AIX with using SQL DELETE, the corrrespondent repository rows will be also deleted. Because of that you have to use above procedure. You probably need to add some additional logic into procedure to handle updated and deleted rows in MLOG$ table.

After that you can create job to pump data automatically.

Mark

P. Bianchi wrote:

> Or departmental info system runs under Oracle 7.3 on a small AIX server
> which is always short on disk space (2 GB). Therefore every two months
> we are forced to dismiss some older data, by exporting them to a dmp
> file. We dump the data from a Windows workstation, by invoking an
> "exp73.exe" utility which creates *.dmp files on the workstation.
> Exported files (avg size 100 MB) can then be archived, stored on CDROM
> etc.
>
> Should we need to examine data contained in those *.dmp files, we could
> reload them into a temporary portion of our database, by means of an
> import utility. Importing however is painstakingly slow and we no
> longer use it.
>
> We now have the opportunity of putting the exported data on another
> windows workstation with plenty of disk space, where Personal Oracle 73
> is installed. I therefore would like to re-create a reduced copy of our
> database, running under Personal Oracle 73, where to import the dumped
> data into, and where to leave them forever, as long as disk will allow.
>
> I have the *.dmp files, I can see from a log file which tables should
> reside into them, I have no immediate infos at the structure of those
> tables.
>
> Can I "create a database right from dump file"? And generally speaking,
> how should I proceed for importing such files on a Personal Oracle
> system?
>
> Thanks in advance
> P. Bianchi
>
Received on Mon Jan 14 2002 - 10:29:13 CST