ORA-28001: the password has expired

That’s the error you get for oracle user for whom password is expired.

One Solution – Connect using sqlplus to set new password, you can even use the same password you had earlier.

$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 13 01:12:07 2010

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

Enter user-name: myuser
Enter password:
ERROR:
ORA-28001: the password has expired

Changing password for myuser
New password:
Retype new password:
Password changed

Connected to:
Oracle Database 11g Release 11.2.0.1.0 – 64bit Production

Oracle Privileges to Import/Export data into any tablespace

Generally we can have a single user with DBA rights to import/export (using imp/exp utilities) data into any tablespace instead of assigning these privileges to every user account.

Suppose you have an oracle user appdba. Grant DBA role to appdba and you can use this user to import data into any tablespace like this:

imp appdba/pwd file=xyz.dmp fromuser=username touser=targetusername

Similarly export

exp appdba/pwd file=xyz.dmp owner=username

where owner is the username for which you want to export data

ORA-01996: GRANT failed: password file is full

If you face this error that means you have exhausted allowed number of users with sysdba privileges and are trying to create another one. Most probably you do not need that many user accounts with sysdba privileges.

run this query:

Select * from v$pwfile_users;

It will show you the list of all users with sysdba and sysoper roles.

To fix this:
Edit existing user accounts and under “System Privileges” remove all, you will probably need only “Unlimited Tablespace” system privilege for user or you can assign a fixed quote. Secondly make sure only “Connect” role is specified under Roles. This should be enough for most of user activities.

Host Credentials ERROR Invalid username and or password Oracle 11g

On Windows XP do following:

Go to Control Panel > Administrative Tools > Local Security Policy > Local Policies > User Rights Assignment

Search “Log on as batch job”, double click it, click Add User or Group and add the userid you are trying to use under Oracle 11g. Once you do so, you should be able to login under Oracle 11g using this user’s credentials.

Database Surrogate PKs – Few points

I am sure this is obvious stuff but anybody can overlook it, so I thought of adding it in my blog. Many programmers and database designers are using surrogate keys instead of natural keys as PKs (primary keys) now a days. Numeric surrogate keys, obviously are easier and better performance wise especially in cases where composite keys are required. At the same time, just adding a numeric PK is not enough, more constraints are required to enforce data integrity.

Here’s an example:

Suppose I have a table PRODUCT with fields:
product_id
vendor_id
product_name
date_of_manufacturing
date_of_expiry

Assume that PK is (product_id,vendor_id)

Now I decide to add a single column numeric surrogate key:

id NUMBER PK
product_id
vendor_id
product_name
date_of_manufacturing
date_of_expiry

This means all constraints that earlier PK enforced upon fields product_id and vendor_id are gone, so I need to EXPLICITLY define those to keep data in right spirits. Specifically:

* Add NOT NULL constraints for product_id & vendor_id
* Add UNIQUE constraints for product_id & vendor_id

Once we do above, we are fine!!