Interview Questions

Home

Oracle

Submitted by arunraj on Tue, 01/25/2011 - 21:53

Oracle TableSpace

Tablespace
A Tablespace in Oracle represents a location where the actual data related to tables, indexes will be stored. A database consists of one or more tablespace (tablespace made up of one or more datafiles).

Types of Tablespace
The different types of tablespace are as follows,
1. Permanent tablespace
- It contains the persistent schema objects which are stored in the data files.

2. Undo tablespace

»
Submitted by arunraj on Fri, 12/17/2010 - 19:56

Materialized Views

In this article let’s discuss about using Materialized views in Oracle.

What is Materialized View?

Materialized view is similar to a view but the actual table data will exist in the Database. These views will be refreshed automatically when the master tables are updated.

CREATE MATERIALIZED VIEW mv_employee
REFRESH COMPLETE START WITH SYSDATE
AS SELECT EMP_ID,EMP_NAME,DEPT_ID,MAX(SALARY) FROM EMPLOYEES@orcl GROUP BY EMP_ID,EMP_NAME,DEPT_ID;

»
Submitted by arunraj on Fri, 08/13/2010 - 15:40

Oracle PL/SQL to delete child records

In this article lets discuss about how to delete a record(or row) from a table, if it has a tree of child tables (like child table has one or more child tables).

»
Submitted by arunraj on Fri, 09/17/2010 - 23:49

Load Data from Excel to Oracle database

In this article lets discuss about how to load data from an excel file in to oracle database table.

In many application projects you may be faced with a requirement to export data from a database table to an excel sheet or load data from an excel or csv file in to a database table. The former can be done using the help of any oracle client like TOAD. The latter can be done using an utility available in oracle called SQL Loader.

Let go through an example of how to import data from excel or csv file in to oracle db.

»
Submitted by arunraj on Sun, 07/18/2010 - 14:22

Oracle Create DB Link

In this section lets see about how to access DB objects from one Database to another database.

Assume that you have two databases testlocalServ and testremoteServ. Log-in in to the database testlocalServ. If you want to access the tables from testremoteServ database, first of all you need to create a DB link to that remote database testremoteServ. OK, How to create a DB link? its very simple using the following DDL statement.

CREATE PUBLIC DATABASE LINK remote_conn CONNECT TO remote_user IDENTIFIED BY remote_pwd USING testremoteServ ;  

»
Submitted by arunraj on Fri, 07/16/2010 - 22:26

Oracle Table Partition

Consider a scenario where you have huge number of records(say 1 million) in a database table and you are required to select 100,000 records from that table based on a condition given in where clause. In most cases it will take more time to retrieve the records from that huge table as the DBMS should select a subset from the huge super set. One simple solution is to use SQL query hints, forcing indexes etc in the select query. But the query running time changed based on certain DB parameters. Another sophisticated solution will be to partition DB table.

»
Submitted by arunraj on Tue, 07/13/2010 - 13:58

ORACLE Query to find child tables

In this section we will see about how to find the child tables for a given parent table.

In many projects where we deal with databases, we would like to find the list of child tables for a parent table. A parent table will have a primary key and one or more child tables will be referencing the parent table using a foreign key. But how will you find the list of child tables referencing the parent table.

»
Submitted by arunraj on Mon, 10/18/2010 - 13:12

SQL Query Optimization

Often you may be faced with a requirement to optimize a SQL query (increase query running time) to be executed in Oracle database. Query Optimization in general is a huge topic to discuss. Let me provide some basic steps to be followed while optimizing a SQL query.

1. If the query has only one table and certain where conditions, then try to index the columns given in the where condition. By default primary key columns are indexed. Then try to reorder the set of where conditions based on number of rows for the column values in descending order.

»
Submitted by arunraj on Fri, 07/02/2010 - 18:12

Oracle - DB Startup Failure unavailability of Datafile

Consider a scenario where you have accidentally deleted a DATA-FILE in oracle when the instance is running. It will throw error when you restart the DB as follows.

1. Log-in to oracle DB server

$ sqlplus system/manager as sysdba

SQL> startup

ORACLE instance started.

Total System Global Area 1174405120 bytes
Fixed Size                  2020288 bytes
Variable Size             285215808 bytes
Database Buffers          872415232 bytes
Redo Buffers               14753792 bytes
Database mounted.

»
Submitted by arunraj on Fri, 07/02/2010 - 18:10

Oracle Create a New DB Instance

Creating a New DB Instance

Steps for Creating New DB instance:

1) Log-in to the DB server as oracle user.

2) Move to the location $ORACLE_HOME/bin

3) Execute the following command (oradb1 is the new db name)

dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname oradb1 -sid oradb1 -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration NONE

After running the above command , the following output will be generated

Copying database files
1% complete
3% complete

»