We help IT Professionals succeed at work.

ORCLE_DB_PRIVILEGES.

sam15
sam15 used Ask the Experts™
on
I have two databases on two machines: one 9i and one is 11g.
I have one schema i need to export from 9i to 11g.

Assume you do not have dba role, what minimum privs you need to do that?

I think I would need to issue "drop user myapp" before doing the impor to clean up everything and then creating the user and then the import. I think this is the normal Standard procedure for doing it.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
you do not "need" to drop the myapp user,  so that's one privilege you don't need.

if you have the myappuser password,  you don't need any privileges for the export except create session in order to have exp log in.

on the import side you will need CREATE SESSION and CREATE xxxxxx privileges for each xxxxxx object type you want to import  (TABLE, PROCEDURE, TRIGGER, etc).
Most Valuable Expert 2011
Top Expert 2012

Commented:
if you want to migrate privileges granted to myappuser from other schema owners or system privileges you'll need additional privileges.
Most Valuable Expert 2011
Top Expert 2012

Commented:
if you do want to drop and recreate the user in its entirety then try

imp_full_database  and exp_full_database roles

Author

Commented:
Yes, the export works fine.

The import keeps failing though.

I think dropping the user is much easier than doing "create or replace" for each object.
I am nto sure if the script has "create or replace". It will fail with "Create" only.

But if i want it to go the easy way and drop user my app does the import create the user automatically and would i only need privs in target 11g DB for

1) drop user
2) create user
3) imp_full_database  
4) exp_full_database roles

I am also doing the export and import at the schema level (not database level).
Most Valuable Expert 2011
Top Expert 2012

Commented:
11g doesn't need the exp_full_database,  it only needs the imp_full_database.


you need the imp_full_database (or similar heightened privilegs)  in order to give grants to the user.

can you post the log of the import?

Author

Commented:
I was testing the import with another 9i instance.

When i got the account on the 11g machine, and I log into unix and i go to

$ cd /ora116/bin
$ export ORACLE_HOME=/ora116
$ export NLS_LANG=.WE8ISO8859P1
$imp

61079944: map: permission denied
Segmentation fault.

It seems the unix account is missing some privs unless I missed something?

This works fine on the 9i machine.

I am not sure also why you do not need "Drop user". IF you refresh a schme dont you drop it and recreate the user and then import. The imp will fail unless you drop all the tables and objects in schema which is tedious so it is much easier to dopr the schema and then recreate it and then do IMP. Do you agree I need these privs in 11g:

DROP USER
CREATE USER
IMP_FULL_DATABASE

or (dba role)

Author

Commented:
here is the import log i was mentioning before too

/apps $ imp test1/test1@apps file=appsexp.dmp log=apps_imp.log full=y

Import: Release 9.2.0.2.0 - Production on Fri Apr 20 11:48:57 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production

Export file created by EXPORT:V09.02.00 via direct path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully
Most Valuable Expert 2011
Top Expert 2012

Commented:
the error says it all - either use an account with similar privileges to the one used to do the export

or, go back to the original and export again with a different account

Author

Commented:
I only have one account. One is in production and one is in test.

I do the export using unix like this

$ exp appadmin/appadmiN@myapp file=myapp.dmp direct=y compress=n

do both account have to have exactly same privs for this to work?

It should work like running CREATE TABLE< CREATE OR REPLACE scripts for all objects which works if i do it manually.
Most Valuable Expert 2011
Top Expert 2012

Commented:
no, they don't need to be exactly the same, but if one is a DBA then other must be too.

the check for privileges occurs before the ddl is executed.
So, you're right the create table "should" work, but it imp doesn't get that far.

Author

Commented:
is there a way to do exp by scott without using the dba role?
or changing the DDL dump so it wont check for privileges first.
Most Valuable Expert 2011
Top Expert 2012

Commented:
if scott has dba,  then create a new user with exp_full_database  privilege

and then use that user to do the export

Author

Commented:
can you list the commands for that. is it something like this:

SQL> create user test identified by test;
SQL> grant connect session to test;
SQL> grant resource to test;
SQL> grant exp_full_Database to test
SQL> host exp scott/tiger@mydb ...
Most Valuable Expert 2011
Top Expert 2012

Commented:
create user test identified by test;
grant create session to test;
grant exp_full_database to test;
host exp test/test@mydb

Author

Commented:
i assume to export the scott schema you would add

host exp test/test@mydb owner=scott file=scott.dmp log=scott-exp.log

right?

I will give this a try. i think it shoud work.

Author

Commented:
What you told me to do did not work.

I did create another user "test" and did the export of scott schema using

exp test/test@mydb owner=scott file=scott..dmp

When i tried to do the import to a new account i createI got same message

IMP-00013: only a DBA can import a file exported by anotehr DBA
IMP-00000: Import terminated unsuccessfully.

You can try that and see for yourself.

Did i do anything wrong with the EXP or do you have other suggestions?