Off topic: IMP EXP as DBA

Toad for Oracle
1

Off topic: IMP EXP as DBA

2

Hi Jerry,

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.
I presume you mean:

exp test/password ... tables=test.table_name ...

We set up a brand new linux box with Oracle 11g (latest
release), same schema names but not with DBA role.
Always wise, TEST shouldn't have had the DBA role. However, as I have
secured a number of apps in the past, I know that sometimes the vendor
can't be bothered to, or simply can't, figure out the actual privs
requirred and simply does DBA, CONNECT and RESOURCE. (They also don't
know that DBA contains CONNECT and RESOURCE either!)

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.
This is expected. Any export, created by a user with DBA role granted
MUST be imported by another DBA enabled user.

The new environment folks do not wish to have the schemas
have DBA roles - I can understand.
:slight_smile:

What's going on?
See above. Because TEST had DBA, you must import with another DBA user.
Try "/ as sysdba" as a workaround until you sort out the TEST user by
revoking the DBA role and taking another export. Then you will be able
to import without needing to be a DBA.

Is there any work around to get those files in without DBA rights ?
Yes, two:

  1. imp "/ as sysdba" file=...
  2. Revoke DBA from TEST, export again and import will work.

Cheers,
Norm. [TeamT]

PS. Never export as "/ as sysdba" if you want CONSISTENT=Y exports.
Using SYS to export ignores consistent!

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk