Oracle Questions

RDBMS is a database management system based on relational model defined by E.F.Codd. Data is stored in the form of rows and columns. The relations among tables are also stored in the form of the table.

Features:
- Provides data to be stored in tables
- Persists data in the form of rows and columns
- Provides facility primary key, to uniquely identify the rows
- Creates indexes for quicker data retrieval
- Provides a virtual table creation in which sensitive data can be stored and simplified query can be applied.(views)
- Sharing a common column in two or more tables(primary key and foreign key)
- Provides multi user accessibility that can be controlled by individual users

SQL*plus allows SQL and PL/SQL scripts to interactively run in command line.

It allows three kinds of commands to run; SQL, PL/SQL and SQL * Plus commands. It’s most commonly used by DBA’s to interact with the oracle database.

%type is used to declare a field of a table while %rowtype is used to declare a record with the same type as specified in that table, view or cursor.

Example of %type:

DECLARE

         v_EmployeeName emp.ename%TYPE

Example of %rowtype

DECLARE

          v_empployee emp%ROWTYPE;

WATER MARK is a divided segment of used and free blocks. Blocks which are below high WATER MARK i.e. used blocks, have at least once contained some data. This data might have been deleted later. Oracle knows that blocks beyond high WATER MARK don’t have data; it only reads blocks up to the high WATER MARK during a full table scan. 

LINQ stands for " Language Integrated Query " and it is pronounced as "LINK".
LINQ was introduced in with .NET framework 3.5 including VS2008. It enables you to query the data from various data source like SQL, XML document, ADO .NET data source, web services and any other objects such as Collection, Generics etc...

- Clustering means one than one database server configured for the same user connection. When users connect, one of the server’s responds and connects based on availability. The user is completely ignorant of the fact that there are more than one database servers. It demands high cost due to infrastructure needs. Clustering is hence done on server level.

- Mirroring means, one has many configured databases on the same server. Mirrors are basically copies of the original database. Mirroring is hence done on database level. 

Insert image into a table

Create the following table:

      create table pics_table (

      bfile_id number,

      bfile_desc varchar2(30),

      bfile_loc bfile,

      bfile_type varchar2(4))

      TABLESPACE appl_data

      storage (initial 1m next 1m pctincrease 0) 

Insert Query:

      INSERT INTO pics_table

      VALUES(4,'test image',bfilename('GIF_FILES','Test.JPG'),'JPEG'); 

- Debugging is esy.
- Reduces Development Time.
- LINQ offers IntelliSense which means writing more accurate queries easily.