13 Point comparison between SQL and PLSQL

SQL and PL/SQL both are the integrated part of DATA BASE MANAGEMENT SYSTEM. SQL is basic while PL/SQL is a procedural language which uses SQL to perform multiple tasks on database transactions and manipulations. We can embed SQL in a PL/SQL program, but we cannot embed PL/SQL within a SQL statement. Here are some noteworthy differences between SQL and PL/SQL. 

1. Procedural Capabilities: SQL does not have any procedural capabilities while PL/SQL supports it. It means that PL/SQL provides conditional, iterative and sequential control structures and many more programming facilities. SQL is a data oriented language for selecting and manipulating sets of data while PL/SQL is a procedural language to create applications. SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tells the database how to do things (procedural).

2. Flexibility: PL/SQL is much more flexible than SQL as we can program any thing as we desire while in case of SQL you have to write just one statement to retrieve the results.

3. Server Performance: PL/SQL improves server performance by reducing the number of calls from application to oracle server. The application pass block of SQL statements to oracle server at one time instead of passing each statement individually. This reduces the network traffic between application and oracle server.

4. Error Detection and handling: PL/SQL provides the facility of error detection and handling. It enables the user to define exceptions on their won which is very poor in SQL.

5. Modular Programming: PL/SQL is modular programming because it allows you to divide your application into managable well defined logic modules such as procedures and functions.

6. Reduced Recompilation: PL/SQL reduces recompilation work as the combination of SQL commands can be executed at the same time which is not possible in SQL where you have to execute only one statement at a time.

7. Reduced I/O: The input output operations occur very less in the case of PL/SQL as the set of SQL commands can be handled at a single time while you have to execute all the statement individually in the case of SQL.

8. Platform Independence and Portability: The code of PL/SQL can be used on any platform which runs oracle while you can not do the same with SQL commands. You have to explicitly write all the SQL commands when you shift from one platform to another. While in case of PL/SQL, if you have made a program, you can run it on any platform.

9. Dynamic SQL: PL/SQL supports dynamic SQL which makes your application more flexible and versatile.

10. Security: By using PL/SQL you can provide security to the sensitive data by moving the code from client to server, you can protect data from tampering, hide the internal details and can restrict who has access to this code.

11. Traffic Congestion: PL/SQL uses block of SQL commands and are passed at one time not frequently as in the case of SQL, so the traffic to the server is considerably reduced. 

12. Transaction Performance: PL/SQL also improves Transaction performance as the many calculations can be performed very efficiently and quickly without calling the oracle engine. 

13. Reusability: PL/SQL code once created can be used any time anywhere but this is not possible for SQL Commands. So the same code can be used by many applications and you have no need of creating the same functions or procedures again and again.

Read More

Relation between Tablespace, Datafile and Control File

Databases, tablespaces, and datafiles are closely related, but they have important differences:

An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data.

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

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

Tablespace:

A database is divided into one or more logical storage units called tablespaces. Tablespaces are divided into logical units of storage called segments, which are further divided into extents. Extents are a collection of contiguous blocks.

Default Tablespaces: System, SysAux, Undo and Temporary

Other Tablespaces: Bigfile, Read-only, Temporary Tablespaces for Sort Operation

1. Tablespaces can be made online and offline.
2. Tablespaces can be transported from one database to another.

Datafiles:

When a datafile is first created, the allocated disk space is formatted but does not contain any user data. However, Oracle reserves the space to hold the data for future segments of the associated tablespace—it is used exclusively by Oracle. As the data grows in a tablespace, Oracle uses the free space in the associated datafiles to allocate extents for the segment.

Control Files:

The database control file is a small binary file necessary for the database to start and operate successfully. A control file is updated continuously by Oracle during database use, so it must be available for writing whenever the database is open. If for some reason the control file is not accessible, then the database cannot function properly.

Each control file is associated with only one Oracle database.

Read More

6 Advantages of using stored procedures in your application

Applications that use stored procedures have the following advantages:

1. Stored Procedures are Precompiled

Once created, these can be used again and again  without compilation.

2. Reduced network usage between clients and servers

A client application passes control to a stored procedure on the database server. The stored procedure performs intermediate processing on the database server, without transmitting unnecessary data across the network. Only the records that are actually required by the client application are transmitted. Using a stored procedure can result in reduced network usage and better overall performance.

Applications that execute SQL statements one at a time typically cross the network twice for each SQL statement. A stored procedure can group SQL statements together, making it necessary to only cross the network twice for each group of SQL statements. The more SQL statements that you group together in a stored procedure, the more you reduce network usage and the time that database locks are held. Reducing network usage and the length of database locks improves overall network performance and reduces lock contention problems.

Applications that process large amounts of SQL-generated data, but present only a subset of the data to the user, can generate excessive network usage because all of the data is returned to the client before final processing. A stored procedure can do the processing on the server, and transmit only the required data to the client, which reduces network usage.

3. Enhanced hardware and software capabilities

Applications that use stored procedures have access to increased memory and disk space on the server computer. These applications also have access to software that is installed only on the database server. You can distribute the executable business logic across machines that have sufficient memory and processors.

4. Improved security

By including database privileges with stored procedures that use static SQL, the database administrator (DBA) can improve security. The DBA or developer who builds the stored procedure must have the database privileges that the stored procedure requires. Users of the client applications that call the stored procedure do not need such privileges. This can reduce the number of users who require privileges.

5. Reduced development cost and increased reliability

In a database application environment, many tasks are repeated. Repeated tasks might include returning a fixed set of data, or performing the same set of multiple requests to a database. By reusing one common procedure, a stored procedure can provide a highly efficient way to address these recurrent situations.

6. Centralized security, administration, and maintenance for common routines

By managing shared logic in one place at the server, you can simplify security, administration, and maintenance . Client applications can call stored procedures that run SQL queries with little or no additional processing.

Read More

DECODE Function vs CASE Statement in Oracle

Decode Function and Case Statement in Oracle: Decode Function and Case Statement are used to transform data values at retrieval time. DECODE and CASE are both analogous to the "IF THEN ELSE" conditional statement.

History of DECODE and CASE:

Before version 8.1, the DECODE was the only thing providing IF-THEN-ELSE functionality in Oracle SQL. Because DECODE can only compare discrete values (not ranges), continuous data had to be contorted into discreet values using functions like FLOOR and SIGN. In version 8.1, Oracle introduced the searched CASE statement, which allowed the use of operators like > and BETWEEN (eliminating most of the contortions) and allowing different values to be compared in different branches of the statement (eliminating most nesting). In version 9.0, Oracle introduced the simple CASE statement, that reduces some of the verbosity of the CASE statement, but reduces its power to that of DECODE.

Decode Function and Case Statement Example: 

Example with DECODE function

Say we have a column named REGION, with values of N, S, W and E. When we run SQL queries, we want to transform these values into North, South, East and West. Here is how we do this with the decode function:

select
decode (
region,
‘N’,’North’,
‘S’,’South’,
‘E’,’East’,
‘W’,’West’,
‘UNKNOWN’
)
from
customer;

Note that Oracle decode starts by specifying the column name, followed by set of matched-pairs of transformation values. At the end of the decode statement we find a default value. The default value tells decode what to display if a column values is not in the paired list.

Example with CASE statement

select
case
region
when ‘N’ then ’North’
when ‘S’ then ’South’
when ‘E’ then ’East’,
when ‘W’ then ’West’
else ‘UNKNOWN’
end
from
customer;

Difference between DECODE and CASE:

Everything DECODE can do, CASE can. There is a lot more that you can do with CASE, though, which DECODE cannot. Differences between them are listed below:

1. DECODE can work with only scaler values but CASE can work with logical oprators, predicates and searchable subqueries.
2. CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.CASE can be used as parameter of a function/procedure.
3. CASE expects datatype consistency, DECODE does not.
4. CASE complies with ANSI SQL. DECODE is proprietary to Oracle.
5. CASE executes faster in the optimizer than does DECODE.
6. CASE is a statement while DECODE is a fucntion.

Read More

Oracle Streams: An Overview

Oracle Streams enables information sharing. Each unit of shared information is called a message. The stream can propagate information within a database or from one database to another. Oracle Streams can be set up in homogeneous (all Oracle databases) or heterogeneous (non-Oracle and Oracle databases) environments.

Oracle Streams Information Flow

The database changes (DDL and DML) are captured at the source; those are then staged and propagated to one or more destination databases to be applied there.

Capturing ---> Staging ----> Propagating ---> Consuming

Capturing a Message:

Oracle Streams provides two ways to capture database changes implicitly: capture processes and synchronous captures.

Capture Process (Implicit Capture)

A capture process can capture DML changes made to tables, schemas, or an entire database, as well as DDL changes. Database changes are recorded in the redo log for the database. A capture process captures changes from the redo log and formats each captured change into a message called a logical change record (LCR - A message with a specific format that describes a database change). The messages captured by a capture process are called captured LCRs. A capture process can capture changes locally at the source database, or it can capture changes remotely at a downstream database. Capture Processes always caputre change from REDO LOGS.

Synchronous Process (Implicit Capture)

A synchronous capture can capture DML changes made to tables. Rules determine which changes are captured by a capture process or synchronous capture. A synchronous capture uses an internal mechanism to capture changes and format each captured change into an LCR. The messages captured by a synchronous capture are called persistent LCRs. A synchronous capture can only capture changes locally at the source database.

Explicit Capture:

Users and applications can also enqueue messages manually. These messages can be LCRs, or they can be messages of a user-defined type called user messages. When users and applications enqueue messages manually, it is referred to as explicit capture.

Staging a Message

Messages are stored (or staged) in a queue. These messages can be logical change records (LCRs) or user messages. Capture processes and synchronous captures enqueue messages into an ANYDATA queue, which can stage messages of different types. Users and applications can enqueue messages into an ANYDATA queue or into a TYPED queue. A TYPED queue can stage messages of one specific type only.

Propagating a Message

Oracle Streams propagations can propagate messages from one queue to another. These queues can be in the same database or in different databases.

Oracle Streams enables you to configure an environment in which changes are shared through directed networks. In a directed network, propagated messages pass through one or more intermediate databases before arriving at a destination database where they are consumed. The messages might or might not be consumed at an intermediate database in addition to the destination database.

Consuming a Message

A message is consumed when it is dequeued from a queue. An apply process can dequeue messages implicitly. A user, application, or messaging client can dequeue messages explicitly. The database where messages are consumed is called the destination database. In some configurations, the source database and the destination database can be the same.

Message Types:

Raw Bytes
Oracle Objects
XML

Read More

Network Configuration Files in Oracle

Oracle uses three files (tnsnames.ora, listener.ora and sqlnet.ora) for network configuration. These are explained below:

1. tnsnames.ora file in oracle

TNS stands for Transparent Network Substrate. The "tnsnames.ora" file contains client side network configuration parameters. tnsnames.ora files contains the information which is used by the system to connect to oracle database.

Location of tnsnames.ora in oracle: By default, tnsnames.ora is located in the $ORACLE_HOME/network/admin directory on UNIX operating systems and in the ORACLE_HOME\network\admin directory on Windows operating systems.

Format / Syntax of tnsnames.ora:

net_service_name=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = yourHostIPAddress)(PORT = portNumber))
    )
    (CONNECT_DATA =
      (SID = databaseName)
    )
  )

Explanation: Syntax contains:

PROTOCOL: Here TCP is used.
HOST IP ADDRESS: Where oracle is installed
PORTNO: Port number of server where oracle is installed
SID: SID stands for System Identifier. SID is the name of database.

Failover and Load-Balancing in tnsnames.ora

Consider the following example

net_service_name=
 (DESCRIPTION=
  (ADDRESS_LIST=
   (LOAD_BALANCE=on)
   (FAILOVER=off)
   (ADDRESS=(PROTOCOL = TCP)(HOST = yourHostIPAddress1)(PORT = portNumber1))
   (ADDRESS=(PROTOCOL = TCP)(HOST = yourHostIPAddress2)(PORT = portNumber2)))
  (ADDRESS_LIST=
   (LOAD_BALANCE=off)
   (FAILOVER=on)
   (ADDRESS=(PROTOCOL = TCP)(HOST = yourHostIPAddress3)(PORT = portNumber3))
   (ADDRESS=(PROTOCOL = TCP)(HOST = yourHostIPAddress4)(PORT = portNumber4)))
  (CONNECT_DATA=
   (SID = databaseName)))

In the above example, we specify the list of addresses which will take over another address in case of failure or overload.

Note: Typically you could have two tnsnames.ora files in the system, one that is set for the entire system and is called the system tnsnames.ora file, and a second file that is used by each user locally so that he can override the definitions dictated by the system tnsnames.ora file.

tnsping: tnsping is used to ping the tnsnames.ora file. If there is no error in tsnnames.ora, it will ping it otherwise error will be displayed.

Format of tnsping command: tsnping hostIPAddress

2. listener.ora file in oracle

The listerner.ora file contains server side network configuration parameters. It is found in the $ORACLE_HOME/network/admin" directory on the server.

Example:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = port))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = databaseName.WORLD)
      (ORACLE_HOME = /u01/app/oracle/product/9.2.0)
      (SID_NAME = databaseName)
    )
  )

After the "listener.ora" file is amended the listener should be restarted or reloaded to allow the new configuation to take effect.

C:> lsnrctl help
C:> lsnrctl status
C:> lsnrctl stop
C:> lsnrctl start
C:> lsnrctl reload

3. sqlnet.ora file in oracle: The "sqlnet.ora" file contains client side network configuration parameters. It is present in the "$ORACLE_HOME/network/admin" It contains following settings:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

Above setting is necessary on Windows if OS authentication is required.

NAMES.DIRECTORY_PATH= (TNSNAMES, ,ONAMES, HOSTNAME)

A list of naming adaptors to be used when resolving a name. These will be used in the order listed. TNSNAMES = tnsnames.ora file, ONAMES = Oracle Names, HOSTNAME = use the hostname

Read More

SQL Replay: A new feature of Oracle 11g

SQL Replay is a new feature introduced in Oracle 11g. SQL Replay features is used to capture the commands executed in a database and then replay them elsewhere. You can use the capture and replay features to perform diagnostic operations or to test applications under production conditions.

Steps to create SQL Replay:

Step 1: Creating a Workload Directory

The log file for the SQL workload will be written to a directory on the source server. To specify the physical location for that directory, you must create a directory object within the database.

Use the create directory command:

create directory workload_dir as '/u01/workload'

Step 2: Starting the Capture of Source Database

To start a capture, execute the START_CAPTURE procedure of  DBMS_WORKLOAD_CAPTURE package.

BEGIN
  DBMS_WORKLOAD_CAPTURE.START_CAPTURE
        (name => 'practice_capture',
          dir => 'workload_dir',
     default_action => 'EXCLUDE');
END;
/

Note:
1. The name parameter is the name you assign to the workload capture.
2. dir is the name of the directory object you created to store the workload-capture files.

Stopping the Capture:

If you have not specified a duration, you must stop the capture manually. To stop the capture, execute the FINISH_CAPTURE procedure, as shown here:

BEGIN
  DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE ();
END;
/

Step 3: Processing the Workload Logs:

Workload Logs are transformed into Replay Files and metadata is created. It can be done by PROCESS_CAPTURE procedure of DBMS_WORKLOAD_REPLAY package.

BEGIN
  DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE
   (capture_dir => 'workload_dir');
END;
/

The capture_dir variable value must be the name of a directory object within the database that points to the physical directory where the workload logs are stored.

Step 4: Replaying the Workload on Target Database

To start the replay, use the START_REPLAY procedure of DBMS_WORKLOAD_REPLAY package, as shown in the following listing:

BEGIN
  DBMS_WORKLOAD_REPLAY.START_REPLAY ();
END;
/

To stop the replay, use the CANCEL_REPLAY procedure, as shown in the following listing:

BEGIN
  DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY ();
END;
/

Precautions while Replaying SQL

1. Before starting the capture on the source database, you should make sure there are no active transactions within the database. Best practice is to restart the database.

2. For best results during the replay, you should alter / reset the system time on the target system.

3. The target database should run the same version of Oracle as the source system.

4. Target database should be separate from the production database and should be as isolated as possible from the external structures accessed by the production database.
Note: Replay is best suited for uses in which the target system is a testing environment, rather than generating the replay in a testing environment and then executing it in a production database

Read More

Partitioned Tables: Types and Advantages

As the number of rows in your tables grows, the management and performance impacts will increase. Backups will take longer, recoveries will take longer, and queries that span an entire table will take longer. You can mitigate the administrative and performance issues for large tables by separating the rows of a single table into multiple parts.

Advantages of Partitioned Tables

1. The performance of queries against the tables may improve because Oracle may have to search only one partition (one part of the table) instead of the entire table to resolve a query.

2. The table may be easier to manage. Because the partitioned table's data is stored in multiple parts, it may be easier to load and delete data in the partitions than in the large table.

3. Backup and recovery operations may perform better. Because the partitions are smaller than the partitioned table, you may have more options for backing up and recovering the partitions than you would have for a single large table.

Types of Partition

1. Range Partition: Divides the table according to the specified range.

Example: Create a Table EMP

create table EMP (EmpID  VARCHAR2(32) primary key, EmpName  VARCHAR2(100));

Lets make partition of this table:

create table EMP (EmpID  VARCHAR2(32) primary key, EmpName  VARCHAR2(100))

partition by range (EmpName)

(partition PART1 values less than ('K') tablespace PART1_TS,
partition PART2 values less than (MAXVALUE) tablespace PART2_TS);

Explanation: The EMP table will be partitioned based on the values in the EmpName column:

partition by range (EmpName)

For any EmpName values less than ‘K’, the records will be stored in the partition named PART1. The PART1 partition will be stored in the PART1_TS tablespace. Any other EmpName will be stored in the PART2 partition.

Note that in the PART2 partition definition, the range clause is

partition PART2 values less than (MAXVALUE)

You do not need to specify a maximum value for the last partition; the maxvalue keyword tells Oracle to use the partition to store any data that could not be stored in the earlier partitions.

2. Hash Partition: A hash partition determines the physical placement of data by performing a hash function on the values of the partition key. In range partitioning, consecutive values of the partition key are usually stored in the same partition. In hash partitioning, consecutive values of the partition key are not generally stored in the same partition. Hash partitioning distributes a set of records over a greater set of partitions than range partitioning does, potentially decreasing the likelihood for I/O contention.

3. List Partition: In list partitioning, you tell Oracle all the possible values and designate the partitions into which the corresponding rows should be inserted.

4. SubPartitions: You can create subpartitions—that is, partitions of partitions. You can use subpartitions to combine all types of partitions: range partitions, list partitions, and hash partitions. For example, you can use hash partitions in combination with range partitions, creating hash partitions of the range partitions. For very large tables, this composite partitioning may be an effective way of separating the data into manageable and tunable divisions

Read More

Cautions while dropping a tablespace

DROP TABLESPACE drops the tablespace from database. But, there are few things which you should take care while firing this statement.

1. DROP TABLESPACE myTablespace;

- drops the tablespace

Cautions:

A) You cannot drop the SYSTEM tablespace.

B) You can drop the SYSAUX tablespace only if you have the SYSDBA system privilege and you have started the database in MIGRATE mode.

C) You cannot use this statement to drop a tablespace group. However, if tablespace is the only tablespace in a tablespace group, then Oracle Database removes the tablespace group from the data dictionary as well.

D) When you drop a tablespace, Oracle Database does not place it in the recycle bin means your data is not recoverable. Therefore, make sure that all data contained in a tablespace to be dropped will not be required in the future.

E) Also, immediately before and after dropping a tablespace from a database, back up the database completely. This is strongly recommended so that you can recover the database if you mistakenly drop a tablespace, or if the database experiences a problem in the future after the tablespace has been dropped.

2. DROP TABLESPACE myTablespace

    INCLUDING CONTENTS

    CASCADE CONSTRAINTS;

-drops the myTablespace tablespace and drops all referential integrity constraints that refer to primary and unique keys inside myTablespace

Cautions:

1. For partitioned tables, DROP TABLESPACE will fail even if you specify INCLUDING CONTENTS, if the tablespace contains some, but not all. If all the partitions of a partitioned table reside in tablespace, then DROP TABLESPACE ... INCLUDING CONTENTS will drop tablespace.

3. DROP TABLESPACE myTablespace

   INCLUDING CONTENTS AND DATAFILES;

-drops the myTablespace tablespace and deletes all associated operating system datafiles.

Suggestion: You can drop a tablespace regardless of whether it is online or offline. But it is suggested that you take the tablespace offline before dropping it to ensure that no SQL statements in currently running transactions access any of the objects in the tablespace.

Read More

Database FLASHBACK mode: Overview

Database FLASHBACK mode: Overview

Oracle flashback database is an extension of the "rollback" functionality, allowing the DBA to flashback a table to a specific date in history.  With Oracle flashback, the length of the flashback recovery is determined by the storage dedicated to Oracle UNDO and the settings for flashback database parameters.

Flashback Database Command

Oracle flashback database is implemented via the flashback database command.  Flashback database allows you to quickly bring your database to a prior point in time by undoing all of the changes that have taken place since that time. The Oracle database flashback process is fast, because you do not need to restore the backups.

How to check whether FLASHBACK is ON/OFF on your database?

SELECT FLASHBACK_ON FROM V$DATABASE;

How to ON flashback of your database?

ALTER DATABASE FLASHBACK ON;

How to OFF flashback of your database?

ALTER DATABASE FLASHBACK OFF;

Example

Enable database to ON the flashback mode and open the database with the following statements:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

With your database open for at least a day, you can flash back the database one day with the following statements:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1;

Turning ON/OFF Flashback on a particular Namespace:

ALTER TABLESPACE <tablespace_name> FLASHBACK ON;
ALTER TABLESPACE <tablespace_name> FLASHBACK OFF;

Read More

Database ARCHIVELOG mode: Overview

Database ARCHIVELOG mode: Overview

An Oracle database could be in ARCHIVELOG or NOARCHIVELOG mode. When the database is in ARCHIVELOG mode, backups are taken of the redo log files when the redo logs are full or switched. That assure that all the database operations are kept in 2 places (in data files and in archive log files). Having the database in ARCHIVELOG mode assure us that the data will not be lost even if the original data (from datafiles) will become inaccessible or will be deleted accidentally.

ARCH Process

In ARCHIVELOG mode, the database will make copies of all online redo logs after they are filled. These copies are called archived redo logs. The archived redo logs are created via the ARCH process. The ARCH process copies the archived redo log files to one or more archive log destination directories.

Configuring the database for ARCHIVELOG Mode

The use of ARCHIVELOG mode requires some configuration of the database. First you must put the database in ARCHIVELOG mode and you must also configure the ARCH process, and prepare the archived redo log destination directories. In earlier versions of Oracle you had to enable a special Oracle process called ARCH by setting another parameter. Oracle Database 10g does not require this. When the database is in ARCHIVELOG mode, it will start the ARCH process automatically.

To see if the database is in archivelog mode or not?

SELECT LOG_MODE FROM V$DATABASE;

Putting the database in ARCHIVELOG Mode

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

ARCHIVE LOG LIST: displays log mode, automatich archival, archive destination.

SQL> archive log list
Database log mode               Archive Mode
Automatic archival                  Enabled
Archive destination                USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     83
Next log sequence to archive  84
Current log sequence              84

How to OFF the archive mode?

ALTER DATABASE ARCHIVELOG OFF;

Read More

Business Intelligence (BI): Data Warehouse, Data Mart, Data Dictionary and Data Mining

Business intelligence aims to support better business decision-making. Business Intelligence (BI) mainly refers to computer-based techniques used in identifying, extracting and analyzing business data. BI technologies provide historical, current and predictive views of business operations. Common functions of business intelligence technologies are data warehousing, data mart, data mining, data dictionary, reporting, online analytical processing, analytics, process mining, complex event processing, business performance management, benchmarking, text mining and predictive analytics. Some of them are discussed below:

Data Warehouse

Data Warehouse (DW) is a database used for reporting and analysis.This definition of the data warehouse focuses on data storage. The main source of the data is cleaned, transformed, catalogued and made available for use by managers and other business professionals for data mining, online analytical processing, market research and decision support. However, the means to retrieve and analyze data, to extract, transform and load data, and to manage the data dictionary are also considered essential components of a data warehousing system. Many references to data warehousing use this broader context. Thus, an expanded definition for data warehousing includes business intelligence tools, tools to extract, transform and load data into the repository, and tools to manage and retrieve metadata.

The typical data warehouse uses staging, integration, and access layers to house its key functions. The staging layer stores raw data, the integration layer integrates the data and moves it into hierarchal groups, and the access layer helps users retrieve data.

Storing Data in Datawarehouse

There are 2 approaches to store data in datawarehouse:

1. Dimensional Approach: It uses Dimensional Model / Star Schema
2. Normalized Approach: It uses 3NF Model

Benefits of a data warehouse

A data warehouse maintains a copy of information from the source transaction systems. This architectural complexity provides the opportunity to:

1. Maintain data history, even if the source transaction systems do not.
2. Integrate data from multiple source systems, enabling a central view across the enterprise. This benefit is always valuable, but particularly so when the organization has grown by merger.
3. Improve data quality, by providing consistent codes and descriptions, flagging or even fixing bad data.
4. Present the organization's information consistently.
5. Provide a single common data model for all data of interest regardless of the data's source.
6. Restructure the data so that it makes sense to the business users.
7. Restructure the data so that it delivers excellent query performance, even for complex analytic queries, without impacting the operational systems.
8. Add value to operational business applications, notably customer relationship management (CRM) systems.

Data Marts:

Data warehouses can be subdivided into data marts.The data mart is a subset of the data warehouse which is usually oriented to a specific business line or team. facts and dimensions, then they will be related. A data mart is the access layer of the data warehouse environment that is used to get data out to the users

Reasons for creating a data mart

1. Easy access to frequently needed data
2. Creates collective view by a group of users
3. Improves end-user response time
4. Ease of creation
5. Lower cost than implementing a full data warehouse
6. Potential users are more clearly defined than in a full data warehouse

Data Dictionary:

A data dictionary is data about data.It is also called  metadata repository. It is defined as centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format.

Data Mining:

Data Mining process is to extract knowledge from a data set in a human-understandable structure.

Read More