1. Explain what is the function of Redo Log?

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.

2. What is a Redo Log in Oracle Architecture?

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.

3. Explain what is remote login user and local user in database? And sid is used when user want to login as local user and Glodal database name is used when user wants to login as remote user..
What does it mean?

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.

4. How to identify database size when you create a database first time?

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;

5. Explain what are the components of Logical database structure of ORACLE database?

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.

6. Tell me how to encrypt and decrypt oracle procedure?

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.

7. What is Database Link in Oracle?

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

8. Tell me what are the Large object types supported by Oracle?

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

9. Tell me the relationship among Database, Tablespace and Data file?

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

10. Explain can a Tablespace hold objects from different Schemes?

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.

Download Interview PDF