I want to export a table 'ACCOUNTS on a daily basis, with export dump file name ACCOUNTS_ddmmyyyy.dmp. I may need to re-import this table at a later date so that I can compare balances between any two days.
At the moment, when I do the import I need to import the table into a different schema as I dont want to overwrite the live ACCOUNTS table.
(The import tries to 'CREATE TABLE ACCOUNTS')
I would like to import the table into the same schema with table name
Is there any option that I can specify at the export stage that can, in effect, rename the table to the same name as the dump file?
12-19-2002, 08:20 AM
12-19-2002, 08:25 AM
Try something like:
a) in PL/SQL as dynamic SQL:
CREATE TABLE ACCOUNTS_ddmmyyyy AS SELECT * FROM ACCOUNTS;
(you will need some code to generate the tablename)
b) the export job should select this table - again you'll need to generate the names to use in the parameter file (how? - will depend on OS).
c) DROP TABLE ACCOUNTS_ddmmyyyy; when you are done.
12-19-2002, 08:39 AM
we are talking about import not export
12-19-2002, 08:41 AM
Thanks for the swift replies.
DaPi, I had used a method similar to yours to get around the problem.
ie import ACCOUNTS to different schema, create ACCOUNTS_ddmmyy in my schema as select from different schema.
ACCOUNTS is very large table so I was just trying to see I could find a quicker way of doing things.
12-19-2002, 08:46 AM
to a different schema obviously you can do it but under same schema impossible, even you change your create table statement in the dump file are you gonna change all INSERT statements in the dump risking of corrupting the dump file? guess not
12-19-2002, 09:00 AM
"great minds . . " Looks like your decison has to be when to spend the extra time (you need the extra disk space no matter what).
Taking extra time after the import minimizes the total time lost (since you do this rarely) BUT it's when the **** hits the fan that you need to act fast, so there might be an argument to make the export job "slow" to enable you to do an import at top speed.
12-19-2002, 09:26 AM
Thanks for the help. Now I need to decide if its better to save time on export or import. Probably just toss a coin!
12-19-2002, 12:14 PM
Another approach would be ...
create table ACCOUNTS_28112002 as select * from accounts where rownum < 1;
grant insert ,select on ACCOUNTS_28112002 to accounts_importer;
create view accounts as select * from accounts_owner.ACCOUNTS_28112002;
... then import ACCOUNTS into the accounts_importer schema with IGNORE=Y.