Saturday, March 17, 2012

Users Created Before the Database Existed

This example is from a version 9 database but I understand it also applies to later Oracle versions. There are several ways to create a database. One method is to copy datafiles from another database and create a new control file. If you do this, the user creation dates in the DBA_USERS view will come from the source database. They will therefore be before the creation date of the target database. You can see what I mean in the SQL below, which I ran in a copy of a production database. The source database was created in 2005 but the target database was not set up until 2012.
 
SQL> select min(created) from dba_users;
 
MIN(CREATED
-----------
15-JUN-2005
 
SQL> select created from v$database;
 
CREATED
-----------
13-MAR-2012
 
SQL>
 
This can be confusing when you first see it. I understand the same thing happens if you create a new database with dbca using a template.

No comments: