Oracle External Table

Suppose we have a text file named “emp.data”. This text files contains employees data in following format,

1, Michle , clerk
2, king , manager
3, joe , clerk

What if we want to use this data in SQL query? Remember we do not want to import this data in oracle database but we want to reference this data from its physical location. To achieve this oracle database has provided a feature called External Tables. Using this feature, we can register “emp.dat” file in oracle database as external table. Continue reading “Oracle External Table”

Configuration of Oracle Shared Server

This article explains configuration of oracle Database 10g as shared server. Before reading this article you must have concept of Oracle shared server and dedicated server architecture. Visit following link to revive shared server and dedicated server architecture.

http://exploreoracle.com/2009/07/17/oracle-shared-server-and-oracle-dedicated-server-architecture/

Configuration of shared server involves only few steps. Oracle database 10g has pre-configured setting to enable shared server. Shared server can be configured by setting values for few initialization parameters at run time. These parameters can also be initialized in initialization parameter file. Following section discusses these parameters in detail. Continue reading “Configuration of Oracle Shared Server”

Oracle Shared Server and Oracle Dedicated Server Architecture

In order to communicate with oracle database, oracle users need a program such as SQL *Plus which can issue SQL statements and few processes which can execute these SQL statements. These processes are divided into User Process, Server Process and Background Processes. User process runs user application like SQL *Plus. Server process manages oracle user process’s requests. Server process executes SQL statements and returns result to user process. Background processes are the core of oracle database which handle over all database operations. Continue reading “Oracle Shared Server and Oracle Dedicated Server Architecture”

Oracle Dictionary Cache | Oracle Data Dictionary

Oracle server requires some metadata information to process SQL statements. This information is necessary to maintain data integrity, data consistency and data security. When a user submits a query, oracle needs detailed information about objects involved in the query, for example, whether the accessed objects exist and the user has required privileges. Continue reading “Oracle Dictionary Cache | Oracle Data Dictionary”

Oracle Library Cache | Hard Parsing Vs Soft Parsing

Library cache is very important part of Oracle Shared pool. Shared Pool controls execution of SQL statements. Shared pool is divided into Data dictionary Cache and Library Cache. Main component of Library cache include Shared SQL Area and Private SQL Area (in case of shared server configuration).In Dedicated server configuration Private SQL area is created in PGA of server process. In oracle database each statement is associated with a shared area and a private area. Both are discussed in detail below.

Continue reading “Oracle Library Cache | Hard Parsing Vs Soft Parsing”

Redo Log Buffer & Log writer process (LGWR)

Oracle database keeps record of changes made to data. Every time user performs a DML, DDL or DCL operation, its redo entries are also created. These redo entries contain commands to rebuild or redo the changes. These entries are stored in Redo Log buffer. Log writer process (LGWR) writes these redo entries to redo log files. Redo log buffer works in circular fashion. It means that it overwrites old entries. Continue reading “Redo Log Buffer & Log writer process (LGWR)”

KEEP Buffer Pool and Recycle Buffer Pool

Data required by oracle user process is loaded into buffer cache, if it is not already present in cache. Proper memory tuning is required to avoid repeated disk access for the same data. This means that there should be enough space in buffer cache to hold required data for long time. If same data is required in very short intervals then such data should be permanently pinned into memory. Oracle allows us to use multiple buffers. Using multiple buffers we can control that how long objects should be kept in memory.

Continue reading “KEEP Buffer Pool and Recycle Buffer Pool”

Database Buffer Cache

Database Buffer cache is one of the most important components of System Global Area (SGA). Database Buffer Cache is the place where data blocks are copied from datafiles to perform SQL operations. Buffer Cache is shared memory structure and it is concurrently accessed by all server processes.

Working of Database buffer Cache

Buffer Cache is organized into two lists

Write List

Write list contains dirty buffers. These are the data blocks which contain modified data and needed to be written to datafiles.

Continue reading “Database Buffer Cache”

Oracle Database

Oracle Corporation is one of the leading IT companies at the moment. Oracle Corporation offers multiple software solution for almost all business areas. List of Products owned by oracle provide us a glint.
List of oracle products
This website will focus on Oracle Database. Oracle RDBMS is undoubtedly the most popular and widely used database. Oracle Database 10g and Oracle Database 11g are the hottest versions at the moment. exploreoracle.com will make an effort to cover following core and basic topics

  • Database Architecture
  • Backup and Recovery ( OS level and RMAN)
  • Performance and tunning
  • Flash back Features
  • Auditing & Scheduling
  • External tables and SQL Loader

Tasks will be performed using Oracle EM as well as CLI .