Oracle 10g Import Export question

Question

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.

Answer 1

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)