Oracle: Password Policy

Well working with a new PeopleSoft Oracle Database, I went to create a new connect id user on the database with a relatively straight forward password and the database angry told me – NO. It said that the password policy required specific elements to be included with the password.  As most people know the system administrator password and connect id password associated with the Oracle Database need to be 8 characters long and should only contact alpha and numeric characters.  Seriously do not try to make it long or shorter and never put special characters in the password.  Over the years I have been mesmerized by the number of times this password has caused me grief.

So if there is a complex password policy set on the default profile you can turn it off by issuing the following command within sqlplus:

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_VERIFY_FUNCTION NULL;

To determine what the profile is, you can issue the following statement:

select profile from DBA_USERS where username = '<username>';

Good To Know:

There are many elements in the profile that can cause issues one of them is the password expiry option.  Often not a good plan to have you main database account to stop functioning because of a password expiration, because Murphy’s Law says it will expire the day your DBA is on vacation and they will NOT pick the phone up that day.

Click here to go article:  Oracle Database User – Password Expiry

Oracle Database Export Data Pump (expdp):  Directory Default Setup

Data Export Pump utility (expdp) is designed for unloading data and metadata into a set of operating system files called a dump file set. The Data Export Pump Dump file set can be imported only by the Data Import Pump utility (impdp). The Data Export Pump Dump file set can be imported on the same system or it can be moved to another system and loaded there. The Data Export Pump Dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The Data Export Pump Dump files are written in a proprietary, binary format. During an import operation, the Data Import Pump utility uses these Data Export Pump Dump files to locate each database object in the dump file set.

Because the dump files are written by the server, rather than by the client, the data base administrator (DBA) must create directory objects. In order to have the Data Export Pump Dump file be in a specific location you will need to do the following:

Step 1:

Login into the database using the command:  sqlplus / as sysdba

Step 2:

SQL> create directory dmpdirectory as ‘/my/new/dump/directory’;

Step 3:

SQL> grant read, write on directory dmpdirectory to scott;

Step 4:

Execute the command:  expdp scott/tiger directory=dmpdirectory dumpfile=scottdata.dmp

Good To Know:

As of Oracle 10gR2, there is a specific directory that is used by default:  DATA_PUMP_DIR, to see what the default value is you can run the command:

SQL> select directory_path from dba_directories where directory_name = ‘DATA_PUMP_DIR’;

DIRECTORY_PATH
--------------------------------------------------------------------------------
/oracle/product/11.2.0.4/db_1/rdbms/log/

Here is a great link for working with Oracle Data Pump.
Happy Exporting!