50 Best Oracle Interview Questions and Answers[Updated 2021]

Table of Contents

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

Cyberithub