July 10, 2022

Top 80 Oracle Database Administrator Interview Questions/FAQs Part3

Oracle DBA Interview Questions/FAQs Part3


41. What are materialized view refresh types and which is the default?
Answer :
Complete, fast, force(default)

42. How fast refresh happens?
Answer :

43. How to find out when was a materialized view refreshed?
Answer :
Query dba_mviews or dba_mview_analysis or dba_mview_refresh_times
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mviews;
(or)
SQL> select NAME, to_char(LAST_REFRESH,'YYYY-MM-DD HH24:MI:SS') from dba_mview_refresh_times;
(or)
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mview_analysis;

44. What is materialized view log (type)?
Answer :
Answer :
From Oracle 10g, complete refresh of a single materialized view can do delete instead of truncate. To force the refresh to do truncate instead of delete, parameter ATOMIC_REFRESH must be set to false.

ATOMIC_REFRESH = FALSE, mview will be truncated and whole data will be inserted. The refresh will go faster, and no undo will be generated.
ATOMIC_REFRESH = TRUE (default), mview will be deleted and whole data will be inserted. Undo will be generated. We will have access at all times even while it is being refreshed.

SQL> EXEC DBMS_MVIEW.REFRESH('mv_emp', 'C', atomic_refresh=FALSE);

46. How to find out whether database/tablespace/datafile is in backup mode or not?
Answer :
Query V$BACKUP view.

47. What is row chaining?
Answer :
If the row is too large to fit into an empty data block in this case the oracle stores the data for the row in a chain of one or more data blocks. Can occur when the row is inserted.

48. What is row migration?
Answer :
An update statement increases the amount of data in a row so that the row no longer fits in its data blocks. Now the oracle tries to find another free block with enough space to hold the entire row if such a block is available oracle moves the entire row to the new block.

49. What are the different types of partitions?
Answer :
With Oracle8, Range partitioning (on a single column) was introduced.
With Oracle8i, Hash and Composite(Range-Hash) partitioning were introduced.
With Oracle9i, List partitioning and Composite(Range-List) partitioning were introduced.
With Oracle 11g, Interval partitioning, REFerence partitioning, Virtual column based partitioning, System partitioning and Composite partitioning [Range-Range, List-List, List-Range, List-Hash, Interval-Range, Interval-List, Interval-Interval] were introduced.

50. What are local partitioned index and global partitioned index?
Answer :
A local index is an index on a partitioned table that is partitioned in the exact same manner as the underlying partitioned table. Each partition of a local index corresponds to one and only one partition of the underlying table.

A global partitioned index is an index on a partitioned or non-partitioned table which are partitioned using a different partitioning key from the table and can have a different number of partitions. Global partitioned indexes can only be partitioned using range partitioning.

51. How you will recover if you lost one/all control file(s)?
Answer :

52. Why are more archive logs are generated, when the database is in begin backup mode?
Answer :
During begin backup mode datafile headers get frozen and as a result row information cannot be retrieved as a result the entire block is copied to redo logs as a result more redo is generated and more log switches and in turn more archive logs. Normally only deltas (change vectors) are logged to the redo logs. When in backup mode, Oracle will write complete changed blocks to the redo log files.

Mainly to overcome fractured blocks. In most of the cases Oracle block size is equal to or a multiple of the operating system block size.

e.g. Consider Oracle blocksize is 2k and OSBlocksize is 4k. so each OS Block is comprised of 2 Oracle Blocks. Now you are doing an update when your db is in backup mode. An Oracle Block is updating and at the same time backup is happening on the OS block which is having this particular DB block. Backup will not be consistent since the one part of the block is being updated and at the same time it is copied to the backup location. In this case we will have a fractured block, so as to avoid this Oracle will copy the whole OS block to redo logfile which can be used for recovery. Because of this redo generation is more.

53. What UNIX parameters you will set while Oracle installation?
Answer :
shmmax, shmmni, shmall, sem, 

54. What is the use of inittrans and maxtrans in table definition?
Answer :

55. What are the differences between dbms_job and dbms_schedular?
Answer :
Through dbms_schedular we can schedule OS level jobs also.

56. What are the differences between dbms_schedular and cron jobs?
Answer :
Through dbms_schedular we can schedule database jobs, through cron we can’t set DB jobs.

57. Difference between CPU & PSU patches?
Answer :
CPU - Critical Patch Update - includes only Security related patches.
PSU - Patch Set Update - includes CPU + other patches deemed important enough to be released prior to a minor (or major) version release.

58. What you will do if (local) inventory corrupted [or] opatch lsinventory is giving an error?
Answer :

59. What are the entries/location of oraInst.loc?
Answer :
/etc/oraInst.loc is pointer to central/local Oracle Inventory.

60. What is the difference between central/global inventory and local inventory?
Answer :

6 comments:

  1. Thanks for sharing this oracle dba interview questions. It is really helpful, I have bookmarked this page for my future reference. Keep sharing more like this.
    Oracle Training in Chennai | Oracle course in Chennai

    ReplyDelete
  2. Thanks for such a great article here. I was searching for something like this for quite a long time and at last, I’ve found it on your blog.thanks for sharing

    ReplyDelete
  3. Nice post
    For Blockchain training in Bangalore, Visit:
    Blockchain training in Bangalore

    ReplyDelete
  4. Such A nice post... thanks For Sharing !!Great information for new guy like Hanuman Chalisa Lyrics

    ReplyDelete
  5. Hi Sir, Some of the questions are not answered ,Can you please provide the ans.

    ReplyDelete