Question about creating a dump file in PL SQL 1

[sjh]

Hi,

I am new to Oracle...

Is using the "Export Tables" feature from PL SQL same as running the "exp" command? Do I have to log in as a SYSDBA or is it OK to log in as schema owner? If my goal is to copy the exact database environment to a different server, is using "Export Tables" enough?

And is there a way to export other user objects (i.e. views, procedures) besides tables? There is "Export User Objects" feature in PL-SQL, but this doesn't include the data.

Thank you for your help!

 

[SantaMufasa]

SJH,

If you wish to export an entire schema (including tables, index definitions, synonyms, stored procedures and functions, sequences, et cetera, et cetera, et cetera), then the best method to accomplish that is still (after 20 years) the Oracle "exp" utility, then the "imp" utility to bring those data back into an Oracle database.

Both the "exp" and "imp" commands have on-line help available. Both the help and the invocation of these commands occurs at your operating-system command prompt. The command sets are identical across operating systems. To obtain a briefing of command syntax and functionality, from a command prompt, issue the commands:

exp help=y

or 

imp help=y

for a listing of "exp" and "imp" commands, respectively.

As an example, if I wanted to export a schema named "test", I would issue the following "exp" command and its parameters (all on a single line...no carriage return):

exp buffer=150000000 compress=n grants=y feedback=1000 consistent=y file=TEST.dump log=TEST_Exp.log statistics=none owner=TEST userid=TEST/TEST@dhunt

Here are the meanings of the "exp" parameters that I used in the example:
[tt]
exp : invoke the export utility
buffer=150000000 : allocate 150M ram for workarea
compress=n : do not store all data in 1 extent
grants=y : export obj. privileges
feedback=1000 : display a dot "." every 1K rows
consistent=y : ensure read-consistent image
file=TEST.dump : file to export
log=TEST_Exp.log : file to show exp progress
statistics=none : do not export statistics
owner=TEST : schema to export
userid=TEST/TEST@dhunt : username/password@Oracle-alias
[/tt]
Here is a sample "imp" command that I would use to read the dump file, above into the same schema name in another database:

imp buffer=150000000 grants=y feedback=1000 fromuser=TEST touser=TEST file=TEST.dump log=TEST_Imp.log  userid=TEST/TEST@dhunt

Here are the meanings of the "imp" parameters that I used in the example:
[tt]
imp : invoke the import utility
buffer=150000000 : allocate 150M ram for workarea
grants=y : import obj. privileges
feedback=1000 : display a dot "." every 1K rows
fromuser : name of source schema
touser : name of target schema
file=TEST.dump : file to import
log=TEST_Exp.log : file to show imp progress
userid=TEST/TEST@dhunt : username/password@Oracle-alias
[/tt]
Let us know if this answers your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sjh]

Hi Mufasa,

Thanks for your post! It was very helpful and easy to understand.

So it's correct to say that the "export table" functionality in PL SQL is different from the "EXP" command?

Many thanks!
Susie

 

[SantaMufasa]

Susie,

I apologise for my dimness, but I don't recognise your term, '...the "export table" functionality in PL SQL...'. Could you please post a reference or a sample of what you mean? Perhaps it is from my being an old, set-in-his-ways Oracler that I'm not familiar with your term.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sjh]

Hi Mufasa,

Sorry...my previous posts were vague. I meant the Export Tables utility in the PL/SQL Developer. (

http://www.allroundautomations.nl/plsqldev.html)

I am going to stick with using the EXP command. Sorry for the trouble & thanks again for your valuable help!

Susie