Question:- Explain how the “Database Writer” process works?
Answer:- Multiple database context processes are possible. In the operating system, they are referred to as “DBWn.” This method is in charge of storing “dirty” buffers on disc. When a server process has to update a data block, it first reads it from disc into the buffer cache if it isn’t already there, and then updates the cache copy. So, a “dirty” block refers to a modified database block in the buffer cache.
Question:- What are the instance parameters that are used for configuring shared server architecture?
Answer:- • DISPATCHERS: In the shared server architecture, configures dispatcher processes. • MAX _DISPATCHERS: The maximum number of dispatcher processes that can run at the same time is defined. • SHARED_SERVERS: There will be a minimum of shared server processes on the server. This number of shared servers is determined during the initialization process. • MAX_SHARED_SERVERS: The maximum number of shared server processes that can run at the same time is determined by this parameter. • SHARED_SERVER SESSIONS: This is the only required parameter for using shared servers and it defines the maximum number of concurrent sessions that can be used for shared server connection. • CIRCUITS: The maximum number of virtual circuits that can exist in the system is determined by this parameter.
Question:- How to know when operations happened on a table?
Answer:- In the database, FLASHBACK_TRANSACTION_QUERY shows all information about flashback transaction queries. SELECT * FROM flashback_transaction_query WHERE TABLE_NAME AND table_owner and operation order by start_timestamp desc
Question:- Tell me a few important views used in Oracle you have learned?
Answer:- These are the few commands that can be used to view in oracle • V$Parameter • V$Database • V$Instance • V$Datafiles • V$controlfiles • V$logfiles
Question:- Compare SQL and PL/SQL.
Answer:- SQL • A single query or command execution • A data source for reports, web pages, etc. • Declarative in nature • Manipulating data PL/SQL • A full programming language • An application language to build, format, and display reports, web pages, etc. • Procedural in nature • Creating applications
Question:- What is PL/SQL?
Answer:- Oracle PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration and conditional branching.
Question:- What is the Basic Structure of PL/SQL?
Answer:- PL/SQL uses a block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
Question:- What is a Trigger and what are its uses?
Answer:- A trigger is a database object that automatically executes in response to some events on the tables or views. It is used to apply the integrity constraint to database objects. A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for: • Audit data modifications • Log events transparently • Enforce complex business rules • Maintain replica tables • Derive column values • Implement complex security authorizations Any constant, variable, or parameter has a data type depending on which the storage constraints, format, and range of values and operations are determined.
Question:- What Data Types are Present in PL/SQL?
Answer:- There are various kinds of data types present in PL/SQL. They are: 1. Scalar: The scalar data type is a one-dimensional data type with no internal components. CHAR, DATE, LONG, VARCHAR2, NUMBER, and BOOLEAN are some examples of the scalar data type. 2. Composite: The composite data type is made up of different data types that are easy to update and have internal components that can be utilized and modified together. For instance, RECORD, TABLE, VARRAY, and so on. 3. Reference: The reference data type stores pointers, which are values that relate to other programs or data elements. REF CURSOR is an example of the reference data type. 4. Large Object: The large object data type stores locators, which define the location of large items stored out of line such as video clips, graphic images, and so on. BLOB, BFILE, CLOB, and NCLOB are examples of the large object data type.
Question:- What are the Basic Parts of a Trigger?
Answer:- There are three basic parts of a trigger. They are: • A triggering statement or event • A restriction • An action
Question:- How is the Process of PL/SQL Compiled?
Answer:- Syntax checking, binding, and P-code generation are all part of the compilation process. Syntax checking looks for compilation issues in PL/SQL code. After all mistakes have been fixed, the data holding variables are given a storage address. This process is referred to as binding. The PL/SQL engine’s P-code is a set of instructions. For named blocks, P-code is saved in the database and used the next time it is run.
Question:- What is a Join?
Answer:- A join is a query that combines rows from two or more tables, views, or materialized views. A join is performed by the Oracle Database whenever there are multiple tables in the FROM clause of the query. Most of these queries contain at least one join condition, either in the FROM or WHERE clause.
Question:- What is a View?
Answer:- A view is created by joining one or more tables. It is a virtual table that is based on the result set of an SQL statement; it contains rows and columns, just like a real table. A view can be created with the CREATE VIEW statement.
Question:- What Does a PL/SQL Package Consist Of?
Answer:- A PL/SQL package consists of: • PL/SQL table and record type statements • Procedures and functions • Cursors • Variables, such as tables, scalars, records, etc., and constants • Exception names and pragmas for relating an error number with an exception • Cursors