SYS.USER$ table in Oracle

last password change time, last locked, last expired, creation time, failed logon.
Oracle internal table SYS.USER$ has got many interesting columns for DBAs. This article describes some of them.

Internal table SYS.USER$ keeps both users and roles. The table is basic table that enlist database users and is referenced by almost all other dictionary views.

DESC sys.user$

Name         Null     Type           
------------ -------- -------------- 
USER#        NOT NULL NUMBER         
NAME         NOT NULL VARCHAR2(30)   
TYPE#        NOT NULL NUMBER         
PASSWORD              VARCHAR2(30)   
DATATS#      NOT NULL NUMBER         
TEMPTS#      NOT NULL NUMBER         
CTIME        NOT NULL DATE           
PTIME                 DATE           
EXPTIME               DATE           
LTIME                 DATE           
RESOURCE$    NOT NULL NUMBER         
AUDIT$                VARCHAR2(38)   
DEFROLE      NOT NULL NUMBER         
DEFGRP#               NUMBER         
DEFGRP_SEQ#           NUMBER         
ASTATUS      NOT NULL NUMBER         
LCOUNT       NOT NULL NUMBER         
DEFSCHCLASS           VARCHAR2(30)   
EXT_USERNAME          VARCHAR2(4000) 
SPARE1                NUMBER         
SPARE2                NUMBER         
SPARE3                NUMBER         
SPARE4                VARCHAR2(1000) 
SPARE5                VARCHAR2(1000) 
SPARE6                DATE


some of the columns have got following meaning:

NAME – name for user or role
TYPE# – 0 for role or 1 for user
CTIME – the date of creation
PTIME – the date the password was last changed
EXPTIME – the date the password has last expired
LTIME – the date the resource was last locked
LCOUNT – number of failed logon


In addition DBA usually looking for last logon that can be found in SYS.AUD$ table.

Backup and Restore Basic questions in ORACLE 11G

1.What is the difference between cold and hot backup?

COLD backup will be taken by shutting down the database, where as HOT backup will be taken while database is up and running

2.What happens when a database or table space is placed in begin backup mode?

• Datafile header will get freezed i.e CKPT will not update latest SCN
• DBWR still will write data into datafiles
• When end backup, CKPT will update the latest SCN to data file header

3.Why more redo will generate during hot backup?
It is to avoid fractured block as oracle will copy entire block as redo entry

4.What is fractured block?
A block which might contain inconsistent data. This happens because the speed of DBWR is different than OS copy during hot backup.

5.What is the difference between complete and incomplete recovery?
No data loss in complete recovery whereas some data loss is observed in incomplete recovery

6.What will happen if we use resetlogs?

• It will create new redolog files (if not already there)
• It will reset log sequence number to 1,2…etc
• Out of 100 datafiles, I lost 29 files. 

7.How you will identify which files to recover?
a.Using the view v$recovery_file

8.How to check if incomplete recovery is performed in the database?

SQL> select RESETLOGS_TIME,RESETLOGS_CHANGE from v$database;

9.I placed a tablespace in hot backup mode and datafile which is being backup is  lost. How you will recover it?

We can restore from old backup and apply all the archives till now

10.Yesterday night backup is successful. Today morning we added a datafile at 11  AM. After noon 3’o clock the newly added datafile is lost. Can I recover that  datafile? If so, how?

Yes we can recover it. We need to create that datafile using “alter database create datafile ‘path’;” command and then apply all the archives

11.What is the importance of archives during recovery process?
As they will store all the changes happened in the database, always we can do complete recovery if we have a perfect backup

12.How you will recover database when all copies of control file are lost?
We can do a complete recovery if we have a latest trace of it. Or else, we need to do incomplete recovery by restoring controlfile from last successful backup

13.Application team informed that an important table is dropped. How you will  recover it?
We can recover it by doing until time recovery. But this will affect other user transactions, so we need to get approval for this first

14.What are the pre-requisite factors you will consider before performing until time  recovery?
We need to see if other users are not getting any affect by doing this

15.You need to restore the database and then realized there are no control files.  How you will proceed?
Either we can resotre controlfiles first and do recovery (but a data loss is there as its incomplete recovery) or we can create new controlfile if we have a latest trace