Oracle DBA Q & A page - 10

ORACLE DBA INTERVIEW QUESTIONS AND ANSWERS

                                                             Page – 10

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

Oracle database is comprised of three types of files. One or more datafiles, two or more redo log files, and one or more control files.

92. What is database link?                                                                     

A database link is a named object that describes a “path” from one database to another.

93. What are the types of database links?

Private database link, public database link & network database link.

94.  What is private database link?      

Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner’s views or procedures.

95.  What is public database link?

Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.

96.  What is network database link?

Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.

97. What is data block?

Oracle database’s data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.

98. How to define data block size?

A data block size is specified for each Oracle database when the database is created. A database users and allocated free database space in Oracle data blocks. Block size is specified in init.ora file and cannot be changed latter.

99. What is an extent?           

An extent is a specific number of contiguous data blocks, obtained in a single allocation and used to store a specific type of information

100. What is dictionary cache?

Dictionary cache is information about the database objects stored in a data dictionary table

                                        Page  1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10

Oracle DBA Q & A page - 9

ORACLE DBA INTERVIEW QUESTIONS AND ANSWERS

                                                

                                                              Page – 9

81. Give the reasoning behind using an index.

Faster access to data blocks in a table

82. What command would you use to create a backup control file?

Alter database backup control file to trace

83. How do you set up tablespaces during an Oracle installation?

You should always attempt to use the Oracle Flexible Architecture standard or another partitioning scheme to ensure proper separation of SYSTEM, ROLLBACK, REDO LOG, DATA, TEMPORARY and INDEX segments

84. Discuss row chaining, how does it happen? How can you reduce it? How do you correct it

Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won?t fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.

85. How can you find out how many users are currently logged into the database? How can you find their operating system id

There are several ways. One is to look at the v$session or v$process views. Another way is to check the current_logins parameter in the v$sysstat view. Another if you are on UNIX is to do a "ps -ef|grep oracle|wc -l? command, but this only works against a single instance installation.

86. What is a Cartesian product

A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join

87. How do you generate file output from SQL

By use of the SPOOL comm

88.Which datatype is used for storing graphics and images

LONG RAW data type is used for storing BLOB's (binary large objects).

89. How should a many-to-many relationship be handled

By adding an intersection entity table

90. Define OFA.

OFA stands for Optimal Flexible Architecture. It is a method of placing directories and files in an Oracle system so that you get the maximum flexibility for future tuning and file placement

                                          Page  1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10

Oracle DBA Q & A page - 8

ORACLE DBA INTERVIEW QUESTIONS AND ANSWERS

                                                

                                                                 Page – 8

71. What are mutating tables?

When a table is in state of transition it is said to be mutating. eg If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select

72. What is rollback segment?

A database contains one or more rollback segments to temporarily store "undo" information

73. What is  datafile?

Every Oracle database has one or more physical data files. A database's data files contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the data files allocated for a database

74. What background process refreshes materialize views?

The Job Queue Processes

75. Can we rename the table tablespace?

No, this is listed as Enhancement Request 148742. Workaround:
Export all of the objects from the tablespace
Drop the tablespace including contents
Recreate the tablespace
Import the objects

76. What is  library?

A library is a collection of subprograms including user named procedures, functions and packages

77. What  you mean by program global area (PGA)?

It is area in memory that is used by a Single Oracle User Process

78. What is an OLE?

Object Linking & Embedding provides you with the capability to integrate objects from many Ms-Windows applications into a single compound document creating integrated applications enables you to use the features form

79. What  you mean by display item?

Display items are similar to text items but store only fetched or assigned values. Operators cannot navigate to a display item or edit the value it contains

80. How can we avoid indexes?

TO make index access path unavailable - Use FULL hint to optimizer for full table scan - Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another. - Use an expression in the Where Clause of the SQL

                                               Page  1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10

Oracle DBA Q & A page - 7

ORACLE DBA INTERVIEW QUESTIONS AND ANSWERS

              

                                                               Page – 7

61. What is deadlock? Explain

Two processes waiting to update the rows of a table which are locked by the other process then deadlock arises. In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.
These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.

62. What are Roles?

Roles are named groups of related privileges that are granted to users or other roles

63. What is  profile?

Each database user is assigned a Profile that specifies limitations on various system resources available to the user

64. What is dynamic data replication?

Updating or Inserting records in remote database through database triggers. It may fail if remote database is having any problem

65. What is snapshot log?

It is a table that maintains a record of modifications to the master table in a snapshot. It is stored in the same database as master table and is only available for simple snapshots. It should be created before creating snapshots

66. How can we reduce the network traffic?

 Replication of data in distributed environment.
 Using snapshots to replicate data.
 Using remote procedure calls

67. What is cluster key?

The related columns of the tables in a cluster are called the cluster key

68. What is trigger associated with the timer?

When-timer-expired

69. What is the use of  image_zoom built-in?

To manipulate images in image items

70. What is  logical backup?

Logical backup involves reading a set of database records and writing them into a file. Export utility is used for taking backup and Import utility is used to recover from backup

                                                Page  1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10                                                

Oracle DBA Q & A page - 6

  ORACLE DBA INTERVIEW QUESTIONS AND ANSWERS

                                                          Page –  6

51. What is difference between procedure and function.?

A function always returns a value, while a procedure does not. When you call a function you must always assign its value to a variable.

52.  What is different between NO DATA FOUND and %NOTFOUND

NO DATA FOUND is an exception which is raised when either an implicit query returns no data, or you attempt to reference a row in the PL/SQL table which is not yet defined. SQL%NOTFOUND, is a BOOLEAN attribute indicating whether the recent SQL statement does not match to any row

53.  What is GWYUID ?

GWYUID , stands for Gateway User ID and password. Usually like APPLSYSPUB/PUB

54.  Where GWYUID defined and what is its used in Oracle Applications ?

GWYUID is defined in dbc i.e. Database Connect Descriptor file . It is used to connect to database by think clients.

55.  What is difference between UNIQUE and PRIMARY KEY constraints?

An UNIQUE key can have NULL whereas PRIMARY key is always not NOT NULL. Both bears unique values.
56.  What is difference between ADPATCH and OPATCH ?

# ADPATCH is utility to apply ORACLE application Patches whereas
# OPATCH is utility to apply database patches

57.  What is  Partial Backup?

A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down

58.  What is Full Backup?

A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter

59.  What is the use of  Control file?

When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery

60.  What are the type of Synonyms?

There are two types of Synonyms Private and Public

                                        Page  1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10

Oracle DBA Q & A page - 5

ORACLE DBA INTERVIEW QUESTIONS AND ANSWERS

                                               

                                                               Page – 5

41. Explain database  clusters?

Group of  tables physically stored together  because they  share  common column and are often used together  is called clusters

42. Explain what is a lookup table in database? Where can we use these type of the table?

Lookup tables are like constraints which hold a  value. The content in the  lookup tables doesn’t change often, hence as lookup(reference)

Example :-emp table

Emp are not add on regular bases hence one can consider the table as a lookup table. As and when need in some other table the emp information can be fetched base on PK-emp-code.

43. Explain when can hash cluster used?

Hash clusters are useful in case where

There is a uniform, even and predictable no. of key values.

Queries using  equality predicates..

The table is NOT growing constantly, and the keys  are rarely updated

44.Expalin what is an oracle sequence?

A sequence  is a database object created by a user that can be used to generate unique integers. A typical  usage  if sequence is to generate primary key values which  are unique for each row.

It is generated  and incremented / decremented  by an internal  oracle routine. It  can be used  by multiple  users and for  multiple tables  too. A sequence  can be used instead  of writing  an application  code for sequence  generating  routine

45. What is the Index cluster?

An index cluster  uses an index to maintain data within the cluster. The  structure of a cluster index  a normal  index does not store NULL key values, and  index cluster can store it.

Cluster index is likely  to be smaller  than a normal index because, there is only  one entry  for each key value in the  cluster index, even if that is repeating

46. Explain do a  “VIEW”  contain data?

A view  does not contain any data of its own, but is like a window through which data from other tables can be  viewed and changed

47.Explain what is privilege auditing ?

Privilege auditing  is auditing  of  the use of powerful system privileges without regard

to specially named  objects

48. What is  a trace  file and how  it is created?

Each server  and background  process can write an associated  trace file. When an internal error is detected  by a process or user process, it dumps information about the error

49 What is  statement auditing ?

Statement  auditing  is the auditing of  the powerful  system  privileges without. Regard to specially name  objects

50. What is an auditing?

Monitoring of  the user access to aid in the investigation of database use

                                      Page  1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10

Oracle DBA Q & A page - 4

ORACLE DBA INTERVIEW QUESTIONS AND ANSWERS

Page – 4

31. Where would you look for errors from the database engine?

In the alert log

32. What type of index should you use on a fact table?

A Bitmap index.

33.Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

34. What is the purpose of the IMPORT option IGNORE? What is it? What is its

default setting?

The IMPORT IGNORE option tells import to ignore "already exists" errors. If it is not specified the tables that already exist will be skipped. If it is specified, the error is ignored and the tables data will be inserted. The default value is N

35. What is the proper method for disabling and re-enabling a primary key constraint

You use the ALTER TABLE command for both. However, for the enable clause you must specify the USING INDEX and TABLESPACE clause for primary keys

 36. How do you generate file output from SQL

By use of the SPOOL command

37. What is the fastest query method for a table

Fetch by rowed

38.  Where can you get a list of all initialization parameters for your instance? How about an indication if they are default settings or have been changed

You can look in the init.ora file for an indication of manually set parameters. For all parameters, their value and whether or not the current value is the default value, look in the v$parameter view

 39. If you see contention for library caches how can you fix it

 Increase the size of the shared pool

40. If you see statistics that deal with "undo" what are they really talking about

Rollback segments and associated structures

                                       Page  1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10

Oracle DBA Q & A page - 3

ORACLE DBA INTERVIEW QUESTIONS AND ANSWERS

                                     
                                              Page - 3

21. What are the conditions to achieve the normalization?

There are few conditions to achieve the normalization :

There should be a unique row identifier.A table should store only data for a single type of entity.A table should avoid columns which can be null–able.A table should avoid duplication of data and columns

22. What statement must you include in your CREATE TRIGGER statement if the trigger definition includes more than one triggered SQL statement?

BEGIN...END statement

23. What is INDEX?

 Index can speed up execution and impose UNIQUENESS upon data. You can use an index to gain fast access to specific information in a database table. An index is a structure that orders the values of one or more columns in a database table. The index provides pointers to the data values stored in specified columns of the table, and then orders those pointers according to the sort order you specify

24  Define BCNF?

BCNF stands for Boyce-COdd normal form.
A relation is said to be in Boyce-COdd normal form. If it is already in the third normal form and every determinant is a candidate key. It is a stronger version of 3NF.

25. What is a ROWID?

ROWID is the logical address of a row, and it is unique within the database.

26. What is the difference between normalization and denormalization?

Normalization data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table.

The main benefit of denormalization is improved performance with simplified data retrieval and manipulation

27. What is the difference between table and view?

Table: Stores the data in the database on the disk drive.Uses a lot of disk space for a large table. The data belongs to the table.The data in a table is stable and does not change by itself.

View : Stores the select statement that defines the view. It has no data of its own.

Uses very little disk space.The data does not belong to the view. It belongs to the table used in the select statement that defines the view.The data in a view is dynamic and changes when the data in the underlying tables is changed

28. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Having Clause with the GROUP BY function in a query and WHERE Clause is applied to each row before, they are part of the GROUP BY function in a query.

29. What can cause a high value for recursive calls? How can this be fixed?

 A high value for recursive calls is cause by improper cursor usage, excessive dynamic space management actions, and or excessive statement re-parses. You need to determine the cause and correct it By either relinking applications to hold cursors, use proper space management techniques (proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse.

30.  How  transferring a table from one schema to another:

There are several possible methods, export-import, CREATE TABLE... AS SELECT, or COPY.

                            Page 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9  | 10

Oracle DBA Q & A page - 2

ORACLE DBA INTERVIEW QUESTIONS AND ANSWERS

                                     
                                              Page - 2

11. What are the various types of databases?

Multimedia database, Spatial database (Geographical Information System Database) , Real–time or Active Database, Data Warehouse or On–line Analytical Processing Database

12. Define Entity type and  set?

Entity type  defines a collection of entities that have same attribute.  Entity set is the collection of a particular  entity type that are  grouped  into an entity set 

13. What Is  snapshot?

Data in the database at a particular moment is known as Database State or Snapshot of the Database

14. How does a referential constraint differ from a unique constraint?

Referential constraints are concerned with how data in one table relates to data in another table, ensuring referential integrity between the two tables. Unique constraints ensure the integrity within a table by blocking duplicate values.

15. What command creates a grouping or collection of tables, views, and privileges?

The CREATE SCHEMA statement

16. What is the difference between SQL and PL/SQL?

SQL is a declarative language. PL/SQL is a procedural language that makes up for all the missing elements in SQL

17. What is another name for an SQL–invoked procedure?

Stored procedure

18. What are the differences between a column constraint and a table constraint?

Both types of constraints are defined in the table definition. A column constraint is included with the column definition, and a table constraint is included as a table element, similar to the way columns are defined as table elements

19. What is the difference between Delete and Truncate command in SQL?

Delete command and truncate command both will delete the data, however the truncate command can not be rolled back as delete can be. The delete command can be used for selected records using the where clause but with the truncate command we have to loose data. DELETE statement is a logged operation and hence takes more time then truncate.

20. What command is used to retrieve the results after a cursor has been opened?

The FETCH command

                                               Page 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10