Oracle DBA Interview Questions & Answers At Online

1
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018
The Best Oracle DBA Interview Questions
Answers UPDATED 2018 mindmajix.com (https/
mindmajix.com/oracle-dba-interview-questions)
by Oracle DBA Interview questions, Interview
questions for Oracle DBA Oracle DBA Interview
Questions (5.0) 18661 Ratings Email This Post

• If you're looking for Oracle DBA Interview
  Questions for Experienced or Freshers, you are
  at right place. There are lot of opportunities
  from many reputed companies in the world.
  According to research Oracle DBA has a market
  share of about 0.7. So, You still have
  opportunity to move ahead in your career in
  Oracle DBA Development. Mindmajix o?ers Advanced
  Oracle DBA Interview Questions 2018 that helps
  you in cracking your interview acquire dream
  career as Oracle DBA Developer.
• List four possible ways (direct or indirect) to
  execute an SQL query against an Oracle Database?
• Using the SQLPlus command line tool. With this
  tool, you can directly

2
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018

• execute SQL commands.
• Using a GUI (Graphical User Interface) tool like
  SQL Developer. You can directly execute SQL
  commands with such tools.
• Using Oracle Enterprise Manager. This is an
  indirect way of executing an SQL query. When you
  perform certain operations with Oracle Enterprise
  Manager, they are converted to SQL queries
  implicitly and these SQL queries are executed
  against the database.
• Writing your own program. This is not a
  conventional way of executing your queries but
  actually it is widely used. Any web or windows
  program that uses Oracle database at backend,
  executes SQL queries. These programs are written
  using a programming language like .NET or JAVA
  and they use a driver to connect to database.
• Q. What is SQLPlus? How can one acquire it and
  what kind of operations can be performed with
  it?
• SQLPlus is a command line tool developed by
  Oracle Corporation.
• It is freely distributed. It is shipped with
  Oracle client installations or Oracle database
  installations as a default. So, if Oracle client
  or Oracle database software is installed on a
  computer, you can ?nd it under
  ORACLE_HOME/bin/ directory. The name of the
  executable is sqlplus on Linux systems and
  sqlplus.exe on Microsoft Window Systems.
• You can connect to an Oracle database with it.
  Once connected, you can execute Oracle commands
  or SQL queries against the connected database.
  SQLPlus has also its own commands for formatting
  the output so that you can display the results
  in a meat way.
• Q. A user is logged on to a Linux server as root
  where Oracle database is running. The Oracle is
  installed at /uo 1/app/oracle/product/11.2.0.4/d
  bhome and the name of the SID is ORCL. The
  user wants to connect to the database locally
  using operating system authentication with
  SYSDBA privileges. Show the command that the
  user has to execute.

3
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018

• First he needs to switch to oracle user su
  oracle
• Later he needs to set required environment
  variables
• export ORACLE_SIDORCL
• export ORACLE_HOME/uo1/app/oracle/product/11.2
  .0.4/dbhome
• Finally he needs to execute the following command
  to connect to database
• /uol/app/oracle/product/11.2.0.4/dbhome/bin/sqlpl
  us/ as sysdba
• Q. In our organization, were using an Oracle
  database whose version is
• 11.2.0.4. Explain what each digit shows.
• 11 This ?rst digit shows the major database
  version. Oracle usually publishes a major
  release once a 4 year. This digit is usually
  followed by a character describing the nature of
  the release. For example 9i (internet), 10g
  (grid), 11g (grid), 12c (cloud).
• 2 This second digit shows the maintenance
  release number of the software. Oracle publishes
  the major release as maintenance release 1 and
  then usually publishes a second maintenance
  release during the lifetime of the software.
• Newfeatures are added to database Software with
  maintenance releases.
• o This third digit is Fusion Middleware
  Number. This will be o for database software.
• 4 This fourth digit is called
  Component-Speci?c Release Number and it shows
  the path set update that was applied to the
  software. Patch set updates are published 4
  times a yearby Oracle and as you apply them to
  your database software, this fourth digit
  advances.
• Want To Get DBA Training From Experts? Enroll Now
  For Free Demo On Oracle DBA Training (https/
  mindmajix.com/oracle-dba-training).
• Q. Youre at a clients o?ce and you are expected
  to solve a problem in their database. The client
  is not sure about their database version and you
  want to ftnd out the version of their existing
  database. Describe three di?erent methods you
  can use to ftnd the version of database software.

4
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018

• You can ?nd the version by connecting to the
  database with SQLPlus. SQLPlus will print the
  name and the version of the database software
  once youre connected to the database. A sample
  output will look like below Connected to
• Oracle Database 11g Enterprise Edition Release
  11.2.0.4.0 Production With the Partitioning,
  OLAP, Data Mining and Real Application Testing
  options
• You can ?nd the version by querying the
  vsversion view. You can execute the SQL query
  below to ?nd the version of the database
• SQL SELECT FROM vversion
• A sample output would look like below BANNER
• Oracle Database 11g Enterprise Edition Release
  11.2.0.4.0 Production PL/SQL Release
  11.2.0.4.0 Production
• CORE 11.2.o.4.o Production
• TNS for Linux Version 11.2.0.4.0 Production
  NLSRTLVersion 11.2.0.4.0 Production
• You can ?nd the version from Enterprise Manager.
  If you logon to Oracle Enterprise Manager, the
  version of the database software will be listed
  at the home page under General web part.
• Q. Your client said that he forgot the password
  for SYSTEM user of his database and he no
  longer could connect. How would you recover this
  admin password?
• If there are other users who have DBA
  privileges, you can connect with those users and
  change the password for SYSTEM user. The users
  who have DBA privileges have the privileges to
  change any users password. This option is the
  easiest method but this may not be the case in
  all scenarios.
• If there are no other users with DBA privileges
  then the only way to connect to the database
  isto connect using operating system privileges.
  The oracle software runs under a speci?c user at
  operating system. This user is usually named
  oracle. Also, there needs to be a user group
  that oracle user belongs.

5
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018

• This user group is usually named dba. The
  operating system users who belong to dba group
  can connect to database with SYSDBA privileges.
  So, you need to ask the system administrator to
  logon to server as oracle user or any user who
  belongs to this dba group. Once logged on to
  operating system, you can connect to database
  locally using operating system authentication
  with SYSDBA privileges. After connecting to the
  database, you can change the reset the password
  for this system user.
• Q. What is a password ftle and why is it needed?
• Passwords for database users are stored in the
  data dictionary of the database. When a user
  wants to loginto the database, the username and
  password provided by the user is checked against
  the values stored in the database. If the
  username and password match, the user is granted
  access to database. The data dictionary is part
  of the database and it will be accessible as long
  as the database is open. The passwords for
  administrators are stored in the dictionary as
  well.
• When the database is closed, the data dictionary
  will be inaccessible. There needs to be a
  mechanism for administrators to logon to database
  even when it is closed, because it is one of the
  administrators tasks to start up a down
  database. A password ?le is a separate operating
  system ?le that is stored on disk outside of the
  database. The username and password for the users
  who have SYSDBA or SYSOPER privileges are stored
  in it. Administrators who have those privileges
  are authenticated using this password ?le even
  when the database is down.
• You want to ftnd out how many users are deftned
  in the password ftle and what privileges those
  user have. How would you accomplish this? You
  need to query the vpw?le_users view to get
  information about the existing users in the
  password ?le. Execute the SQL query below
• SqlgtSELECT FROM vpw?le_users
• The query above will return four columns for each
  user in the password?le. The column names are
  USERNAME, SYSDBA, SYSOPER and SYSASM.
• The USERNAME column shows the username of the
  user in the password ?le.
• The SYSDBA column shows whether the user has
  SYSDBA privileges or not.

6
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018

• The SYSOPER column shows whether the user has
  SYSOPER privileges or not.
• The SYSASM column shows whether the user has
  SYSASM privileges or not.
• Q. What would be the main responsibilities of an
  Oracle DBA in an organization?
• The main duty of an Oracle DBA isto keep the
  Oracle Databases of the organization up and
  running. This may involve installing and
  con?guring a database from scratch.
• On a running system, the DBA will be the only
  privileged person who can shut down and start up
  the database.
• The DBA will create new users and manage the
  privileges of each user.
• He will take regular backups to ensure that data
  is safe. In case of a disaster, he will be
  responsible of restoring the database from
  backups. He will have to do monitor the space
  usage and do capacity planning for the database.
• He will be responsible for enforcing security
  policies. He will have to monitor database
  activities. He will have to tune the database so
  that it works at an acceptable speed.
• He is expected to follow the latest patches and
  apply them when applicable.
• Q. How does an Oracle DBA role di?er from an
  Oracle Developer role in an organization? Are
  there any similarities between these too?
• An Oracle developer is mainly responsible for
  developing backend applications. They do data
  modelling according to business rules. They
  design tables, create indexes and other type of
  constraints. They are expected to know SQL and
  PL/SQL. The develop procedures using this
  languages. However, the Oracle developers are
  not expected to administer the database software
  itself.
• On the other side, an Oracle DBAs main duty is
  to administer the database which involves tasks
  like doing maintenance to keep the databases up
  and running, taking backups, enforcing security
  policies etc. DBAs are not primarily assigned to
  develop code. DBAs are supposed to have a good
  knowledge of SQL and PL/SQL like a developer as
  these are also required for administering the

7
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018
database. According to the structure of the
organization, DBAs might also be assigned
development tasks or at least assist the
developers where necessary. Q. There are 10
identical servers and you want to install Oracle
Database on each of them. What would you use to
automate the installation process? If you are
going to do batch installations, it is best to do
it with Oracle Universal Installer in silent
mode. For single installations.it is best to
start installer in interactive modeand set
installation options at each window. However in
batch installations, this will take long. You
need to do the installations in silent mode
with a response ?le. In silent installation,
you start the Oracle Universal Installer from a
command prompt and specify the location of the
response ?le. The installation ?les and the
response ?le can be shared among the servers via
NFS so that you wont have to copy the setup ?les
to each server. Checkout Oracle DBA Interview
Questions (https/ mindmajix.com/oracle-dba-interv
iew-questions) Q. You want to create a response
ftle to speed up the installation of databases.
How would you prepare a response ftle? A response
?le is a plain text ?le, where options to create
a database are stored. It is possible to create
it manually from scratch but that would take long
and would be erroneous. Installation media comes
with a template response ?le. It is rather easier
to customize it manually. This ?le also contains
notes about the parameters. However the easiest
and most reliable way to create a response ?le is
using Oracle Universal Installer. If you start
the installer in record mode, every option you
choose at each step is automatically recorded in
a response ?le in correct format. After the
installer completes in record mode, youll have
a complete response ?le with all the options set
in it.
8
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018

• Q. When creating a database with SQL script, what
  would you specify in the script?
• It is also possible to create a database via an
  SQL script. In this script I would specify
• Name of the database
• Password of the SYS user
• Password of the SYSTEM user
• At least three online redolog groups. I would
  also specify at least two members for each
  redolog group.
• Character set and the national character set of
  the database.
• Location and size of the SYSTEM and
  SYSAUXtablespace. These table spaces will be
  used for holding system data.
• I would specify a normal tablespace to use as the
  default tablespace of the database.
• I would specify a temporary tablespace to use as
  the default temporary tablespace of the
  database.
• I would specify an undotablespace.
• Q. What makes up an Oracle Instance?
• An instance is made up of a shared memory region
  on RAM called System Global Area (SGA)
  andbackground processes.
• The system global area is a shared memory, which
  means it can be accessed by multiple processes.
  This are holds data which is required by the
  instance to operate.
• The background processes are operating system
  processes and each process has a speci?c
  responsibility in the instance.
• The System Global Area and background processes
  are created when the instance is started. When
  the instance is shut down, the processes are
  killed and the shared memory region is
  released back to operating system.

9
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018

• Q. What constitutes an Oracle Database?
• An Oracle database is resides on disk and thus is
  permanent. It is composed of ?les that are
  stored on disk. These ?les can be categorizedinto
  three types
• DataFiles These ?les hold user data or
  system data. Any data that belongs to an
  application is an example of user data. The
  data dictionary of the database is an example
  of system data.
• OnlineRedo Log Files These ?les hold the
  change records. Any change, which will be made
  to a data ?le, is ?rst written to online redo log
  ?les.
• ControlFiles These ?les are relatively small but
  they are essentialfor a database.
  Theyholdinformation about the physical structure
  of the database like location of data ?les,
  online redo log ?les etc.
• Q. Which tools can you use to start up an Oracle
  database? You can start up a database with three
  tools.
• SQLPlus This is the most widely used option.
  You ?rst connect to an idle instance with
  SQLPlus and then startup the instance with
  startup command.
• Oracle Enterprise Manager This is another way of
  starting up a database. You can logon to Oracle
  Enterprise Manager even if the database is
  stopped. OEM will detect the status of the down
  database and will present you Startup button.
  You can startup the database by clicking this
  button.
• RMAN This is rather a less used tool for
  starting up a database but it is possible to
  startup a database from Recovery Manager command
  line.
• Q. During startup of a database, at which order
  does Oracle software search a parameter ftle?
• A parameter ?le holds instance parameters which
  govern how an instance operates. In order to
  startup an instance, Oracle needs to locate this
  ?le.
• The search order is as below /dbs/spftle.ora
  This is an server parameter ?le and this is the
  ?rst place that oracle will look for. SID- is the
  service identi?er of the instance.
• ltORACLE_HOME-/dbs/spftle.ora -If Oracle cannot
  ?nd the ?le in the ?rst location, it will search
  this ?le. This is again a server parameter ?le.

10
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018

• /dbs/init.ora This is a parameter ?le and it is
  plain text. If Oracle cannot ?nd the two ?le
  listed above, it will search for this ?le. This
  is the last location to search.
• Q. At what stages does an instance pass while
  starting up? You can startup a database with the
  modes below
• NOMOUNT This is the ?rst stage. At this mode the
  instance is started.
• MOUNT This is the second stage. At this mode,
  the instance is started and the database is
  mounted. However, the database is not open so you
  cannot still access data. However you can
  perform several maintenance tasks at this stage.
• OPEN This is the ?nal stage. The database is
  open and all the data is accessible. The default
  open mode is read/write which means you can
  read data or write to it. However, it is also
  possible to open itin read only mode where you
  can only read data but cannot change it.
• Q. You want to do maintenance on your database
  but during the maintenance period, you dont
  want any user to be able to connect to the
  database. How would you accomplish this?
• When a database is open, any user with CREATE
  SESSION privilege can make a connection.
  However it is possible to open the database in
  restricted mode. When a database is open
  inrestricted mode, only users with RESTRICTED
  SESSION privilege can make a connection to the
  database. By default, only DBAs have RESTRICTED
  SESSION privilege and it should not be granted
  to regular users.
• Opening a database in restricted mode is a good
  way to prevent regular users from accessing the
  database during maintenance.
• Q. Your database is open. You dont want to
  interrupt currently connected users but you want
  to temporarily disable further logons. What
  would you do to achieve this and how would you
  revert the database back to normal state after
  that?
• I would put the database in restricted mode.
  While in restricted mode, only

11
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018

• users with RESTRICTED SESSION privilege can
  make a connection. I would run the below command
  to put database in restricted mode
• Sqlgt alter system enable restricted session
• After executing this command regular users wont
  be able to logon to the database. Once I want to
  revert the database to normal, I execute this
  command Sqlgtalter system disable restricted
  session
• Q. What are the types of shutdown modes of an
  Oracle database?
• Normal In this mode, no new connections are
  allowed and the database is closed after all the
  sessions disconnect themselves.
• Immediate No new connections are allowed and the
  existing active transactions are rolled back.
  Changes made by an active transaction are lost in
  this option.
• Transactional No new connections are allowed and
  Oracle waits until all active transactions are
  completed.
• Abort This happens immediately however the
  database is not shutdown cleanly. Database will
  have to perform instance recovery next time it is
  started. This option should not be used in
  regular activities.
• Q. The data ftles of your database reside on a
  storage system. You want to take a snapshot of
  the storage so that you can use it backup
  purposes. You also want to ensure that no data
  is written to data ftles while the snapshot is
  being taken. Is it possible to accomplish this
  while the database is open?
• Yes, it is possible to stop all I/O activity
  while the database is open. Normally, when a
  database is open, there will be constant I/O to
  online redolog ?les or data ?les. Even if the
  database is idle, there is no guarantee that
  database will not write anything to ?les during
  snapshot.
• However, if you suspend the database, Oracle
  will halt I/O operations to these data?les until
  it is reverted back to normal mode. So, you
  should suspend the database, take the snapshot
  of the disk and then put the databaseback in
  normal

12
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018

• mode immediately after that.
• Q. What kind of information can be given while
  creating a sequence?
• Sequence Name This is the name of the sequence.
  It should be unique inside the schema.
• Start With This is the number that the sequence
  will start from.
• Increment By This number shows how much the
  sequence will increment at each move.
• Nocycle This determines whether the sequence
  will start from the beginning once it reaches
  the end.
• Nocache This determines how much next sequence
  number willbe cached in SGA. Nocache means no
  next sequence will be cached.
• Q. You want your database to start automatically,
  after a reboot of the server. How would you do
  that?
• In default con?guration, Oracle database will not
  automatically start after the server reboots.
  Youll have to start it manually after each
  reboot. Youll usually want it to start
  automatically. There are two methods to
  accomplish this
• Using Oracle Restart Oracle Restart is a
  feature of Oracle High Availability Service
  (OHAS). You need to install Grid Infrastructure
  to enable Oracle Restart feature. Using
  Oracle Restart is the recommended way.
• Using Your Own Script It is also possible for
  you to write your own bash script to start the
  database and place that script in the startup of
  the operating system.
• Q. Which components of your database environment
  can be protected by an Oracle Restart
  conftguration?
• Database Instances and Automatic Storage
  Management (ASM) Database instances and ASM
  instances will be restarted if they crash
  somehow.
• Oracle NET Listener Oracle NET Listener will be
  started if it crashes and stops listening for
  incoming connection.
• ASM Disk Groups Oracle Restart will mount ASM
  Disk groups if they are

13
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018

• dismounted.
• Database Services Non-default database services
  will be started by Oracle Restart feature.
• Oracle Notiftcation Services (ONS) This is
  another Oracle component that can be protected
  by Oracle Restart.
• Q. Explain the di?erence between shared server
  architecture and dedicated server architecture
  ?
• When a user connects to a database, he sends SQL
  queries to the database to execute. These SQL
  queries are executed by a server process and
  the result is returned back to the user. In
  dedicated server architecture, the instance
  will create one server process for each
  connected user.
• That process will be dedicated to that user and
  will only serve that client. However in shared
  server architecture, a single server process
  will serve multiple clients. In shared server
  architecture, the total memory consumption will
  be less. However, certain operations like DBA
  activities can only be performed in dedicated
  server.
• Q. Explain how shared server architecture
  works.
• In shared server architecture, the clients
  connect to a dispatcher process. This
  dispatcher is responsible for delivering the SQL
  requests to the request queue.
• The shared server process monitors the request
  queue. When they ?nd an incoming request, they
  execute this SQL query and place the results in
  the response queue. The request queue and the
  response queue reside in the system global area.
• The dispatcher processes also monitor response
  queue. When it receives a result, they deliver
  the result to the relevant client.
• In this architecture, there will be multiple
  shared server processes and dispatcher processes.

14
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018

• Q. What are the instance parameters that are used
  for conftguring shared server architecture?
• DISPATCHERS Astring value which is used to
  con?gure dispatchers.
• SHARED_SERVERS Minimum number of shared server
  processes that will be present in the server.
  Also, this number of shared servers is created
  during startup.
• MAX SHARED_SERVERS This parameterdetermines the
  maximum number of shared server processes that
  can run at the same time.
• SHARED_SERVER SESSIONS This parameter speci?es
  the maximum number of sessions that can exist at
  the same time using shared server connection.
• CIRCUITS This parameter determines the maximum
  number of virtual circuits that can exist in the
  system.
• Q. Explain how the Database Writer process
  works.
• There can be multiple databasebackground
  processes. They are named as DBWn at operating
  system. This process is responsible for writing
  dirty bu?ers to disk. When a server process
  wants to update a data block, it reads the block
  from disk to bu?er cache if the block is not
  already in the cache and then updates the copy
  in the cache. The modi?ed database block in the
  bu?er cache is called a dirty block.
• Explore Oracle DBA Sample Resumes! Download
  Edit, Get Noticed by Top Employers!Download Now!
  (https/ mindmajix.com/oracle-dba-sample-
  resumes)
• Oracle Data Structures SQL
• Q. Why is index used?
• Index is used to increase the performance of
  retrieval. We can make use of one or more rows
  in order to make the index. Index can increase
  the performance of retrieval and slows down the
  performance of insertion.

15
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018
Q. Who is responsible to update the
indexes? Oracle automatically maintains and uses
indexes and when any change is made in the table
data Oracle automatically distributes it into
relevant indexes. Q. What is the deftnition of
table in Oracle? Table is the ?rst level of
physical unit in database. Oracle uses tables of
a database to store data into rows and columns.
Table is the ?rst level of physical unit in
database. Q. What do you mean by view and what
are its types? View is a type of virtual table
and there is a query attached to every view in
order to identify speci?c rows and columns of
the table. Views are read-only as well as
read-write. Q. In Oracle terminology, what do
you mean by tablespace? Tablespace is a Logical
Storage Unit used to group related logical
structures together. It is the logical structure
where all the objects of database will be
grouped. Q. When does the SYSTEM tablespace get
created? In Oracle every database has tablespace
called SYSTEM and it is automatically created
when database is created. It also contains the
data dictionary table for the whole data. Q.
What is the relationship between tablespace and
dataftles? Each tablespace is divided into one
or more data ?les and one and more tablespace(s)
are created for each database. Q. How do we use
materialized view? Materialized views are objects
that have reduced sets of information that have
been summarized, grouped, or aggregated from base
tables. They are typically used in data
warehouse or decision support systems.
16
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018
Q. In Oracle terminology, what is Synonym? A
synonym is an identi?er that can be used to
reference another database object in a SQL
statement. The types of database objects for
which a synonym may be created are a table,
view, sequence, or another synonym. Q. Di?erent
types of synonyms are? Synonym types are private
and public. Q. What you understand by public
synonym? A public synonym does not belong to any
schema. In other words, when any database user
can access it, it is called public synonym. Q.
What you understand by private synonym? A private
synonym is one that does belong to a speci?c
schema. In other words, when only owner can
access it, it is called private synonym. Q. What
are the advantages of synonyms? Synonym is used
to mask the original name and owner of an object
and provides public access to an object. Q.
What is a sequence? A sequence generates a serial
list of unique numbers for numerical columns of a
database tables. We can use sequence on columns
for data where we want to insert data in
sequential manner. Q. What you understand by
private database link? Private database link is
created for a speci?c user. It is only used when
the owner of the link speci?es a global object
name in a SQL statement or in the de?nition of
the owners views or procedures. Q. What you
understand by public database link? Database link
is a schema object in one database to access
objects in another database. When you create
database link with Public clause it is available
for
17
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018
access to all the users. Q. What do you mean by
row chaining? Row Chaining occurs when the row is
too large to ?t into one data block when it is
?rst inserted. In this case, Oracle stores the
data for the row in a chain of data blocks (one
or more) reserved for that segment. Row chaining
most often occurs with large rows, such as rows
that contain a column of datatype LONG, LONG
RAW, LOB, etc. Row chaining in these cases is
unavoidable. Q. What is the deftnition of
extent? An extent is a set of contiguous blocks
allocated in a database. In the Oracle database
program, the ?rst set of contiguous blocks, set
up automatically when a segment is created, is
called the initial extent. After the initial
extent has been ?lled, the program allocates
more extents automatically. These are known as
next extents. The total number of extents that
can be allocated in a database is limited by the
amount of storage space available, or in some
cases, by the program used. Q. Explain the
advantages of using view? The view helps provide
security, presentation of data in a di?erent
perspective and store complex queries. Q. What
do you mean by dataftle? An Oracle data?le is a
big unit of physical storage in the OS ?le
system. One or many Oracle data ?les are
organized together to provide physical storage to
a single Oracle tablespace. Data?le is used to
store tables and indexes allocated to the
database. Every database consists of one or more
data ?les. Q. Explain the properties of data
ftles? Each data ?le can only be associated with
only one database and once it is created it can
not change its size.
18
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018

• Q. What do you mean by redo log?
• The most crucial structure for recovery
  operations is the redo log, which consists of
  two or more pre-allocated ?les that store all
  changes made to the database as they occur.
  Every instance of an Oracle Database has an
  associated redo log to protect the database in
  case of an instance failure.
• Q. Main function(s) of redo log is?
• Redo logs main function is to store all changes
  made to the database as they occur.
• Q. What are the contents of control ftle?
• Every Oracle database has a control ?le. A
  control ?le is a small binary ?le that records
  the physical structure of the database and
  includes
• The database name
• Names and locations of associated data?les and
  online redo log ?les
• The timestamp of the database creation
• The current log sequence number
• Checkpoint information
• Q. What are the advantages of control ftle?
• The control ?le must be available for writing by
  the Oracle database server whenever the database
  is open. Without the control ?le, the database
  cannot be mounted and recovery is di?cult. You
  might also need to create control ?les, if you
  want to change particular settings in the control
  ?les.
• Q. What is deftnition of SQL?
• SQL is a database computer language designed for
  managing data in relational database management
  systems (RDBMS), and originally based upon
  relational algebra. Its scope includes data
  insert, query, update and delete, schema
  creation and modi?cation, and data access control.

19
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018

• Q. What is the use of SELECT statement?
• SELECT statement is used to select the set of
  speci?c values from a table in a database
  depending on the various conditions speci?ed in a
  SQL query.
• Q. How can you compare a part of the name rather
  than the entire name? In order to compare part
  we use LIKE operator which acts like a regex
  engine for database.
• Q. What is the keyword to get distinct records
  from a table?
• SELECT DISTINCT allows the user to select the
  distinct values from a table in a database.
• Q. In order to get sorted records from a table,
  what is the keyword? ORDER BY keyword is used
  for sorting the results. It returns the sorted
  results to your program.
• Q. In order to get total records from a table,
  what is the keyword? To ?nd the total number of
  records in a table, COUNT keyword is used.
• Q. What is deftnition of GROUP BY?
• GROUP BY keyword is an aggregate function such as
  SUM, MULTIPLE, etc and without this function sum
  for each individual group values can not be
  calculated.
• Explain the di?erence among dropping a table,
  truncating a table and deleting all records
  from a table.
• Dropping a table means to drops the invalid
  indexes. It deletes table de?nition and records
  both,
• Truncating means to delete invalid data values
  automatically. Internally the database marks the
  table as empty and the deleted records are not
  recorded in the transaction log.
• Deleting all records, deletes all records but
  records all details in Transaction log ?le.

20
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018

• Di?erent types of SQL statements are? There are
  ?ve types of SQL statements
• Data De?nition Language,
• Data Manipulation Language,
• Transactional Control,
• Session Control and
• System Control.
• Q. What is deftnition of transaction?
• Oracle supports transactions as de?ned by the SQL
  standard. A transaction is a sequence of SQL
  statements that Oracle treats as a single unit of
  work. As soon as you connect to the database, a
  transaction begins. Once the transaction begins,
  every SQL DML (Data Manipulation Language)
  statement you issue subsequently becomes a part
  of this transaction. A transaction ends when you
  disconnect from the database, or when you issue a
  COMMIT or ROLLBACK command.

Social Share Previous (https/ mindmajix.com/servi
cenow-interview-questions) Next (https/
mindmajix.com/aws-interview-questions) Popular
Courses in 2018 Salesforce Training (https/
mindmajix.com/salesforce- training) (5.0) 2258
Enrolled Selenium Training (https/
mindmajix.com/selenium- training) (5.0)
21
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018
3370 Enrolled Splunk Training (https/
mindmajix.com/splunk-training) (5.0) 1256
Enrolled Python Training (https/
mindmajix.com/python-training) (5.0) 1463
Enrolled DevOps Training (https/
mindmajix.com/devops-training) (5.0) 2895
Enrolled RPA Training (https/
mindmajix.com/rpa-training) (5.0) 4025
Enrolled OpenStack Training (https/
mindmajix.com/openstack- training) (5.0) 945
Enrolled Data Science Training (https/
mindmajix.com/data- science-training) (5.0)
22
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018
1578 Enrolled Microsoft Azure Training (https/
mindmajix.com/microsoft-azure-training) (5.0) 195
6 Enrolled MongoDB Training (https/
mindmajix.com/mongodb- training) (5.0) 1220
Enrolled Hadoop Training (https/
mindmajix.com/hadoop- training) (5.0) 1090
Enrolled Cognos Training (https/
mindmajix.com/cognos-training) (5.0) 3012
Enrolled JENKINS Training (https/
mindmajix.com/jenkins- training) (4.0) 880
Enrolled SAS Training (https/
mindmajix.com/sas-training) (5.0)
23
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018
1899 Enrolled VMware Training (https/
mindmajix.com/vmware- training) (5.0) 3350
Enrolled AngularJS Training (https/
mindmajix.com/angularjs- training) (5.0) 3766
Enrolled Salesforce Lightning Training (https/
mindmajix.com/salesforce-lightning-training) (5.0)
1458 Enrolled Automation Anywhere
Training (https/ mindmajix.com/automation-anywher
e-training) (5.0) 3780 Enrolled Machine
Learning Training (https/ mindmajix.com/machine-l
earning-training) (5.0) 1285 Enrolled Artiftcia
l Intelligence (AI) Training (https/
mindmajix.com/artiftcial-intelligence-training)
24
4/12/2018
The Best Oracle DBA Interview Questions Answers
UPDATED 2018
(5.0) 75 Enrolled
mindmajix.com (https/ mindmajix.com/oracle-dba-in
terview-questions) by Oracle DBA Interview
questions, Interview questions for Oracle DBA