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

1.Illustrate the design of DAC?

Ans:

contains the DAC consumer and therefore the DAC server. it’s necessary to notice they have to align themselves with info Integration Service and data Repository.

2.How to export and import DAC architecture?

Ans:

DAC export and import square measure primarily used for backup or repository information. The system and runtime objects will facilitate essential and trade goods.

3.Can we have a tendency to facilitate the running of multiple plans associated with accomplishments at a similar time in DAC?

Ans:

Yes. However, it is expedited only if the accomplishment method isn’t loading into a similar table.

4.How are you able to execute the SQL script with the assistance of DAC? If the solution is affirmative, then illustrate however it is achieved?

Ans:

The SQL script is dead with the assistance of the DAC server. However, it is enforced solely at the task level. It is accomplished by choosing the SQL get into the method of execution.

5.What does one mean by an associate degree authentication file?

Ans:

The file of authentication typically authenticates the information within which the repository lies. On the other hand, if you decide to make an associate degree authentication file, then you have got the freedom to specify the actual table and secret for a mere set of the information.

6.What does one mean by Table, Task Actions, and Index in DAC?

Ans:

The table refers to override the default behavior for assessing and truncating numerous tables that square measure being allotted to a selected kind of information. On the other hand, Task Action refers to the actual fact that one will add multiple forms of new functionalities associated with the behavior of the tasks. It contains failure action, success action, and failure restart. Moreover, index action refers to overriding the observer of making and dropping indexes.

7.How are you able to differentiate between homogeneous and heterogeneous plans associated with accomplishments in DAC?

Ans:

A different arrangement associated with accomplishments refers to the actual fact that it will extract information from one or a lot of possibilities of supply systems that square measure dissimilar. For example, a business organization will have a score of Siebel seven.8 in one location. On the opposite hand, an associate degree example of Oracle compass point eleven is in another position. One can even decide on staggering the temporal order of information extraction once the skilled is victimizing this sort of arrangement associated with accomplishments.

8.Define however DAC will verify the tasks that square measure required for a selected kind of subject area?

Ans:

• It is very important to notice that a subject matter space is outlined by distribution of a truth table. Moreover, a subject matter space can even be mere by distribution of a group of truth tables. Once a subject matter space is sufficiently outlined, DAC additionally performs the below-mentioned procedures to assess the essential tasks associated with information reposition.
• DAC involves the identification of the supply tables for the tasks that square measure being known in processes previous to the one that was required to hold out information reposition. DAC additionally adds tables to the places of a subject matter.
• With the assistance of DAC, one can even eliminate associate degree assignment from a selected study by victimization add or take away command. These commands square measure placed on the tasks tab. it’s additionally found within the places of a subject matter tab. However, once somebody deletes a selected assignment, it gets removed solely from {the square measure|the world|the realm} of a subject matter till and unless you refigure the info patterns that are placed within the places of matter.

9.Illustrate the assorted forms of DAC Repository Objects that square measure typically command within the containers associated with the supply system?

Ans:

There exist multiple styles of DAC repository objects that square measure essential so as to form positive that the info reposition tasks square measure being accomplished during a correct manner. The subsequent square measures the assorted objects associated with the DAC Repository.

Tables: they’re additionally referred to as physical tables in DAC.

Assignments: {they square measure|they’re} additionally popularly said as tasks and are typically referred to as a unit of labor loading tables.

Indexes: Indexes are referred to as the physical DAC indexes that facilitate the worth assignment of information.

Data structure Tables: arrangement tables play an important role within the correct measure of varied forms of information. they’re additionally said because the indexes are associated with DAC.

Group of Tasks: The classification of assignments is collected to run as a selected collective cluster.

10.Illustrate the method of however DAC typically keeps track of the time of refresh for the supply tables and target tables?

Ans:

The dates associated with refreshing square measure are typically half-tracked for the tables. The table is a primary supply or can even be a major target. These primary and source-target tables square measure generally supported the finished trial of specific plans associated with accomplishments. The DAC will even run the total load command for the assignments that square measure supports a table that could be a primary supply of a target.

11.Shed light-weight on the merge and upgrade choices for DAC version seven.8 and additionally for the new releases?

Ans:

The merge and update possibility for the DAC version is distributed by exploiting the merge and update method that is given within the DAC server. The Repository Upgrade is additionally used for changing DAC. you’ll use the simplified refresh base from the refresh base menu. it’d permit you to update the repository of DAC from associate degree earlier unharness of Oracle metal.

12.Define the execution associated with small ETL Plans and additionally illustrate the method of building and running them?

Ans:

The performance compared to small ETL plans are referred to as ETL techniques which will be scheduled at a hard and fast set of intervals. It’s vital to notice that these small plans associated with accomplishments are classified into units of time and hourly basis. they’re related to the management of subsets or tiny places of a theme. The DAC server may also track the time of refresh for numerous sorts of tables within the plans associated with the accomplishments of the small ETL.

13.Explain the areas where execution bearing on small ETL plans has made inaccurate results on numerous varieties of reports?

Ans:

It is vital to notice that the procedures associated with small ETL will cause issues associated with knowledge inconsistencies. It may also produce an enormous issue of the provision of information and cargo on the information bearing on transactional. One ought to remember the factors which will as a result of the small ETL plans to provide wrong info.

14.Over All design Of Dac?

Ans:

DAC server and DAC shopper. They have to co-locate with Informatica Integration service, repository service and Informatica repository.

15.Why should we Always Use Dac And Not Manage All Execution Through Informatica?

Ans:

For higher performance management, like making index, dropping index, truncating before load. while not DAC a custom ETL method is required, that should survive the upgrade.

16.Can we tend to Run Multiple Executions set up At an equivalent Time In Dac?

Ans:

Yes. given that the execution set up doesn’t seem to be loading into an equivalent table or exploiting an equivalent physical table supply.

17.Explain Dac Export/import?

Ans:

A way to import or export DAC repository data for upgrade or backup. Logical, System, runtime objects are import/export.

18.Have You Modified Any Of The Dac Parameters ? If therefore that One And Why ?

Ans:

You have to know what area unit the DAC parameters and therefore the purpose of every. For instance, Initial_extract_date is changed once assembled for initial full load, that the worth for initial extract date is wont to strain records from the supply that area unit older than this date.

19.How does one verify The Informatica Server most Sessions Parameter Setting In Dac?

Ans:

One you register an informatic server in Dac shopper.

20.Can Dac Send associate degree Email simply just in case Of Any Failures ?

Ans:

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

21.Can You Execute The Sql Security Through Dac ? If affirmative however ?

Ans:

Yes, at task level, in execution kind, choose SQL file. As a bonus to the present answer, this text explains the way to run stored procedures in DAC.

22.In Dac however you’ll Disable Table Indexes Before Loading And alter The Index Once Load Is Complete ?

Ans:

Just drop and recreate the index.

23.Let Say you’re Running the traditional progressive Load. however only for nowadays you would like To Extract knowledge From Ap_invocies_all From 12/12/2011? however you’ll attain This ?

Ans:

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

24.How Dac Determines The Order Of Task Execution among associate degree Execution set up ?

Ans:

Based on tasks source/target table, Task section (extract dim, load reality etc) and ‘truncate always’ properties, to run them above all order, produce task clusters.

25.What area unit small Etl Execution Plans ? However, are you able to Build And Run Them ?

Ans:

According to Oracle document: Micro ETL execution plans are unit ETL processes that you just schedule at terribly frequent intervals, like hourly or unit of time. they sometimes handle tiny subject areas or subsets of larger subject areas. The DAC tracks refresh dates for tables in small ETL execution plans singly from different execution plans and uses these refresh dates within the amendment capture method.

26.From You Past expertise – make a case for state of affairs wherever small Etl Execution Plans made Wrong Results On Reports?

Ans:

According to Oracle Document:

CAUTION: small ETL processes will cause problems with knowledge inconsistencies, knowledge convenience, and extra load on the transactional information. Therefore, you should.

consider the subsequent factors before implementing a small ETL process: For connected star schemas, if one schema is omitted from a small ETL execution set up, the cross-star report is also inaccurate. For instance, if the Person reality table is rested a lot more often than the Revenue reality table, a report that spans the Person and Revenue dimensional schemas could manufacture inconsistent results.

27.Let Say you’ll Not Use Dac hardware To Schedule your Execution set up. What different choices does one Have ? however you’ll attain This ?

Ans:

Use Informatica hardware.

28How Dac Keeps Track Of Refresh Dates For All The Source/Target Tables ?

Ans:

Refresh dates area unit tracked just for tables that area unit either a primary supply or a primary target on tasks {in a|during a|in associate degree exceedingly|in a very} completed run of an execution set up. The DAC runs the total load command for tasks on which a table could be a primary supply or target if the refresh date against the table is null. Once there are multiple primary sources, the earliest of the refresh dates can trigger a full load or associate degree progressive load.

29.Can we have 2 Dac Server On a similar Machine ?

Ans:

You can run 2DAC servers on a similar machine aslongas they’re listening on completely different| completely different} ports and inform 2 different repositories.

30.Explain concisely What reasonably Dac Repository Objects command In supply System Containers ?

Ans:

Subject Areas — A logical grouping of tables associated with a selected subject or application context. It conjointly includes the tasks that are related to the tables, additionally because the tasks needed to load the tables. Subject areas are appointed to execution plans, which might be regular for full or progressive masses.

31.What Is an Authentication File ? If you’ve got Dac consumer put in are you able to Access Dac Repository while not Authentication File?

Ans:

When you tack together an association to the DAC Repository, the configuration method includes making a replacement authentication file or choosing an associate degree existing authentication file. The authentication file authenticates the info within which the repository resides.

32. Describe how Index, Table And Task Actions In Dac ?

Ans:

Index action: Override the default behavior for dropping and making indexes.

Task action: will add new practicality of task behavior, like preceding action, success action, failure action, upon failure restart.

33.How Dac Handles Parameters At Runtime ?

Ans:

During associate degree ETL execution, DAC reads and evaluates all parameters related to that ETL run, as well as static and runtime parameters outlined in DAC, parameters command in flat files, and parameters outlined outwardly to DAC. DAC consolidates all the parameters for the ETL run, deduplicates any redundant parameters, then creates a private parameter file for every Informatica session.

34.How Dac Determines Tasks Needed For Any Given Knowledge base ?

Ans:

You outline a topic space by specifying a reality table or set of reality tables to be the central table or tables within the knowledge base. once a topic space is outlined,DAC performs the subsequent method to work out the relevant tasks:

• DAC identifies the dimension tables related to the facts and adds these tables to the topic space.
• DAC identifies the connected tables, like aggregates, related to the very fact or dimension tables and adds them to the topic space definition.

35.Difference Between Homogeneous And Heterogeneous Execution Plans?

Ans:

Homogeneous: this sort of execution set up extracts information from multiple instances of a similar supply system. As an example, a business might need an associate degree instance of Oracle point 11i in one location and geographical zone and another instance of Oracle point 11i in another location and geographical zone. In such cases, the temporal arrangement of knowledge extraction from the various instances will be staggered to satisfy your business needs.

Heterogeneous: this sort of execution set up extracts information from one or a lot of instances of dissimilar supply systems. As an example, a business might need an associate degree instance of Siebel seven.8 in one location, an associate degree instance of Oracle point 11i in another location, and a second instance of Oracle point 11i in a 3rd location. you’ll be able to conjointly stagger the temporal arrangement of knowledge extraction after you use this sort of execution set up.

36.What Is Oracle?

Ans:

Oracle may be a company. Oracle is additionally an info server, that manages information in an exceedingly} very structured method. It permits users to store and retrieve connected information during a multiuser environment so several users will at the same time access similar information.

37.What is an associate degree Oracle Database?

Ans:

An Oracle info may be an assortment of knowledge treated as a giant unit within the info server.

38.What is an associate degree Oracle Instance?

Ans:

Every running Oracle info is related to associate degree Oracle instances. Once an info is started on an info server (regardless of the sort of computer), Oracle allocates a memory space known as the System world space (SGA) and starts one or a lot of Oracle processes. This mixture of the SGA and therefore the Oracle processes is termed associate degree Oracle instance.

39.What is an associate degree Oracle View?

Ans:

A read may be a virtual table. Each read incorporates a question connected thereto. (The question may be a choose statement that identifies the columns and rows of the table(s) the reader uses.)

40.What is an associate degree Oracle Sequence?

Ans:

To build a sequence, use the CREATE SEQUENCE statemeqnt. A sequence is a database object from which numerous users can generate distinct integers. Sequences can be used to generate primary key values automatically.

41.What is the associate degree Oracle Index?

Ans:

An index is an associate degree elective structure related to a table to own direct access to rows, which might be created to extend the performance of knowledge retrieval. Index will be created on one or a lot of columns of a table.

42.What is the associate degree Oracle information File?

Ans:

An Oracle record may be a huge unit of physical storage within the OS filing system. One or several Oracle information files are organized along to produce physical storage to one Oracle tablespace.

43.What are The Elements Of Physical infraStructure Of Oracle Database?

Ans:

Oracle info consists of 3 sorts of files. One or a lot of datafiles, 2 are a lot of redo log files, and one or a lot of management files.

44.What are The elements Of Logical info Structure Of Oracle Database?

Ans:

There are tablespaces and database’s schema objects.

45.What Is A Tablespace?

Ans:

The info is split into a Logical Storage Unit known as tablespaces. A tablespace is employed to sort connected logical structures along.

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

Ans:

Every Oracle information contains a tablespace named SYSTEM, that is mechanically created once the information is made. The SYSTEM tablespace continuously contains the information wordbook tables for the whole information.

47.Explain the connection Among information, Tablespace And record ?

Ans:

Each information base is logically divided into one or a lot of tablespaces, one or a lot of data files square measure expressly created for every tablespace.

48.What Is Schema?

Ans:

A schema is an assortment of information objects of a user.

49.What square measures Schema Objects?

Ans:

Schema objects square measure the logical structures that directly discuss with the informationbase’s data. Schema objects embody tables, views, sequences, synonyms, indexes, clusters, information triggers, procedures, functions packages and information links.

50.Can Objects Of an Equivalent Schema Reside in Several Tablespaces?

Ans:

Yes.

51.Can A Tablespace Hold Objects From Totally Different Schemes?

Ans:

Yes.

52.What Is Partial Backup ?

Ans:

A Partial Backup is any OS backup in need of a full backup, taken whereas the information is open or stopped working.

53.What Is reflected On-line Redo Log ?

Ans:

A reflected on-line redo log consists of copies of on-line redo log files physically situated on separate disks, changes created to at least one member of the cluster square measure created to any or all members.

54.What Is Full Backup ?

Ans:

A full backup is associate OS backup of all information files, on-line redo log files and management files that represent ORACLE information and therefore the parameter.

55.Can A read support Another read ?

Ans:

Yes.

56.Can A Tablespace Hold Objects From Totally Different Schemes ?

Ans:

Yes.

57.Can Objects Of an Equivalent Schema Reside in Several Tablespace ?

Ans:

Yes.

58.What Is the Utilization Of Management File ?

Ans:

When an associate instance of associate ORACLE information is started, its management file is employed to spot the information and redo log files that have to be opened for information operation to proceed. it’s conjointly employed in information recovery.

59.Do read Contain information ?

Ans:

Views don’t contain or store information.

60.What square measure The denotative Actions Supported By Foreign Key Integrity Constraint ?

Ans:

UPDATE and DELETE prohibit – A denotative integrity rule that disallows the update or deletion of documented information. DELETE Cascade – once a documented row is deleted all associated dependent rows square measure deleted.

61.What square measures the kind Of Synonyms?

Ans:

There square measure 2 kinds of Synonyms:

• Private.
• Public.

62.What Is A Redo Log ?

Ans:

The redo log, which consists of two or more preallocated files and stores all database changes as they happen, is the most important structure for recovery operations. Redo logs are linked to each instance of an Oracle Database to safeguard the database in the event of instance failure.

63.What is the associate Index phase ?

Ans:

Each Index has an associated Index phase that stores all of its information.

64.Explain the connection Among information, Tablespace And information File?

Ans:

Each information base is logically divided into one or a lot of tablespaces, one or a lot of data files square measure expressly created for every tablespace.

65.What square measures the various styles of Segments ?

Ans:

The different style of Segments are:

• Data phase.
• Index phase.
• Rollback phase and.
• Temporary phase.

66.What square measure Clusters ?

Ans:

Clusters square measure teams of 1 or a lot of tables physically stored along to share common columns and square measure usually used along.

67.What Are Associate Integrity Constraints ?

Ans:

An integrity constraint could be a declarative thanks to outline a business rule for a column of a table.

68.What Is the Associate Index ?

Ans:

An Index is an associated facultative structure related to a table to possess direct access to rows, which may be created to extend the performance of information retrieval. Index is created on one or a lot of columns of a table.

69.What Is Associate Extent ?

Ans:

An Extent could be a specific range of contiguous information blocks, obtained in an exceedingly single allocation, and accustomed to store a selected style of info.

70.What Is A Read ?

Ans:

A read could be a virtual table. Each read includes a question hooked up to that.

71.What Is Table ?

Ans:

A table is the basic unit of information storage in associate ORACLE information. The tables of information hold all of the user accessible information. Table information is held in rows and columns.

72.Can A read support Another View?

Ans:

Yes.

73.What square measures the benefits Of Views?

Ans:

• Provide a further level of table security, by proscribing access to a planned set of rows and columns of a table.
• Hide information complexness.
• Simplify commands for the user.
• Present the information in an exceedingly totally different perspective from that of the bottom table.
• Store advanced queries.

74.What Is A Synonym?

Ans:

An equivalent word is associate alias for a table, view, sequence or program unit.

75.What Is a Personal Synonym?

Ans:

Only its owner will access a personal equivalent word.

76.What Is A Public Synonym?

Ans:

Any information user will access a public equivalent word.

77.What square measure Synonyms Used For?

Ans:

• Mask the $64000 name associated with the owner of an object.
• Provide public access to associate objects.
• Provide location transparency for tables, views or program units of far off information.
• Simplify the SQL statements for information users.

78.How square measure The Index Updates?

Ans:

Indexes square measure mechanically maintained and employed by Oracle. Changes to table information square measure mechanically incorporated into all relevant indexes.

79.What Is the Rollback Phase ?

Ans:

The information contains one or a lot of Rollback Segments to briefly store “undo” info.

80.What square measure The Characteristics of information Files ?

Ans:

A data file is related to just one piece of information. Once created an information file can’t modify size. One or a lot of information files is a logical unit of information storage known as a tablespace.

81.How To outline information Block Size ?

Ans:

A data block size is such that for every ORACLE info once the info is formed. An info user and allotted free info area in ORACLE information blocks. Block size is per INIT.ORA file and can’t be modified later.

82.What will an effect File Contain ?

Ans:

A Control file records the natural object of the info. It contains the subsequent information:

• Database Name.
• Time stamp of info creation.

83.What Is the distinction Between distinctive Constraint and first Key Constraint ?

Ans:

A column outlined as distinctive will contain Nulls whereas a column outlined as PRIMARY KEY can’t contain Nulls.

84.What Is an Index Cluster ?

Ans:

A Cluster with Associate in Nursing index on the Cluster Key.

85.When will the deal finish ?

Ans:

When it’s committed or Rollback.

86.What Is The Impact Of Setting The Worth “all_rows” For Optimizer_goal Parameter Of The Alter Session Command ? What square measure The Factors That have an effect on Optimizer In selecting Associate in Nursing ovement Approach ?

Ans:

The OPTIMIZER_MODE data formatting parameter Statistics within the information wordbook the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints within the statement.

87.What Is The Impact Of Setting the Worth “choose” For Optimizer_goal, Parameter Of The Alter Session Command ?

Ans:

The Optimizer chooses a Cost_based approach and optimizes with the goal of best output if statistics for at least one among the tables accessed by the SQL statement exist within the information wordbook. Otherwise the OPTIMIZER chooses a RULE_based approach.

88.How do you produce a brand new Database?

Ans:

One will produce and modify Oracle databases victimizing the Oracle “dbca” (Database Configuration Assistant) utility. The utility is found within the $ORACLE_HOME/bin directory. The Oracle Universal Installer (oui) usually starts it once putting in the info server computer code.

89.What info Block Size ought to I Use?

Ans:

Oracle recommends that your info block size match, or be multiples of your software block size. One will use smaller block sizes, however the performance value is critical. Your selection ought to depend upon the kind of application you’re running. If you have got several little transactions like OLTP, use a smaller block size. With fewer however larger transactions, like a DSS application, use a bigger block size. If you’re employing a volume manager, think about your “operating system block size” to be 8K. This is often as a result of volume manager product use of 8K blocks (and this is often not configurable).

90.What square measures the various Approaches employed by Optimizer In selecting Associate in Nursing Execution set up ?

Ans:

The different approaches employed by Optimizer in selecting Associate in Nursing execution set up are:

• Rule-based.
• Cost-based.

91.What will Rollback Do ?

Ans:

LLBACK retracts any of the changes ensuing from the SQL statements within the dealings.

92.What Is a Cost-Based Approach To Improvement ?

Ans:

Considering out there access methods and deciding the foremost economical execution set up supported statistics within the information wordbook for the tables accessed by the statement and their associated clusters and indexes.

93.What will Commit Do ?

Ans:

COMMIT makes permanent the changes ensuing from all SQL statements within the dealings. The changes created by the SQL statements of a dealings come into view to alternative user sessions dealings that begin solely once transaction is committed.

94.How square measure Extents allotted To A Segment? (for Dba)?

Ans:

Oracle8 and higher rounds off extents to a multiple of five blocks once quite five blocks square measure requested. If one requests 16K or a pair of blocks (assuming a 8K block size), Oracle doesn’t spherical it up to five blocks, however it allocates a pair of blocks or 16K as requested. If one asks for eight blocks, Oracle can spherical it up to ten blocks.

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

Ans:

No, this is often listed as improvement 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 dealings ?

Ans:

A deal could be a logical unit of labor that includes one or additional SQL statements dead by one user.

97.What Are Read-only dealings ?

Ans:

A Read-Only dealings ensures that the results of every question dead within the dealings square measure consistent with relation to identical purpose in time.

98.What Is An Impasse ?

Ans:

Two methods waiting to update the rows of a table that square measure latched by the opposite process then impasse arises. In very info surroundings this may typically happen thanks to not supplying correct row lock commands. Poor style of front-end application could cause this example and also the performance of the server can scale back drastically.

99.What Is A Schema ?

Ans:

The set of objects closely-held by the user account is named the schema.

100.What Is Associate in Nursing Index ? however it’s enforced In Oracle info ?

Ans:

An index could be an info structure employed by the server to possess direct access to a row in a table. Associate in Nursing index is mechanically produced once a novel of primary key constraint clause is per create table command (Ver seven.0).