In this article, we will go through 50 Best Oracle Interview Questions and Answers which generally gets asked in most of the Interviews. You might have observed many of the Enterprises are using Oracle Database so it is very important to know about frequently asked Oracle Interview Questions. This is also very important for those who is planning build his/her career in DB domain. Specially for DBA domain individuals, below Oracle Interview Questions and Answers are very important. I will also bring few more Oracle Interview Questions in upcoming articles.

Oracle Interview Questions and Answers

Also Read: Easy Steps to Install Oracle Database 12c in Windows 10

1. How to set automatic memory management in Oracle DB ?

Ans. By setting MEMORY_TARGET Parameter

2. Which parameters one has to ignore in case of Automatic Memory Management ?

Ans. SGA_TARGET,PGA_AGGREGATE_TARGET,DB_CACHE_SIZE,SHARED_POOL_SIZE,LARGE_POOL_SIZE and JAVA_POOL_SIZE

3. Which buffer cache is recommended when a certain tables are referenced frequently ?

Ans. KEEP Buffer Pool

4. Which algorithm is used to remove objects from Buffer Cache ?

Ans. Least Used Algorithm

5. What should you do when automatic memory management does not allocate enough memory to some pool ?

Ans. You need to manually allocate it. In that case, it will never decrease from the specified allocation.

6. Which view provides the currently allocated memory ?

Ans. V$MEMORY_DYNAMIC_COMPONENTS

7. How to enable row movement for shrinking a table ?

Ans. alter table inv enable row movement

8. Where does all the log file group and thread details are stored ?

Ans. In controlfile

9. Which command is used to refresh the control file records ?

Ans. catalog

10. What is the default period set for CONTROL_FILE_RECORD_KEEP_TIME parameter ?

Ans. 7 days

11. Which command is used to manually rebuild the control file ?

Ans. create control file

12. What are the two types of channels used in RMAN process ?

Ans. Disk channels and Tape Channels

13. Can we use both disk channels and tape channels in a backup ?

Ans. No.Only one channel can use used in a backup.

14. Which package is used to access the tables in control file ?

Ans. SYS.DBMS_RCVMAN

15. Which parameter you need to set in init.ora to enable tape I/O slave ?

Ans. BACKUP_TAPE_IO_SLAVES

16. What are the two different types of I/O slaves RMAN Support ?

Ans. Disk I/O Slaves and Tape I/O Slaves.

17. What is Program Global Area(PGA) ?

Ans. It is nonshared memory region that contains data and control information for a Server Process.

18. What is System Global Area(SGA) ?

Ans. It is a shared memory location that contains data and control information for a database schema.

19. Can we rollback changes done through DDL Commands ?

Ans. No

20. Why can’t we rollback changes done through DDL Commands ?

Ans. DDL commands does changes directly into database hence it cannot be rollback.

21. Why DDL commands are faster than DML Commands ?

Ans. Due to its direct interaction with database.

22. What are the different groups of command SQL has ?

Ans. DDL,DML,TCL and DCL

23. Which group of commands usually has an implicit commit ?

Ans. DDL Commands

24. What are the few commands under DDL ?

Ans. alter,create,drop,purge

25. Which option is usually recommended with alter command to remove large size columns ?

Ans. set unused(COLUMN_NAME)

26. What does set unused option will do with column ?

Ans. It will logically remove the column from table.

27. What is the option used with alter statement to remove all the unused columns ?

Ans. drop unused columns

28. Can we remove the hiding of column which was done through set unused option ?

Ans. No.It can only be dropped now using drop unused columns.

29. Can we rollback the changes done through DML Commands ?

Ans. Yes

30. Why DML commands are slower than DDL commands ?

Ans. It is because it interacts with buffer first and then on commit necessary changes will be done on database.

31. What are the few commands in DDL group ?

Ans. select,delete,merge,update and insert

32. Does DML Commands has implicit commit ?

Ans. No

33. What are the commands used to delete data from a table ?

Ans. Delete and Truncate

34. Can you delete a single row using truncate command ?

Ans. No.You need to use delete command for that.

35. What is the command to set transaction read only ?

Ans. set transaction read only

36. What is default permission mode on transaction ?

Ans. Read,Write

37. What is the use of System Monitor Process(SMON) ?

Ans. It is used to perform recovery at instance startup.

38. What is the use of Process Monitor(PMON) ?

Ans. It is used to perform recovery when a user process fails.

39. What is the use of Log writer process ?

Ans. It is used to write redo log buffer to a redo log file on a disk.

40. What is the function of database writer process ?

Ans. A database writer process writes data from buffer cache to data files.

41. What is the use of checkpoint ?

Ans. A checkpoint is used to sync the updated data blocks in buffer cache with the data files on disk.

42. What is System Change Number(SCN) ?

Ans. It is a unique number which Oracle keeps incrementing after every commit.

43. What is the use of archiver process ?

Ans. It is used to copy the redo log files from database to specified storage device after a log switch.

44. When does archiver process runs ?

Ans. Only when database is in archival mode.

45. How to enable dynamic increase in data file ?

Ans. By setting AUTOEXTEND=ON

46. Which command is used to add datafile ?

Ans. add datafile

47. What are the different modes to shutdown a database ?

Ans. There are four modes to shutdown a database:-
a)Immediate
b)Transactional
c)Normal
d)Abort

48. What are the different modes to start a database ?

Ans. nomount,mount and open

49. What is the option you need to set to hold the undo data to undo tablespace ?

Ans. retention guarantee

50. What is the command to create a user in Oracle ?

Ans. create user

 

Also Read: Oracle Database 11g Performance

Reference: Oracle Database 12C RMAN Backup and Recovery