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 :
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 :
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 :
45. What is atomic refresh in mviews?
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?
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?
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?
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?
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?
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 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?
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 :
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?
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 :
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 :
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 :
/etc/oraInst.loc is pointer to central/local Oracle Inventory.
60. What is the difference between central/global inventory and local inventory?
Answer :
Related Oracle Database Administrator Interview Questions Articles: Data Warehouse Interview Questions and answers MySQL DBA Interview Questions and Answers Oracle DBA Interview Questions/FAQs – Part4
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.
ReplyDeleteOracle Training in Chennai | Oracle course in Chennai
This is good site and nice point of view.I learnt lots of useful information.
ReplyDeleteData Science Course in Indira nagar | Data Science Course in Electronic city
Python course in Kalyan nagar | Data Science course in Indira nagar
Data Science Course in Marathahalli | Data Science Course in BTM Layout
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
ReplyDeleteNice post
ReplyDeleteFor Blockchain training in Bangalore, Visit:
Blockchain training in Bangalore
Such A nice post... thanks For Sharing !!Great information for new guy like Hanuman Chalisa Lyrics
ReplyDeleteHi Sir, Some of the questions are not answered ,Can you please provide the ans.
ReplyDelete