Oracle Architecture Interview Questions

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 ...

it's you table, index and else

ps -ef | grep oracle

On Unix the command is ps -aef | grep -i pmon

A Archive log file is used to archive the filled redo log files. and used in recovery.

It is used in recovery but not in instance recovery.
Rest it is used to make database consistent after recovery by matching header of data file and control file checkpoint information.

Hello Ganesh,Q : What is the use of archive log file? A : It is used for Point In Time Database Instance Recovery.     Q : How will you apply archive log file? A : Archive Log need...

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

alerts will log to alert.log  and alert trace files will be saved in  $ORACLE_HOME/admin/bdump directory.

A background process maintain relationship between memory structure and physical structure of Oracle server. Started when Oracle instance is started. Consist of 5 mandatory and some optional backgro...

The Background process related to Oracle Architecture are as follows:1.DBWR(DATABASE WRITER) 2.LGWR(LOG WRITER) 3.SMON(SYSTEM MONITOR) 4.PMON(PROCESS MONITOR) 5.CKPT(CHECK POINT)6....

Oracle architecture consist of four structures: 1. Memory structure (SGA, PGA) 2. Process structure (user process, server process, background process) 3. Physical structure (data files, redo log files...

physical architecture of oracle?


physical architecture is nothing but the physical files is stored in database

1.Data file
2.Control file
3.redo log file

It is enough

dbwn write to datafile not to data buffer cache

When update or any other changes occurs and it commits, CKPT forces DBWr to flush the dirty block from DB Buffer cache to data files..

A schema is the set of objects (tables, views, indexes, etc) that belongs to an user account. It is often used as another way to refer to an Oracle account. Most Oracle database installations traditio...

schema is a collect of object (table,view,cluster etc) that belongs to an user account.

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

lgwr writes from log buffer to log files not to log buffer..

Four conditions when LGWr writes from redo log cache to redo log files

1) 1/3 of redolog buffer is full
2) Every 3 secs
3) For every 1mb collections
4) When Commit occurs

Redo log records the changes made in database. Redo logs is mainly used to recover data.

Hi,Redo log information is the one of the major pard of the database , at any time if the oracle server instance get aborted and then it need recovery, so at that time redo log plays a role to recover...

A B-Tree index stores the index value and the physical rowid of the row. The index values are arranged in the form of leaves. A B-Tree index is used most when the cardinality is high. A Bitmap index ...

For example suppose we have using many distinct value for particular column in that time using b-tree index.

Bitmap indexes which is used low cardinaties values .
For example suppose we have using gender or flag column in that time using bitmap index .

In data files, we cant change the extent management, type of it. But we can change the storage space for it if we need after the database (tablespace) creation.

Steps for changing the size of the datafile.1. Change that datafile to offline. eg. SQL>ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;2. Chang the size of the datafile. eg SQL>...

When shutdown abort happens, oracle server gets crashed. To recover from the crash, SMON will check the online Redo data files and last SCN no in the Redo log files and SCN number in the undo tablespa...

When the Oracle instance fails. The SMON process performs instance recovery at startup using the online redo log files.

database link is created by a user in his schema,it has 2 types

1.private database link
2.public database link /global

private database link is access the object in separate user

syntax:

create database link connect to username identified by password using 'db_name';

Private database link is created by a user in his schema. While creating the link, the keyword GLOBAL is not used. The syntax for creation is CREATE DATABASE LINK myPrivateDBLink connect to identified by using ;
Private DB links can not be used by other schema's.

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 f...

v$database

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.

Yes one tablespace can hold objects from different schemas.Eg. when we create any schema (user) than we can assign default tablespace like this for any user user we can use same tablespace. Eg. SYSTE...

Smallest logical of Oracle database. In Oracle 10g default data block size is 8192 byres means 8k.

Other data block size are 2k, 4k, 8k, 16k, 32k
We have only these available data blocks in oracle database.

Oracle Data Blocks is logical storage unit of Oracle and One Oracle Data Blocks containts multiple of Operating system Blocks. Data blocks contains contigous of Bytes.