Interview Questions for Jr.DBA Oracle

1. What is an Oracle Instance?

The collection of allocated memory and the running processes on the server is called an "instance".

2. What information is stored in Control File?

The control files include information about the physical structure of the database, such as
database name, names and locations of datafiles and redo log files , archived log information, backup set and backup piece information, the current log sequence number and checkpoint information. Therefore, if you make a change to the physical structure of your database (using ALTER DATABASE statements), then you should immediately make a backup of your control file.

3. When you start an Oracle DB which file is accessed first?

Initialization Parameter file i.e spfile_SID.ora or init_SID.ora.

4. What is the Job of  SMON, PMON processes?

System monitor (SMON): monitors the system activity and performs the instance recovery when a system failure occurs or when a shutdown immediate or abort statement is issued it will disconnect the users and performs necessary commit and rollback operations respectively.

Process monitor (PMON): Monitors the users activity and the performs the session recovery when a session fails for ex: when I session failed or lost connection the PMON performs the cleanup process of session data or transactions.

5. What is Instance Recovery?

When an Oracle instance fails, Oracle performs an instance recovery when the associated database is re-started. 

Instance recovery occurs in two steps: 

Cache recovery: Changes being made to a database are recorded in
the database buffer cache. These changes are also recorded in online redo log
files simultaneously. When there are enough data in the database buffer cache,
they are written to data files. If an Oracle instance fails before the data in
the database buffer cache are written to data files, Oracle uses the data
recorded in the online redo log files to recover the lost data when the
associated database is re-started. This process is called cache recovery.

 Transaction recovery: When a transaction modifies data in a
database, the before image of the modified data is stored in an undo segment.
The data stored in the undo segment is used to restore the original values in
case a transaction is rolled back. At the time of an instance failure, the
database may have uncommitted transactions. It is possible that changes made by
these uncommitted transactions have gotten saved in data files. To maintain
read consistency, Oracle rolls back all uncommitted transactions when the
associated database is re-started. Oracle uses the undo data stored in undo
segments to accomplish this. This process is called transaction recovery.

6. What is written in Redo Log Files?

The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files 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.

7. How do you control number of Datafiles one can have in an Oracle database?

 By using DB_FILES parameter update the init file before starting the DB.

8. How many Maximum Datafiles can there be in an Oracle Database?

 Depends on the OS support.

9. What is a Tablespace?

A logical object which is mapped to the physical data files on the OS/storage.

A logical container which holds the DB objects such as tables, indexes, MV’s.

10. What is the purpose of Redo Log files?

Redo log files provide a means and a mechanism for allowing Oracle to redo transactions in the event of a database failure. Every transaction that occurs that alters data in any way on the database (other than transactions such as direct loads, and loads that are done with NOLOGGING option) is written synchronously to the redo log files to provide a recovery mechanism in the case of media or other system failure. This includes even transactions that have not been committed, undo segment information, and any schema and object management statements for objects in the database.

11. Which default Database roles are created when you create a Database?

Resource, Connect and DBA are the three default roles created when you create a database.

12. What is a Checkpoint?

A checkpoint performs the following three operations:

1.      Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the data blocks in the buffer cache with the datafiles on disk.
It's the DBWR that writes all modified database blocks back to the datafiles.

2.      The latest SCN is written (updated) into the datafile header.

3.      The latest SCN is also written to the control files.

The update of the datafile headers and the control files is done by the LGWR.

Events that trigger a checkpoint

The following events trigger a checkpoint.

·         Redo log switch

·         LOG_CHECKPOINT_TIMEOUT has expired

·         LOG_CHECKPOINT_INTERVAL has been reached

·         DBA manually runs (alter system checkpoint)

13. Which Process reads data from Datafiles?

  

Server process.

14. Which Process writes data in Datafiles?

DBWR db writer process.

15. Can you make a Data file auto extendible. If yes, how?

Alter database datafile '/u01/oradata/DBSID/EXAMPLE01.DBF'  autoextend on MAXSIZE 1G.

16. What is a Shared Pool?

The shared pool is like a buffer for SQL statements. It is to store the SQL statements so that the identical SQL statements do not have to be parsed each time they're executed.

17. What is kept in the Database Buffer Cache?

Database Buffer cache is one of the most important components of System Global Area (SGA). Database Buffer Cache is the place where data blocks are copied from datafiles to perform SQL operations. Buffer Cache is shared memory structure and it is concurrently accessed by all server processes.

18. How many maximum Redo Logfiles one can have in a Database?

In a database we can create 255 maximum redo log files.

19. What is difference between PFile and SPFile?

We can change parameter in spfile dynamically but we cant change pfile dynamically ...to change parameter in pfile we need to bounce the instance.

20.  What is PGA_AGGREGRATE_TARGET parameter? size exceeds 

PGA_AGGREGATE_TARGET is an Oracle server parameter that specifies the target aggregate PGA memory available to all server processes attached to the instance.

 The Program Global Area (PGA) is a memory region that contains data and control information for a single process (server or background). The PGA is made up of the following:

Stack Space: memory to hold session variable, arrays and other session related info.

Non-shared area: an area where process writes their trace information and copied to the trace files on the server.

21. Large Pool is used for what?

Large Pool is an optional memory structure used for the following purposes:-
  (1) Session information for shared server
  (2) I/O server processes
  (3) Parallel queries
  (4) Backup and recovery if using through RMAN.

22. What is PCT Increase setting?

PCTINCREASE refers to the percentage by which each next extent (beginning with the third extend) will grow.

23. What is PCTFREE and PCTUSED Setting?

The setting for PCTFREE sets the value for the percent of a block to reserve for updates. If PCTFREE is to small, adequate space may not be reserved in the block for update of variable sized rows in the block, or, may not have enough space for a complete row insert. In this case a block chaining will occur where the data is migrated to a new block and a pointer will be established from the old block to a new block. 

24. What is Row Migration and Row Chaining?

A row will be migrated when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently).  A migration means that the entire row will move and we just leave behind the «forwarding address». So, the original block just has the rowid of the new block and the entire row is moved.

A row is too large to fit into a single database block. For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces. Some conditions that will cause row chaining are: Tables whose row size exceeds  the blocksize. Tables with LONG and LONG RAW columns are prone to having chained rows. Tables with more than 255 columns will have chained rows as Oracle break wide tables up into pieces. So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.

25. What is 01555 - Snapshot Too Old error and how do you avoid it?

The ORA-01555 is caused by Oracle read consistency mechanism.  If you have a long running SQL that starts at 10:30 AM, Oracle ensures that all rows are as they appeared at 10:30 AM, even if the query runs until noon!

Oracles does this by reading the "before image" of changed rows from the online undo segments.  If you have lots of updates, long running SQL and too small UNDO, the ORA-01555 error will appear.

26. What is a Locally Managed Tablespace?

DICTIONARY: Specifies that the table space is managed using dictionary tables

LOCAL: Specifies that tablespace is locally managed with a bitmap

27. Can you audit SELECT statements?

Auditing SQL select statements is used in systems governed by GLB, SOX and HIPAA.  But beware; you will need a storage mechanism to hold your SQL select audits, a high data volume that can exceed the size of your whole database, every day.

Auditing select statement is extremely complex because of the huge amount of data that is generated every day.  In large hospital systems, the select audit trails can exceed terabytes every month with millions of dollars in extra storage costs.

28. What does DBMS_FGA package do?

The central mechanism for the FGA is implemented in the package dbms_fga, where all the APIs are defined.  Typically, a user other than SYS is given the responsibility of maintaining these policies. 

29. What is Cost Based Optimization?

The Cost Based Optimizer (CBO) is a SQL Query optimizer that uses data statistics to identify the query plan with lowest cost before execution. The cost is based on the number of rows in a table, index efficiency, etc.

30. How often you should collect statistics for a table?

When you collect new stats you are almost guaranteed to change some execution plans in your system. Do you want that? No, you want the same execution plans as long as they have the response time your users want. They haven't asked you for a better (or worse) plan.

31. How do you collect statistics for a table, schema and database?

There are 3 different methods of gathering stats

o   ANALYZE statements

o   DBMS_UTILITY package

o   DBMS_STATS package

32. Can you make collection of statistics for table automatic?

Oracle gather statistics on all database objects automatically and maintains those statistics in a regularly-scheduled maintenance job. Automated statistics collection significantly reduces the chances of getting poor execution plans because of missing or stale statistics.

33. On which columns you should create indexes?

The following list gives guidelines in choosing columns to index:

·      You should create indexes on columns that are used frequently in WHERE clauses.

·      You should create indexes on columns that are used frequently to join tables.

·      You should create indexes on columns that are used frequently in ORDER BY clauses.

·      You should create indexes on columns that have few of the same values or unique values in the table.

·      You should not create indexes on small tables (tables that use only a few blocks) because a full table scan may be faster than an indexed query.

·      If possible, choose a primary key that orders the rows in the most appropriate order.

·      If only one column of the concatenated index is used frequently in WHERE clauses, place that column first in the CREATE INDEX statement.

·      If more than one column in a concatenated index is used frequently in WHERE clauses, place the most selective column first in the CREATE INDEX statement.

34. What type of indexes aree available in oracle?

 Bitmap and B-tree.

35. What is B-tree index?

    

36. A table is having few rows should you create indexes on this table?

 No.

37. A column is having many repeated values which type of index you should create on this column?

Bitmap for low cardinality columns.

38. When should you rebuilt indexes?

When you have large data updates /loads or deletes.

39. can you built indexes online?

Yes. Alter index index_name rebuilt online. It always good to drop an recreate the indexes then rebuilding.

40. can you see execution plan of a statement?

Yes. Through oracle’s Plan_table or using sql*plus trace command.

41. A table is created with the following setting

storage (initial 200k   next 200k  minextents 2

                        maxextents 100   pctincrease 40)

what will be size of 4^th extent?

200k

42.  what is DB buffer cache advisor?

The Buffer Cache Advisor provides advice on how to size the Database Buffer Cache to obtain optimal cache hit ratios.

43. Can you change shared pool size online?

Yes. It can be changed online as it’s a dynamic parameter.

45. you want users to change the passwords every two months. How do you enforce it?

 Using profile parameters.

46. how do you delete duplicate rows?

 Using rowID as each row in the table has unique rowID.

DELETE FROM EMP t

WHERE  t.rowid > ( SELECT MIN(X.rowid)

                                    FROM   emp X

                                    WHERE  X.empid= t.empid );          

47. What is difference between DELETE and TRUNCATE statements? size exceeds 

TRUNCATE is a DDL command whereas DELETE is a DML command.

TRUNCATE is much faster than DELETE.

You can't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.

In case of TRUNCATE, Trigger doesn't get fired. But in DML commands like DELETE .Trigger get fired.

You can't use conditions (WHERE clause) in TRUNCATE. But in DELETE you can write conditions using WHERE clause.

48. What is difference between direct path loading and conventional loading?

A conventional path load executes SQL INSERT statements to populate tables in an Oracle database. A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. A direct load does not compete with other users for database resources, so it can usually load data at near disk speed.

49. can you disable and enable primary key?

Yes,

Alter table disable constraint constraint_name.

Alter table enable constraint constraint_name.

50. What is the difference between range partition and hash partition?

Selects a partition by determining if the partitioning key is inside a certain range. 

The value of a hash function determines membership in a partition. Assuming there are four partitions, the hash function could return a value from 0 to 3.

See also my another post for the extension of this session on DBA Interview Questions -II