Skip navigation.

Blogs

Oracle Database renamed to Oracle10G

Oracle Corporation renamed the latest version of their database management system from Oracle 10i to Oracle10G to illustrate their commitment to Grid computing and the GGG (Great Global Grid). Oracle's Chairman and CEO, Larry Ellison, will release Oracle10G at the OracleWorld conference in San Francisco on the 9th of September 2003.

Oracle9i New Feature: Oracle Managed Files

Oracle Managed Files (OMF), a new feature introduced in Oracle9i, enables the Oracle Server to automatically create and delete database files using standard operating system interfaces. This
feature hugely simplifies the job of the DBA, as the DBA doesn't need to interact with the underlying operating system to create and delete files.

Initialization Parameter files: PFILEs vs. SPFILEs

When an Oracle Instance is started, the characteristics of the Instance are established by parameters specified within the initialization parameter file. These initialization parameters are either stored in a PFILE or SPFILE. SPFILEs are available in Oracle 9i and above. All prior releases of Oracle are using PFILEs.

On-line Table Reorganization and Redefinition

Tables can be reorganized and redefined (evolved) on-line with the DBMS_REDEFINITION package. The process is similar to on-line rebuilds of indexes, in that the original table is left on-line, while a new copy of the table is built. However, an index index-rebuild is a singular operation, while table redefinition is a multi-step process.

Locally vs. Dictionary Managed Tablespaces

When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.

TEMPORARY Tablespaces and TEMPFILES

What are Temporary Tablespaces:

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace. This can be done with one of the following commands:

Automatic Undo Management (AUM)

In Oracle 8i and below, Rollback Segments provide read consistency and the ability to rollback transactions. In Oracle 9i, Undo segments can be used to provide this functionality. The advantage of using Automatic Undo Management is that it relieves the DBA of manually creating, sizing and monitoring the rollback segments in the database.

Difference between DBFile Sequential and Scattered Reads

Both "db file sequential read" and "db file scattered read" events signify time waited for I/O read requests to complete. Time is reported in 100's of a second for Oracle 8i releases and below, and 1000's of a second for Oracle 9i and above. Most people confuse these events with each other as they think of how data is read from disk. Instead they should think of how data is read into the SGA buffer cache or user PGA memory.

db file sequential read:

A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads.

Protecting Oracle executables on Unix

Execute the following commands to protect Oracle executables on Unix from unauthorized access:

cd $ORACLE_HOME/bin

chmod 750 lsnrctl
chmod 750 svrmgrl
chmod 750 orapwd
chmod 750 tnslsnr
chmod 750 dbshut
chmod 750 dbstart
chmod 750 tstshm
chmod 750 runInsaller

Also, change file permissions for all you Oracle database files:

Controlfiles = chmod 640 rw-r

Oracle REF CURSORs

Below is an example of how to setup a REF CURSOR and how to call it from a PL/SQL block.

Why would you want to use REF CURSOR's? REF CURSORs can often provide greater performance when working with data. Much of these comes from the fact that packages are stored into the SHARED POOL of the Oracle database or otherwise known as PINNED to memory.

What I did find interesting was that the process that executes this procedure never needs to know what tables the view touches or even be able to see the view. The procedure simply returns the requested columns.

Also: It would be entirely feasible, using REF CURSOR design, to have queries stored in a LONG column in a table. Remembering that the length of the data cannot exceed 32K. These queries can then be retrieved at package execution time. This could often reduce the number of production moves regarding packages. You could further enhance it by versioning your queries, so that only the most recent would be retrieved.