Oracle DBA Interview Questions and answwer-- 2 in oracle

1) What is a datafile?
ANS:
Every Oracle database has one or more physical datafiles. Datafiles contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the datafiles allocated for a database.

2) What are the contents of control file?

ANS:
Database name, SCN, LSN, datafile locations, redolog locations, archive mode, DB Creation Time, RMAN Backup & Recovery Details, Flashback mode.

3) What is the use of redo log files?

ANS:

Online redo logs serve to protect the database in the event of an instance failure. Whenever a transaction is committed, the corresponding redo entries temporarily stored in redo log buffers of the system global area are written to an online redo log file by the background process LGWR.

4) What are the uses of undo tablespace or redo segments?

ANS:
Undo records are used to:

Roll back transactions when a ROLLBACK statement is issued
Recover the database
Provide read consistency
Analyze data as of an earlier point in time by using Flashback Query
Recover from logical corruptions using Flashback features

5) How undo tablespace can guarantee retain of required undo data?

ANS:

Alter tablespace undo_ts retention guarantee;


6) What is 01555 - snapshot too old error and how do you avoid it?

ANS:

http://www.dba-oracle.com/t_ora_01555_snapshot_old.htm

7) What is the use/size of temporary tablespace?

ANS:

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables

8) What is the use of password file?

ANS:

If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate this DBA. That is if (s)he is allowed to do so. Obviously, his password can not be stored in the database, because Oracle can not access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database. There are two distinct mechanisms to authenticate the DBA: using the password file or through the operating system.
The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. If it set either to shared or exclusive a password file will be used.

9)  How to create password file?

ANS:

$ orapwd file=orapwSID password=sys_password force=y nosysdba=y

10) How many types of indexes are there?
ANS:

Clustered and Non-Clustered

1.B-Tree index
2.Bitmap index
3.Unique index
4.Function based index

Implicit index and explicit index.
Explicit indexes are again of many types like simple index, unique index, Bitmap index, Functional index, Organisational index, cluster index.


11)  What is bitmap index & when it’ll be used?

ANS:
Bitmap indexes are preferred in Data warehousing environment.
Preferred when cardinality is low.


12) What is B-tree index & when it’ll be used?

ANS:

B-tree indexes are preferred in OLTP environment.
Preferred when cardinality is high.


13) How you will find out fragmentation of index?

ANS:

AUTO_SPACE_ADVISOR_JOB will run in daily maintenance window and report fragmented indexes/Tables.

analyze index validate structure;

This populates the table ‘index_stats’. It should be noted that this table contains only one row and therefore only one index can be analysed at a time.

An index should be considered for rebuilding under any of the following conditions:

* The percentage of deleted rows exceeds 30% of the total, i.e. if
del_lf_rows / lf_rows > 0.3.
* If the ‘HEIGHT’ is greater than 4.
* If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.


14) What is the difference between delete and truncate?]

ANS:

Truncate will release the space. Delete won’t.
Delete can be used to delete some records. Truncate can’t.
Delete can be rollbacked.
Delete will generate undo (Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence data removed by Delete command can be rolled back but not the data removed by TRUNCATE).
Truncate is a DDL statement whereas DELETE is a DML statement.
Truncate is faster than delete.

15)  What's the difference between a primary key and a unique key?
ANS:

Both primary key and unique enforce uniqueness of the column on which they are defined.
But by default primary key creates a clustered index on the column, where unique key creates a nonclustered index by default.
Primary key doesn't allow NULLs, but unique key allows one NULL only.

16) What is the difference between schema and user?

Schema is collection of user’s objects.


17)  What is the difference between SYSDBA, SYSOPER and SYSASM?
ANS:

SYSOPER can’t create and drop database.
SYSOPER can’t do incomplete recovery.
SYSOPER can’t change character set.
SYSOPER can’t CREATE DISKGROUP, ADD/DROP/RESIZE DISK
SYSASM can do anything SYSDBA can do.

18) What is the difference between SYS and SYSTEM?
SYSTEM can’t shutdown the database.
SYSTEM can’t create another SYSTEM, but SYS can create another SYS or SYSTEM.

19) What is the difference between view and materialized view?

View is logical, will store only the query, and will always gets latest data.
Mview is physical, will store the data, and may not get latest data.