Oracle DBA Interview Questions/FAQs Part2
21. What is a datafile?
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.
22. What are the contents of control file?
Database name, SCN, LSN, datafile locations, redolog locations, archive mode, DB Creation Time, RMAN Backup & Recovery Details, Flashback mode.
23. What is the use of redo log files?
24. What are the uses of undo tablespace or redo segments?
25. How undo tablespace can guarantee retain of required undo data?
Alter tablespace undo_ts retention guarantee;
26. What is ORA-01555 - snapshot too old error and how do you avoid it?
27. What is the use/size of temporary tablespace?
28. What is the use of password file?
29. How to create password file?
$ orapwd file=orapwSID password=sys_password force=y nosysdba=y
30. How many types of indexes are there?
Clustered and Non-Clustered
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.
31. What is bitmap index & when it’ll be used?
Bitmap indexes are preferred in Data warehousing environment.
Preferred when cardinality is low.
32. What is B-tree index & when it’ll be used?
B-tree indexes are preferred in OLTP environment.
Preferred when cardinality is high.
33. How you will find out fragmentation of index?
AUTO_SPACE_ADVISOR_JOB will run in daily maintenance window and report fragmented indexes/Tables.
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.
34. What is the difference between delete and truncate?
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.
35. What's the difference between a primary key and a unique key?
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.
36. What is the difference between schema and user?
Schema is collection of user’s objects.
37. What is the difference between SYSDBA, SYSOPER and SYSASM?
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.
38. 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.
39. How to improve sqlldr (SQL*Loader) performance?
40. 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.
Related Articles: Oracle DBA Interview Questions/FAQs – Part1 Oracle DBA Interview Questions/FAQs – Part3 Oracle DBA Interview Questions/FAQs – Part4