Oracle DBA Questions

Oracle Questions and Answers -9

Q.What is a database link ?

Database Link is a named path through which a remote database can be accessed.

Q.How does one create a repository? (for DBA)

For OEM v2 and above, start the Oracle Enterprise Manager Configuration Assistant (emca on Unix) to create and configure the management server and repository. Remember to setup a backup for the repository database after creating it.

Q.Can a view be updated/inserted/deleted? If Yes under what conditions ?

A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.

Q.If a View on a single base table is manipulated will the changes be reflected on the base table ?
If changes are made to the tables which are base tables of a view will the changes be reference on the view.
The following describes means to create a OEM V1.x (very old!!!) repository on WindowsNT:
.Create a tablespace that would hold the repository data. A size between 200- 250 MB would be ideal. Let us call it Dummy_Space.
.Create an Oracle user who would own this repository. Assign DBA, SNMPAgent, Exp_Full_database, Imp_Full_database roles to this user. Lets call this user Dummy_user. Assign Dummy_Space as the default tablespace.
.Create an operating system user with the same name as the Oracle username. I.e. Dummy_User. Add 'Log on as a batch job' under advanced rights in User manager.
.Fire up Enterprise manager and log in as Dummy_User and enter the password. This would trigger the creation of the repository. From now on, Enterprise manager is ready to accept jobs.

Q.How does one list one's databases in the OEM Console? (for DBA)

Follow these steps to discover databases and other services from the OEM Console:
1.Ensure the GLOBAL_DBNAME parameter is set for all databases in your LISTENER.ORA file (optional). These names will be listed in the OEM Console. Please note that names entered are case sensitive. A portion of a listener.ora file:
(SID_NAME = ...
2.Start the Oracle Intelligent Agent on the machine you want to discover. See section "How does one start the Oracle Intelligent Agent?".
3.Start the OEM Console, navigate to menu "Navigator/ Discover Nodes". The OEM Discovery Wizard will guide you through the process of discovering your databases and other services.

Q.What are the data types allowed in a table ?


Q.What is a Transaction in Oracle?

A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statement and ends when it is explicitly committed or rolled back.

Q.What is difference between CHAR and VARCHAR2 ? What is the maximum SIZE allowed for each type?

CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR it is 255 and 2000 for VARCHAR2.

Q.What are the different types of Coordinations of the Master with the Detail block?


Q.Use the ADD_GROUP_COLUMN function to add a column to a record group that was created at design time?


Q.What is CYCLE/NO CYCLE in a Sequence ?

CYCLE specifies that the sequence continues to generate values after reaching either maximum or minimum value. After pan ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.

Q.What is correlated sub-query?

Correlated sub query is a sub query which has reference to the main query.

Q.Use the ADD_GROUP_ROW procedure to add a row to a static record group?



Q.maxvalue.sql Select the Nth Highest value from a table?
select level, max('col_name') from my_table where level = '&n' connect by prior ('col_name') > 'col_name')
group by level;
Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
-- For the second highest salary:
-- select level, max(sal) from emp
-- where level=2
-- connect by prior sal > sal
-- group by level

Q.Find out nth highest salary from emp table?

For E.g.:-
Enter value for n: 2

Q.What utility is used to create a physical backup?

Either rman or alter tablespace begin backup will do..

Q.What are the Back ground processes in Oracle and what are they.

This is one of the most frequently asked question.There are basically 9 Processes but in a general system we need to mention the first five background processes.They do the house keeping activities for the Oracle and are common in any system.
The various background processes in oracle are

a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from Database buffer cache to Data Files.This is required since the data is not written whenever a transaction is committed.
b)LogWriter(LGWR) :: LogWriter writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor(SMON) :: The System Monitor performs instance recovery at instance startup. This is useful for recovery from system failure
d)Process Monitor(PMON) :: The Process Monitor performs process recovery when user Process fails. Pmon Clears and Frees resources that process was using.
e) CheckPoint(CKPT) :: At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the most recent checkpoint
f)Archieves(ARCH) :: The Archiver copies online redo log files to archival storal when they are busy.
g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network
h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture
i) Lckn :: We can have upto 10 lock processes for inter instance locking in parallel sql.

Q.How you will avoid your query from using indexes?

Where emp_no+' '=12345;
i.e you have to concatenate the column name with space within codes in the where condition.
SELECT /*+ FULL(a) */ ename, emp_no from emp
where emp_no=1234;
i.e using HINTS

Q.How many types of Sql Statements are there in Oracle?

There are basically 6 types of sql statments.They are

a) Data Definition Language(DDL) :: The DDL statements define and maintain objects and drop objects.

b) Data Manipulation Language(DML) :: The DML statements manipulate database data.

c) Transaction Control Statements :: Manage change by DML
d) Session Control :: Used to control the properties of current session enabling and disabling roles and changing .e.g. :: Alter Statements, Set Role
e) System Control Statements :: Change Properties of Oracle Instance .e.g.:: Alter System
f) Embedded Sql :: Incorporate DDL, DML and T.C.S in Programming Language.e.g:: Using the Sql Statements in languages such as 'C', Open, Fetch, execute and close

Q.How many Integrity Rules are there and what are they?

There are Three Integrity Rules. They are as follows ::
a) Entity Integrity Rule :: The Entity Integrity Rule enforces that the Primary key cannot be Null
b) Foreign Key Integrity Rule :: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced.When there is data in Child Tables the Master tables cannot be deleted.
c) Business Integrity Rules :: The Third Intigrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

Q. What are the Keywords Used in Oracle?

The Key words that are used in Oracle are ::
a) Committing :: A transaction is said to be committed when the transaction makes permanent changes resulting from the SQL statements.
b) Rollback :: A transaction that retracts any of the changes resulting from SQL statements in Transaction.
c) SavePoint :: For long transactions that contain many SQL statements, intermediate markers or savepoints are declared. Savepoints can be used to divide a transaction into smaller points.
d) Rolling Forward :: Process of applying redo log during recovery is called rolling forward.
e) Cursor :: A cursor is a handle ( name or a pointer) for the memory associated with a specific stamen. A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explicit cursor for a multi row query.
f) System Global Area(SGA) :: The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance. It consists of Database Buffer Cache and Redo log Buffer.
g) Program Global Area (PGA) :: The PGA is a memory buffer that contains data and control information for server process.
g) Database Buffer Cache :: Database Buffer of SGA stores the most recently used blocks of database data. The set of database buffers in an instance is called Database Buffer Cache.
h) Redo log Buffer :: Redo log Buffer of SGA stores all the redo log entries.
i) Redo Log Files :: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
j) Process :: A Process is a 'thread of control' or mechanism in Operating System that executes series of steps.

Q.Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?
SELECT customer_no, payments from customer C1
WHERE 3<=(SELECT COUNT(*) from customer C2
WHERE C1.payment <= C2.payment)

Q.What are Procedure, functions and Packages?

Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
Procedures do not Return values while Functions return one One Value Packages :: Packages Provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents

Q.What are the Various Block Coordination Properties?

The various Block Coordination Properties are
a) Immediate Default Setting. The Detail records are shown when the Master Record are shown.
b) Deffered with Auto Query Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deffered with No Auto Query The operator must navigate to the detail block and explicitly execute a query

Q.What are Database Triggers and Stored Procedures?

Database Triggers :: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table.
Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which cannot be enforced using the integrity rules.We can have the trigger as Before trigger or After Trigger and at Statement or Row level. e.g:: operations insert,update ,delete 3 before ,after 3*2 A total of 6 combinatons
At statment level(once for the trigger) or row level( for every execution ) 6 * 2 A total of 12. Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 Onwards.
Stored Procedures :: Stored Procedures are Procedures that are stored in Compiled form in the database.The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.

Q.What are the Different Optimization Techniques?

The Various Optimisation techniques are
a) Execute Plan :: we can see the plan of the query and change it accordingly based on the indexes
b) Optimizer_hint ::
Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept
where (Deptno > 25)
c) Optimize_Sql ::
By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements.This slow downs the processing because for evertime the SQL must be parsed whenver they are executed.
f45run module = my_firstform userid = scott/tiger optimize_sql = No
d) Optimize_Tp ::
By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger optimize_Tp = No

Q.How does one change an Oracle user's password?(for DBA)
Issue the following SQL command:
ALTER USER <username> IDENTIFIED BY <new_password>;
From Oracle8 you can just type "password" from SQL*Plus, or if you need to change another user's password, type "password user_name". Look at this example:
SQL> password
Changing password for SCOTT
Old password:
New password:
Retype new password:

Q.What are the Various Master and Detail Relation ships?

The various Master and Detail Relationship are
a) NonIsolated :: The Master cannot be deleted when a child is exisiting
b) Isolated :: The Master can be deleted when the child is exisiting
c) Cascading :: The child gets deleted when the Master is deleted.

Q.How does one create and drop database users?
Look at these examples:
IDENTIFIED BY tiger -- Assign password
DEFAULT TABLESACE tools -- Assign space for table and index segments
TEMPORARY TABLESPACE temp; -- Assign sort space
DROP USER scott CASCADE; -- Remove user
After creating a new user, assign the required privileges:
GRANT DBA TO scott; -- Make user a DB Administrator
Remember to give the user some space quota on its tablespaces:

Q.Who created all these users in my database?/ Can I drop this user? (for DBA)

Oracle creates a number of default database users or schemas when a new database is created. Below are a few of them:
Oracle Data Dictionary/ Catalog
Created by: /rdbms/admin/sql.bsq and various cat*.sql scripts
Can password be changed: Yes (Do so right after the database was created)
Can user be dropped: NO
The default DBA user name (please do not use SYS)
Created by: /rdbms/admin/sql.bsq
Can password be changed: Yes (Do so right after the database was created)
Can user be dropped: NO
Stored outlines for optimizer plan stability
Created by: /rdbms/admin/sql.bsq
Can password be changed: Yes (Do so right after the database was created)
Can user be dropped: NO
Training/ demonstration users containing the popular EMP and DEPT tables
Created by: /rdbms/admin/utlsampl.sql
Can password be changed: Yes
Can user be dropped: YES - Drop users cascade from all production environments
HR/HR (Human Resources), OE/OE (Order Entry), SH/SH (Sales History).
Training/ demonstration users containing the popular EMPLOYEES and DEPARTMENTS tables
Created by: /demo/schema/mksample.sql
Can password be changed: Yes
Can user be dropped: YES - Drop users cascade from all production environments
Oracle interMedia (ConText Cartridge) administrator user
Created by: /ctx/admin/dr0csys.sql
Oracle Trace server
Created by: /rdbms/admin/otrcsvr.sql
Oracle Intelligent agent
Created by: /rdbms/admin/catsnmp.sql, called from catalog.sql
Can password be changed: Yes - put the new password in snmp_rw.ora file
Can user be dropped: YES - Only if you do not use the Intelligent Agents
Object Relational Data (ORD) User used by Time Series, etc.
Created by: /ord/admin/ordinst.sql
Object Relational Data (ORD) User used by Time Series, etc
Created by: /ord/admin/ordinst.sql
Oracle Dynamic Services and Syndication Server
Created by: /ds/sql/dssys_init.sql
Oracle Spatial administrator user
Created by: /ord/admin/ordinst.sql
Used for users who do not authenticate in Aurora/ORB
Created by: /javavm/install/init_orb.sql called from /javavm/install/initjvm.sql
Oracle Statistics Package (STATSPACK) that supersedes UTLBSTAT/UTLESTAT
Created by: /rdbms/admin/statscre.sql
Remember to change the passwords for the SYS and SYSTEM users immediately after installation!
Except for the user SYS, there should be no problem altering these users to use a different default and temporary tablespace. 

Also Read:
Oracle Interview Questions -1

Oracle Interview Questions -2

Oracle Interview Questions -3

Oracle Interview Questions -4

Oracle Interview Questions -5

Oracle Interview Questions -6

Oracle Interview Questions -7

Oracle Interview Questions -8

Oracle Interview Questions -10

Oracle Interview Questions -11

Oracle Interview Questions -12

Oracle Interview Questions -13
Oracle Interview Questions -14

Oracle Interview Questions -15

Oracle Interview Questions -16

Oracle Interview Questions -17
Oracle Interview Questions -18

Oracle Interview Questions -19

Oracle Interview Questions -20

Oracle Interview Questions -21
Oracle Interview Questions -22


Post a Comment