1748053 Members
4848 Online
108758 Solutions
New Discussion юеВ

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 3
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.