Oracle Architecture and Concepts

1. High water mark is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations.

2. Delete Table operation wont reset HWM.

3. TRUNCATE will reset HWM.

4. The high water mark level is just a line separate the used blocks and free blocks.

The blocks above the HWM level is free blocks, they are ready to use.

The blocks below the HWM level is used blocks, they are already used.

When hardware failure, software failue and shutdown abnormally happen...

SMON taking care of Instance failure, coalesce failure space and deallocating temp segment

start to recover instance Roll fwd with help of redologfiles for commited transaction and roll backward upto uncommited

transaction with help of undotablespace.

coalesce failure space: De-fragmenting every 3 sec

deallocating temp segment: Sorting purpose

The Five Mandatory Background Processes in Oracle Architecture are as Follows:

1.Database Writer(DBWR)

2.Log Writer(LGWR)

3.Check Point(CKPT)

4.System Monitor(SMON)

5.Processes Monitor(PMON)

Background process that modifies the db buffer cache and not data file is Server process(it fetch the data blocks from database and get it on data buffer cache)Process which modifies/writes the data file: DBWR will write back the data to data file. (But this is not the question)

LGWR writes one contiguous portion of the buffer to disk. LGWR writes:

1. A commit record when a user process commits a transaction

2. Redo log buffers

– Every three seconds

– When the redo log buffer is one-third full

– When a DBWn process writes modified buffers to disk, if necessary

As a sys or system user,select name from v$database;As a normal user,select global_name from global_name;select ora_database_name from dual; - dual is a dummy table in oracleshow parameter db_name - has to be given in sql prompt.

The level of logical database storage above an extent is called a segment. A segment is a set of extents, each of which has been allocated for a specific data structure and all of which are stored in the same tablespace. For example, each tables data is stored in its own data segment, while each index data is stored in its own index segment. If the table or index is partitioned, each partition is stored in its own segment.

No, you cant ALTER VIEW to add or remove columns.

Need to recreate view with new column using CREATE OR REPLACE view command

The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes. The maximum number of DBWn processes is 20. If it is not specified by the user during startup, Oracle determines how to set DB_BLOCK_PROCESSES based on the number of CPUs and processor groups.

It is used for Point In Time Database Instance Recovery.

Archive Log need to apply when you need to recover database.

SQL> startup mount

SQL> alter database recover

<<-- Here database instance shall prompt you for required archive log file, you may perform Manual or AUTO recovery-->>

If database is in Archive Log Mode the only it is possible to perform HOT (Online) Backup, however all transaction entries would be done in Redo File and eventually it will be written to Archive Log File, it also called Archived Redo Log files.Hence by backing up Datafiles/Redo Log Files/Control Files and Archived redo Log Files you can have a consistent backup. Only in caseof Cold backup you dont reuire Archive Redo Log files as database instance is down while performing Cold backup.

For more details you may refer - http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12007.htm

Schema is a Collection of Database objects of a user.Example: Tables, Views, Synonyms, Sequences, Clusters, Triggers, Procedures, Packages.

Errors are stored in alert.log file it can be shecked using the parameter background_dump_dest

Use show parameter background_dump_dest

for path of the alert.log in trace directory

Either virtual table is a temporary table or v$ or Xv$ views

Please share your answers.

Bitmap indexes are more advantageous than b-tree indexes when the table has millions of rows and the key columns have low cardinality. bitmap indexes provide better performance than b tree indexes when queries often use a combination of multiple where conditions involving the OR operator, the table is read-only, or when there is low update activity on the key columns.

Oracle uses query executed by user because when you use a view name in an other query, it simply transform query.

select empid,name from emp; --- original

create view emp_view as select empid,name from emp; ----- view created as name emp_view

select * from emp_view where empid=100; ---------- this quey transform into

select * from (select empid,name from emp) where empid=100;

In above query it uses view because we use view name.

If we execute original query ,It does not have any view any to interpret,hence it uses original query.