We had an automated routine (via .bat file ) that did an EXP of one table from a
specific schema (i.e. TEST) using just the password/schema
TEST had a DBA role at the time.
We set up a brand new linux box with Oracle 11g (latest release), same schema
names but not with DBA role.
We utilized TOAD to try and import into TEST as TEST. It errors saying that it
was exported with DBA rights and it can not be imported because the user
doesn’t have DBA rights.
The new environment folks do not wish to have the schemas have DBA roles –
I can understand.
What’s going on?
We take files in and out all day, so importing as DBA then exporting without DBA
is not an option right now.
Is there any work around to get those files in without DBA rights ?
Is there any work around to get those files in without DBA rights ?
From a developers perspective, I’d probably go through the files looking
for hard-coded schemas that don’t belong to the owning schema. Odds are
those would be what the DBA rights are needed for.
Then, with that information in hand, you could form an appropriate strategy that
would work.
I’d think though that you first need to find out exactly what DBA access
the import would require before you could decide how to handle it.
For starters on your 11gR2 DB your test user should have the role
IMP_FULL_DATABASE. E xporting and importing are DBA activities since they need
to access DBA objects like sys DBMS_ packages and tables. So you don’t and
shouldn’t have to give someone the DBA role to do imports just the correct role.
Also I would look at switching over to using Data Pump since at some point in
maybe Oracle 12x imp and exp will disappear. In that case you will need the
roles DATAPUMP_EXP_FULL_DATABASE and DATAPUMP_IMP_FULL_DATABASE.