Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
cancel
Showing results for 
Search instead for 
Did you mean: 

Import Users tables

phillipsp_acca
Occasional Advisor

Import Users tables

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
3 REPLIES
spex
Honored Contributor

Re: Import Users tables

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
Honored Contributor

Re: Import Users tables

Pat,

For future reference, Oracle forums:

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

PCS
phillipsp_acca
Occasional Advisor

Re: Import Users tables

Thanks for the help.