Oracle DBA Interview Questions & Answers At Online - PowerPoint PPT Presentation

View by Category
About This Presentation
Title:

Oracle DBA Interview Questions & Answers At Online

Description:

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. – PowerPoint PPT presentation

Number of Views:18
Slides: 25
Provided by: miaavery

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: 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
About PowerShow.com