Let’s have a quick discussion about oracle
Oracle Database is the oldest database designed for enterprise grid computing, the most flexible and budget-effective way to manage information and applications. Enterprise grid computing creates large pools of industry-standard, modular storage, and servers.
It is a database management software product. A database contains an organized collection of information. A database management system is not only used for storing the data but to effectively manage it and provides high performance, authorized access, and failure recovery features. It provides a software solution that is easy to use and manage database operations, from Personal to Enterprise level applications.
Now moving to questions,
- what is oracle?
Answer: Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost-effective way to manage information and applications. Enterprise grid computing creates large pools of industry-standard, modular storage, and servers.
2. How does oracle work?
Answer: Oracle database is a collection of physical operating system files or disks (in the simplest terms). Oracle instance is a set of Oracle background processes or threads and a shared memory area, which is a memory that is shared across those threads or processes running on a single computer.
3. what are the features of oracle?
Answer: Oracle performs Database Management, Storage Management, High Availability, and Scalability which helps developers concentrate on delivering high-performance applications.
4. why do we use oracle?
Answer: we do use oracle because of its:
Durability
Isolation
Consistency
Atomicity
5. what are the Benefits of oracle?
Answer: Benefits of Oracle Databases:
* Reduces operations costs. …
* Uses a single database for all data types. …
* Provides a secure hybrid cloud environment. …
* Offers better identity management and user controls. …
* Executes quick backup and recovery. …
* Offers multiple database support. …
* Provides superior scalability. …
* Includes Flashback technology.
6. what is varchar2 in oracle?
Answer: The VARCHAR2 datatype stores variable-length character strings. … For each row, Oracle stores each value in the column as a variable-length field (unless a value exceeds the column’s maximum length and Oracle returns an error). For example, assume you declare a column VARCHAR2 with a maximum size of 50 characters.
7. Describe oracle table
Answer: Tables are the basic unit of data storage in an Oracle Database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns.
8. what is a temporal data type in oracle?
Answer: The date and time data types for representing temporal values are DATE, TIME, DATETIME, TIMESTAMP, and YEAR. Each temporal type has a range of valid values, as well as a “zero” value that may be used when you specify an invalid value that MySQL cannot represent.
9. How to store pictures on to the database?
Answer: It is possible to store pictures in the database by using the Long Raw Data type. This data type is used to store binary data of length 2GB. Although, the table can have only one Long Raw data type.
Step 1: Connect to the database. You can connect to a database using the getConnection() method of the DriverManager class. …
Step 2: Create a Prepared statement. …
Step 3: Set values to the placeholders. …
Step 4: Execute the statement.
10. Where do you use DECODE and CASE Statements?
Answer: CASE is a statement and DECODE is a function We can use the CASE in the where clause and can not use the DECODE in the where clause. DECODE can check equality operators only whereas CASE can support all relational operators DECODE can be used in SQL only whereas CASE can be used in SQL AND PL/SQL CASE is better than DECODE.
11. Describe VArray
Answer: A VARRAY is a type of collection in which each element is referenced by a positive integer called the array index. The maximum cardinality of the VARRAY is specified in the type definition. The TYPE IS VARRAY statement is used to define a VARRAY collection type.
12. What is PL/SQL?
Answer: PL/SQL is a block-structured language that enables developers to combine the power of SQL with procedural statements. All the statements of a block are passed to the oracle engine all at once which increases processing speed and decreases the traffic.
13. What are the uses of a database trigger?
Answer: Triggers in the database have several uses.
Triggers allow you to perform the following tasks:
.Enforce business rules
.Validate input data
.Generate a unique value for a newly inserted row on a different file (surrogate function)
.Write to other files for audit trail purposes
.Query from other files for cross-referencing purposes
.Access system functions (for example, print an exception message when a rule is violated)
.Replicate data to different files to achieve data consistency
14. What are the differences between Primary Key and Unique Key?
Answer: Both keys provide a guaranteed uniqueness for a column or a set of columns in a table or relation. The main difference among them is that the primary key identifies each record in the table, and the unique key prevents duplicate entries in a column except for a NULL value.
15. Name the various constraints used in Oracle
Answer: These are the following constraints used:
–NULL: It is to indicate that a particular column can contain NULL values.
–NOT NULL: It is to indicate that a particular column cannot contain NULL values.
-CHECK: Validate the values in the given column to meet the specific criteria.
–DEFAULT: It is to indicate the value is assigned to a default value.
16. What is the fastest query method to fetch data from the table?
Answer: What is the fastest query method to fetch data from the table? Ans: Row can be fetched from the table by using ROWID. Using ROW ID is the fastest query method to fetch data from the table.
17. What is the difference between Triggers and Constraints?
Answer: Trigger affects only those rows, which are added after it is enabled. Constraints affect all the rows therefore the ones that existed before and the ones that were newly added. A constraint is responsible only for maintaining the integrity of the database.
18. What Difference between Cartesian Join and Cross Join?
Answer: Both the joins give the same result. Cross-join is SQL 99 join and the Cartesian product is Oracle Proprietary join. A cross join that does not have a ‘where’ clause gives the Cartesian product. Cartesian product result-set contains the number of rows in the first table, multiplied by the number of rows in the second table.
19. What are the characteristics of PL/SQL?
Answer: Features of PL/SQL,
-PL/SQL is tightly integrated with SQL.
-It offers extensive error checking.
-It offers numerous data types.
-It offers a variety of programming structures.
-It supports structured programming through functions and procedures.
-It supports object-oriented programming
20. Kinds of Exception handling in PL/SQL
Answer: An exception is an error that disrupts the normal flow of program instructions. PL/SQL provides us the exception block which raises the exception thus helping the programmer to find out the fault and resolve it.
There are two types of exceptions defined in PL/SQL
-User-defined exception.
-System-defined exceptions.
21. Differentiate between the “VERIFY” and “FEEDBACK” command
Answer: The main difference between both of them is
Verify Command: You can use this command to confirm the changes in the SQL statement which can have old and new values that are defined with Set Verify On/OFF.
Feedback Command: It shows the number of records that are returned by a query.
22. What is the difference between SUBSTR and INSTR?
Answer:
23. State the difference between USER TABLES and DATA DICTIONARY?
Answer: USER_TABLES describes the relational tables owned by the current user. Its columns (except for OWNER ) are the same as those in ALL_TABLES. To gather statistics for this view, use the ANALYZE SQL statement.
One of the most important parts of an Oracle database is its data dictionary, which is a read-only set of tables that provides information about the database. A data dictionary contains: … Auditing information, such as who has accessed or updated various schema objects. Other general database information.
24. Main difference between Truncate and Delete?
Answer: The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
25. List out the difference between TRANSLATE and REPLACE?
Answer: REPLACE() replaces one string with another string. Therefore, if a string contains multiple characters, each character must be in the same order. TRANSLATE() on the other hand, replaces each character one by one, regardless of the order of those characters.
26. What do you mean by Redo Log file mirroring?
Answer:
- The process of having a copy of redo log files is called mirroring.
- It is done by creating group of log files together. This ensures that LGWR automatically writes them to all the members of the current on-line redo log group.
- In case a group fails, the database automatically switches over to the next group. It diminishes the performance.
27. Point out the difference between ORACLE_BASE and ORACLE_HOME?
Answer: Both ORACLE_HOME and ORACLE_BASE are the directories that are located in OFA (Oracle Flexible Architecture) from which the ORACLE_BASE is the Home/Root/Top-level directory and the ORACLE_HOME is located beneath the ORACLE_BASE directory where the oracle products reside or we can say that ORACLE_HOME is the subdirectory of ORACLE_BASE.
ORACLE_BASE includes the multiple oracle products and ORACLE_HOME specifies the directory containing the Oracle software for a given release.
The value recommended for an OFA configuration is
software_mount_point/app/oracle.
For example: /u01/app/oracle.
28. What is an Oracle base directory?
Answer: The Oracle base directory is the location where Oracle software and configuration files installed are stored. By default, Oracle Universal Installer (OUI) installs the Oracle Database software binary files by version and Oracle Home Name in a subdirectory of the Oracle base directory.
29. What is an Oracle home Directory?
Answer: An Oracle home is a directory into which all Oracle software is installed and is referenced by an environment variable. The Oracle home consists of the following: Directory location where the products are installed. … Program groups associated with the products installed in the home (where applicable).
30. Different data types available in PL/SQL?
Answer: PL/SQL provides many predefined datatypes. For instance, you can choose from integer, floating-point, character, BOOLEAN, date, collection, reference, and large object (LOB) types.
31. How many memory layers are in the Oracle shared pool?
Answer: Oracle shared pools contains two layers:
1. library cache
2. data dictionary cache
32. What is a hash cluster in Oracle?
Answer: A hash cluster provides an alternative to a non-clustered table with an index or an index cluster. With an indexed table or index cluster, Oracle Database locates the rows in a table using key values that the database stores in a separate index. To use hashing, you create a hash cluster and load tables into it.
33. What are the different types of modules in Oracle forms?
Answer: Form module – a collection of objects and code routines
Menu modules – a collection of menus and menu item commands that together make up an application menu
library module – a collection of a user named procedures, functions, and packages that can be called from other modules in the application
34. Can we create a synonym without having a table?
Answer: Yes we can create a synonym without having a base table.
35. What is the usage of the control file in Oracle?
Answer: Every Oracle database has a control file. A control file is a small binary file that records the physical structure of the database and includes: The database name. Names and locations of associated data files and online redo log files. The timestamp of the database creation.
36. How do you store pictures in a database?
Answer: To insert images into a database, the database must support images. Images are stored in binary in a table cell. The data type for the cell is a binary large object (BLOB), which is a new SQL type in SQL3 for storing binary data.
37. What is the BLOB data type in Oracle?
Answer: A BLOB (binary large object) is a varying-length binary string that can be up to 2,147,483,647 characters long. Like other binary types, BLOB strings are not associated with a code page. In addition, BLOB strings do not hold character data.
38. What is the difference between post-database commit and post-form commit?
Answer: The post-form-commit trigger fires after inserts, updates, and deletes have been posted to the database but before the transactions have been finalized in the issuing the command. The post-database–commit trigger fires after the oracle form issue the commit to finalized transactions.
39. What is Logical backup in Oracle?
Answer: A logical backup copies data, but not physical files, from one location to another. A logical backup is used to move or archive a database, tables, or schemas and to verify database structures. Data repositories such as the Oracle Hyperion Shared Services Registry and Oracle Essbase cubes.
40. What is the use of the GRANT option in the IMP command?
Answer: The WITH GRANT OPTION keywords convey the privilege or role to a user with the right to grant the same privileges or role to other users. You create a chain of privileges that begins with you and extends to the user as well as to whomever user subsequently conveys the right to grant privileges.
41. In which language Oracle was developed?
Answer: Oracle has been developed using C Language.
42. What is a RAW datatype?
Answer: RAW datatype is used to store values in binary data format. The maximum size for a raw in a table is 32767 bytes.
43. What are nested tables in oracle?
Answer: Nested table is a data type in Oracle which is used to support columns containing multi-valued attributes. It also holds an entire sub-table.
44. How do we represent comments data in Oracle?
Answer: There are two ways to represent Comments in Oracle–
- Two dashes(–) before the beginning of the line – Single statemen
- /*—— */ is used to represent it as comments for a block of statement…
45. What do you understand by DML?
Answer: Data Manipulation Language (DML) is used to access and manipulate data in the existing objects. DML statements are inserted, selected, update and delete and it won’t implicitly commit the current transaction.
46. What is the usage of the Merge Statement?
Answer: The MERGE statement in SQL is a very popular clause that can handle inserts, updates, and deletes all in a single transaction without having to write separate logic for each of these. You can specify conditions on which you expect the MERGE statement to insert, update, delete, etc.
47. What is a NULL value in oracle?
Answer: If a column in a row has no value, then the column is said to be null, or to contain null. Oracle Database currently treats a character value with a length of zero as null. … However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.
48. What is WITH CHECK OPTION?
Answer: WITH CHECK OPTION is an optional clause on the CREATE VIEW statement. It specifies the level of checking when data is inserted or updated through a view. If WITH CHECK OPTION is specified, every row that is inserted or updated through the view must conform to the definition of the view.
49. What is a cursor variable?
Answer: A cursor variable is associated with different statements which can hold different values at run time. A cursor variable is a kind of reference type.
50. What are the disadvantages of Oracle databases?
Answer: Disadvantages of Oracle: Expensive, particularly for a big installation. You can easily spend eight figures per year on licenses on a Web-class, petabyte-scale Oracle deployment, which is why they are basically not done.
*It was expensive!
*No new software updates.
*No new security alerts and updates.
*No new critical patch updates.
*No new upgrade tools and scripts.
*No more tax, legal, and regulatory updates.
*No access to Oracle Platinum Services.
*No new certifications for Oracle products/versions.
Hope this will help you with your preparation
ALL THE BEST!
- DevOps Certified Professionals (DCP)
- Site Reliability Engineering Certified Professionals (SRECP)
- Master in DevOps Engineering (MDE)
- DevSecOps Certified Professionals (DSOCP)
URL - https://www.devopsschool.com/certification/
My Linkedin - https://www.linkedin.com/in/rajeshkumarin
- Apache Lucene Query Example - April 8, 2024
- Google Cloud: Step by Step Tutorials for setting up Multi-cluster Ingress (MCI) - April 7, 2024
- What is Multi-cluster Ingress (MCI) - April 7, 2024
