Top 20 Oracle Cloud Interview Questions & Answers

Ques: 1. What are the database related public cloud offerings in oracle cloud?

Answer:

Oracle public cloud offering comes with three database related services in the public cloud. They are as follows:

a) Schema as a Service (SAAS).

b) Database as a service (DAAS)– Also known as DBaaS, this public cloud is offered as both Infrastructure as a service IAAS and Platform as a Service PAAS.

c) Oracle database cloud Ex-Data service.

 

Oracle Fusion Applications interview Questions and Answers

 

Ques: 2. How will you provision an oracle database?

Answer:

As an oracle cloud dba, we collect requirements from customers and create it as per given requirements. In case your project needs a clone of production database immediately for testing and UAT purpose. for example, it can be done as per standard specification requirements often based on a template.

 

Oracle Accounts Payables Interview Questions and Answers

 

Ques: 3. Why should I choose oracle cloud over others, as there are many different cloud services available in market?
 

Answer:

Oracle cloud offers database as a service. Oracle database is the popular relational database company that has been in existence so long. With oracle databases supporting enterprise databases, this is a cloud service that can support databases that are used for testing, development, all the way up to live production databases. Oracle cloud offers high availability, scalability options offering business continuity, time save and lot more advantages. A cloud service from a database company to keep it simple.

 

Oracle ADF Interview Questions and Answers                                 

 

Ques: 4. what database management services are included in oracle cloud platform as service offering?

Answer:

Following database management services are included as part of Oracle cloud platform as a service PAAS: 

• Database
• Database backup 
• Big Data
• Big Data Cloud 
• Event Hub 
• MySQL 
• Autonomous NoSQL Database Cloud 
• Data hub
• Autonomous Data Warehouse Cloud.

 

Oracle Access Manager Interview Questions and Answers

 

Ques: 5. Where can you store your oracle database backup in oracle cloud?
 

Answer:

Oracle cloud objects storage solution that is reliable and scalable. As of information from oracle website this is a 8000TB storage that is used for storing and accessing the data in databases that are ever growing. This is storage used for storing oracle database backup data.
 

Oracle Fusion HCM Interview Questions and Answers

 

Ques: 6. What are the unique features of oracle cloud database backup management offering?
 

Answer:

This is a reliable and scalable object data storage from oracle cloud. Some unique features includes:

• Security – This solution comes with enterprise-grade data protection policies. The privacy policies are in enterprise grade as well. Oracle being the most popular enterprise database solution this cloud offerings from oracle are designed. 
• Reliability – Redundancy policies ensure high availability of data. 
• Scalability – Oracle cloud is a pay as you go model solution wherein you can choose to purchase universal credits, BYOL etc. Based on capacity and growing demands storage hardware is allocated making this a scalable solution. 
• Simplification by using existing RMAN for backups – Even with cloud oracle is still making use of RMAN backup for performing the database backups. This makes backup, restore and recovery operations transparent using RMAN.

 

Oracle SCM Interview Questions and Answers

 

Ques: 7. What is the difference between normal database and Oracle database cloud service?
 

Answer:

Oracle database cloud service is same as single-instance oracle database except for that database is deployed in cloud and computing resources including storage, power etc is provided by Oracle.

 

Oracle Financials Interview questions and Answers

 

Ques: 8. What are the two service levels available with oracle database cloud service?
 

Answer:

Oracle Database Cloud Service Virtual image wherein customer is responsible for installing software, maintenance of software. Customer has root privilege and full database administrative privilege.
 

Oracle Database Cloud Service wherein database deployment is easy using custom options provided online. Oracle database cloud service can perform automated backups. Customer is responsible for setting up maintenance operations, recovery operation setup in the event of failure.

Oracle PL/SQL Interview Questions and Answers

 

Ques: 9. Which component of Oracle cloud provides service console and REST API?
 

Answer:

Platform Service Manager the PSM component of Oracle cloud is responsible for this. This component is the one used in Oracle golden gate and Oracle Java cloud service as well.

 

Oracle SQL Interview Questions and Answers

 

Ques: 10. How does PSM interact with compute nodes to perform predefined cloud service actions like backup, patching?
 

Answer:

PSM uses secure shell the SSH in port 22 of compute nodes. Compute nodes hosts databases which as database deployments in oracle cloud terms. These cations can be initiated over web service console that uses PSM or REST API.

 

Oracle RDMS Interview Questions and Answers

 

Ques: 11. How will you achieve high availability in Oracle Cloud Infrastructure?
 

Answer:

Attach block volume from availability domain 1 to a compute instance in availability domain 2 or vice versa. Distribute application servers across all availability domains within a region.

BI Publisher Interview Questions and Answers

 

Ques: 12. What are the components of backend set of a load balancer?

 

Answer:

a) Load balancing policy
b) list of backend servers
c) health check policy
d) SSL handling
e) session persistence configuration

Oracle 10g Interview Questions and Answers

 

Ques: 13. What are the many different database deployment model available with oracle cloud?
 

Answer:

Following are the many different types of database deployment models that come as part of oracle cloud:

• Oracle database cloud service. 
• Oracle database cloud service bare metal. 
• Oracle Database Exadata Cloud Service. 
• Oracle Database Exadata Cloud at Customer. 
• Oracle Database Exadata Express Cloud Service – Managed. 
• Oracle Database Schema Cloud Service – Managed.

 

Ques: 14. How can I try oracle cloud for free?
 

Answer:

Oracle cloud offers $300 in free credits. You can make use of this credit for building your test, development, production databases, compute options, containers, IoT, bigdata, API, chatbots, integrations and lots more that are valid for 30 days. Create a free account and try these features for free.

 

Ques: 15. How is internal communication between PSM and Oracle cloud compute nodes established?
 

Answer:

SSH key-value pairs are used for this communication in port 22. This key value pair is specific to each database deployment and used for internal communication purposes. This SSH is internal to Oracle and not accessible. If there is an issue here PSM communication with compute node fails.

  

Ques: 16. Who has access to the SSH keys used for PSM communication with compute nodes?
 

Answer:

Only owner of that project has this access. For security purposes even oracle support and operations are not granted this access unless explicitly shared by customers for troubleshooting purposes.

 

Ques: 17. Give some invalid REST API operations for DNS Zone in OCI:
 

Answer:

The below REST API operations are invalid in OCI:

a) ListZones.
b) GetZone.
c) CreateZone.
d) UpdateZone.
e) DeleteZone.

 

Ques: 18. In TCP level health check you send requests to the backend servers at a specific URL. How will you validate the response?

Answer:

a). based on status code.
b). based on entity data returned.
c). based on entity body returned.

Ques: 19. What load balancer components that are mandatory backend set components?

Answer:

a). Load balancing policy.
b). List of backend servers.
c). Health check policy.
d). TCP handling.
 

Ques: 20. What configurations must be made for the process of designing a load balancer to accept incoming traffic?

Answer:

a). Listener must be configured.
b). A certificate must be available.
c). A security list that is open on a listener port must be available.

Top 20 Oracle PL/SQL Interview Questions and Answers

The following technical interview questions and answers are as per my experiences while working in Oracle PL/SQL projects. These may be useful for the freshers and experienced developers whenever facing PL/SQL interview.

 

Ques: 1. What is a mutating table error? How can you get this error?

Answer:

This error can come with triggers, whenever a trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables, so the database is selecting from one while updating the other. This is all because of the table is in middle of a transaction and referencing the same table again in the middle of the updating action causes the trigger to mutate.

Oracle Fusion Applications interview Questions and Answers

Ques: 2. What is the key difference between SQL and PL/SQL?

Answer:

SQL and PL/SQL are used to access data within Oracle databases. SQL is a limited language that allows you to directly interact with the database. You can write queries, manipulate objects and data of oracle database with SQL language. SQL doesn't include the programming concepts.

By using PLSQL, you can extract and manipulate the objects and data of oracle database. And can do all the things that normal programming languages can have, such as looping and controlled executions.

 

Oracle Accounts Payables Interview Questions and Answers

 

Ques: 3. What is the difference between Form triggers and Database level triggers?

Answer:

Form level triggers are use in forms and fire on any level like item level, row level or on block level on requirement of application. And database triggers are written in database directly and fire on behalf of any transaction like Insert, Update and delete on table automatically.

The key difference in form level triggers and database trigger is that form level trigger fire on user or application requirement and database triggers will fire automatically.

 

Oracle ADF Interview Questions and Answers                                 

 

Ques: 4. What is Pseudo column?

Answer:

Pseudo columns are database columns which are used for different purposes in oracle database.

ROWNUM, ROWID, SYSDATE, UID, USER, ORA_ROWSCN, SYSTIMESTAMP are pseudo columns in oracle database.

 

Oracle Access Manager Interview Questions and Answers

Ques: 5. What is exception handling in oracle? How can they be handled?

Answer:

When an error occurs, exception is raised normally, and execution is stopped. Control transfers to exception handling part. Exception is an error situation which arises during program execution. Exception handlers are routines written to handle the exception. The exceptions can be internally defined User-defined exception. In oracle, exception can be handled by using these exception statements:
EXCEPTION WHEN
DUP_VAL_ON_INDEX
NOT_LOGGED_ON
TOO_MANY_ROWS
VALUE_ERROR
NO_DATA_FOUND

 

Oracle Fusion HCM Interview Questions and Answers

 

Ques: 6. What is the key difference between OPEN-FETCH-CLOSE and FOR LOOP in CURSOR?

Answer:

A FOR LOOP in cursor implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed.

While using the OPEN-FETCH-CLOSE, we have to explicitly open the query and closing the query.

 

Oracle SCM Interview Questions and Answers

 

Ques: 7. What is Dynamic SQL? How can we use it in Oracle?

Answer: 

Dynamic SQL is used by PL/SQL to execute Data Definition Language (DDL) statements, Data Control (DCL) statements, or Transaction Control statements within PL/SQL blocks. These statements can, probably will change from execution to execution means change at runtime. These statements are not stored within the source code but are stored as character variables in the program.

    The SQL statements are created dynamically at runtime by using variables. This is used either using native dynamic SQL or through the DBMS_SQL package. Dynamic SQL supports all SQL data types.

 

Oracle Financials Interview questions and Answers

 

Ques: 8. What is the key difference Between Row Level Trigger and Statement Level Trigger?

Answer:

Row level trigger executes once for each row after or before in the DML event.  It can be defined by using FOR EACH ROW.

Statement Level trigger executes once after or before the DML event, it doesn’t matter many rows are affected by the DML event.  

 

Oracle Cloud Interview Questions and Answers

Ques: 9. What are the various steps included in the compilation process of a PL/SQL block?

Answer:

In the compilation process of a PL/SQL block, the syntax checking, binding, and p-code generation are involved. Syntax checking involves checking PL/SQL code for compilation errors. Syntax errors have been corrected, a storage address is assigned to the variables that are used to hold data for Oracle. This process is called binding. After binding, P-code is generated for the PL/SQL block. P-code is a list of instructions to the PL/SQL engine. For named blocks, p-code is stored in the database, and it is used the next time the program is executed.

 

Oracle SQL Interview Questions and Answers

Ques: 10. What packages have Oracle provided to PLSQL developers?

Answer:

Oracle provides the DBMS_ series of packages to PLSQL developers to smooth the programming. The below packages, the developer should be aware of:

DBMS_DDL

UTL_FILE

DBMS_OUTPUT

DBMS_JOB

DBMS_SQL

DBMS_PIPE

DBMS_TRANSACTION

DBMS_LOCK

DBMS_ALERT

DBMS_UTILITY

 

Oracle RDMS Interview Questions and Answers

 

Ques: 11. How can you protect your PL/SQL source code?

Answer:

Oracle provides a binary wrapper utility that can be used to scramble PL/SQL source code. This utility was introduced in Oracle 7.

This utility use human-readable PL/SQL source code as input and writes out portable binary object code. It can larger than the original in size. The binary code can be distributed without fear of exposing your used algorithms and methods. Oracle will still understand and know how to execute the code. Be careful, there is no "decode" command available. So, always keep your source code saved.

 

BI Publisher Interview Questions and Answers

 

Ques: 12. What are SQLCODE and SQLERRM in PLSQL? What is their importance in programming in PLSQL?

Answer:

In PLSQL, SQLCODE returns the value of the error number for the last encountered error. Whereas the SQLERRM returns the actual error message for the last encountered error. They are used in exception handling in PLSQL. These are very useful for the WHEN OTHERS exception.

Oracle 10g Interview Questions and Answers 

Ques: 13. What do you understand by cursors in PLSQL?

Answer:

Cursor is a pointer variable in a memory and use for data manipulation operations in PLSQL. Basically, cursor is a private SQL memory area. It is also used to improve the performance of the PLSQL block.

Two types of cursors are:

a). Implicit cursor: Implicit cursors use oracles to manipulate the data manipulation operations internally and developers have no control on this type of cursor. We use sql%notfound and sql%rowcount cursor attributes in implicit cursor.

b). Explicit cursor: Explicit cursors are created by the developers and can control it by using the Fetch, Open and close keywords.

There are five types of cursors attributes in PLSQL:

1). %isopen: used to verify whether this cursor is open or not.
2). %found: If cursor fetch the data then %found return true.
3). %notfound: If cursor fetches not data then %notfound return true.
4). %rowcount: It return no. of rows that are in cursor.
5). %bulk_rowcount: It is same as %rowcount but it is used in bulk.

 

Ques: 14. What is the Index-By-Tables in PLSQL?

Answer:

Index-By-Tables is also known as Associative Arrays. These are the sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.

Syntax : TYPE tab_type_name IS TABLE OF element type

               INDEX  BY  BINARY_INTEGER;

Ex:  TYPE DeptTabTyp IS TABLE OF Dept%ROWTYPE

         INDEX BY BINARY_INTEGER;

         Dept_tab DeptTabTyp;

 

Ques: 15. What do you understand by bulk binding?

Answer:

The bulk binding technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. A DML operation statement can transfer all the elements of a collection in a single operation.

           For example, If the collection has 100 elements, It lets you to perform the 100 SELECT, INSERT, UPDATE, or DELETE statements using a single operation.              

 

Ques: 16. What do you under by AUTONOMOUS_TRANSACTION?

Answer:

The pragma AUTONOMOUS_TRANSACTION instructs the PL/SQL compiler to mark a PLSQL block as autonomous i.e. independent. Autonomous transactions let the compiler stop the main transaction to do DML operations, commit or roll back those operations, then resume the main transaction.

 

Ques: 17. What are the two components of LOB datatype in PLSQL?

Answer:

The two components of the LOB datatype in PLSQL are:

1). LOB locator: LOB Locator is a locator, which points to the location in the database where the actual value is stored. This value is stored along with the record in the table row and is like a pointer to the actual location of LOB value.

2). LOB value: It is referred to an actual image, file or value of the LOB datatype.

 

Ques: 18. What are cascading of triggers?

Answer:

If we insert data in one table and that table have trigger on it then trigger fire. And in this trigger, there is another table that we are using for inserting the data in it and this table has also trigger on it then this trigger also fire. This is called cascading of triggers.

 

Ques: 19. What do you understand by Table Functions in PLSQL?

Answer:

We can use the table function like the name of the database table. Table functions are functions that produce a collection of rows (either a nested table or a Varray) that can be queried like a physical database table or assigned to PL/SQL collection variable.

 

Ques: 20. What is the use of NOCOPY in PLSQL?

Answer:

In PLSQL programming, the NOCOPY is Compiler Hint. When the Parameters hold large data structures such as collections and records, all this time copying slows down the execution. To prevent this, we can specify NOCPY. This allows the PL/SQL Compiler to pass OUT and INOUT parameters by reference.