1. Mention Oracle DBA Characteristics.

Criteria Oracle DBA Characteristics

Main storage structures Logical and physical

Architecture Grid architecture

ACID Compliance Fully compliant

Data stored as Data blocks, extents, segments, and tablespaces

2. What is Oracle?

Oracle is a company. Oracle is also a database server, which manages data in a very structured way. It allows users to store and retrieve related data in a multi-user environment so that the users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery. A standby database is a database replica created by taking a backup of a primary database.

3. What are the components of physical database structure of Oracle database?

Components of physical database structure are given below.

One or more data files.

Two or more redo log files.

One or more control files.

4. What are the components of logical database structure in Oracle database?

Components of logical database structure.

Tablespaces

Database's schema objects

5. What is a tablespace?

A database contains Logical Storage Unit called tablespaces. A tablespace is a set of related logical structures. Actually a tablespace groups related logical structures together.

6. What is a SYSTEM tablespace and when it is created?

When the database is created in Oracle database system, it automatically generate a SYSTEM named SYSTEM tablespace. The SYSTEM tablespace contains data dictionary tables for the entire database.

7. What is an Oracle table?

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

8. In the Oracle version 9.3.0.5.0, what does each number shows?

Oracle version number refers:

9 - Major database release number

3 - Database maintenance release number

0 - Application server release number

5 - Component Specific release number

0 - Platform Specific release number

9. What is 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 does not copy structure of same data.

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

10. What is the relationship among database, tablespace and data file?

An Oracle database contains one or more logical storage units called tablespaces. These tablespaces collectively store whole data of databases and each tablespace in Oracle database consists of one or more files called datafiles. These datafiles are physical structure that confirm with the operating system in which Oracle is running.

11. What is a snapshot in Oracle database?

A snapshot is a replica of a target master table from a single point-in-time. In simple words you can say, snapshot is a copy of a table on a remote database.

12. What is the difference between hot backup and cold backup in Oracle? Tell about their benefits also.

Hot backup (Online Backup): A hot backup is also known as online backup because it is done while the database is active. Some sites can not shut down their database while making a backup copy, they are used for 24 hour a day, 7 days a week.

Cold backup (Offline Backup): A cold backup is also known as offline backup because it is done while the database has been shutdown using the SHUTDOWN normal command. If the database is suddenly shutdown with a uncertain condition it should be restarted with RESTRICT mode and then shutdown with NORMAL option.

For a complete cold backup the following files must be backed up.

All datafiles, All control files, All online redo log files(optional) and the init.ora file (you can recreate it manually).

13. How many memory layers are in the Oracle shared pool?

Oracle shared pools contains two layers:

library cache

data dictionary cache

14. What is save point in Oracle database?

Save points are used to divide a transaction into smaller parts. It allows rolling back of a transaction. Maximum five save points are allowed. It is used to save our data, whenever you encounter an error you can roll back from the point where you save your SAVEPOINT.

15. What is hash cluster in Oracle?

Hash cluster is a technique to store a data in hash table and improve the performance of data retrieval. Hash function is applied on table row's cluster key value and store in hash cluster.

16. What are the various Oracle database objects?

Tables: This is a set of elements organized in vertical and horizontal fashion.

Tablespaces: This is a logical storage unit in Oracle.

Views: It is virtual table derived from one or more tables.

Indexes: This is a performance tuning method to process the records.

Synonyms: This is a name for tables.

17. What is the difference between pre-select and pre-query?

A pre-query trigger fire before the query executes and fire once while you try to query. With the help of this trigger you can modify the where clause part dynamically.

Pre-select query fires during the execute query and count query processing after Oracle forms construct the select statement to be issued, but before the statement is actually issued.

Pre-query trigger fires before Pre-select trigger.

18. What are the different 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

19. What is the usage of ANALYZE command in Oracle?

ANALYZE command is used to perform various functions on index, table, or cluster. The following list specifies the usage of ANALYZE command in Oracle:

It is used to identify migrated and chained rows of the table or cluster.

It is used to validate the structure of the object.

It helps in collecting the statistics about object used by the optimizer. They are then stored in the data dictionary.

It helps in deleting statistics used by object from the data dictionary.

20. Can you create a synonym without having a table?

Yes. We can create a synonym without having a base table.

21. What types of joins are used in writing SUBQUERIES?

Self join

Outer Join

Equi-join

22. What is the usage of control file in Oracle?

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

23. What is a synonym?

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

24. What are the different types of synonyms?

There are two types of synonyms or alias:

Private: It can only accessed by the owner.

Public: It can be accessed by any database user.

25. What is the usage of synonyms?

Synonym can be used to hide the real name and owner of an object.

It provides public access to an object.

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

It simplifies the SQL statements for database users.

26. How do you store pictures in a database?

Yes, you can store pictures in a database using Long Raw Data type. This data type is used to store binary data for 2 gigabytes of length. However, the table can have only one Long Raw data type.

27. What is BLOB data type in Oracle?

BLOB data type is a data type with varying length binary string. It is used to store two gigabytes memory. For BLOB data type, the length needs to be specified in bytes.

28. What is the difference between TRANSLATE and REPLACE in Oracle?

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

29. What are the different types of database objects?

A list of different types of database objects:

Tables: This is a set of elements organized in vertical and horizontal fashion.

Tablespaces: This is a logical storage unit in Oracle.

Views: It is virtual table derived from one or more tables.

Indexes: This is a performance tuning method to process the records.

Synonyms: This is a name for tables.

30. What is the usage of Save Points in Oracle database?

Save Points are used to divide a transaction into smaller phases. It enables rolling back part of a transaction. There are maximum 5 save points allowed in Oracle Database. Whenever an error is encountered, it is possible to rollback from the point where the SAVEPOINT has been saved.

31. What is the difference between post-database commit and post-form commit?

The post-database commit trigger is executed after Oracle forms issue the commit to finalized transaction while, the post-form commit is fired during the post and commit transactions process, after the database commit occurs.

32. What is Logical backup in Oracle?

Logical backup is used to read a set of database records and writing them into a file. An Export utility is used to take the backup while an Import utility is used to recover from the backup.

33. What do you understand by Redo Log file mirroring?

Mirroring is a process of having a copy of Redo log files. 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. If the group fails, the database automatically switches over to the next group. It diminishes the performance.

34. What is the meaning of recursive hints in Oracle?

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

35. What are the limitations of CHECK constraint?

The main limitation of CHECK constraint is that the condition must be a Boolean expression evaluated using the values in the row being inserted or updated and can't contain sub queries.

36. What is the use of GRANT option in IMP command?

GRANT is used to import object grants.

37. What is the use of ROWS option in IMP command?

The ROWS option indicates whether the table rows should be imported.

38. What is the use of INDEXES option in IMP command?

The INDEXES option is used to determine whether indexes are imported.

39. What is the use of IGNORE option in IMP command?

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

40. What is the use of SHOW option in IMP command?

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

41. What is the use of FILE param in IMP command?

FILE param is used to specify the name of the export file to import. Multiple files can be listed, separated by commas.

42. How to convert a date to char in Oracle? Give one example.

The to_char() function is used to convert date to character. You can also specify the format in which you want output.

SELECT to_char ( to_date ('12-12-2012', 'DD-MM-YYYY') , 'YYYY-MM-DD') FROM dual;

Or,

SELECT to_char ( to_date ('12-12-2012', 'DD-MM-YYYY') , 'DD-MM-YYYY') FROM dual;

43. What are actual and formal parameters?

Actual Parameters: Actual parameters are the variables or expressions referenced in the parameter list of a subprogram.

Let's see a procedure call which lists two actual parameters named empno and amt:

raise_sal(empno, amt);

Formal Parameters: Formal parameters are variables declared in a subprogram specification and referenced in the subprogram body.

Following procedure declares two formal parameters named empid and amt:

PROCEDURE raise_sal(empid INTEGER, amt REAL) IS current_salary REAL;

44. What are the extensions used by Oracle reports?

Oracle reports are use to make business enable with the facility to provide information of all level within or outside in a secure way. Oracle report uses REP files and RDF file extensions.

45. How to convert a string to a date in Oracle database?

Syntax: to_date (string , format)

Let us take an example :

to_date ('2012-12-12', 'YYYY/MM/DD')

It will return December 12, 2012.

46. How do you find current date and time in Oracle?

The SYSDATE() function is used in Oracle to find the current date and time of operating system on which the database is running.

SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Current_Date" FROM DUAL;

47. What will be the syntax to find current date and time in format "YYYY-MM-DD"?

SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "Current_Date" FROM DUAL;

48. What is an Oracle Database?

Oracle provides software to create and manage the Oracle Database. The database consists of physical and logical structures in which system, user, and control information are stored. The software that manages the database is called the Oracle Database server. Collectively, the software that runs Oracle and the physical database is called the Oracle Database system.

A database buffer cache stores the data in memory for quicker access. The redo logs track and store all the changes made to the database. A Data Guard ensures data protection and high availability of data, and a control file records the physical structure of the database.

Learn more about Oracle DBA from this Oracle DBA Tutorial!

49. What is an Oracle index?

An index is an optional structure associated with a table to have direct access to rows that can be created to increase the performance of data retrieval. An index can be created on one or more columns of a table.

Oracle Index

50. Why is the index used?

Indexes are used for the easy access of data. To increase the performance of retrieval data, Indexes are used. It helps to find the required data quickly without actually searching each row.

51. Who is responsible to update the indexes?

Oracle is capable of maintaining and using indexes and Oracle automatically distributes the data into relevant indexes when any change is made in the table data.

52. What are the various databases available in the market?

There are many databases available in the market. The commonly used databases are Oracle,

IBM db2, Microsoft SQL Server, Microsoft Access, MySQL and SQLite, PostgreSQL, MariaDB.

53. What are the physical components of Oracle Database?

Below 5 are the physical components of Oracle Database:

Data files

Control files

Redo log files

Password files

Parameter files

54. What are the roles of DBA?

A DBA has the authority to create new users, remove the existing users, or modify any of the environment variables or privileges assigned to other users.

Manage database storage

Administer users and security

Manage schema objects

Monitor and manage database performance

Perform backup and recovery

Schedule and automate jobs

Want to become a certified Oracle DBA Course? Enroll now!

55. What are the different Oracle Database objects?

Tables

Views

Indexes

Synonyms

Sequences

Tablespaces

56. What is a Synonym in Oracle terminology?

A synonym which is also known as an alias is an identifier that can be used to reference another database object in a SQL statement. A table, view, sequence are the types of database objects that can be created for a synonym.

57. Explain the types of Synonyms?

There are two types of Synonyms which are- Public and Private.

A public synonym isn’t part of any schema. A public synonym is one that can be used by any database user.

A private synonym does belong to a specific schema. In other words, when only the owner can access it, it is called a private synonym.

58. What are the types of backups in Oracle?

The main four types of backups in Oracle are:

(ⅰ) COLD Backup

(ⅱ) HOT Backup

(ⅲ) Import – Export Backup

(ⅳ) RMAN Backup

59. What are Hot Backup and Cold Backup?

Hot backup is also recognized as an Online Backup because here the backup is taken while the database is active and running. And when the backup can only happen while the database is in shut down mode then it is called Cold Backup which can also be identified as Offline Backup.

60. What is the password file and why it is needed?

Database users’ passwords are stored in the database’s data dictionary. When a user attempts to log into the database, the user’s usernames and passwords are compared to the values contained in the database. The user is given database access only if the username and password match. The data dictionary is stored in the database and can be accessed as long as the database is available. The dictionary also contains the passwords for the administrators.

The data dictionary would be unavailable until the database is locked. Since starting up a down database is one of the administrator’s jobs, there needs to be a way for them to log in even if the database is locked. That’s where the password file comes into the picture. A password file is an operating system file that is held on a separate disc from the database. It stores the username and password for users with the SYSDBA or SYSOPER privileges. And when the database is down, administrators with certain privileges are authenticated using the password files.

61. What are datafiles?

All the tables are stored in data files. The data file has all the data stored. The data files hold all the database data. The data of relational database structures, such as tables and indexes, is bodily stored in the data files owed for a database

62. How do you switch from an init.ora file to a spfile?

To switch from an init.ora file to a spfile, we should create spfile from pfile command then shutdown instance and startup once again

63. What are the different types of SQL statements?

The five type of SQL statements are:

Data Definition Language

Data Manipulation Language

Transactional control

Session Control

System Control

64. What is Data Normalization?

The process of arranging data in a database is known as normalization. This involves building tables and defining relationships between them according to rules designed to protect data while also allowing the database to be more flexible by removing redundancy and conflicting dependencies.

65. What is a control file?

A binary file that records the physical structure of the database and is required to start and run the database.

A control file contains information such as

Data file

Database name

Redo file name

Locations of associated data files and redo files.

Timestamp of database creation.

Current log sequence number

Checkpoint information

66. How will you recover a lost control file?

Steps to recover a lost control file are:

Start the database in the NOMOUNT mode

Create the control file from the control file backup with CREATE CONTROLFILE statement, and place it in the correct location.

Mount the database

Recover the database

Open the database

67. What do you mean by Red logo files?

The redo log’s main purpose is to keep track of all data changes. If a failure stops personalised data from being permanently written to data files, corrections may be made from the redo log, ensuring that the work is never wasted.

68. Define Parameter files?

A parameter file is a text file that includes a list of initialization parameters as well as their values. Initialization parameters are defined in a parameter file that is exclusive to your installation.

69. What do you mean by Recovery Catalog?

The recovery catalog is a database scheme that contains the metadata that RMAN manages for data restoration and recovery processes. The metadata used by RMAN for reinstallation and healing process is stored in a recovery catalog. The RMAN metadata is still present in the recovery catalogue even if the target control file and all backups are lost.

70. Define system tablespace and why do we need it?

When the database is created, the system tablespace is formed. Since this tablespace contains all of the data dictionary tables, it is used to create a number of database objects. For the database to run efficiently, the system tablespace must remain online.

71. How to find the database version?

Running a query from the command prompt will reveal to you the Oracle version. The version information is held in the v$version table.

SELECT * FROM v$version;

72. What is the sequence?

For numerical columns in database tables, a sequence produces a serial list of unique numbers. For data where we want to insert data in a systematic way, we may use the sequence on columns.

73. What is the definition of the table in Oracle?

In a database, the table is the first physical object. Oracle divides data into rows and columns using tables in a database. In a database, the table is the first physical object.

74. What do you mean by a view in SQL terminology?

A query is added to any view in order to define specific rows and columns of the table. The view is a type of virtual table. Read-only and read-write views are available types of views.

75. Explain the advantages of using view?

The advantages of using a view in the table are

It is a subset of the data in table

It store complex queries

It can simplify multiple tables into one

It occupies very little space

It presents the data from different perspectives

76. In Oracle terminology, what do you mean by tablespace?

A tablespace is a logical storage unit that groups together similar logical structures. It is the logical structure that will organise all of the database’s objects.

77. How long does it take for the SYSTEM tablespace to be created?

Every database in Oracle has a tablespace called SYSTEM, which is generated automatically when the database is created. It also includes a data dictionary table for the entire set of data.

78. What is the relationship between tablespace and datafiles?

Each database has one or more tablespaces, each of which is divided into one or more data files.

79. How do we use the materialized view?

Materialized views are items that contain condensed sets of data from base tables that have been summarised, clustered, or aggregated. They’re commonly used in data centres and decision-making systems.

80. What is the use of the SELECT statement?

The SELECT statement is used to retrieve a collection of unique values from a database table based on the conditions defined in a SQL query.

81. How can you compare a part of the name rather than the entire name?

To compare different parts, we use the LIKE operator, which functions as a database’s regex engine.

82. What is the keyword to get distinct records from a table?

The user will use SELECT DISTINCT to select distinct values from a database table.

83. In order to get sorted records from a table, what is the keyword?

The keyword ORDER BY is used to sort the data.. It returns the sorted results to your program.

84. In order to get total records from a table, what is the keyword?

The COUNT keyword is used to find the total number of records in a table.

85. What is the definition of GROUP BY?

The keyword GROUP BY is an aggregate function like SUM, MULTIPLE, and so on, and without it, the sum for each individual group value cannot be determined.

86. What are the methods to shutdown an Oracle database?

Oracle has several modes for shutting down the database

Normal Mode:

In normal mode the database is shut down by default. It can be used when no other clause is provided. the database waits for all currently linked users to detach from the database before shutting it down and no new connections are permitted once the statement is released. The command line is

SHUTDOWN NORMAL

Transactional Mode:

Transactional mode helps to shut down the database while allowing to complete the active transactions. In this process, no new connections are allowed and this mode waits for all transactions to finish before shutting down the database. Use this command line to shut down the database

SHUTDOWN TRANSACTIONAL

Immediate Mode:

When you know a power outage is coming up fast, then you can use immediate mode. All sessions will be disconnected, all running transactions will be rolled back, the database does not wait for existing database users to disconnect until proceeding and the database will be shut down. No instance recovery is needed during this next startup.

Issue the command SHUTDOWN with an IMMEDIATE clause to shut down a database immediately.

SHUTDOWN IMMEDIATE

Abort Mode:

When you want to shut down the database immediately within seconds without any active transactions then you can use this abort method. Hereafter the statement has been released, no new connections or transactions are permitted to be initiated. The Oracle database automatically terminates all current client SQL statements and does not wait for existing database users to disconnect. Transactions that have not been committed will not roll back. All linked users are automatically disconnected by the database.

Use the command SHUTDOWN with ABORT clause:

SHUTDOWN ABORT

Intermediate Oracle DBA Interview Questions for Experienced Professionals

87. What are the benefits of ORDBMS?

In ORDBMS, the objects can be stored as they are. The language of the DBMS can be integrated with an object-oriented programming language. The language may even be exactly the same as that used in the application, which does not force the programmer to have two representations of his objects.

88. What are the common Oracle DBA tasks?

As an Oracle DBA, we have to carry out the following tasks:

Installing Oracle software

Creating Oracle databases

Performing upgrades of the database and software to new release levels

Starting up and shutting down the database

Managing the database’s storage structures

Managing users and security

Managing schema objects, such as tables, indexes, and views

Making database backups and performing recovery when necessary

Proactively monitoring the database’s health and taking preventive or corrective actions as required

Monitoring and tuning performance

In a small-to-midsize database environment, a single DBA might be the sole person performing all these tasks. In large enterprise environments, the whole job is often divided among several DBAs titled as Database Security Administrator or Database Tuning Expert, each with his/her own area of specialties.

Get ready for the industry with an Oracle DBA Certification now!

89. List out the tools for administering the database.

Following are some of the products, tools, and utilities we use in achieving our goals as a Database Administrator:

Oracle Universal Installer (OUI): The Oracle Universal Installer installs the Oracle software and options. It can automatically launch the Database Configuration Assistant to install a database.

Database Configuration Assistant (DBCA): The Database Configuration Assistant creates a database from the templates that are supplied by Oracle, or we can create our own templates. In this case, it enables us to copy a preconfigured seed database, thus saving the time and effort of customizing and generating a database from scratch.

Database Upgrade Assistant: This tool guides us through the upgrading of our existing database to a new Oracle release.

Oracle Net Manager: This tool guides us through our Oracle network configuration.

Oracle Enterprise Manager: The primary tool for managing our database is Oracle Enterprise Manager, a web-based interface. Once we install the Oracle software, create or upgrade a database, and configure the network, we can use Oracle Enterprise Manager as the single interface for managing our database. In addition, Oracle Enterprise Manager also provides an interface for performance advisors and an interface for Oracle utilities such as SQL*Loader and Recovery Manager.

Become a Database Architect

90. Differentiate between a cluster and a grid.

Clustering is one technology used to create a grid infrastructure. Simple clusters have static resources for specific applications by specific owners.

Grids, which can consist of multiple clusters, are dynamic resource pools shareable among many different applications and users. A grid does not assume that all servers in it are running the same set of applications. Applications can be scheduled and migrated across servers in the grid. Grids share resources from and among independent system owners.

At the highest level, the idea of grid computing is computing as a utility. In other words, we need not care where our data resides or which computer processes our request. We should be able to request information or computation and have it delivered as per our requirement. This is analogous to the way the electric utilities work; without knowing where the generator is or how the electric grid is wired, we just ask for electricity and we get it. The goal is to make computing a utility, a commodity, and ubiquitous. Hence the name ‘Grid’. This perspective of utility computing is, of course, a ‘client-side’ view.

From the ‘server-side’ (or behind the scenes), the grid is about resource allocation, information sharing, and high availability. Resource allocation ensures that all those, who request resources, are getting what they need and that those resources are not standing idle while requests go unserviced. Information sharing makes sure that the information, the users and applications need, is available as and when it is requested for. High availability features guarantee that all the data and computation are always available as a utility.

91. Explain the architecture of Oracle Grid.

The Oracle Grid architecture pools large numbers of servers, storage, and networks into a flexible, on-demand computing resource for enterprise computing needs. The grid computing infrastructure continually analyzes the demand for resources and adjusts the supply accordingly.

For example, we can run different applications on a grid of several linked database servers. When reports are due at the end of the month, the Database Administrator can automatically provision more servers to that application to handle the increased demand.

Grid computing uses sophisticated workload management that makes it possible for applications to share resources across many servers. Data processing capacity can be added or removed on demand, and resources within a location can be dynamically provisioned. Web services can quickly integrate applications to create new business processes.

92. What are the tools you can use to start up an Oracle database?

You can start up a database using three tools:

SQL *Plus: To startup an Oracle database instance, you can use the SQL *Plus startup command.

Oracle Enterprise Manager: It is a system management tool, you can startup the Oracle database with Oracle enterprise manager also. It provides an integrated solution for managing your heterogeneous environment. Even if the database is stopped you can still log in to OEM. It will present you with the Startup button by detecting the status of the down database.

Recovery Manager: RMAN is also known as the RMAN repository that is connected with the TARGET keyword which is also a database on which RMAN performs backup and recovery operations in the control file of the database.

93. What would you specify in the script while creating a database with SQL script?

An SQL script can also be used to build a database. I will include the following in this script:

The database’s name

The SYS user’s password.

The Device user’s password.

At least three redo log classes are available online. In my view, each redo log group should have at least two members.

The database’s character set and national character set.

SYSTEM and SYSAUX tablespace locations and sizes. These tablespaces will be used to store device information.

As the database’s default tablespace, I will define a normal tablespace.

I’d specify a temporary tablespace to use as the database’s default temporary tablespace.

I’d build an undo tablespace.

94. Can you find out the indexes for a table in Oracle ?

This is the basic syntax that is used to find out the indexes for a table in Oracle.

SELECT owner, index_name,
tablespace_name, status
Set pages
Break on table_name on index_name
column table_name
column index_name
column column_name
select table_name, index_name, column_name
from dba_ind_columns
where table_owner
order by table_name, index_name
column_position

95. What is SQL*plus? And what can be done with it?

SQL* Plus is a component of Oracle Database. It is basically a command-line tool that allows you to submit SQL queries to the server interactively.

We can view the results by Running a SELECT query

With SQL*Plus we can startup and shutdown a Oracle database.

Submit PL/SQL blocks for execution to the Oracle server.

Execution of SQL*Plus script file can be done.

Create, modify, or drop database objects(eg., tables, indexes, and users) using DDL statements, as well as any other SQL statements that Oracle supports.

We can write the output to a file.

Execute stored procedures and functions in a database

96. Can you explain each digit in the Oracle database version?

This first digit indicates the major database version and it describes the nature of release. Oracle releases a new release every four years on average.

For example: Oracle 9i (internet), Oracle 10g (grid), Oracle 11g (grid), Oracle 12c (cloud).

The second digit represents the software’s maintenance release number. Oracle releases the major update as maintenance release 1 and then follows up with a second maintenance release later in the software’s lifecycle. With maintenance releases, new features are introduced to database software.

For example: Oracle 12c release 1; Oracle 12c release 2.

Fusion Middleware Number is the third digit. This will be 0 for database software.

This fourth digit is called Component-Specific Release Number And a component release level is identified by the fourth digit. Depending on component patch sets or temporary releases, different components can have different numbers in this role.

A platform- specific release is identified by the fifth digit. This is usually a patch kit. this digit will be the same across all affected platforms when different platforms need the same patch package.

97. What kind of information can be given while creating a sequence?

Syntax for creating sequence:

CREATE SEQUENCE schema_name. sequence_name
[INCREMENT BY interval]
[START WITH first_number]
[MAXVALUE max_value | NOMAXVALUE]
[MINVALUE min_value | NOMINVALUE]
[CYCLE | NOCYCLE ]
[CACHE cache_size | NOCACHE]
[ORDER |NOORDER];

While creating a sequence, Use the CREATE SEQUENCE command and add a sequence_name and that should be unique. The next increment by is used to show how much the sequence will increment at each move.

Then using START WITH, we start the sequence either in ascending or descending.

And MAXVALUE, NOMAXVALUE are maximum limits. with max value we can provide the maximum sequence value whereas nomax value is fixed, 10^27 for ascending sequence or -1 for descending sequence.

MINVALUE is to specify the minimum value of sequence. NOMINVALUE is fixed. For an ascending sequence it indicates a minimum value of 1 and -10^26 for a descending sequence.

To allow the sequence to generate value after it reaches the limit, then use CYCLE which is the minimum value for a descending sequence and max value for an ascending sequence. NOCYCLE is a default and it will be used when the sequence reaches the limit and you want to stop generating the next value.

And ORDER ensures that oracle generates sequence numbers in the order in which they are requested. if you don’t want Oracle to generate sequence numbers in the order of your requests, use NOORDER. This is a default setting.

98. Explain how the “Database Writer” process works?

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.

99. What are the instance parameters that are used for configuring shared server architecture?

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.

100. How to know when operations happened on a table?

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

101. Tell me a few important views used in Oracle you have learned?

These are the few commands that can be used to view in oracle

SELECT * from V$Parameter

SELECT * from V$Database

SELECT * from V$Instance

SELECT * from V$Datafiles

SELECT * from V$controlfiles

SELECT * from V$logfiles

Last Updated: 2023-09-23