Import Users tables

[phillipsp_acca]

Hi,

Can you please confirm that for example if I wanted to import from a recent export of the database just all table's from user scott the following command will acheive this.

imp scott/tiger@orcl file=orcl.dump FROMUSER=scott TOUSER=scott GRANTS=Y INDEXES=Y ROWS=Y COMMIT=Y CONSTRAINTS=Y DESTROY=Y SKIP_UNUSABLE_INDEXES=Y ANALYZE=Y

Will this recover all tables only for this user ?


Regards,
Pat

[spex]

Hi Pat,

Please check this page for 'imp' syntax (from Oracle 8i, newer versions similar):

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch02.htm#22250

Short answer: it depends. The safest way to import is into an empty schema. If tables already exist in the 'SCOTT' schema, your import will report an error for each duplicate table, as 'imp' will not overwrite an existing object. However, depending on what you're trying to accomplish, you might be okay with this behavior.

Here's a more streamlined version with parameters set to default values removed:

imp scott/tiger@orcl file=orcl.dump FROMUSER=scott TOUSER=scott COMMIT=Y DESTROY=Y SKIP_UNUSABLE_INDEXES=Y;

I would consult that web page I mentioned and make sure you really want "DESTROY=Y", which reuses existing datafiles.

If you want to limit the import just to tables and associated objects, you could do a table-mode import ("TABLES=*"):

imp scott/tiger@orcl file=orcl.dump FROMUSER=scott TOUSER=scott TABLES=* COMMIT=Y;

It's probably a good idea to preview the import with "SHOW=Y" before doing it for real. Also consider using a parameter file instead of specifying individual parameters on the command line ("PARFILE=xxx").

And don't forget "nohup imp ... &" will run 'imp' in the background with hangups/quits ignored.

PCS

[spex]

Pat,

For future reference, Oracle forums:

http://forums.oracle.com/forums/category.jspa?categoryID=18

PCS

[phillipsp_acca]

Thanks for the help.