-2

I am new to Oracle. I have 2 Oracle 10g systems running. I need to export database in 1 system and then import the dmp file into another Oracle system using exp and imp commands

I cannot use data pumps.

Questions 1- When exporting and importing databases, must I stop the databases running ? I need to exp and imp them while databases are running. I cannot stop the DBs running. 2- What is the SQL query to find out
a) what db system priviledges the user has. I need to find out if a certain user has the priviledge to imp and exp. b) how to create and add new user to be able to exp and imp databases 3- In using imp command, can the fromuser and touser values be the same user value ? 4- Must I drop the db on the target box before I could import to prevent object duplication errors ?

Any help is appreciated.

Thank you. Sincerely.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
chz
  • 355
  • 1
  • 7
  • 21

1 Answers1

2

  1. No you don't need to stop the database. You might want to use CONSISTENT=Y

  2. SELECT * FROM USER_SYS_PRIVS Generally though a user doesn't need any special privileges to export from their own schema or import to their own schema. In the latter you may need CREATE TABLE etc.

  3. If the fromuser and touser are the same you don't need to specify either

  4. You definitely don't want to drop the database (unless you are going to do an RMAN database clone). You might want to drop existing schema objects.

If you are going to move an entire database, rather than just a schema, I'd look into RMAN rather than IMP/EXP unless the database is REALLY small (eg < 10 GBs)

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
  • Hi Gary Thanks for responding – chz Jan 26 '11 at 02:47
  • Gary, with regards to 1) does exp exports both schema and data or does it only export schema only ? 2) I dont ge t your reason that I may need to create table etc. I like to import schema AND data that's was exported. Can imp and exp still do that ? 3) If I dont need to specify fromuser and touser, wouldn't that and I tried it myself, imp complains of duplicate objects and cannot import. 4) What's the actual command string to just drop existing schema objects ? Good day. – chz Jan 26 '11 at 03:12