Oracle interview Q&A

Oracle interview Q&A

Oracle is one of the most important vendors within the enterprise IT market and therefore the shorthand name of its flagship product, a relational database management system (RDBMS) that is formally known as Oracle database. The information code sits at the middle of the many company IT environments, supporting a mixture of dealings process, business intelligence and analytics applications.

What is the usage of control file in Oracle?

In Oracle, management file is used for database recovery. The control file is also wont to identify the database and redo log files that must be opened for Best Oracle Training Institutes in Marathahalli database operation to go ahead, whenever an instance of an ORACLE database begins.

What is a synonym?

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

What are the different kinds of synonyms?

There are 2 kinds of synonyms or alias:

·         Private: It will only accessed by the owner.

·         Public: It will be accessed by any database user.

What is the usage of synonyms?

Synonym will be wont to hide the important name and owner of an object.

·         It provides public access to an object.

·         It additionally provides location transparency for tables, views or program units of a remote database.

·         It simplifies the SQL statements for database users.

How do you store pictures in a database?

Yes, you'll store pictures during a info using Long data type. This data type is used to store binary knowledge for two gigabytes of length. However, the table will have only 1 Long data type.

What is BLOB data type in Oracle?

BLOB data type is a data type with variable length binary string. It’s wont to store 2 gigabytes memory. For BLOB data type, the length must be per bytes.

What the distinction between TRANSLATE and REPLACE in Oracle?

Translate is used to substitute a character by another character while Replace is used to substitute a single character with a word.

What are the different types of database objects?

A list of various types of database objects:

·         Tables: this can be a set of elements organized in vertical and horizontal fashion.

·         Table spaces: in Oracle table space is a logical storage unit.

·         Views: its virtual table derived from one or additional tables.

·         Indexes: this can be a performance standardization methodology to method the records.

·         Synonyms: this can be a reputation for tables.

In Oracle database what is the usage of Save Points?

It is used to divide a transaction into smaller parts. It enables rolling back a part of a transaction. There are most five save points allowed in Oracle info. Whenever an error is encountered, it's possible to rollback from the point where the SAVEPOINT has been saved.

What is the distinction between post-database commit and post-form commit?

The post-form commit is fired during the post and commit transactions method, once the database commit happens. The post-database commit trigger is executed once Oracle Training in Marathahalli  Oracle forms issue the plan to finalized transaction .

What do you meaning by Redo Log file mirroring?

Mirroring may be a method of getting a duplicate of Redo log files. It’s done by making cluster of log files along. This ensures that LGWR automatically writes them to any or all the members of this on-line redo log cluster. If the cluster fails, the database automatically switches over to the next cluster. It diminishes the performance.

What is that means of recursive hints in Oracle?

The number of times a dictionary table is repeatedly known as by various processes is known as recursive hint. Recursive hint is occurred because of the little size of information dictionary cache.

What area unit the constraints of CHECK constraint?

The main limitation of CHECK constraint is that the condition should be a Boolean expression evaluated using the values within the row being inserted or updated and cannot contain sub queries.

What is the utilization of GRANT option in IMP command?

GRANT is used to import object grants.

What is the utilization of ROWS option in IMP command?

The ROWS option indicates whether the table rows should be imported or not in the particular table.

What is the utilization of INDEXES option in IMP command?

The INDEXES option is used to see whether indexes area unit foreign.

What is the utilization of IGNORE option in IMP command?

The IGNORE option indicates to specify how object creation errors should be handled.

What SHOW option indicates in IMP command? 

The SHOW option specifies once the value of show=y, the DDL within the export file is displayed.

What FILE param indicates in IMP command?

FILE param indicates to specify the name of the export file to import. Multiple files are often listed, separated by commas.

How many extensions used by Oracle reports?

Reports are use to make business modify with the ability to produce info of all level among or outside during a secure manner. Reports uses REP files and RDF file extensions.

Author

One of the Best Oracle Training Institutes in Marathahalli is TIB Academy. Real time projects will be provided along with Oracle Training in Marathahalli with Placement. Free demo classes will be available at TIB Academy. To attend demo class, contact 9513332301., visit https://www.trainingmarathahalli.com/oracle-pl-sql-training-in-marathahalli/

Oracle Interview questions

Oracle is a secured database that's wide utilized in international companies. The commonly asked questions from oracle database are given below.

1) What are the elements of physical database structure of Oracle database?

Components of physical database structure are given below.

·                     One or a lot of information files.

·                     Two or a lot of redo log files.

·                     One or a lot of control files.

2) What are the elements of logical database structure in Oracle database?

Components of logical database structure are given below.

·                     Tablespaces

·                     Database's schema objects

3) What's a tablespace?

A database contains Logical Storage Unit known as tablespaces. A tablespace may be a set of Oracle Training in Marathahalli connected logical structures. Truly a tablespace teams connected logical structures along.

4) What's a SYSTEM tablespace and once it's created?

When the database is created in Oracle info system, it automatically generates a SYSTEM named SYSTEM tablespace. The SYSTEM tablespace contains information dictionary tables for the whole database.

5) What's an Oracle table?

A table is basic unit of information storage in Oracle database. A table contains all the accessible information in the form of rows and columns.

6) Within the Oracle version 9.3.0.5.0, what will every number shows?

Oracle version number refers:

·                     9 - Major database release number

·                     3 - database maintenance release number

·                     0 - Application server release number

·                     5 - element Specific release number

·                     0 - Platform Specific release number

7) What's bulk copy or BCP in Oracle?

Bulk copy or BCP in Oracle, is used to import or export data from tables and views but it doesn't copy structure of same data.

The main advantage of BCP is fast mechanism for coping data and you can conjointly take the backup of data easily.

8) What's the relationship among database, tablespace and data file?

An Oracle database contains one or more logical storage units known as tablespaces. These tablespaces jointly store whole data of databases and every tablespace in Oracle database consists of 1 or more files known as data files. These data files are physical structure that makes sure with the OS in which Oracle is running.

9) What's a snapshot in Oracle database?

A snapshot may be a duplicate of a target master table from a single point-in-time. In easy words you can Oracle Training in Marathahalli with Placement say; snapshot may be a copy of a table on a remote database.

10) What’s the difference between hot backup and cold backup in Oracle? Tell about their advantages also.

Hot backup (Online Backup): A hot backup is additionally called on-line backup because it's done whereas the info is active. Some sites cannot shut down their database while creating a backup copy; they're used for 24 hour a day, seven days a week.

Cold backup (Offline Backup): a cold backup is additionally called offline backup because it's done while the database has been shutdown using the shutdown normal command. If the database is suddenly shutdown with a unsure condition it should be restarted with limit mode and so shutdown with normal option.

11) How many memory layers are within the Oracle shared pool?

Oracle shared pools contains 2 layers:

1.                  library cache

2.                  data dictionary cache

12) What's save purpose in Oracle database?

Save points are used to divide a transaction into smaller elements. It permits rolling back of a transaction. Maximum 5 save points are allowed. It’s accustomed save our data, whenever you encounter an error you can roll back from the point where you save your SAVEPOINT.

13) What's hash cluster in Oracle?

Hash cluster may be a technique to store information in hash table and improve the performance of information retrieval.

14) What are the various Oracle database objects?

·                     Tables: this is often a group of components organized in vertical and horizontal fashion.

·                     Tablespaces: storage unit in Oracle.

·                     Views: its virtual table derived from one or a lot of tables.

·                     Indexes: tuning method to process the records.

·                     Synonyms: this is a name for tables.

15) What's the difference between pre-select and pre-query?

·                     A pre-query trigger fire before the query executes and fire once while you are attempting to query. With the assistance of this trigger you can modify the wherever clause part dynamically.

·                     Pre-query trigger fires before Pre-select trigger.

16) What are the various types of modules in Oracle forms?

Following are the different modules in Oracle forms:

·                     Form module

·                     Menu module

·                     Pl/SQL Library module

·                     Object Library module

17) What's the usage of ANALYZE command in Oracle?

ANALYZE command is used to perform varied functions on index, table, or cluster.

ANALYZE command in Oracle:

·         Validate the structure of the object.

·         It helps in collecting the statistics regarding object utilized by the optimizer. They’re then keeping within the data dictionary etc.

18) Can you create a synonym while not having a table?

Yes. We are able to create a synonym without having a base table.

19) What types of joins are used in writing SUBQUERIES?

·         Self join

·         Outer join

·         Equi-join

Author

Learn Oracle Training in Marathahalli with a experienced professional who have expertise in their particular technology. Register for free Demo on weekdays & weekend classes. TIB Academy- Oracle Training in Marathahalli with Placement.

Contact: 9513332301
Visit:   https://www.trainingmarathahalli.com/oracle-pl-sql-training-in-marathahalli/

Advantages & Disadvantages of Oracle SQL

Structured query language helps you manage data in a relational database. Relational databases use a system of tables to store individual items of knowledge. They’re referred to as "relational" as a result of the info is organized supported the relationships between things.

Grouping Transactions

The ability to cluster many transactions into constant batch for process sets Oracle with the exception of its competitors. Microsoft's version of SQL is proscribed to executing every transaction individually in a very serial order. This offers Oracle SQL way larger scalability over serial versions of SQL that are restricted to solely increasing vertically. Vertical growth will be Oracle Training in Marathahalli considerably dearer than horizontal growth. Increasing vertically needs the addition of server memory; disk drives capability and ventilation equipment. Oracle will expand horizontally by cluster transactions for additional economical process.

Improved Performance

In addition to batch processing of transactions, Oracle offers alternative strategies of up your database's performance. You’ll use multiple servers to figure on constant info with the real Application Cluster feature. This could considerably increase your process power for under the value of a further server. Oracle SQL additionally provides you additional choices to fine-tune the operation of your info to suit the capabilities of your server.

Versatility

Oracle SQL provides you the flexibleness of selecting to run your info in any OS. Dedicated languages are solely compatible with in operation systems from constant manufacturer. For example, you'll only run Microsoft SQL Server on a Windows-based machine. As compared, you'll install Oracle SQL on a UNIX server and benefit from the reliability of UNIX while keeping the standardization of SQL. UNIX is a smaller amount liable to several common computer viruses, which keeps your info secure. Oracle SQL is also backward-compatible, therefore you have got the choice of upgrading within the future while not losing any information.

Cost

The cost of in operation Oracle SQL puts it at a drawback as compared to alternative versions of SQL. The value distinction starts with the licensing fees, which can be up to 10 times as high for Oracle. As a result of Oracle SQL is such a fancy and extremely specialized language, it will be troublesome to search out qualified info directors to run your system.

Difficulty

Oracle SQL is additionally tougher to find out and operate than its competitors. A easy version like SQL Server is straightforward to put in and discovered with a minimum of customization. It includes 5 sample databases you'll use as a framework for your own company's info. Microsoft additionally provides Visual Studio-based SQL Server management Best Oracle Training Institutes in Marathahalli tools to assist you modify the database's style, modification the info it contains and execute queries.

The Advantages of Oracle Databases

Oracle has several options that build it popular in the business world. New versions of Oracle databases are discharged with new and improved options geared for businesses and firms. Inside the code market, Oracle has taken a leading role thanks to their persistence to supply the proper databases. Thus, there are various benefits related to exploitation Oracle databases.

Functionality

Oracle databases are used for much all corporation level applications. Thus, Oracle databases dominate the banking system. 10 of the world's high banks use Oracle databases to conduct their business. This is often primarily because of Oracle's practicality. They supply a combination of high-level technology and integrated business solutions. This is often an ideal for much all firms that have large amounts of data to store and access.

Reliability

Another necessary advantage offered by Oracle databases are their reliability. Oracle could be a info that delivers excellent performance once challenged with exacting tasks. The appraisal, that is a crucial tool accustomed makes sure the integrity of data keep, was simply lapsed Oracle databases. This test is very important since reliable data storage is that the main purpose of a info. Thus, Oracle databases are tried to deliver high integrity of data storage.

Flashback Technology

Oracle databases incorporate Flashback technology, that is a important advantage. Within the event of an application outage, because of any variety of reasons, it's necessary to not lose data keep on a info system. Oracle's Flashback technology allows for economical recovery of data incorrectly deleted or lost. Thus, Flashback technology primarily removes human error and increases info recovery time. In the end, this feature allows for a simplified management and body method.

Other benefits

Oracle databases have additionally with success featured the four properties that each one database systems should have. These four properties are atomicity, consistency, isolation and sturdiness. All four of the properties are well maintained by Oracle databases, so providing a reliable and competent info system.

Author

One of the Best Oracle Training Institutes in Marathahalli is TIB Academy. Real time projects will be provided along with Oracle Training in Marathahalli with Placement. Free demo classes will be available at TIB Academy. To attend demo class, contact 9513332301.

Visit: https://www.trainingmarathahalli.com/oracle-pl-sql-training-in-marathahalli/

Oracle vs. Hadoop

Although Hadoop and big data (whatever that is) are the new kids on the block, don’t be too fast to write off relational database technology. In this article, the differences (and benefits) of both solutions.

Hadoop is not a Database!

As much because the marketing plus would have us believe, Hadoop isn't a database, however a collection of open-source software that runs as a distributed storage framework (HDFS) to manage very large information sets. Its primary purpose is that the storage, management, and delivery of data for analytical functions. It’s hard to talk regarding Hadoop without getting into keywords and jargon (for example, Impala, YARN, Parquet, and Spark), so start by explaining the basics.

Hadoop could be a totally different reasonably Animal

It’s impossible to really understand Hadoop without understanding its underlying hardware architecture, which gives it 2 of its biggest strengths, its measurability and large data processing (MPP) capability.

To illustrate the distinction, the diagram below illustrates a typical database design during which a user executes SQL queries against one massive database server. Despite Oracle Training in Marathahalli refined caching techniques, the largest bottleneck for many Business Intelligence applications remains the power to fetch information from disk into memory for processing. This limits each the system process and its ability to scale — to quickly grow to affect increasing information volumes.

As there’s one server, it also needs expensive redundant hardware to ensure availability. This can embody dual redundant power supplies, network connections and disk mirroring that, on very massive platforms will build this an expensive system to create and maintain.

Compare this with the Hadoop Distributed design below. In this resolution, the user executes SQL queries against a cluster of commodity servers, and also the entire method is run in parallel. As effort is distributed across many machines, the disk bottleneck is less of a problem, and as information volumes grow, the answer is extended with further servers to hundreds or even thousands of nodes.

Hadoop has automatic recovery in-built such that if one server becomes unavailable, the work is automatically redistributed among the extant nodes, that avoids the large value overhead of an expensive standby system. This may result in a large advantage in availability, as one machine is taken down for service, maintenance or an operating system upgrade with zero overall system time period.

The 3 Vs and the Cloud

Hadoop has many alternative potential benefits over traditional RDBMS most often explained by the 3 (and increasing) Vs.

•Volume — it’s distributed MPP architecture makes it ideal for dealing with large data volumes.

•Variety — not like an RDBMS wherever you would like to define the structure of your information before loading it, in HDFS, loading information is as easy as repetition a file – which can be in any format. This means Hadoop will just as simply manage, store and integrate data from a database extract, a free text document or maybe JSON or XML documents and digital photos or emails.

•Velocity — once more the MPP architecture and powerful in-memory tools (including Spark, Storm, and Kafka), which kind part of the Hadoop framework, build it a perfect solution to deal with real or near-real-time streaming feeds that arrive at velocity. This suggests you can use it to deliver analytics-based solutions in real time. As an example, using prophetic analytics to advocate choices to a client.

The advent of The Cloud ends up in a fair larger advantage (although not another “V” during this case) — physical property.

That’s the ability to supply on-demand scalability using cloud-based servers to affect unexpected or unpredictable workloads. This means entire networks of machines will spin up as needed to deal with huge data processing challenges whereas hardware prices are restrained by a pay-as-you-go model. Of course, in a extremely regulated business (e.g. monetary Services) with highly sensitive information, the cloud may well be treated with suspicion, in which case you may need to consider an "On-Premises Cloud"-based resolution to secure your data.

Column based Storage

As if the hardware benefits weren't already compelling, Hadoop may also natively column based storage which gives analytic queries a massive performance and compression advantage. This technique has been adopted a number an information Warehouse databases including the incredibly quick Vertical.

Bigger, Faster, Cheaper: What’s the Catch?

To start, you would like to choose the right tool for the job. Throughout this text, I’ve repeatedly talked about Analytics, information warehousing, and Business Intelligence. That’s as a result of Hadoop isn't a standard information, and isn't appropriate for dealings Oracle Training in Marathahalli with Placement process tasks — as a back-end information store for screen-based dealings systems.

This is as a result of Hadoop and HDFS don't seem to be ACID compliant. This means:

•A — Atomic: once change information, all elements of the modification can complete or none at all. as an example, an update touching each client and their SALES history will be committed along. Not thus on HDFS.

•C — Consistent: once dealing completes, all information are going to be left in an exceedingly consistent state.

•I — Isolation: Changes made by other users are proscribed in isolation that in Oracle implies browse consistency — any given user can see an even read of the information at that time in time.

•D — Durable: when a change is applied it'll be durable, and if the system fails mid-way through a change, the partial changes will be rolled-back throughout system recovery.

In fact, Hadoop sacrifices ACID compliance in favor of turnout. It’s additionally designed to trot out massive data volumes, and also the smallest typical unit of work is around 128Mb.

Conclusion

The fact is, Oracle isn't reaching to depart any time before long. It’s been the core enterprise information platform for over thirty years, and that’s not reaching to modification long.

Author

Learn Oracle Training in Marathahalli with a experienced professional who have expertise in their particular technology. Register for free Demo on weekdays & weekend classes. TIB Academy-  Oracle Training in Marathahalli with Placement.

Contact: 9513332301
Visit:   https://www.trainingmarathahalli.com/oracle-pl-sql-training-in-marathahalli/

The Advantages of Oracle Databases

Oracle has numerous highlights that make it mainstream in the business world. New forms of Oracle databases are discharged with as good as ever includes intended for organizations and partnerships. Inside the product showcase, Oracle has played a main job because of their determination to give the ideal databases. In this manner, there are various focal points related with utilizing Oracle databases.

Consumer loyalty

One preferred standpoint of utilizing Oracle databases is because of Oracles faith in consumer loyalty. For instance, all Oracle databases are in reverse Oracle Training in Marathahalli good. This enables organizations to update their frameworks without a total upgrade of their database framework. This gives productive and ease refreshes. Also, new forms of Oracle databases give new highlights while keep the famous highlights from more established variants. This guarantees their item depends on the client's capacity instead of what is financially savvy for Oracle.

Usefulness

Oracle databases are utilized for all intents and purposes all company level applications. In this way, Oracle databases overwhelm the financial business. Ten of the world's top banks use Oracle databases to direct their business. This is basically because of Oracle's usefulness. They give a mix of abnormal state innovation and coordinated business arrangements. This is an ideal for all intents and purposes all organizations that have enormous measures of information to store and access.

Reliability

Another essential favourable position offered by Oracle databases are their dependability. Prophet is a database that conveys magnificent execution when tested with requesting errands. The ACID test, which is a vital apparatus used to guarantee the respectability of information put away, was effectively passed by Oracle databases. This test is vital since dependable information stockpiling is the principle reason for a database. In this manner, Oracle databases have been demonstrated to convey high Oracle Training in Marathahalli with Placement uprightness of information stockpiling.

Flashback Technology

Prophet databases fuse Flashback innovation, which is a huge favorable position. In case of an application blackout, because of any number of reasons, it is vital not to lose information put away on a database framework. Prophet's Flashback innovation takes into account productive recuperation of information mistakenly erased or lost. In this way, Flashback innovation basically expels human mistake and expands database recuperation time. At last, this component takes into account a rearranged administration and regulatory procedure.

Different Advantages

Prophet databases have likewise effectively highlighted the four properties that all database frameworks must have. These four properties are atomicity, consistency, disconnection and strength. Each of the four of the properties are all around kept up by Oracle databases, in this manner giving a dependable and equipped database framework.

End:

There are such a large number of benefits of utilizing prophet database.Larry Erison is the father of Oracle who has begun the organization named Software Development Laboratory in 1977. Now This Company is known as 'Prophet Corporation'.

Author

Learn Oracle Training in Marathahalli with a experienced professional who have expertise in their particular technology. Register for free Demo on weekdays & weekend classes. TIB Academy-  Oracle Training in Marathahalli with Placement.

Contact: 9513332301
Visit:   https://www.trainingmarathahalli.com/oracle-pl-sql-training-in-marathahalli/