Oracle DBA Interview Questions 3
1) What are materialized view refresh types and which is default?
ANS:
Complete, fast, force(default)
2) How to find out when was a materialized view refreshed?
ANS:
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;
3) What is atomic refresh in mviews?
ANS:
From Oracle 10g, complete refresh of 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);
4) How to find out whether database/tablespace/datafile is in backup mode or not?
ANS:
Query V$BACKUP view.
5) What is row chaining?
ANS:
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.
6) What is row migration?
ANS:
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 entire row to new block.
7) What are different types of partitions?
ANS:
With Oracle8, Range partitioning (on single column) was introduced.
With Oracle8i, Hash and Composite(Range-Hash) partitioning was introduced.
With Oracle9i, List partitioning and Composite(Range-List) partitioning was 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] was introduced.
8) What is local partitioned index and global partitioned index?
ANS:
A local index is an index on a partitioned table which 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 tables which are partitioned using a different partitioning key from the table and can have different number of partitions. Global partitioned indexes can only be partitioned using range partitioning.
9) How you will recover if you lost one/all control file(s)?
10) Why more archivelogs are generated, when database is begin backup mode?
ANS:
During begin backup mode datafile headers get freezed and as result row information cannot be retrieved as a result the entire block is copied to redo logs as a result more redo generated and more log switch 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. 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.
11) What UNIX parameters you will set while Oracle installation?
ANS:
shmmax, shmmni, shmall, sem,
12) What is the use of inittrans and maxtrans in table definition?
13) What are differences between dbms_job and dbms_schedular?
Through dbms_schedular we can schedule OS level jobs also.
14) What are differences between dbms_schedular and cron jobs?
Through dbms_schedular we can schedule database jobs, through cron we can’t set.
15) Difference between CPU & PSU patches?
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.
16) What you will do if (local) inventory corrupted [or] opatch lsinventory is giving error?
17) What are the entries/location of oraInst.loc?
ANS:
/etc/oraInst.loc is pointer to central/local Oracle Inventory.
18) What is the difference between central/global inventory and local inventory?
ANS:
ANS:
Complete, fast, force(default)
2) How to find out when was a materialized view refreshed?
ANS:
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;
3) What is atomic refresh in mviews?
ANS:
From Oracle 10g, complete refresh of 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);
4) How to find out whether database/tablespace/datafile is in backup mode or not?
ANS:
Query V$BACKUP view.
5) What is row chaining?
ANS:
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.
6) What is row migration?
ANS:
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 entire row to new block.
7) What are different types of partitions?
ANS:
With Oracle8, Range partitioning (on single column) was introduced.
With Oracle8i, Hash and Composite(Range-Hash) partitioning was introduced.
With Oracle9i, List partitioning and Composite(Range-List) partitioning was 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] was introduced.
8) What is local partitioned index and global partitioned index?
ANS:
A local index is an index on a partitioned table which 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 tables which are partitioned using a different partitioning key from the table and can have different number of partitions. Global partitioned indexes can only be partitioned using range partitioning.
9) How you will recover if you lost one/all control file(s)?
10) Why more archivelogs are generated, when database is begin backup mode?
ANS:
During begin backup mode datafile headers get freezed and as result row information cannot be retrieved as a result the entire block is copied to redo logs as a result more redo generated and more log switch 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. 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.
11) What UNIX parameters you will set while Oracle installation?
ANS:
shmmax, shmmni, shmall, sem,
12) What is the use of inittrans and maxtrans in table definition?
13) What are differences between dbms_job and dbms_schedular?
Through dbms_schedular we can schedule OS level jobs also.
14) What are differences between dbms_schedular and cron jobs?
Through dbms_schedular we can schedule database jobs, through cron we can’t set.
15) Difference between CPU & PSU patches?
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.
16) What you will do if (local) inventory corrupted [or] opatch lsinventory is giving error?
17) What are the entries/location of oraInst.loc?
ANS:
/etc/oraInst.loc is pointer to central/local Oracle Inventory.
18) What is the difference between central/global inventory and local inventory?
ANS:
Comments
Post a Comment