Top Concepts and Architecture Interview Preparation Guide

Download PDF

Redo log is a part of physical structure of oracle. its basic function is to record all the changesmade to daatabase information. wheneveer an abnormal shutdown take place preventing system to update the database, changes can be obtained from redolog and hence the changes are not lost.

Oracle maintains logs of all transactions against the databse. These transactions are recorded in files called Online Redo log files.These logs are used to recover the database's transaction in their proper order in the evnt of database crash. The redo log information is stored in Redo log file which is external to the datafilesEach database have two or more online redo log files. oracle writes tedo log file in cyclic order after the first log is filled , it writes to the second log files, until that one is filled. whn all of the online redo log file have been filled it move to the first online redo log file and start overwriting the transaction recordNote : if the Databse is in ARCHIVE log mode then database will make the copy of the redo log file before overwriting the contents of the log file..These ARCHIEVED redo log file can then be used to recover any part of the database to any point of time.

When you have distributed environment,you may access one database from other.This is normally done via db link.So lets say you have flight database and customer dabatabase.Now from customer database you intend to access flight database then you have to create a database link in customer database providing the login credentials for flight database which would mean that you are mentioning the user name(say flight_admin) of flight database in customer database.Hence the remote user(in this case flight_admin).You may require to mention the domain name in the definition if the domain of both the databases are different else its ok.Local user,as the name suggest is the local user of the database residing within that database.

1. Sum of the data files select sum(bytes/1024/1024/1024) 'Size in GB' from dba_data_files;

2. Actual data size:select sum(bytes/1024/1024/1024) 'Size in GB' from DBA_segments;

There are two types of database
1) logical database
2) physical database

logical database consist of tablespaces....and a tablespace consist of one or more segments....a segment consist of one or more extents.....an extent consist of contigious oracle data blocks.
logical database contain objects of users such as tables,indexes etc.

The WRAP utility 'wraps', or hashes, the PL/SQL source code, but will not 'unwrap' it to restore the plain text starting point. Oracle can decipher this 'translation' internally, however to edit the code one must retain the plaintext source.WRAP is intended to obfuscate source code distributed to client sites in an effort to prevent prying eyes from seeing the underlying logic and to prevent any unauthorized code modification by the client.Again, there is no UNWRAP utility, so, once code is WRAPed it remains such to the naked eye.

Data Base Link. A DB link is reference to the remote database, which can be located on completely different place.It is allows local user to access the remote database. create database link connected to Identified by using

Blob datatype is used for storing large Binary data Like Photos,signatures etc Clob datatype is used for stoeing large character data upto 4GB like resume and other docs

Databases, tablespaces, and datafiles are closely related, but they have important differences:

1) An Oracle database consists of at least two logical storage units called tablespaces, which collectively store all of the database's data. You must have the SYSTEM and SYSAUX tablespaces and a third tablespace, called TEMP, is optional.
2) Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle Database is running.
3) A database's data is collectively stored in the datafiles that constitute each tablespace of the database.
For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).

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. SYSTEM tablespace in oracle database, which is used by all default users whos default tablespace are not mentioned. or
all user will user system tablepsace as default tablespace if their default tablespace is not mentioned at the time of user creation.

SID (System Identifier) : A SID (almost) uniquely identifies an instance. Actually, $ORACLE_HOME, $ORACLE_SID and $HOSTNAME identify an instance uniquely. The SID is 64 characters, or less; at least on Oracle 9i. The system identifier is included in the CONNECT_DATA parts of the connect descriptors in a tnsnames.ora file. The SID defaults to the database name.

Global Database Name : A database is uniquely identified by a global database name. Usually, a global database name has the form somename.domain. The global database name is the composit of db_domain and db_name.

You can create any number of check constraintsseparated by AND, OR, or NOT to create more complex conditions.

The main advantage of using dictionary managed tablespaces is that in this we can give the size of the extents according to our need. We can modify extent size but in locally managed tablespace extents are of uniform size.

Synonyms are used to : Mask the real name and owner of an object.

Provide public access to an object

Provide location transparency for tables, views or program units of a remote database.

Simplify the SQL statements for database users.

A database is uniquely identified by a global database name. Usually, a global database name has the form somename.domain. The global database name is the composit of db_domain and db_name.

A SID (almost) uniquely identifies an instance. Actually, $ORACLE_HOME, $ORACLE_SID and $HOSTNAME identify an instance uniquely. The SID is 64 characters, or less; at least on Oracle 9i. The system identifier is included in the CONNECT_DATA parts of the connect descriptors in a tnsnames.ora file. The SID defaults to the database name.

When user process is terminated abruptly due to power cut or any other factors then oracle database instance goes down then PMON mandatory background process is activated i.e it will then writes the commited data into respective datafiles which we can call this process as rollforward. if the data is not commited then by the action of rollbackword it erases the data from datafiles.

View is also a data base object which represent the subset of data from one or more tables.. when u select a view it internally refers the table on which it's created ,,when u drop a table the index on that table will be dropped ,, but the view can't dropped .......but if u want to drop view u can drop itdrop view ..;ok friends byeee

Table is data base object which stores the data in the logical representatiom in form of rows n coluns which is existing in two dimension form... and is stored in the databse.

Yes objects of the same schema can reside in different tablepaces. Becuase when we create any schema (user) we can assign many tablespaces to one shcema so objects (eg. tables) will create in different tablespaces.

Data Block:At the finest level of granularity, Oracle stores data in data blocks (also called logical blocks, Oracle blocks, or pages). One data block corresponds to a specific number of bytes of physical database space on disk.

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';

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.

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.

Please share your answers.