80 Best Oracle DBA Interview Questions Part2

Oracle Database Administrator Interview Questions FAQs with Answers Part2 [ 2023 ]

21. What is a datafile?
Answer :

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 the control file?
Answer :

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?
Answer :

24. What are the uses of undo tablespace or redo segments?
Answer :

25. How undo tablespace can guarantee to retain required undo data?
Answer :

Alter tablespace undo_ts retention guarantee;

26. What is ORA-01555 - snapshot too old error and how do you avoid it?
Answer :

27. What is the use/size of a temporary tablespace?
Answer :

28. What is the use of password file?
Answer :

29. How to create password file?
Answer :

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

30. How many types of indexes are there?
Answer :

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.

31. What is the bitmap index & when it’ll be used?
Answer :

Bitmap indexes are preferred in Data warehousing environments.
Preferred when cardinality is low.

32. What is the B-tree index & when it’ll be used?
Answer :

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

33. How you will find out fragmentation of index?
Answer :

AUTO_SPACE_ADVISOR_JOB will run in the 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.

34. What is the difference between delete and truncate?
Answer :

Truncate will release the space. Delete won’t.
Delete can be used to delete some records. Truncate can’t.
Delete can be rollbacked. Truncate can’t.
Delete will generate undo (Delete command will log the data changes in the log file whereas the truncate will simply remove the data without it. Hence data removed by the 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?
Answer :

Both primary key and unique enforce the 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?
Answer :

Schema is a collection of user’s objects.

37. What is the difference between SYSDBA, SYSOPER and SYSASM?
Answer :

SYSOPER can’t create and drop databases.
SYSOPER can’t do incomplete recovery.
SYSOPER can’t change the 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?
Answer :

SYSTEM can’t shut down the database.
SYSTEM can’t create another SYSTEM, but SYS can create another SYS or SYSTEM.

39. How to improve sqlldr (SQL*Loader) performance?
Answer :

40. What is the difference between view and materialized view?
Answer :

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

Related Oracle Interview Articles: Oracle DBA Interview Questions/FAQs – Part1    Oracle DBA Interview Questions/FAQs – Part3    Oracle DBA Interview Questions/FAQs – Part4