Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Hiding export/import passwords

Re: Hiding export/import passwords

From: Radoulov, Dimitre <>
Date: Fri, 5 May 2006 09:26:42 +0200
Message-ID: <002101c67015$42a96660$1a03310a@MPILA9>

> Windows:
> exp '/ as sysdba' file=%expfile%.dmp log=%expfile%.log consistent=yes
> full=y
> statistics=none
> Unix:
> exp \'/ as sysdba\' file ....
> if you launch from cron:
> su - oracle -c "exp \'/ as sysdba\' file=...."

Be careful, because for connections as sysdba the parameter consistent is treated in a particular manner.

SYSDBA is used internally in the Oracle database and has specialized functions. Its behavior is not the same as for generalized users. For example, the SYS user cannot do a transaction level consisent read (read-only transaction). Queries by SYS will return changes made during the transaction even if SYS has set the transaction to be READ ONLY. Therefore export parameters like CONSISTENT, OBJECT_CONSISTENT, FLASHBACK_SCN, and FLASHBACK_TIME cannot be used.
Starting with Oracle10g, the export shows a warning that the export is not consistent when the export is started with CONSISTENT=Y and connects to the database with the user SYS (or as SYSDBA):

   EXP-00105: parameter CONSISTENT is not supported for this user

Note that Oracle automatically provides read consistency to a query so that all the data that the query sees comes from a single point in time (statement-level read consistency). For export this means that the export of table data is consistent. However, if a table contains nested tables, the outer table and each inner table are exported as separate transactions. And if a table is partitioned, each partition is exported as a separate transaction. If a nested table or a partitioned table was updated during the export, the data that is exported while connected as the SYS schema could be inconsistent.

Typically, there is no need to invoke Export or Import as SYSDBA, except in the following situations:
- at the request of Oracle technical support; - when exporting a transportable tablespace set with the old-style export utility (Oracle9i and Oracle8i);
- when importing a transportable tablespace set with the old-style import utility (Oracle10g, Oracle9i, and Oracle8i).

The examples below are based on:

- the export of table emp, owned by the demo schema scott.
- schema SYS with the password: change_on_install.
- alias 'instance' that is specified in the tnsnames.ora file and used for a 
connect to the database.

To invoke Export or Import as SYSDBA, use the following syntax (this syntax is



Received on Fri May 05 2006 - 02:26:42 CDT

Original text of this message