ORACLE SQL Tutorials

Optimal Flexible Architecture (OFA)

noreply@blogger.com (Unknown) — Tue, 23 Dec 2008 06:58:00 +0000

OFA is designed to:
-Organize large amounts of software
-Facilitate routine administrative tasks
-Facilitate switching between multiple Oracle databases
-Manage and administer database growth adequately
-Help eliminate fragmentation of free space

OFA is a method for configuring the Oracle database and other databases. OFA takes advantage of the capabilities of the OS and disk subsystems to create an easy-to-administer configuration that allows maximum flexibility for growing and high-performance databases. The methods described here are the basics of OFA.

OFA is designed to:

Organize large amounts of complicated software and data on the disk to avoid device bottlenecks and poor performance
Facilitate routine administrative tasks, such as software and data backup, which are often vulnerable to data corruption
Facilitate switching between multiple Oracle databases
Adequately manage and administer database growth
Help eliminate fragmentation of free space in the data dictionary, isolate other fragmentation, and minimize resource contention
For details about the goals and implementation of OFA, refer to the Oracle Installation Guide for UNIX Systems.

Checking the System Requirements

noreply@blogger.com (Unknown) — Mon, 22 Dec 2008 06:48:00 +0000

Adequate
temporary space
64-bit versus 32-bit issues
Checks for the correct operating system (OS)
OS patch level
System packages
System and kernel parameters
X Server permissions
Sufficient swapping
Nonempty ORACLE_HOME

The Oracle Database 10g installation automates most of the prerequisite checks:

Adequate temporary space is checked for. It is determined what the minimum temporary space requirements are for installation and configuration, and those requirements are validated during the installation process.
64-bit installations are prevented from being installed into Oracle homes with 32-bit software already installed (and vice versa).
On the Linux platform, RedHat-3.0, 4.0, Asianux 1.0, 2.0, and SUSE Linux E.S. 9.0 are certified.
The installation process checks whether all the required OS patches are installed.
The installation process checks whether all the required system and kernel parameters are set correctly.
The installation process verifies that the DISPLAY environment variable is set and that the user has sufficient permissions to display to the specified DISPLAY.
The installation process verifies that the system has sufficient swapping set.
The installation process verifies that the Oracle home into which the new installation is being performed is either empty or is one of a handful of supported releases on top of which Oracle Database 10g can be installed, and that they are registered in the Oracle inventory.

Installation: System Requirements

noreply@blogger.com (Unknown) — Mon, 22 Dec 2008 06:42:00 +0000

Memory requirements:
1 GB for the instance with Database Control

Disk space requirements:
1.5 GB of swap space
400 MB of disk space in the /tmp directory
Between 1.5 GB and 3.5 GB for the Oracle software
1.2 GB for the preconfigured database (optional)
2.4 GB for the flash recovery area (optional)

Operating system: See documentation.

A standard installation can be completed on a computer with 1 GB of RAM and 1.5 GB of swap space or larger.
Depending on the activity level of the machine on which you are installing the Oracle database software, the standard installation can complete in 20 minutes or less.

Some installation details:

Oracle Database 10g ships only one seed database template. Duplicated files are removed.
Many other products and demonstrations are installable from additional CDs.
The hardware requirements listed in the slide are minimal requirements across all platforms. Your installation may have additional requirements (especially disk space).
Note: An Enterprise Edition installation type that includes a standard seed database is referred to as a “standard installation.”

Tools Used to Administer an Oracle Database

noreply@blogger.com (Unknown) — Fri, 05 Dec 2008 06:00:00 +0000

Oracle Universal Installer
Database Configuration Assistant
Database Upgrade Assistant
Oracle Net Manager
Oracle Enterprise Manager
SQL*Plus and iSQL*Plus
Recovery Manager
Oracle Secure Backup
Data Pump
SQL*Loader
Command-line tools

You can use the following tools for installation and upgrade:

Oracle Universal Installer (OUI): Oracle Universal Installer installs your Oracle software and options. It can automatically launch the Database Configuration Assistant (DBCA) to create a database.

Database Configuration Assistant (DBCA): It creates a database from Oracle-supplied templates. It enables you to copy a preconfigured seed database. Alternatively, you can create your own database and templates.

Database Upgrade Assistant (DBUA): This tool guides you through the upgrade of your existing database to a new Oracle release.

Oracle Net Manager: This is used to configure network connectivity for your Oracle databases and applications.

The following tools are used to manage your Oracle instance and database:

Oracle Enterprise Manager (EM): EM combines a graphical console, agents, common services, and tools to provide an integrated and comprehensive system management platform for managing Oracle products. After you have installed the Oracle software, created or upgraded a database, and configured the network, you can use Enterprise Manager as the single interface for managing your database. In addition to providing a Web-based user interface for executing SQL commands, it seamlessly interfaces with other Oracle components that are used to administer your database (for example, Recovery Manager and Scheduler).

The three main Enterprise Manager tools that are used to administer an Oracle database are:

Enterprise Manager Database Console: Used to administer one database
Enterprise Manager Grid Control: Used to administer many databases at the same time
Enterprise Manager Java Console: Used to access tools that are not Web enabled

SQL*Plus: SQL*Plus is the standard command-line interface for managing your database.
iSQL*Plus: iSQL*Plus is a browser-based interface to an Oracle database.

Recovery Manager (RMAN): RMAN is an Oracle tool that provides a complete solution for the backup, restoration, and recovery needs of the entire database or of specific database files.

Oracle Secure Backup provides tape backup management for the Oracle ecosystem, which includes:

Oracle database protection to tape through integration with Recovery Manager
Seamless support of Oracle Real Application Clusters (RAC)
Central administration of distributed clients and media servers including Oracle Application Servers, Oracle Collaboration Suites, Oracle home, and binaries

Data Pump: Data Pump enables the high-speed transfer of data from one database to another. For example, you may want to export a table and import it into another database.

SQL*Loader: The SQL*Loader utility enables the loading of data from an external file into an Oracle database. It is one of several Oracle utilities that you can use to load data into database tables.

Tasks of an Oracle Database Administrator

noreply@blogger.com (Unknown) — Fri, 05 Dec 2008 05:55:00 +0000

A prioritized approach for designing, implementing, and maintaining an Oracle database involves the following tasks:
1. Evaluating the database server hardware
2. Installing the Oracle software
3. Planning the database and security strategy
4. Creating, migrating, and opening the database
5. Backing up the database
6. Enrolling system users and planning for their Oracle Network access
7. Implementing the database design
8. Recovering from database failure
9. Monitoring database performance

A DBA is typically responsible for installing the Oracle software and creating the database. As a DBA, you may be responsible for creating database storage structures, such as tablespaces. In addition, you may create the schema or set of objects to hold application data.

You must ensure that the database is available for users. You can accomplish this by starting up the database, backing up the database on a regular basis, and monitoring the performance of the database. These tasks should be performed within the framework of a security strategy.

Logical and Physical Database Structure

noreply@blogger.com (Unknown) — Wed, 03 Dec 2008 08:32:00 +0000

An Oracle database is a collection of data that is treated as a unit. The general purpose of a database is to store and retrieve related information. The database has logical structures and physical structures.

Tablespaces
A database is divided into logical storage units called tablespaces, which group related logical structures together. For example, tablespaces commonly group all of an application’s objects to simplify some administrative operations. You may have a tablespace for application data and an additional one for application indexes.

Databases, Tablespaces, and Data Files
The relationship among databases, tablespaces, and data files is illustrated in the slide. Each database is logically divided into one or more tablespaces. One or more data files are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. If it is a TEMPORARY tablespace, instead of a data file, then the tablespace has a temporary file.

A schema is a collection of database objects that are owned by a database user. Schema objects are the logical structures that directly refer to the database’s data. Schema objects include such structures as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links. In general, schema objects include everything that your application creates in the database.

Data Blocks
At the finest level of granularity, an Oracle database’s data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on the disk. A data block size is specified for each tablespace when it is created. A database uses and allocates free database space in Oracle data blocks.

Extents
The next level of logical database space is called an extent. An extent is a specific number of contiguous data blocks (obtained in a single allocation) that are used to store a specific type of information.

Segments
The level of logical database storage above an extent is called a segment. A segment is a set of extents allocated for a certain logical structure. For example, the different types of segments include:

Data segments: Each nonclustered, non-indexed-organized table has a data segment. All of the table’s data is stored in the extents of its data segment. For a partitioned table, each partition has a data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster’s data segment.

Index segments: Each index has an index segment that stores all of its data. For a partitioned index, each partition has an index segment.

Undo segments: One UNDO tablespace is created by the database administrator to temporarily store undo information. The information in an undo segment is used to generate read-consistent database information and, during database recovery, to roll back uncommitted transactions for users.

Temporary segments: Temporary segments are created by the Oracle database when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment’s extents are returned to the instance for future use. Specify a default temporary tablespace for every user or a default temporary tablespace, which is used databasewide.

The Oracle database dynamically allocates space. When the existing extents of a segment are full, additional extents are added. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on the disk.

Segments,Extents and Blocks

noreply@blogger.com (Unknown) — Mon, 01 Dec 2008 05:56:00 +0000

Database objects, such as tables and indexes, are stored as segments in tablespaces. Each segment contains one or more extents. An extent consists of contiguous data blocks, which means that each extent can exist only in one data file. Data blocks are the smallest unit of I/O in the database.
When the database requests a set of data blocks from the operating system (OS), the OS maps this to an actual file system or disk block on the storage device. Because of this, you need not know the physical address of any of the data in your database. This also means that a data file can be striped or mirrored on several disks.
The size of the data block can be set at the time of the creation of the database. The default size of 8 KB is adequate for most databases. If your database supports a data warehouse application that has large tables and indexes, then a larger block size may be beneficial.
If your database supports a transactional application where reads and writes are random, then specifying a smaller block size may be beneficial. The maximum block size depends on your OS. The minimum Oracle block size is 2 KB and should rarely (if ever) be used.
You can have tablespaces with different block sizes. However, this should be used only for transportable tablespaces. For details, see the Database Administrator’s Guide.

SYSTEM and SYSAUX Tablespaces

noreply@blogger.com (Unknown) — Fri, 28 Nov 2008 06:58:00 +0000

The SYSTEM and SYSAUX tablespaces are mandatory tablespaces.
They are created at the time of database creation.
They must be online.
The SYSTEM tablespace is used for core functionality (for example, data dictionary tables).
The auxiliary SYSAUX tablespace is used for additional database components (such as the Enterprise Manager Repository).

Each Oracle database contains a SYSTEM tablespace and a SYSAUX tablespace. They are automatically created when the database is created. The system default is to create a smallfile tablespace. You can also create bigfile tablespaces, which enable the Oracle database to manage ultralarge files (up to 8 exabytes).
A tablespace can be online (accessible) or offline (not accessible). The SYSTEM tablespace is always online when the database is open. It stores tables that support the core functionality of the database, such as the data dictionary tables.
The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. The SYSAUX tablespace stores many database components, and it must be online for the correct functioning of all database components.

Tablespaces and Data Files

noreply@blogger.com (Unknown) — Thu, 27 Nov 2008 06:42:00 +0000

A database is divided into logical storage units called tablespaces, which can be used to group related logical structures together. Each database is logically divided into one or more tablespaces. One or more data files are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace.

Note: You can also create the bigfile tablespaces, which are tablespaces with a single but very large (up to 4 billion data blocks) data file. The traditional smallfile tablespaces (which are the default) can contain multiple data files, but the files cannot be as large. For more information about the bigfile tablespaces, see the Database Administrator’s Guide.

Physical DB Structure

noreply@blogger.com (Unknown) — Thu, 27 Nov 2008 05:42:00 +0000

The files that constitute an Oracle database are organized into the following:

Control files: Contain data about the database itself (that is, physical database structure information). These files are critical to the database. Without them, you cannot open data files to access the data within the database.

Data files: Contain the user or application data of the database.

Online redo log files: Allow for instance recovery of the database. If the database crashes and does not lose any data files, then the instance can recover the database with the information in these files.

The following additional files are important to the successful running of the database:

Parameter file: Is used to define how the instance is configured when it starts up.

Password file: Allows users to connect remotely to the database and perform administrative tasks.

Backup files: Are used for database recovery. You typically restore a backup file when a media failure or user error has damaged or deleted the original file.

Archive log files: Contain an ongoing history of the data changes (redo) that are generated by the instance. Using these files and a backup of the database, you can recover a lost data file. That is, archive logs enable the recovery of restored data files.

Trace files: Each server and background process can write to an associated trace file. When an internal error is detected by a process, the process dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, whereas other information is for Oracle Support Services.

Alert log files: These are special trace files. They are also known as alert logs. The alert log of a database is a chronological log of messages and errors. Oracle recommends that you review these files.

Server Process and DB Buffer Cache

noreply@blogger.com (Unknown) — Wed, 26 Nov 2008 08:23:00 +0000

When a query is processed, the Oracle server process looks in the database buffer cache for any blocks that it needs. If the block is not found in the database buffer cache, the server process reads the block from the data file and places a copy in the database buffer cache. Because subsequent requests for the same block may find the block in memory, the requests may not require physical reads. The Oracle server uses the least recently used algorithm to age out buffers that have not been accessed recently to make room for new blocks in the database buffer cache.
Buffers in the buffer cache can be in one of the following four states:
Pinned: Multiple sessions are kept from writing to the same block at the same time. Other sessions wait to access the block.

Clean: The buffer is now unpinned and is a candidate for immediate aging out, if the current contents (data block) are not referenced again. Either the contents are in sync with the block contents stored on the disk or the buffer contains a consistent read (CR) snapshot of a block.

Free or unused: The buffer is empty because the instance has just started. This state is very similar to the clean state, except that the buffer has not been used.

Dirty: The buffer is no longer pinned but the contents (data block) have changed and must be flushed to the disk by DBWn before it can be aged out.

Oracle Instance Management

noreply@blogger.com (Unknown) — Tue, 25 Nov 2008 08:56:00 +0000

An Oracle database server consists of an Oracle database and an Oracle instance. An Oracle instance is made up of memory structures, known as the System Global Area (SGA), and background processes that handle much of the behind-the-scenes work involved in running an instance. The most common background processes are the following:

System Monitor (SMON): Performs crash recovery when the instance is started following a failure

Process Monitor (PMON): Performs process cleanup when a user process fails

Database Writer (DBWn): Writes modified blocks from the database buffer cache to the data files on the disk

Checkpoint (CKPT): Updates all the data files and control files of the database to indicate the most recent checkpoint

LogWriter (LGWR): Writes redo log entries to the disk

Archiver (ARCn): Copies redo log files to the archival storage when a log switch occurs

Process Structure

noreply@blogger.com (Unknown) — Fri, 21 Nov 2008 05:53:00 +0000

When you invoke an application program or an Oracle tool, such as Enterprise Manager, the Oracle server creates a server process to execute the commands issued by the application. The Oracle server also creates a set of background processes for an instance that interact with each other and with the operating system to manage the memory structures, asynchronously perform I/O to write data to disk, and perform other required tasks. Which background processes are present depends on the features that are being used in the database.

Oracle Memory Structure

noreply@blogger.com (Unknown) — Thu, 20 Nov 2008 07:59:00 +0000

The basic memory structures associated with an Oracle instance include the following:

System Global Area (SGA): Shared by all server and background processes

Program Global Area (PGA): Private to each server and background process. There is one PGA for each process.

The SGA is a memory area that contains data and control information for the instance.
The SGA includes the following data structures:

Database buffer cache: Caches blocks of data retrieved from the database

Redo log buffer: Caches redo information (used for instance recovery) until it can be written to the physical redo log files stored on the disk

Shared pool: Caches various constructs that can be shared among users

Large pool: Is an optional area that provides large memory allocations for certain large processes, such as Oracle backup and recovery operations, and I/O server processes

Java pool: Is used for all session-specific Java code and data within the Java Virtual Machine (JVM)

Streams pool: Is used by Oracle Streams
When you start the instance by using Enterprise Manager or SQL*Plus, the amount of memory allocated for the SGA is displayed.

A Program Global Area (PGA) is a memory region that contains data and control information for each server process. An Oracle server process services a client’s requests. Each server process has its own private PGA that is created when the server process is started. Access to the PGA is exclusive to that server process, and the PGA is read and written only by the Oracle code acting on its behalf.
With the dynamic SGA infrastructure, the size of the database buffer cache, the shared pool, the large pool, the Java pool, and the Streams pool changes without shutting down the instance.
The Oracle database uses initialization parameters to create and configure memory structures. For example, the SGA_TARGET parameter specifies the total amount of space available to the SGA. If you set SGA_TARGET to 0, Automatic Shared Memory Management is disabled.

Oracle Database Architecture

noreply@blogger.com (Unknown) — Wed, 19 Nov 2008 08:47:00 +0000

Database Structures

Each running Oracle database is associated with an Oracle instance. When a database is started on a database server, the Oracle software allocates a shared memory area called the System Global Area (SGA) and starts several Oracle background processes. This combination of the SGA and the Oracle processes is called an Oracle instance.
After starting an instance, the Oracle software associates the instance with a specific database. This is called mounting the database. The database is then ready to be opened, which makes it accessible to authorized users. Multiple instances can execute concurrently on the same computer, each accessing its own physical database.
You can look at the Oracle database architecture as various interrelated structural components.
An Oracle database uses memory structures and processes to manage and access the database. All memory structures exist in the main memory of the computers that constitute the database server. Processes are jobs that work in the memory of these computers. A process is defined as a “thread of control” or a mechanism in an operating system that can run a series of steps.

Oracle 10g

noreply@blogger.com (Unknown) — Tue, 18 Nov 2008 06:59:00 +0000

-Global Grid Forum (GGF)
-Oracle’s grid infrastructure:
-Low cost
-High quality of service
-Easy to manage

Oracle Database 10g: “g” Stands for Grid

Global Grid Forum (GGF) is a standards body that develops standards for grid computing. It comprises a set of committees and working groups that focus on various aspects of grid computing. The committees and working groups are composed of participants from academia, the research community, and (increasingly) commercial companies. You can see the Web site of GGF at http://www.gridforum.org.
Oracle has created the grid computing infrastructure software that balances all types of workloads across servers and enables all those servers to be managed as one complete system. Grid computing can achieve the same very high level of reliability as mainframe computing because all components are clustered. But unlike mainframes and large UNIX symmetric multiprocessing (SMP) servers, a grid can be built with open system technologies, such as Intel processors and the Linux operating system, at a very low cost.

Oracle’s grid computing technology includes:

Automatic Storage Management (ASM)
Real Application Clusters (RAC)
Oracle Streams
Enterprise Manager Grid Control

Automatic Storage Management spreads database data across all disks, creates and maintains a storage grid, and provides the highest input/output (I/O) throughput with minimal management costs. As disks are added or dropped, ASM redistributes the data automatically. (There is no need for a logical volume manager to manage the file system.) Data availability increases with optional mirroring, and you can add or drop disks online. For more information, see the lesson titled “Managing Database Storage Structures.”
Oracle’s Real Application Clusters runs and scales all application workloads on a cluster of servers and offers the following features:
Integrated clusterware: This includes functionality for cluster connectivity, messaging and locking, cluster control, and recovery. It is available on all platforms that are supported by Oracle Database 10g.
Automatic workload management: Rules can be defined to automatically allocate processing resources to each service both during normal operations and in response to failures. These rules can be dynamically modified to meet the changing business needs. This dynamic resource allocation within a database grid is unique to Oracle RAC.
Automatic event notification to the mid-tier: When a cluster configuration changes, the mid-tier can immediately adapt to instance failover or availability of a new instance. This enables end users to continue working in the event of instance failover without the delays typically caused by network timeouts. In the event of new instance availability, the mid-tier can immediately start load balancing connections to that instance. Oracle Database 10g Java Database Connectivity (JDBC) drivers have the “fast connection failover” functionality that can be automatically enabled to handle these events.
Oracle Streams provides a unified framework for information sharing, combining message queuing, data replication, event notification, data warehouse loading, and publishing and subscribing functionality into a single technology. Oracle Streams can keep two or more data source copies synchronized when updates are applied at either site. It can automatically capture database changes, propagate the changes to subscribing nodes, apply changes, and detect and resolve data update conflicts. Oracle Streams can be used directly by applications as a message-queuing or workflow feature, enabling communications between applications in the grid.
Enterprise Manager Grid Control manages gridwide operations that include managing the entire stack of software, provisioning users, cloning databases, and managing patches. It can monitor the performance of all applications from the point of view of your end users. Grid Control views the performance and availability of the grid infrastructure as a unified whole rather than as isolated storage units, databases, and application servers. You can group hardware nodes, databases, and application servers into single logical entities and manage a group of targets as one unit.

Oracle Products

noreply@blogger.com (Unknown) — Mon, 17 Nov 2008 10:33:00 +0000

Oracle databases: The Oracle database is the first database that is designed for enterprise grid computing (the most flexible and cost-effective way to manage information and applications).

Oracle Application Server: Oracle’s Java 2 Platform, Enterprise Edition (J2EE)–certified server integrates everything that is needed to develop and deploy Web-based applications. The application server deploys e-business portals, Web services, and transactional applications, including PL/SQL, Oracle Forms, and J2EE-based applications.

Oracle applications: Oracle E-Business Suite is a complete set of business applications for managing and automating processes across your organization.

Oracle Collaboration Suite: Oracle Collaboration Suite is a single, integrated system for all your organization’s communications data: voice, e-mail, fax, wireless, calendar information, and files.

Oracle Developer Suite: Oracle Developer Suite is a complete, integrated environment that combines application development and business intelligence tools.

Oracle services: Services such as Oracle Consulting and Oracle University provide you with the necessary expertise for your Oracle projects. For useful links to a variety of resources, see the appendix titled “Next Steps, Continuing Your Education.”

Difference between VARCHAR, VARCHAR2 and CHAR data types?

noreply@blogger.com (Unknown) — Fri, 14 Nov 2008 06:11:00 +0000

Both CHAR and VARCHAR2 types are used to store character string values, however, they behave very differently. The VARCHAR type should not be used:

CHAR

CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store variable length strings, it will waste a lot of disk space.

SQL> CREATE TABLE char_test (col1 CHAR(10));
Table created.

SQL> INSERT INTO char_test VALUES ('qwerty');
1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM char_test;
COL1       LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty               10 Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32

Note: ASCII character 32 is a blank space.

VARCHAR

Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.

SQL> CREATE TABLE varchar_test (col1 VARCHAR2(10));
Table created.

SQL> INSERT INTO varchar_test VALUES ('qwerty');
1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar_test;
COL1       LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty                6 Typ=1 Len=6: 113,119,101,114,116,121

VARCHAR2

VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.

SQL> CREATE TABLE varchar2_test (col1 VARCHAR2(10));
Table created.

SQL> INSERT INTO varchar2_test VALUES ('qwerty');
1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar2_test;
COL1       LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty                6 Typ=1 Len=6: 113,119,101,114,116,121

How does one count/sum data values in a column?

noreply@blogger.com (Unknown) — Tue, 04 Nov 2008 11:04:00 +0000

Count/sum FIX values:

Use this simple query to count the number of data values in a column:

select my_table_column, count(*)
from   my_table
group  by my_table_column;

A more sophisticated example...

select dept, sum(  decode(sex,'M',1,0)) MALE,
          sum(  decode(sex,'F',1,0)) FEMALE,
          count(decode(sex,'M',1,'F',1)) TOTAL
from my_emp_table
group by dept;

Count/sum RANGES of data values in a column:

A value x will be between values y and z if GREATEST(x, y) = LEAST(x, z). Look at this example:

select f2,
    sum(decode(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",
    sum(decode(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",
    sum(decode(greatest(f1, 0), least(f1, 29), 1, 0)) "Range 00-29"
from   my_table
group  by f2;

For equal size ranges it might be easier to calculate it with DECODE(TRUNC(value/range), 0, rate_0, 1, rate_1, ...). Eg.

select ename "Name", sal "Salary",
    decode( trunc(f2/1000, 0), 0, 0.0,
                               1, 0.1,
                               2, 0.2,
                               3, 0.31) "Tax rate"
from   my_table;

How does one code a hierarchical tree-structured query?

noreply@blogger.com (Unknown) — Fri, 31 Oct 2008 10:25:00 +0000

The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for testing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss.

The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Look at this example:

SQL> SELECT     level, empno, ename, mgr
2    FROM     emp
3  CONNECT BY PRIOR empno = mgr
4    START WITH mgr IS NULL
5  /
   LEVEL      EMPNO ENAME             MGR
---------- ---------- ---------- ----------
       1       7839 KING
       2       7566 JONES            7839
       3       7788 SCOTT            7566
...

One can produce an indented report by using the level number to substring or lpad() a series of spaces, and concatenate that to the string. Look at this example:

SQL> SELECT     LPAD(' ', LEVEL * 2) || ename
2    FROM     emp
3  CONNECT BY PRIOR empno = mgr
4    START WITH mgr IS NULL;
LPAD(,LEVEL*2)||ENAME
------------------------------------------------------
KING
  JONES
    SCOTT
...

Use the "start with" clause to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a "connect by prior" clause is that you cannot perform a join to other tables. The "connect by prior" clause is rarely implemented in the other database offerings. Trying to do this programmatically is difficult as one has to do the top level query first, then, for each of the records open a cursor to look for child nodes.

One way of working around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.

NOTE: Tree-structured queries are definitely non-relational (enough to kill Codd and make him roll in his grave). Also, this feature is not often found in other database offerings.

How does one add a column to the middle of a table?

noreply@blogger.com (Unknown) — Wed, 29 Oct 2008 09:33:00 +0000

Oracle only allows columns to be added to the end of an existing table. Example:

SQL> CREATE TABLE tab1 ( col1 NUMBER );
Table created.

SQL> ALTER TABLE tab1 ADD (col2 DATE);
Table altered.

SQL> DESC tab1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
COL1                                               NUMBER
COL2                                               DATE

Nevertheless, some databases also allow columns to be added to an existing table after a particular column (i.e. in the middle of the table). For example, in MySQL the following syntax is valid:

ALTER TABLE tablename ADD columnname AFTER columnname;

Oracle does not support this syntax. However, it doesn't mean that it cannot be done.

Workarounds:

1. Create a new table and copy the data across.

SQL> RENAME tab1 TO tab1_old;
Table renamed.

SQL> CREATE TABLE tab1 AS SELECT 0 AS col1, col1 AS col2 FROM tab1_old;
Table created.

2. Use the DBMS_REDEFINITION package to change the structure on-line while users are working.

Can one retrieve only the Nth row from a table?

noreply@blogger.com (Unknown) — Tue, 28 Oct 2008 09:20:00 +0000

provided this solution to select the Nth row from a table:

SELECT * FROM t1 a
WHERE  n = (SELECT COUNT(rowid)
            FROM t1 b
           WHERE a.rowid >= b.rowid);

provided this solution:

SELECT * FROM (
 SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 )
WHERE  RN = 100;

Note: In this first query we select one more than the required row number, then we select the required one. Its far better than using a MINUS operation.

provided these solutions:

SELECT f1 FROM t1
WHERE  rowid = (
      SELECT rowid FROM t1
      WHERE  rownum <= 10
      MINUS
      SELECT rowid FROM t1
      WHERE  rownum < 10);

SELECT rownum,empno FROM scott.emp a
GROUP BY rownum,empno HAVING rownum = 4;

Alternatively...

SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN (SELECT rowid FROM emp WHERE rownum <>Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.

Can one retrieve only rows X to Y from a table?

noreply@blogger.com (Unknown) — Tue, 28 Oct 2008 09:13:00 +0000

SELECT * FROM (
 SELECT ename, rownum rn
          FROM emp WHERE rownum < 101
) WHERE  RN between 91 and 100 ;

Note: the 101 is just one greater than the maximum row of the required rows (means x= 90, y=100, so the inner values is y+1).

SELECT rownum, f1 FROM t1
GROUP BY rownum, f1 HAVING rownum BETWEEN 2 AND 4;

Another solution is to use the MINUS operation. For example, to display rows 5 to 7, construct a query like this:

SELECT *
FROM   tableX
WHERE  rowid in (
 SELECT rowid FROM tableX
  WHERE rownum <= 7
MINUS
 SELECT rowid FROM tableX
 WHERE rownum < 5);

The generic solution to get full information of rows between x and y

SELECT * FROM emp WHERE empno in (SELECT empno FROM emp GROUP BY rownum,empno HAVING rownum BETWEEN &x AND &y);

How does one add a day/hour/minute/second to a date value?

noreply@blogger.com (Unknown) — Mon, 20 Oct 2008 08:34:00 +0000

he SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:

SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;
SYSDATE              SYSDATE+1/24         SYSDATE+1/1440       SYSDATE+1/86400
-------------------- -------------------- -------------------- --------------------
03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13

The following format is frequently used with Oracle Replication:

select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;
NOW                  NOW_PLUS_30_SECS
-------------------- --------------------
03-JUL-2005 16:47:23 03-JUL-2005 16:47:53

Here are a couple of examples:

Description	Date Expression
Now	SYSDATE
Tomorow/ next day	SYSDATE + 1
Seven days from now	SYSDATE + 7
One hour from now	SYSDATE + 1/24
Three hours from now	SYSDATE + 3/24
An half hour from now	SYSDATE + 1/48
10 minutes from now	SYSDATE + 10/1440
30 seconds from now	SYSDATE + 30/86400
Tomorrow at 12 midnight	TRUNC(SYSDATE + 1)
Tomorrow at 8 AM	TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon	NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of the month at 12 midnight	TRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.m	TRUNC(LEAST(NEXT_DAY(sysdate,MONDAY' ' ),NEXT_DAY(sysdate,WEDNESDAY), NEXT_DAY(sysdate,FRIDAY ))) + (9/24)

How does one get the time difference between two date columns?

noreply@blogger.com (Unknown) — Mon, 20 Oct 2008 08:31:00 +0000

Oracle allows two date values to be subtracted from each other returning a numeric value indicating the number of days between the two dates (may be a fraction). This example will show how to relate it back to a time value.

Let's investigate some solutions. Test data:

SQL> CREATE TABLE dates (date1 DATE, date2 DATE);
Table created.
SQL>
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1);
1 row created.
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/24);
1 row created.
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/60/24);
1 row created.
SQL> SELECT (date1 - date2) FROM dates;
DATE1-DATE2
-----------
        1
.041666667
.000694444

Solution 1

SQL> SELECT floor(((date1-date2)*24*60*60)/3600)
2         || ' HOURS ' ||
3         floor((((date1-date2)*24*60*60) -
4         floor(((date1-date2)*24*60*60)/3600)*3600)/60)
5         || ' MINUTES ' ||
6         round((((date1-date2)*24*60*60) -
7         floor(((date1-date2)*24*60*60)/3600)*3600 -
8         (floor((((date1-date2)*24*60*60) -
9         floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) ))
10         || ' SECS ' time_difference
11    FROM dates;
TIME_DIFFERENCE
--------------------------------------------------------------------------------
24 HOURS 0 MINUTES 0 SECS
1 HOURS 0 MINUTES 0 SECS
0 HOURS 1 MINUTES 0 SECS

Solution 2

SQL> SELECT to_number( to_char(to_date('1','J') +
2         (date1 - date2), 'J') - 1)  days,
3         to_char(to_date('00:00:00','HH24:MI:SS') +
4         (date1 - date2), 'HH24:MI:SS') time
5   FROM dates;
    DAYS TIME
---------- --------
       1 00:00:00
       0 01:00:00
       0 00:01:00

Solution 3

SQL> SELECT trunc(date1-date2) days,
2         trunc(sysdate) + (date1 - date2), 'HH24 "Hours" MI "Minutes" SS "Seconds"') time
3   FROM dates;
    DAYS TIME
---------- ------------------------------
       1 00 Hours 00 Minutes 00 Seconds
       0 01 Hours 00 Minutes 00 Seconds
       0 00 Hours 01 Minutes 00 Seconds