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

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

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.

One can debug PL/SQL program by printing the output using DBMS_OUTPUT package. Put_line can be used to display a line as shown below:

BEGIN

        dbms_output.put_line(‘Sample line');

END;

Oracle Spatial, often referred to as spatial, provides a SQL schema and functions that facilitate the storage, retrieval, update, and query of collections of spatial features in an Oracle database. Oracle Spatial is designed to make spatial data management easier and more natural to users of location-enabled applications and geographic information system (GIS) applications. Once spatial data is stored in an Oracle database, it can be easily manipulated, retrieved, and related to all other data stored in the database.

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

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