topic Re: Import Users tables in Operating System - HP-UX

Import Users tables

phillipsp_acca — Mon, 03 Jul 2006 11:40:35 GMT

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

Re: Import Users tables

spex — Mon, 03 Jul 2006 13:21:12 GMT

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

Re: Import Users tables

spex — Mon, 03 Jul 2006 14:35:00 GMT

Pat,

For future reference, Oracle forums:

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

PCS

Re: Import Users tables

phillipsp_acca — Tue, 04 Jul 2006 05:35:16 GMT

Thanks for the help.