Oracle DAC Interview Questions and Answers [ TOP & MOST ASKED ]

Last updated on 14th Nov 2021, Blog, Interview Questions

This Oracle DAC Interview Questions have been designed specially to get you acquainted with the nature of questions you may encounter during your interview for the subject of Oracle DAC . As per my experience good interviewers hardly plan to ask any particular question during your interview, normally questions start with some basic concept of the subject and later they continue based on further discussion and what you answer.we are going to cover top 100 Oracle DAC Interview questions along with their detailed answers. We will be covering Oracle DAC scenario based interview questions, Oracle DAC interview questions for freshers as well as Oracle DAC interview questions and answers for experienced.

1.Illustrate the architecture of DAC.

Ans:

DAC comprises the DAC client and the DAC server. It is important to note they must realign themselves with Information Integration Service and Information Repository.

2.How to export and import DAC architecture?

Ans:

DAC export and import are primarily used for backup or repository metadata. The logical system and runtime objects can facilitate essential and commodity.

3.Can we facilitate the running of multiple plans related to accomplishments at the same time in DAC?

Ans:

Yes. However, it can be facilitated only when the accomplishment process is not loading into the same table.

4.How can you execute the SQL script with the help of DAC? If the answer is yes, then illustrate how it can be achieved?

Ans:

The SQL script can be executed with the help of the DAC server. However, it can be implemented only at the task level. It can be accomplished by selecting the SQL file in the process of execution.

5.What do you mean by an authentication file?

Ans:

The file of authentication usually authenticates the database in which the repository lies. On the other hand, if you opt for creating an authentication file, then you have the liberty to specify the particular table and password for a specified set of the database.

6.What do you mean by Table, Task Actions, and Index in DAC?

Ans:

The table refers to override the default behavior for assessing and truncating various tables that are being assigned to a particular type of database. On the other hand, Task Action refers to the fact that one can add multiple types of new functionalities related to the behavior of the tasks. It comprises failure action, success action, and failure restart. Moreover, index action refers to override the practice of creating and dropping indexes.

7.How can you differentiate between homogeneous and heterogeneous plans related to accomplishments in DAC?

Ans:

A different plan related to accomplishments refers to the fact that it can extract data from one or more chances of source systems that are dissimilar. For instance, a business organization can have a score of Siebel 7.8 in one location. On the other hand, an example of Oracle EBS 11 can be in another position. One can also opt for staggering the timing of data extraction when the professional is using this type of plan related to accomplishments.

8.Define how DAC can determine the tasks that are needed for a particular type of subject area?

Ans:

• It is vital to note that a subject area can be defined by assigning a fact table. Moreover, a subject area can also be specified by assigning a set of fact tables. When a subject area is adequately defined, DAC also performs the below-mentioned procedures to assess the essential tasks related to data warehousing.
• DAC is involved with the identification of the source tables for the tasks that are being identified in processes previous to the one that was called for to carry out data warehousing. DAC also adds tables to the places of a subject.
• With the help of DAC, one can also eliminate an assignment from a particular subject area by using add or remove command. These commands are located on the tasks tab. It is also found in the places of a subject tab. However, when someone deletes a particular assignment, it gets removed only from the area of a subject until and unless you refigure the data patterns that are located in the places of matter.

9.Illustrate the various types of DAC Repository Objects that are usually held in the containers related to the source system?

Ans:

• There exist multiple kinds of DAC repository objects that are essential in order to make sure that the data warehousing tasks are being accomplished in a proper manner. The following are the various objects related to the DAC Repository.
• Tables: They are also known as physical tables in DAC.
• Assignments: They are also popularly referred to as tasks and are usually known as a unit of work loading tables.
• Indexes: Indexes are also known as the physical DAC indexes which facilitate the value assignment of data.
• Data structure Tables: Data structure tables play a pivotal role in the proper measurement of various types of data. They are also referred to as the indexes related to DAC.
• Group of Tasks: The classification of assignments can be collected to run as a particular collective group.

10.Illustrate the process of how DAC usually keeps track of the time of refresh for the source tables and target tables?

Ans:

The dates related to refreshing are usually tracked for the tables. The tables can be a primary source or can also be a prime target. These primary and source-target tables are typically based on the completed trial of particular plans related to accomplishments. The DAC can even run the full load command for the assignments that are based on a table which is a primary source of a target.

11.Shed light on the merge and upgrade options for DAC version 7.8 and also for the new releases?

Ans:

The merge and update option for the DAC version can be carried out by using the merge and update process which is present in the DAC server. The Repository Upgrade is also used for updating DAC. You can use the simplified refresh base from the refresh base menu. It would allow you to update the repository of DAC from an earlier release of Oracle BI.

12.Define the execution related to Micro ETL Plans and also illustrate the process of building and running them?

Ans:

The performance compared to Micro ETL plans are also known as ETL techniques that can be scheduled at a fixed set of intervals. It is important to note that these micro plans related to accomplishments can be classified into half-hourly and hourly basis. They are associated with the management of subsets or small places of a subject. The DAC server can also track the time of refresh for various types of tables in the plans related to the accomplishments of the micro ETL.

13.Explain the areas where execution pertaining to Micro ETL plans has produced inaccurate results on various kinds of reports?

Ans:

It is important to note that the procedures related to Micro ETL can cause problems related to data inconsistencies. It can also create a massive issue of the availability of data and load on the database pertaining to transactional. One should be aware of the factors that can because the Micro ETL plans to produce wrong information.

14.Over All Architecture Of Dac?

Ans:

DAC server and DAC Client. They must co-locate with Informatica Integration service, repository service and Informatica repository.

15.Why We Should Use Dac And Not Control All Execution Through Informatica?

Ans:

For better performance management, such as creating index, dropping index, truncating before load. Without DAC a custom ETL process will be needed, which has to survive the upgrate.

16.Can We Run Multiple Execution Plan At The Same Time In Dac?

Ans:

Yes. only if the execution plan are not loading into the same table or using the same phyiscal table source.

17.Explain Dac Export/import.

Ans:

A way to import or export DAC repository metadata for upgrade or backup. Logical, System, runtime objects can be import/export.

18.Have You Change Any Of The Dac Parameters ? If So Which One And Why ?

Ans:

You have to understand what are the DAC parameters and the purpose of each. For example, Initial_extract_date can be modified when configure for initial full load, so the value for initial extract date will be used to filter out records from the source that are older than this date.

19.How Do You Determine The Informatica Server Maximum Sessions Parameter Setting In Dac?

Ans:

One you register informatica server in Dac client.

20.Can Dac Send An Email Just In Case Of Any Failures ?

Ans:

In DAC Client, toolbar, click email recipient, then in Tools–> DAC Server setup, Email configuration.

21.Can You Execute The Sql Scrip Through Dac ? If Yes How ?

Ans:

Yes, at task level, in execution type, select SQL file. As a bonus to this answer, this article explains how to run store procedures in DAC.

22.In Dac How You Can Disable Table Indexes Before Loading And Enable The Index Once Load Is Complete ?

Ans:

Just drop and recreate index.

23.Let Say You Are Running The Normal Incremental Load. But Just For Today You Want To Extract Data From Ap_invocies_all From 12/12/2011? How You Can Achieve This ?

Ans:

Modify the refresh date to be 12/12/2011.

24.How Dac Determines The Order Of Task Execution Within An Execution Plan ?

Ans:

Based on tasks source/target table, Task phase (extract dim, load fact etc) and ‘truncate always’ properties, to run them in particular order, create task group.

25.What Are Micro Etl Execution Plans ? How Can You Build And Run Them ?

Ans:

According to Oracle document:-

• Micro ETL execution plans are ETL processes that you schedule at very frequent intervals, such as hourly or half-hourly. They usually handle small subject areas or subsets of larger subject areas. The DAC tracks refresh dates for tables in micro ETL execution plans separately from other execution plans and uses these refresh dates in the change capture process.
• in design — subject areas, create copy of subject area, inactive the unwanted tasks and create new execution plan for this subject area.

26.From You Past Experience – Explain Scenario Where Micro Etl Execution Plans Produced Wrong Results On Reports?

Ans:

According to Oracle Document:-

• CAUTION: Micro ETL processes can cause issues with data inconsistencies, data availability, and additional load on the transactional database. Therefore, you should
• consider the following factors before implementing a micro ETL process:
• For related star schemas, if one schema is omitted from a micro ETL execution plan, the cross-star reports may be inaccurate. For example, if the Person fact table is refreshed more frequently than the Revenue fact table, a report that spans the Person and Revenue dimensional schemas may produce inconsistent results.

27.Let Say You Can Not Use Dac Scheduler To Schedule You Execution Plan. What Other Options Do You Have ? How You Can Achieve This ?

Ans:

Use Informatica scheduler.

28.Does Dac Keeps Track Of Refresh Dates For All The Source/target Tables ?

Ans:

Refresh dates are tracked only for tables that are either a primary source or a primary target on tasks in a completed run of an execution plan. The DAC runs the full load command for tasks on which a table is a primary source or target if the refresh date against the table is null. When there are multiple primary sources, the earliest of the refresh dates will trigger a full load or an incremental load. If any one of the primary source tables has no refresh date, then the DAC will run the full load command.

29.Can We Have Two Dac Server On The Same Machine ?

Ans:

You can run two DAC servers on the same machine as long as they are listening on different ports and pointing to two different repositories.

30.Explain Briefly What Kind Of Dac Repository Objects Held In Source System Containers ?

Ans:

Subject Areas — A logical grouping of tables related to a particular subject or application context. It also includes the tasks that are associated with the tables, as well as the tasks required to load the tables. Subject areas are assigned to execution plans, which can be scheduled for full or incremental loads.

31.What Is Authentication File ? If You Have Dac Client Installed Can You Access Dac Repository Without Authentication File ?

Ans:

When you configure a connection to the DAC Repository, the configuration process includes creating a new authentication file or selecting an existing authentication file. The authentication file authenticates the database in which the repository resides. If you create a new authentication file, you will specify the table owner and password for the database.

32.Explain Index, Table And Task Actions In Dac ?

Ans:

• Index action: Override the default behavior for dropping and creating indexes.
• Table action: Override the default behavior for truncating and analyzing tables.
• Task action: Can add new functionality of task behavior, such as precedinf action, success action, failure action, upon failure restart.

33.How Dac Handles Parameters At Runtime ?

Ans:

During an ETL execution, DAC reads and evaluates all parameters associated with that ETL run, including static and runtime parameters defined in DAC, parameters held in flat files, and parameters defined externally to DAC. DAC consolidates all the parameters for the ETL run, deduplicates any redundant parameters, and then creates an individual parameter file for each Informatica session. This file contains the evaluated name-value pairs for all parameters, both static and runtime, for each workflow that DAC executes.

34.How Dac Determines Tasks Required For Any Given Subject Area ?

Ans:

You define a subject area by specifying a fact table or set of fact tables to be the central table or tables in the subject area. When a subject area is defined,DAC performs the following process to determine the relevant tasks:-

• DAC identifies the dimension tables associated with the facts and adds these tables to the subject area.
• DAC identifies the related tables, such as aggregates, associated with the fact or dimension tables and adds them to the subject area definition.
• DAC identifies the tasks for which the dimension and fact tables listed in the two processes above are targets tables and adds these tasks into the subject area.

35.Difference Between Homogeneous And Heterogeneous Execution Plans?

Ans:

• Homogeneous: This type of execution plan extracts data from multiple instances of the same source system. For example, a business might have an instance of Oracle EBS 11i in one location and time zone and another instance of Oracle EBS 11i in another location and time zone. In such cases, the timing of data extraction from the different instances can be staggered to meet your business requirements.
• Heterogeneous: This type of execution plan extracts data from one or more instances of dissimilar source systems. For example, a business might have an instance of Siebel 7.8 in one location, an instance of Oracle EBS 11i in another location, and a second instance of Oracle EBS 11i in yet a third location. You can also stagger the timing of data extraction when you use this type of execution plan.

36.What Is Oracle?

Ans:

Oracle is a company. Oracle is also a database server, which manages data in a very structured way. It allows users to store and retrieve related data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.

37.What Is An Oracle Database?

Ans:

An Oracle database is a collection of data treated as a big unit in the database server.

38.What Is An Oracle Instance?

Ans:

Every running Oracle database is associated with an Oracle instance. When a database is started on a database server (regardless of the type of computer), Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and processes of an instance manage the associated database’s data efficiently and serve the one or multiple users of the database.

39.What Is An Oracle View?

Ans:

A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

40.What Is An Oracle Sequence?

Ans:

A sequence generates a serial list of unique numbers for numerical columns of a database’s tables.

41.What Is An Oracle Index?

Ans:

An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

42.What Is An Oracle Data File?

Ans:

An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.

43.What Are The Components Of Physical Database Structure Of Oracle Database?

Ans:

Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

44.What Are The Components Of Logical Database Structure Of Oracle Database?

Ans:

There are tablespaces and database’s schema objects.

45.What Is A Tablespace?

Ans:

A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

46.What Is System Tablespace And When Is It Created?

Ans:

Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

47.Explain The Relationship Among Database, Tablespace And Data File ?

Ans:

Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

48.What Is Schema?

Ans:

A schema is collection of database objects of a user.

49.What Are Schema Objects?

Ans:

Schema objects are the logical structures that directly refer to the database’s data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

50.Can Objects Of The Same Schema Reside In Different Tablespaces?

Ans:

Yes.

51.Can A Tablespace Hold Objects From Different Schemes?

Ans:

Yes.

52.What Is Partial Backup ?

Ans:

A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.

53.What Is Mirrored On-line Redo Log ?

Ans:

A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.

54.What Is Full Backup ?

Ans:

A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter.

55.Can A View Based On Another View ?

Ans:

Yes.

56.Can A Tablespace Hold Objects From Different Schemes ?

Ans:

Yes.

57.Can Objects Of The Same Schema Reside In Different Tablespace ?

Ans:

Yes.

58.What Is The Use Of Control File ?

Ans:

When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

59.Do View Contain Data ?

Ans:

Views do not contain or store data.

60.What Are The Referential Actions Supported By Foreign Key Integrity Constraint ?

Ans:

UPDATE and DELETE Restrict – A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade – When a referenced row is deleted all associated dependent rows are deleted.

61.What Are The Type Of Synonyms?

Ans:

There are two types of Synonyms:-

• Private
• Public.

62.What Is A Redo Log ?

Ans:

The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.

63.What Is An Index Segment ?

Ans:

Each Index has an Index segment that stores all of its data.

64.Explain The Relationship Among Database, Tablespace And Data File?

Ans:

Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

65.What Are The Different Type Of Segments ?

Ans:

The different type of Segments are:-

• Data Segment,
• Index Segment,
• Rollback Segment and
• Temporary Segment.

66.What Are Clusters ?

Ans:

Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

67.What Is An Integrity Constrains ?

Ans:

An integrity constraint is a declarative way to define a business rule for a column of a table.

68.What Is An Index ?

Ans:

An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

69.What Is An Extent ?

Ans:

An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.

70.What Is A View ?

Ans:

A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

71.What Is Table ?

Ans:

A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

72.Can A View Based On Another View?

Ans:

Yes.

73.What Are The Advantages Of Views?

Ans:

• Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
• Hide data complexity.
• Simplify commands for the user.
• Present the data in a different perspective from that of the base table.
• Store complex queries.

74.What Is A Synonym?

Ans:

A synonym is an alias for a table, view, sequence or program unit.

75.What Is A Private Synonym?

Ans:

Only its owner can access a private synonym.

76.What Is A Public Synonym?

Ans:

Any database user can access a public synonym.

77.What Are Synonyms Used For?

Ans:

• Mask the real name and owner of an object.
• Provide public access to an object.
• Provide location transparency for tables, views or program units of a remote database.
• Simplify the SQL statements for database users.

78.How Are The Index Updates?

Ans:

Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.

79.What Is Rollback Segment ?

Ans:

A Database contains one or more Rollback Segments to temporarily store “undo” information.

80.What Are The Characteristics Of Data Files ?

Ans:

A data file can be associated with only one database. Once created a data file can’t change size. One or more data files form a logical unit of database storage called a tablespace.

81.How To Define Data Block Size ?

Ans:

A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE data blocks. Block size is specified in INIT.ORA file and can’t be changed latter.

82.What Does A Control File Contain ?

Ans:

A Control file records the physical structure of the database. It contains the following information:-

• Database Name
• Names and locations of a database’s files and redolog files.
• Time stamp of database creation.

83.What Is Difference Between Unique Constraint And Primary Key Constraint ?

Ans:

A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can’t contain Nulls.

84.What Is Index Cluster ?

Ans:

A Cluster with an index on the Cluster Key.

85.When Does A Transaction End ?

Ans:

When it is committed or Rollbacked.

86.What Is The Effect Of Setting The Value “all_rows” For Optimizer_goal Parameter Of The Alter Session Command ? What Are The Factors That Affect Optimizer In Choosing An Optimization Approach ?

Ans:

The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.

87.What Is The Effect Of Setting The Value “choose” For Optimizer_goal, Parameter Of The Alter Session Command ?

Ans:

The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.

88.How Does One Create A New Database?

Ans:

One can create and modify Oracle databases using the Oracle “dbca” (Database Configuration Assistant) utility. The dbca utility is located in the $ORACLE_HOME/bin directory. The Oracle Universal Installer (oui) normally starts it after installing the database server software.

89.What Database Block Size Should I Use?

Ans:

Oracle recommends that your database block size match, or be multiples of your operating system block size. One can use smaller block sizes, but the performance cost is significant. Your choice should depend on the type of application you are running. If you have many small transactions as with OLTP, use a smaller block size. With fewer but larger transactions, as with a DSS application, use a larger block size. If you are using a volume manager, consider your “operating system block size” to be 8K. This is because volume manager products use 8K blocks (and this is not configurable).

90.What Are The Different Approaches Used By Optimizer In Choosing An Execution Plan ?

Ans:

The different approaches used by Optimizer in choosing an execution plan are:-

• Rule-based
• Cost-based.

91.What Does Rollback Do ?

Ans:

ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.

92.What Is Cost-based Approach To Optimization ?

Ans:

Considering available access paths and determining the most efficient execution plan based on statistics in the data dictionary for the tables accessed by the statement and their associated clusters and indexes.

93.What Does Commit Do ?

Ans:

COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.

94.How Are Extents Allocated To A Segment? (for Dba).

Ans:

Oracle8 and above rounds off extents to a multiple of 5 blocks when more than 5 blocks are requested. If one requests 16K or 2 blocks (assuming a 8K block size), Oracle doesn’t round it up to 5 blocks, but it allocates 2 blocks or 16K as requested. If one asks for 8 blocks, Oracle will round it up to 10 blocks.

95.Can One Rename A Database User (schema)? (for Dba).

Ans:

No, this is listed as Enhancement Request 158508. Workaround:-

• Do a user-level export of user A.
• create new user B.
• Import system/manager fromuser=A touser=B.
• Drop user A.

96.Define Transaction ?

Ans:

A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.

97.What Is Read-only Transaction ?

Ans:

A Read-Only transaction ensures that the results of each query executed in the transaction are consistant with respect to the same point in time.

98.What Is A Deadlock ?

Ans:

Two processes wating to update the rows of a table which are locked by the other process then deadlock arises. In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.

99.What Is A Schema ?

Ans:

The set of objects owned by user account is called the schema.

100.What Is An Index ? How It Is Implemented In Oracle Database ?

Ans:

An index is a database structure used by the server to have direct access of a row in a table. An index is automatically created when a unique of primary key constraint clause is specified in create table comman (Ver 7.0).