Using SQLPLUS, Import, and Export Without a Password

Introduction
Storing passwords in plain text in your shell scripts can be a bad practice. If you need to run an export or import from a script and do not want to store your password in the script, you can perform the import/export as SYSDBA. As long as you're in the dba/oradba group and can log into Oracle as SYSDBA without a password you can perform your imports and exports without a password as well.

Using SQLPLUS Without a Password
If your UNIX / Windows account is part of the the 'dba' or 'oradba' operating system groups, you can log into SQLPLUS without a password as sysdba.

[troy@myserver home]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 10 09:40:28 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

Using Import and Export (imp / exp) Without a Password
The same technique can also be applied to performing imports and exports without a password.

[troy@myserver home]$ exp \'/ as sysdba\' file=emp.dmp OWNER=scott

Export: Release 9.2.0.4.0 - Production on Tue Jan 10 08:27:49 2006

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

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         15 rows exported
. . exporting table                      EMP_AUDIT          1 rows exported
. . exporting table                       SALGRADE          5 rows exported
. exporting synonyms
. exporting views
...

...and the same is of course true for doing imports:

[troy@myserver home]$ imp \'/ as sysdba\' file=emp.dmp FROMUSER=scott TOUSER=scott

Import: Release 9.2.0.4.0 - Production on Tue Jan 10 08:29:17 2006

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


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

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. . importing table                        "BONUS"          0 rows imported
. . importing table                         "DEPT"          4 rows imported
. . importing table                          "EMP"         15 rows imported
. . importing table                    "EMP_AUDIT"          1 rows imported
. . importing table                     "SALGRADE"          5 rows imported
About to enable constraints...
Import terminated successfully without warnings.

As you can see, using these examples in scripts can be very useful because you can avoid any type of password management since you're logging in as SYSDBA without a password.
share on: facebook