Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm doing an oracle imp and in the dump there is a big table that I would like to only create but not to insert row into.

Is this possible ?

Exp was done on 10.2.0.4. I'm doing imp on XE (10.2.0.1).

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Were you using the classic export and import utilities? Or the new DataPump versions? I assume from your references to "exp" and "imp," rather than "expdp" and "impdp," that you were using the classic versions rather than the DataPump versions. If so, that substantially decreases your options.

In the classic export and import utilities, the ROWS=N parameter applies to all tables. So there is no single command line for "import all the data into all the tables but one." You can, however, do a series of imports

  • Do a first import with ROWS=N in order to import the structure of all the tables but none of the data.
  • Do a second import with ROWS=Y and TABLES=(<<list of all other tables>>)

Since you did the first import already, you can generate the list of tables for the second import just by querying the DBA_TABLES (or ALL_TABLES or USER_TABLES) data dictionary view.

share|improve this answer
1  
Good suggestion, tnx. I would mark your post as answer, but I posted the question with email registration then registered with open id and now SF doesn't recognizes the question as mine. Sorry. –  Petar Repac Sep 9 '09 at 19:36
    
Marked as answer at work. Thanks again. –  Petar Repac Sep 10 '09 at 4:26

Of course it is.

"imp" has a "ROWS" parameter for this. If you set ROWS=N to your command line, import will skipped the data insert.

Edit: It will skip rows for all tables. If you want to skip rows for only one table, I think you will have to do the import in two distinct steps. One for importing only this specific table, one step for the others tables.

share|improve this answer
    
Would this skip rows for all tables ? How I specify that only on one table rows must be skipped ? –  Petar Repac Sep 9 '09 at 12:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.