ORACLE TEXT

Oracle Text is a technology that enables you to build text query applications and document classification applications. Oracle Text provides indexing, word and theme searching, and viewing capabilities for text.

To design your Oracle Text application, you must determine the type of queries you expect to execute. We can divide application queries into four different categories:

1. Text Queries on Document Collections: A text query application enables users to search document collections such as Web sites, digital libraries, or document warehouses. Searching is enabled by first indexing the document collection. The collection is typically static with no significant change in content after the initial indexing run. Documents can be of any size and of different formats such as HTML, PDF, or Microsoft Word. These documents are stored in a document table. Queries usually consist of words or phrases. Other query operations such as stemming, proximity searching, and wild carding can be used to improve the search results. The queries for this type of application are best served with a CONTEXT index on your document table. To query this index, your application uses the SQL CONTAINS operator in the WHERE clause of a SELECT statement.
2. Queries on Catalog Information: Catalog information consists of inventory type information such as online book store or auction site. The stored information consists of text information such as book titles and related structured information such as price. The information is usually updated regularly to keep the online catalog up to date with the inventory. Queries are usually a combination of a text component and a structured component, such as price or author. Results are almost always sorted by a structured component such as date or price. Catalog applications are best served by a CTXCAT index. You query this index with the CATSEARCH operator in the WHERE clause of a SELECT statement.
3. Document Classification: In a document classification application, an incoming stream or a set of documents is compared to a predefined set of rules. When a document matches one or more rules, the application performs some action. For example, assume we have an incoming stream of news articles. We can define a rule to represent the category of Finance. The rule is essentially one or more queries that select document about the subject of Finance. The rule might have the form 'stocks or bonds or earnings'. When a document arrives about a Wall Street earnings forecast and satisfies the rules for this category, the application takes an action such as tagging the document as Finance or emailing one or more users. To create a document classification application, you create a table of rules and then create a CTXRULE index. To classify an incoming stream of text, use the MATCHES operator in the WHERE clause of a SELECT statement.
4. XML Searching: An XML search application performs searches over XML documents. In a regular document search, you usually search across a set of documents to return documents that satisfy a text predicate; in an XML search, you often use the structure of the XML document to restrict the search. Typically, only that part of the document that satisfies the search is returned. For example, instead of finding all purchase orders that contain the word electric , the user might need only purchase orders in which the comment field contains electric . Oracle Text enables you to perform XML searching using the following approaches: A. Using Oracle Text B. Using the Oracle XML DB Framework and C. Combining Oracle Text features with Oracle XML DB.
   

Related Topics:

1. ORACLE TEXT Installation.
2. Example of Text Queries on Document Collections.
3. Example of Queries on Catalog Information.
   
4. Example of Document Classification.

ORACLE TEXT: Document Classification

The function of a classification application is to perform some action based on document content. These actions can include assigning a category id to a document or sending the document to a user. The result is classification of a document. Documents are classified according to pre-defined rules. These rules select for a category. For instance, a query rule of 'presidential elections' might select documents for a category about politics. Oracle Text provides several types of classification. One type is simple , or rule-based classification, discussed here, in which you create both document categories and the rules for categorizing documents. With supervised classification , Oracle Text derives the rules from a set of training documents you provide. With clustering , Oracle Text does all the work for you, deriving both rules and categories.

Create the Rule Table


CREATE TABLE queries (
query_id NUMBER,
query_string VARCHAR2(80)
);

INSERT INTO queries VALUES (1, 'oracle'); INSERT INTO queries VALUES (2, 'larry or ellison');
INSERT INTO queries VALUES (3, 'oracle and text'); INSERT INTO queries VALUES (4, 'market share');

Create Your CTXRULE Index


CREATE INDEX queryx ON queries(query_string) INDEXTYPE IS CTXRULE;

Classify with MATCHES


COLUMN query_string FORMAT a35;
SELECT query_id,query_string FROM queries WHERE MATCHES (query_string, 'Oracle announced that its market share in databases increased over the last year.')>0;

Related Topics:

1. Example of Text Queries on Document Collections.
2. Example of Queries on Catalog Information.
   

ORACLE TEXT: Queries on Catalog Information

This example creates a catalog index for an auction site that sells electronic equipment such as cameras and CD players. New inventory is added everyday and item descriptions, bid dates, and prices must be stored together. The application requires good response time for mixed queries. The key is to determine what columns users frequently search so that we can create a suitable CTXCAT index. Queries on this type of index are issued with the CATSEARCH operator.

Create Your Table

Set up an auction table to store your inventory:

CREATE TABLE auction(
item_id NUMBER,
title VARCHAR2(100),
category_id NUMBER,
price NUMBER,
bid_close DATE);


Populate Your Table

Now populate the table with various items, each with an id, title, price and bid_date:

INSERT INTO AUCTION VALUES(1, 'NIKON CAMERA', 1, 400, '24-OCT-2002'); INSERT INTO AUCTION VALUES(2, 'OLYMPUS CAMERA', 1, 300, '25-OCT-2002'); INSERT INTO AUCTION VALUES(3, 'PENTAX CAMERA', 1, 200, '26-OCT-2002'); INSERT INTO AUCTION VALUES(4, 'CANON CAMERA', 1, 250, '27-OCT-2002');


Determine your Queries

You need to determine what criteria are likely to be retrieved. In this example, you determine that all queries search the title column for item descriptions, and most queries order by price. When using the CATSEARCH operator later, we'll specify the terms for the text column and the criteria for the structured clause. Step 2 Create the Sub-Index to Order by Price For Oracle Text to serve these queries efficiently, we need a sub-index for the price column, since our queries will order by price. Therefore, create an index set called auction_set and add a sub-index for the price column:

EXEC CTX_DDL.CREATE_INDEXT_SET('auction_iset'); EXEC CTX_DDL.ADD_INDEX('auction_iset','price'); /* sub-index A*/


Create the CTXCAT Index

Create the combined catalog index on the AUCTION table with CREATE INDEX as follows:

CREATE INDEX auction_titlex ON AUCTION(title) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS ('index set auction_iset');

Querying Your Table with CATSEARCH : When you have created the CTXCAT index on the AUCTION table, you can query this index with the CATSEARCH operator. First set the output format to make the output readable:

COLUMN title FORMAT a40;
SELECT title, price FROM auction WHERE CATSEARCH(title, 'CAMERA', 'order by price')> 0;
SELECT title, price FROM auction WHERE CATSEARCH(title, 'CAMERA', 'price <= 300')>0;

Update Your Table

You can update your catalog table by adding new rows. When you do so, the CTXCAT index is automatically synchronized to reflect the change. For example, add the following new rows to our table and then reexecute the query:

INSERT INTO AUCTION VALUES(5, 'FUJI CAMERA', 1, 350, '28-OCT-2002');
INSERT INTO AUCTION VALUES(6, 'SONY CAMERA', 1, 310, '28-OCT-2002');

SELECT title, price FROM auction WHERE CATSEARCH(title, 'CAMERA', 'order by price')> 0;

Related Topics:

1. Example of Text Queries on Document Collections.
2. Example of Document Classification.

ORACLE TEXT: Text Queries on Document Collections

In a basic text query application, users enter query words or phrases and expect the application to return a list of documents that best match the query. Such an application involves creating a CONTEXT index and querying it with CONTAINS.


Create your Text Table

The following example creates a table called docs with two columns, id and text, by using the CREATE TABLE statement. This example makes the id column the primary key. The text column is VARCHAR2.

CREATE TABLE docs (id NUMBER PRIMARY KEY, text VARCHAR2(200));

Load Documents into Table: You can use the SQL INSERT statement to load text to a table. To populate the docs table, use the INSERT statement as follows:

INSERT INTO docs VALUES(1, 'California is a state in the US.');
INSERT INTO docs VALUES(2, 'Paris is a city in France.');
INSERT INTO docs VALUES(3, 'France is in Europe.');


Create the CONTEXT index

Index the HTML files by creating a CONTEXT index on the text column as follows. Since you are indexing HTML, this example uses the NULL_FILTER preference type for no filtering and uses the HTML_SECTION_GROUP type:

CREATE INDEX idx_docs ON docs(text) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');

Use the NULL_FILTER because you do not need to filter HTML documents during indexing. However, if you index PDF, Microsoft Word, or other formatted documents, use the CTXSYS.INSO_FILTER (the default) as your FILTER preference. This example also uses the HTML_SECTION_GROUP section group which is recommended for indexing HTML documents. Using HTML_SECTION_GROUP enables you to search within specific HTML tags, and eliminates from the index unwanted markup such as font information.


Querying Your Table with CONTAINS

You query the table with the SELECT statement with CONTAINS to retrieve the document ids that satisfy the query. Before doing so, set the format of the SELECT statement's output so that it is easily readable. To do so, set the width of the text column to 40 characters:

COLUMN text FORMAT a40;
SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'France', 1) > 0;


Present the Document

In a real application, you might want to present the selected document to the user with query terms highlighted. Oracle Text enables you to mark up documents with the CTX_DOC package. We can demonstrate HTML document markup with an anonymous PL/SQL block in SQL*Plus. However, in a real application you might present the document in a browser. This PL/SQL example uses the in-memory version of CTX_DOC.MARKUP to highlight the word France in document 3. It allocates a temporary CLOB (Character Large Object datatype) to store the markup text and reads it back to the standard output. The CLOB is then de-allocated before exiting:

SET SERVEROUTPUT ON;

DECLARE
mklob CLOB;
amt NUMBER := 40;
line VARCHAR2(80);
BEGIN

CTX_DOC.MARKUP('idx_docs','3','France', mklob);
DBMS_LOB.READ(mklob, amt, 1, line);
DBMS_OUTPUT.PUT_LINE('FIRST 40 CHARS ARE:'||line); DBMS_LOB.FREETEMPORARY(mklob);

END;
/

Synchronize the Index After Data Manipulation

When you create a CONTEXT index, you need to explicitly synchronize your index to keep it up to date with any inserts, updates, or deletes to the text table. Oracle Text enables you to do so with the CTX_DDL.SYNC_INDEX procedure. Add some rows to the docs table:

INSERT INTO docs VALUES(4, 'Los Angeles is a city in California.');
INSERT INTO docs VALUES(5, 'Mexico City is big.');

Since the index is not synchronized, these new rows are not returned with a query on city :

SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'city', 1) > 0;

Therefore, synchronize the index with 2Mb of memory, and reexecute the query:

EXEC CTX_DDL.SYNC_INDEX('idx_docs', '2M');

COLUMN text FORMAT a50;
SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'city', 1) > 0;


Related Topics:

1. Example of Queries on Catalog Information.
   
2. Example of Document Classification.

installation of Oracle Text on 10g

This article lists the steps for manual installation of Oracle Text 10gR1 and 10gR2, verification and uninstalling Oracle Text installation.If you create an Oracle database using the Database Configuration Assistant (DBCA) Text is installed by default and you do not need to perform the installation steps described in the Manual installation section.

Instead installation you need to grant permission to the oracle user that use oracle text.

Create User:

CREATE USER myuser IDENTIFIED BY myuser_password.

Grant Roles:

GRANT RESOURCE, CONNECT, CTXAPP TO MYUSER;

Grant EXECUTE Privileges on CTX PL/SQL Packages:

There are ten Oracle Text packages that enable you to perform actions ranging from synchronizing an Oracle Text index to highlighting documents. For example, the CTX_DDL.SYNC_INDEX package enables you to synchronize your index.

GRANT EXECUTE ON CTX_CLS TO myuser;
GRANT EXECUTE ON CTX_DDL TO myuser;
GRANT EXECUTE ON CTX_DOC TO myuser;
GRANT EXECUTE ON CTX_OUTPUT TO myuser;
GRANT EXECUTE ON CTX_QUERY TO myuser;
GRANT EXECUTE ON CTX_REPORT TO myuser;
GRANT EXECUTE ON CTX_THES TO myuser;

Oracle Text is available for no extra Licensing in all four database editions:

1. Oracle Database Standard Edition One.
2. Oracle Database Standard Edition (SE).
3. Oracle Database Enterprise Edition (EE).
4. Oracle Database Personal Edition.

Manual installation of Text 10gR1 (10.1.0.x) and 10gR2 (10.2.0.x)

1. You must install Oracle Data Mining (ODM), feature available in Oracle Database Enterprise Edition (EE), before you install Oracle Text in order to use the SVM classifier and the KMEANS clustering. All other functions should work without ODM installed, including RULE classifier and TEXTK clustering.
2. Oracle Text makes use of a "Knowledge Base" for English and French, which is loaded from different files provided on the second CD, known as the "Oracle Database 10g Companion CD". The Knowledge Base is required to use any of the theme-based features in Oracle Text. Therefore, if you install from the main CD only, theme functionality will not be available. For information about how to install products from the Companion CD, refer to the "Installing Oracle Database 10g Products from the Companion CD" section.

If you created your database manually or you want to install Text later, then follow these steps.


Note: In SQL*Plus we use '?' instead of $ORACLE_HOME

1. Text dictionary, schema name CTXSYS, is created by calling following script from SQL*Plus connected as SYSDBA:

SQL> connect SYS/password as SYSDBA
SQL> spool text_install.txt
SQL> @?/ctx/admin/catctx.sql CTXSYS SYSAUX TEMP NOLOCK

Where:
CTXSYS - is the ctxsys user password
SYSAUX - is the default tablespace for ctxsys
TEMP - is the temporary tablespace for ctxsys
LOCK|NOLOCK - ctxsys user account will be locked or not

2. The next step is to install appropriate language-specific default preferences. There is script which creates language-specific default preferences for every language Oracle text supports in /ctx/admin/defaults directory, such as English(US), Danish(DK), Dutch(NL), Finnish(SF), French(F), German(D), Italian(IT), Portuguese(PT), Spanish(E), and Swedish(S). They are named in the form drdefXX.sql, where XX is the international license plate code.
To manually install US default preferences, for example, log into sqlplus as CTXSYS, and run 'drdefus.sql' as described below:

SQL> connect CTXSYS/password@tns_alias
SQL> @?/ctx/admin/defaults/drdefus.sql
SQL> spool off

If you have installed Oracle Data Mining (ODM) before Text you will see in the text_install.txt logfile ORA-955 errors for public synonyms, e.g. dm_svm_build, which can be ignored. We have a dummy package that mimics the API in CTXSYS schema, and we attempt to create public synonyms to it. Now, if ODM has been installed, these public synonym creates fail and the public synonyms point to ODM objects, which is what we want.


Text 10gR1 (10.1.0.x) and Text 10gR2 (10.2.0.x) Installation verification

1. Check to make sure that all Text objects were created in CTXSYS schema and correct version is installed
2. Check to make sure that there are not invalid objects for CTXSYS.
You should get: "no rows selected". If there are then you can compile each invalid object manually.


connect SYS/password as SYSDBA

set pages 1000
col object_name format a40
col object_type format a20
col comp_name format a30
column library_name format a8
column file_spec format a60 wrap
spool text_install_verification.log

select comp_name, status, substr(version,1,10) as version from dba_registry where comp_id = 'CONTEXT';
select * from ctxsys.ctx_version;
select substr(ctxsys.dri_version,1,10) VER_CODE from dual;

select count(*) from dba_objects where owner='CTXSYS';

-- Get a summary count
select object_type, count(*) from dba_objects where owner='CTXSYS' group by object_type;

-- Any invalid objects
select object_name, object_type, status from dba_objects where owner='CTXSYS' and status != 'VALID' order by object_name;

spool off


Steps to Deinstall Oracle Text Manually

Before deinstalling Oracle Text, it is best to first drop all Text Indexes built in schemas other than CTXSYS. Text dictionary, schema name CTXSYS, is removed by calling following script from SQL*Plus connected as SYSDBA:

SQL> connect SYS/password as SYSDBA
SQL> spool text_deinstall.log
SQL> @?/ctx/admin/catnoctx.sql
SQL> drop procedure sys.validate_context;
SQL> spool off

Review the output file text_deinstall.log for errors.
Deinstallation of Oracle Text is complete.

*** Note ***
ORA-04043 for dropping sys.validate_context can be ignored, as in base release version this procedure was owned by ctxsys and needs to be owned by sys.

Related Posts:

1. Introduction of ORACLE TEXT
2. Find Text index size

Constraint Information

While performing DML operations, Some times we experienced constraint violation. Then we searching the details of that culprit constraint :)

Here is a simple script that help us a lot to handle such situation:

SELECT uc.owner , uc.constraint_name
, DECODE(uc.constraint_type,'C','CHECK','P' ,'Primary key','R','Referential','U'
,'Unique Key') constraint_type , uc.table_name
, ucc.column_name , uc.r_owner , uc.r_constraint_name
, ( SELECT 'Table : '||uc1.table_name||' Column: '|| ucc1.column_name
FROM user_constraints uc1,USER_CONS_COLUMNS ucc1
WHERE uc1.constraint_name=ucc1.constraint_name
AND uc1.constraint_name=uc.r_constraint_name
) R_description
FROM user_constraints uc,USER_CONS_COLUMNS ucc
WHERE uc.constraint_name=ucc.constraint_name
AND uc.constraint_name IN('FK625EF6937B99960');

The only thing you need to change the constraint name 'FK625EF6937B99960'

ORA-01552

Message

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERDATA'

Cause

Tried to use the system rollback segment for operations involving non-system tablespace. If your DB is a clone database then this will happen when you attempting any data modification outside of the system tablespace. Only the system rollback segment can be online in a clone database.

Action

Create one or more private/public segment(s), shutdown and then startup your DB again. May need to modify the INIT.ORA parameter rollback_segments to acquire private rollback segment. If this is a clone database being used for tablspace point in time recovery then this operation is not allowed. If the non-system tablespace has AUTO segment space management, then create an undo tablespace and make it the default undo tablespace of your DB.

Resize the Online Redo Log files

When you are working on large data movement in Oracle, you must concern about some performance overhead and online redo log switch is one of them. Recently i am working on a migration project where i need to migrate a 19.5GB MySQL 5.0 database to ORACLE 10gR2 database. I used Oracle SQL developer to do the job and the job takes about 5 hour to complete. To do the job faster i perform some tuning stuff on ORACLE and increasing the Online Redo Log files is the most fruitful one.

Now i am going to describe how i have done this. The procedure was learned from a release note of metalink .



1. First see the size of the current logs:

SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 52428800 INACTIVE
2 52428800 CURRENT
3 52428800 INACTIVE

Logs are 50MB from above which is default in oracle 10g, let's size them to 100MB.


2. Retrieve all the log member names for the groups:

SQL> select group#, member from v$logfile;

GROUP# MEMBER
--------------- ----------------------------------------
1 /usr/oracle/dbs/log1PROD.dbf
2 /usr/oracle/dbs/log2PROD.dbf
3 /usr/oracle/dbs/log3PROD.dbf


3. Now drop the log group 1 and recreate it with increased size

SQL> alter database drop logfile group 1;

SQL> alter database add logfile group 1
'/usr/oracle/dbs/log1PROD.dbf' size 100M reuse;

4. Check the size of the current logs:

SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 CURRENT
3 52428800 INACTIVE
5. Do the same for log group 3

SQL> alter database drop logfile group 3;

SQL> alter database add logfile group 3
'/usr/oracle/dbs/log3PROD.dbf' size 100M reuse;


SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 CURRENT
3 10485760 INACTIVE
6. Now we go for group 2 but it is now used by oracle, so first switch the log

SQL> alter system switch logfile;

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 INACTIVE
3 10485760 CURRENT
SQL> alter database drop logfile group 2;


SQL> alter database add logfile group 2
'/usr/oracle/dbs/log2PROD.dbf' size 100M reuse;

7. Check the size of the current logs:

SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 10485760 UNUSED
3 10485760 CURRENT

** some times you may find that a log group status is ACTIVE, in that case you should
make a database checkpoint like this

SQL> ALTER SYSTEM CHECKPOINT;

** FOR safety reason please take a full backup of your database before performing this.

Automatic Undo Management

In earlier versions of ORACLE, transactions undo information was stored into undo segment until a commit or rollback command was issued. When the commit or rollback command was issued, it purge the undo segment which contains corresponding transaction undo information. This system is known as manual undo management.

In version 9i ORACLE introduce automatic undo management along with manual management. So one can use either automatic or manual management but not both at a time. The new method freed DBA from periodical undo management and tuning. In also facilitate the DBA to specify how long undo information is stored after a commit occur. This feature eliminate the "snapshot too old" error of long running queries and also support ORACLE flashback queries.

Create Undo Tablespace

CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/u01/undotbs01_01.dbf'
SIZE 1024M BLOCKSIZE 16K;

alter system set undo_tablespace='UNDOTBS' scope=both;

Enabling Automatic Undo Management

UNDO_MANAGEMENT = AUTO -- Default is MANUAL
UNDO_TABLESPACE = undotbs_01 -- The name of the undo tablespace.
UNDO_RETENTION = 900 -- The time undo is retained. Default is 900 seconds.
UNDO_SUPPRESS_ERRORS = TRUE -- Suppress errors when MANUAL undo admin SQL statements are issued.

Please set the following parameters

-- Dynamic Parameters.
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS_02;
ALTER SYSTEM SET UNDO_RETENTION=1800;
ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=FALSE;

-- Static Parameters.
ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;

Maintenance of Undo Tablespace

-- Add a datafile.

ALTER TABLESPACE undotbs_01 ADD DATAFILE '/u0/undo0102.dbf'
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

-- Resize an undo datafile.

ALTER DATABASE DATAFILE '/u0/undo0102.dbf' RESIZE 10M;

-- Perform backup operations

ALTER TABLESPACE undotbs_01 BEGIN BACKUP;
ALTER TABLESPACE undotbs_01 END BACKUP;

-- Drop an undo tablespace.
DROP TABLESPACE undotbs_01;

Sometimes the undo tablespace become too big to manage. In such case you can resize the datafiles or create a new undo tablespace in another disk location

- Resize an undo datafile.

ALTER DATABASE DATAFILE '/u0/undo0102.dbf' RESIZE 10M;

-- create new undo tablespace and drop the old one

CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/u0/undotbs01_01.dbf' SIZE 1024M BLOCKSIZE 16K;

ALTER SYSTEM SET undo_tablespace='UNDOTBS' scope=both;

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Monitoring Undo Tablespace

You may use the following dictionary viewers:

V$UNDOSTAT
V$ROLLSTAT
V$TRANSACTION
DBA_UNDO_EXTENTS

SQL*Loader Part #1

SQL*Loader loads data from external files into tables of an Oracle database. It has a powerful data parsing engine that puts little limitation on the format of the data in the datafile. You can use SQL*Loader to do the following:

1. Load data across a network. This means that you can run the SQL*Loader client on a different system from the one that is running the SQL*Loader server.
2. Load data from multiple datafiles during the same load session.
3. Load data into multiple tables during the same load session.
4. Specify the character set of the data.
5. Selectively load data (you can load records based on the records' values).
6. Manipulate the data before loading it, using SQL functions.
7. Generate unique sequential key values in specified columns.
8. Use the operating system's file system to access the datafiles.
9. Load data from disk, tape, or named pipe.
10. Generate sophisticated error reports, which greatly aid troubleshooting.
11. Load arbitrarily complex object-relational data.
12. Use secondary datafiles for loading LOBs and collections.
13. Use either conventional or direct path loading. While conventional path loading is very flexible, direct path loading provides superior loading performance.
    

A typical SQL*Loader session takes as input a control file, which controls the behavior of SQL*Loader, and one or more datafiles. The output of SQL*Loader is an Oracle database (where the data is loaded), a log file, a bad file, and potentially, a discard file.


SQL*Loader Parameters

SQL*Loader is invoked when you specify the sqlldr command and, optionally, parameters that establish session characteristics. In situations where you always use the same parameters for which the values seldom change, it can be more efficient to specify parameters using the following methods, rather than on the command line:

1. Parameters can be grouped together in a parameter file. You could then specify the name of the parameter file on the command line using the PARFILE parameter.
2. Certain parameters can also be specified within the SQL*Loader control file by using the OPTIONS clause.
   

Parameters specified on the command line override any parameter values specified in a parameter file or OPTIONS clause.


SQL*Loader Control File

The control file is a text file written in a language that SQL*Loader understands. The control file tells SQL*Loader where to find the data, how to parse and interpret the data, where to insert the data, and more. Although not precisely defined, a control file can be said to have three sections. The first section contains session-wide information, for example:

1. Global options such as bindsize, rows, records to skip, and so on.
2. INFILE clauses to specify where the input data is located.
3. Data to be loaded.
   

The second section consists of one or more INTO TABLE blocks. Each of these blocks contains information about the table into which the data is to be loaded, such as the table name and the columns of the table. The third section is optional and, if present, contains input data. Some control file syntax considerations to keep in mind are:

1. The syntax is free-format (statements can extend over multiple lines).
2. It is case insensitive; however, strings enclosed in single or double quotation marks are taken literally, including case.
3. In control file syntax, comments extend from the two hyphens (--) that mark the beginning of the comment to the end of the line. The optional third section of the control file is interpreted as data rather than as control file syntax; consequently, comments in this section are not supported.
4. The keywords CONSTANT and ZONE have special meaning to SQL*Loader and are therefore reserved. To avoid potential conflicts, Oracle recommends that you do not use either CONSTANT or ZONE as a name for any tables or columns.

Data Fields

Once a logical record is formed, field setting on the logical record is done. Field setting is a process in which SQL*Loader uses control-file field specifications to determine which parts of logical record data correspond to which control-file fields. It is possible for two or more field specifications to claim the same data. Also, it is possible for a logical record to contain data that is not claimed by any control-file field specification. Most control-file field specifications claim a particular part of the logical record. This mapping takes the following forms:

1. The byte position of the data field's beginning, end, or both, can be specified. This specification form is not the most flexible, but it provides high field-setting performance. The strings delimiting (enclosing and/or terminating) a particular data field can be specified. A delimited data field is assumed to start where the last data field ended, unless the byte position of the start of the data field is specified.
2. The byte offset and/or the length of the data field can be specified. This way each field starts a specified number of bytes from where the last one ended and continues for a specified length.
3. Length-value datatypes can be used. In this case, the first n number of bytes of the data field contain information about how long the rest of the data field is.
   

Data Conversion and Datatype Specification

During a conventional path load, data fields in the datafile are converted into columns in the database (direct path loads are conceptually similar, but the implementation is different). There are two conversion steps:

1. SQL*Loader uses the field specifications in the control file to interpret the format of the datafile, parse the input data, and populate the bind arrays that correspond to a SQL INSERT statement using that data.
2. The Oracle database accepts the data and executes the INSERT statement to store the data in the database.
   

The Oracle database uses the datatype of the column to convert the data into its final, stored form. Keep in mind the distinction between a field in a datafile and a column in the database. Remember also that the field datatypes defined in a SQL*Loader control file are not the same as the column datatypes.


Discarded and Rejected Records

Records read from the input file might not be inserted into the database. Such records are placed in either a bad file or a discard file.

The Bad File

The bad file contains records that were rejected, either by SQL*Loader or by the Oracle database. If you do not specify a bad file and there are rejected records, then SQL*Loader automatically creates one. It will have the same name as the data file, with a.bad extension. Some of the possible reasons for rejection are discussed in the next sections.

SQL*Loader Rejects

Datafile records are rejected by SQL*Loader when the input format is invalid. For example, if the second enclosure delimiter is missing, or if a delimited field exceeds its maximum length, SQL*Loader rejects the record. Rejected records are placed in the bad file.

Oracle Database Rejects

After a datafile record is accepted for processing by SQL*Loader, it is sent to the Oracle database for insertion into a table as a row. If the Oracle database determines that the row is valid, then the row is inserted into the table. If the row is determined to be invalid, then the record is rejected and SQL*Loader puts it in the bad file. The row may be invalid, for example, because a key is not unique, because a required field is null, or because the field contains invalid data for the Oracle datatype.

Please go SQL*Loader Part #2 for more details


Related Topic:

1. SQL*Loader Part #1
2. SQL*Loader Part #2

SQL*Loader Part #2

The Discard File

As SQL*Loader executes, it may create a file called the discard file. This file is created only when it is needed, and only if you have specified that a discard file should be enabled. The discard file contains records that were filtered out of the load because they did not match any record-selection criteria specified in the control file. The discard file therefore contains records that were not inserted into any table in the database. You can specify the maximum number of such records that the discard file can accept. Data written to any database table is not written to the discard file.

Log File and Logging Information

When SQL*Loader begins execution, it creates a log file. If it cannot create a log file, execution terminates. The log file contains a detailed summary of the load, including a description of any errors that occurred during the load.

Conventional Path Loads

During conventional path loads, the input records are parsed according to the field specifications, and each data field is copied to its corresponding bind array. When the bind array is full (or no more data is left to read), an array insert is executed. SQL*Loader stores LOB fields after a bind array insert is done. Thus, if there are any errors in processing the LOB field (for example, the LOBFILE could not be found), the LOB field is left empty. Note also that because LOB data is loaded after the array insert has been performed, BEFORE and AFTER row triggers may not work as expected for LOB columns. This is because the triggers fire before SQL*Loader has a chance to load the LOB contents into the column. For instance, suppose you are loading a LOB column, C1, with data and that you want a BEFORE row trigger to examine the contents of this LOB column and derive a value to be loaded for some other column, C2, based on its examination. This is not possible because the LOB contents will not have been loaded at the time the trigger fires.

Direct Path Loads

A direct path load parses the input records according to the field specifications, converts the input field data to the column datatype, and builds a column array. The column array is passed to a block formatter, which creates data blocks in Oracle database block format. The newly formatted database blocks are written directly to the database, bypassing much of the data processing that normally takes place. Direct path load is much faster than conventional path load, but entails several restrictions.

A parallel direct path load allows multiple direct path load sessions to concurrently load the same data segments (allows intrasegment parallelism). Parallel direct path is more restrictive than direct path.


Invoking SQL*Loader

When you invoke SQL*Loader, you can specify certain parameters to establish session characteristics. Parameters can be entered in any order, optionally separated by commas. You specify values for parameters, or in some cases, you can accept the default without entering a value. For example:

SQLLDR CONTROL=sample.ctl, LOG=sample.log, BAD=baz.bad, DATA=etc.dat
USERID=scott/tiger, ERRORS=999, LOAD=2000, DISCARD=toss.dsc, DISCARDMAX=5

If you invoke SQL*Loader without specifying any parameters, SQL*Loader displays a help screen similar to the following. It lists the available parameters and their default values.


Alternative Ways to Specify Parameters

If the length of the command line exceeds the size of the maximum command line on your system, you can put certain command-line parameters in the control file by using the OPTIONS clause. You can also group parameters together in a parameter file. You specify the name of this file on the command line using the PARFILE parameter when you invoke SQL*Loader.

Examples:

1. One can load data into an Oracle database by using the sqlldr utility. Invoke the utility as follows:

sqlldr username@server/password control=loader.ctl
sqlldr username/password@server control=loader.ctl

This sample control file (loader.ctl) will load an external data file containing delimited data:
-------------------------------------------------------------------------------------------------------------------------------------
load data
infile 'c:\data\mydata.csv'
into table emp
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )
-----------------------------------------------------------------------------------------------------------------------------------
The mydata.csv file may look like this:

10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20
-----------------------------------------------------------------------------------------------------------------------------------

2. Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file:
--------------------------------------------------------------------------------------------------------------------------------------
load data
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
-------------------------------------------------------------------------------------------------------------------------------------

3. Data can be modified as it loads into the Oracle Database. One can also populate columns with static or derived values. However, this only applies for the conventional load path (and not for direct path loads). Here are some examples:
--------------------------------------------------------------------------------------------------------------------------------
LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no "my_db_sequence.nextval",
region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
--------------------------------------------------------------------------------------------------------------------------------------
LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state
)
------------------------------------------------------------------------------------------------------------------------------------

For more examples please go here :

Related Topic:

1. SQL*Loader Part #1
2. SQL*Loader Part #2
   

Resizing Temporary Tablespace

In many database configurations, the DBA will choose to allow their temporary tablespace (actually the tempfile(s) for the temporary tablespace) to autoextend. A runaway query or sort can easily chew up valuable space on the disk as the tempfiles(s) extends to accommodate the request for space. If the increase in size of the temporary tablespace (the tempfiles) gets exceedingly large because of a particular anomaly, the DBA will often want to resize the temporary tablespace to a more reasonable size in order to reclaim that extra space. The obvious action would be to resize the tempfiles using the following statement:

SQL> alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M;
alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Ouch. You next bounce the database and attempt the same statement only to be greeted with the same error!

Several methods exist to reclaim the used space used for a larger than normal temporary tablespace depending on which release of Oracle you are running. The method that exists for all releases of Oracle is to simply drop and recreate the temporary tablespace back to its original (or another reasonable) size. If you are using Oracle9i or higher, you can apply another method which is to drop the large tempfile (which will drop the tempfile from the data dictionary AND the O/S file system) using the alter database tempfile '' drop including datafiles; command. Each method is explained below.

Dropping / Recreating Temporary Tablespace Method

Keep in mind that the procedures documented here for dropping and recreating your temporary tablespace should be performed during off hours with no users logged on performing work.

If you are working with a temporary tablespace in Oracle8i or a temporary tablespace in Oracle9i that is NOT the default temporary tablespace for the database, this process is straight forward. Simply drop and recreate the temporary tablespace:

SQL> DROP TABLESPACE temp;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

Oracle9i OR Above Default Temporary Tablespace

The procedures above document how to drop a temporary tablespace that is not the default temporary tablespace for the database. You will know fairly quickly if the tablespace is a default temporary tablespace when you are greeted with the following exception:

SQL> DROP TABLESPACE temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

In cases where the temporary tablespace you want to resize (using the drop/recreate method) is the default temporary tablespace for the database, you have several more steps to perform, all documented below. The first step you need to perform is create another temporary tablespace (lets call it TEMP2). Next step is making TEMP2 the default temporary tablespace for the database. Drop / recreate the TEMP tablespace to the size you want. Finally, make the newly created TEMP tablespace your default temporary tablespace for the database and drop the TEMP2 tablespace. A full example session is provided below:

SQL> CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.


SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.


SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.


Related Topic :

1. Move Datafiles
   

Parse To Execute Ratio

All Oracle SQL statements must be parsed at the first time that they execute. Parsing involves a syntax check, a semantic check (against the dictionary), the creation of a decision tree, and the generation of the lowest cost execution plan. Once the execution plan is created, it is stored in the library cache (part of the shared pool) to facilitate re-execution. There are two types of parses:

Hard parse

A new SQL statement must be parsed from scratch. If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1% (high hard parses), often indicating non-reentrant SQL that does not use host variables (Bind Variables).

Soft parse

A reentrant SQL statement where the only unique feature are host variables. The best-case scenario is a parse to execute ratio of 100% which would indicate an application with fully reentrant SQL that parses SQL once and executes many times.




In a real database, some SQL statements will be fully reentrant (execute to parse = 100%), while others must be re-parsed for every execution (execute to parse = 1%). You can see this is the instance efficiency of any STATSPACK and AWR report.


High parses suggests that your system has many incoming unique SQL statements, or that your SQL is not reentrant (i.e. literal values in the WHERE clause, not using bind variables). A hard parse is expensive because each incoming SQL statement must be re-loaded into the shared pool; with the associated overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables.



If the execute to parse ratio is too low, it is possible that the application is not using shareable SQL, or the database has sub-optimal parameters that are reducing the effectiveness of cursor sharing. A problem like excessive parsing is likely to manifest itself as additional network traffic between the application server and clients. The additional parse activity may also show up as a marked increase in CPU consumption on the database server.

Here is a simple sql query which describe the parse call and execution of current sql queries :

Select x.sql_text , x.parse_calls , x.executions
,round( 100*(1-( x.parse_calls / x.executions )),2) execute_to_parse_ratio
FROM v$sql x
WHERE x.parse_calls >0
AND x.executions !=0
AND x.parsing_schema_name='EMP'
ORDER BY execute_to_parse_ratio ;

SELECT x.executions ,
  x.parse_calls ,
  ROUND( 100*(1-(x.parse_calls/x.executions)),2) execute_to_parse_ratio
  , x.sql_text
FROM
  (SELECT DBMS_LOB.SUBSTR (sq.sql_text,500,1) sql_text ,
    SUM(st.executions_delta) executions ,
    SUM(st.parse_calls_delta) parse_calls
  FROM DBA_HIST_SQLSTAT st ,
    DBA_HIST_SQLTEXT sq ,
    DBA_HIST_SNAPSHOT s
  WHERE s.snap_id           = st.snap_id
  AND s.begin_interval_time > sysdate-14
  AND s.end_interval_time   < sysdate
  AND st.sql_id             = sq.sql_id
  AND st.parsing_schema_name='EMP'
  GROUP BY DBMS_LOB.SUBSTR (sq.sql_text,500,1)
  ) x
WHERE x.executions != 0
AND ROUND( 100*(1-(x.parse_calls/x.executions)),2) < 10
ORDER BY execute_to_parse_ratio ;

Related Topics :

1. Bind Variable

Top N SQL Query Run within 30 minuts

When the database load increase rapidly it is even hard to log in Enterprise Manager (EM) to see what is the actual cause by seeing top activity or generating AWR report. Most of the time bad SQl is behind such problem. Here is a script which give you Top 20 (sort by elapsed time) SQL quer run on last 30 minutes

SELECT x.sql_id,x.sql_text,x.users,x.executions,x.disk_reads
,x.buffer_gets,x.cpu_time,x.elapsed_time FROM
(select sql_id,sql_text,PARSING_SCHEMA_NAME users
,executions, disk_reads,buffer_gets
,DECODE (executions,0,0,round(((cpu_time/1000000)/executions),2)) cpu_time
,DECODE (executions,0,0,round(((elapsed_time/1000000)/executions),2)) elapsed_time
from v$sqlarea
where parsing_user_id !=3D
and
LAST_ACTIVE_TIME<(sysdate-60/2600)
)x WHERE rownum <=20
ORDER BY x.elapsed_time DESC;

Parent Table locket while inserting in child table !

The parent table lock with LMODE=3 when an insert command is issued on its child table. An index is also present on the foreign key , but still a table level lock apply on the parent table. Because there is a constraint that requires that the parent exist before the child can be inserted, Oracle locks the parent table to keep the parent record from being deleted while the child record is being inserted. This is a well known issue on Oracle.

Analytical SQL functions

Oracle has introduced some exciting extensions to ANSI SQL to allow us to quickly compute aggregations and rollups. These new statements include:

1. rollup
2. cube
   

These simple SQL operators allow us to create easy aggregations directly inside the SQL without having to employ SQL*Plus break and compute statements. Let’s start by examining the ROLLUP syntax.

Tabular aggregates with ROLLUP

ROLLUP enables an SQL statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use.

SELECT deptno, job,count(*), sum(sal) FROM emp GROUP BY ROLLUP(deptno,job);


DEPTNO JOB COUNT(*) SUM(SAL)
--------- --------- --------- ---------
10 CLERK 1 1300
10 MANAGER 1 2450
10 PRESIDENT 1 5000
10 3 8750
20 ANALYST 2 6000
20 CLERK 2 1900
20 MANAGER 1 2975
20 5 10875

cross-tabular reports with CUBE

In multidimensional jargon, a “cube” is a cross-tabulated summary of detail rows. CUBE enables a SELECT statement to calculate subtotals for all possible combinations of a group of dimensions. It also calculates a grand total. This is the set of information typically needed for all cross-tabular reports, so CUBE can calculate a cross-tabular report with a single select statement.

Note in the example below that totals are calculated for each department, and also for each job category.

SELECT deptno,job,count(*),sum(sal) FROM emp GROUP BY CUBE(deptno,job);

DEPTNO JOB COUNT(*) SUM(SAL)
--------- --------- --------- ---------
10 CLERK 1 1300
10 MANAGER 1 2450
10 PRESIDENT 1 5000
10 3 8750
20 ANALYST 2 6000
20 CLERK 2 1900
20 MANAGER 1 2975
20 5 10875
30 CLERK 1 950
30 MANAGER 1 2850
30 SALESMAN 4 5600
30 6 9400
ANALYST 2 6000
CLERK 4 4150
MANAGER 3 8275
PRESIDENT 1 5000
SALESMAN 4 5600
14 29025

Table Compression

Now a days Most systems usually involve large amounts of data stored in a few very large tables. As these systems evolve, the demand on disk space will grow quickly. In today's environment, data warehouses of hundreds of terabytes have become increasingly common. To manage disk capacity, the table compression feature introduced in Oracle9i Release 2 can significantly reduce the amount of disk space used by database tables and improve query performance in some cases.

Working Mechanism

The Oracle9i Release 2 table compression feature works by eliminating duplicate data values found in database tables. Compression works at the database block level. When a table is defined as compressed, the database reserves space in each database block to store single copies of data that appear in multiple places within that block. This reserved space is called the symbol table. Data tagged for compression is stored only in the symbol table and not in the database rows themselves. As data tagged for compression appears in a database row, the row stores a pointer to the relevant data in the symbol table, instead of the data itself. The space savings come from eliminating redundant copies of data values in the table. The effects of table compression are transparent to a user or an application developer. Developers access a table the same way regardless of whether a table is compressed or not, so SQL queries don't have to change once you decide to compress a table. Table compression settings are usually configured and managed by database administrators or architects, with little involvement from developers or users.

Create a Compressed Table

To create a compressed table, use the COMPRESS keyword in the CREATE TABLE statement. The COMPRESS keyword directs Oracle Database to store rows in the table in compressed format wherever possible. The following is an example of the CREATE TABLE COMPRESS statement:

CREATE TABLE EMP (
ID NUMBER NOT NULL,
NAME VARCHAR2(50) NOT NULL,
SAL NUMBER NOT NULL,
JOIN_DATE DATE NOT NULL,
ADDRESS VARCHAR2(100)
) COMPRESS;

Alternatively, you can use the ALTER TABLE statement to change the compression attribute of an existing table, as in the following:

ALTER TABLE EMP COMPRESS;

To determine whether a table has been defined using COMPRESS, query the USER_TABLES data dictionary view and look at the COMPRESSION column, as in the example below:

SELECT TABLE_NAME, COMPRESSION FROM USER_TABLES WHERE TABLE_NAME='EMP';

TABLE_NAME COMPRESSION
------------------ -----------
EMP ENABLED


The COMPRESS attribute can also be defined at the tablespace level, either at the time of creation (by using CREATE TABLESPACE) or later (by using ALTER TABLESPACE). The COMPRESS attribute has inheritance properties similar to those of storage parameters. When a table is created in a tablespace, it inherits the COMPRESS attribute from the tablespace. To determine whether a tablespace is defined using COMPRESS, query the DBA_ TABLESPACES data dictionary view and look at the DEF_TAB_COMPRESSION column, as in the following example:

SELECT TABLESPACE_NAME,DEF_TAB_COMPRESSION FROM DBA_TABLESPACES;

TABLESPACE_NAME DEF_TAB_COMPRESSION
--------------- -------------------
DATA_TS_01 DISABLED
INDEX_TS_01 DISABLED

As you might expect, you can still explicitly compress or uncompress a table in a tablespace, regardless of the COMPRESS value at the tablespace level.

Loading Data into a Compressed Table

Note that when you specify COMPRESS as shown above, you aren't actually compressing any data. The commands above only modify a data dictionary setting. Data isn't actually compressed until you load or insert data into a table. Furthermore, to ensure that data is actually compressed, you need to use a proper method to load or insert data into the table. Data compression takes place only during a bulk load or bulk insert process, using one of the following four methods:

1. Direct path SQL*Loader
2. Serial INSERT with an APPEND hint
3. Parallel INSERT
4. CREATE TABLE ... AS SELECT


Note: If you don't use the correct loading or INSERT method, the data in the table will remain uncompressed, even if the table is defined using COMPRESS. For example, if you use conventional path SQL*Loader or regular INSERT statements, data will not be compressed.

When to Use Table Compression

The way that Oracle Database chooses to compress or not compress table data has implications for the kind of applications best suited for table compression. As described above, data in a table defined using COMPRESS gets compressed only if it is loaded using direct path mode or inserted using append or parallel mode. Data inserted through regular insert statements will remain uncompressed. In online transaction processing (OLTP) systems, data is usually inserted using regular inserts. As a result, these tables generally do not get much benefit from using table compression. Table compression works best on read-only tables that are loaded once but read many times. Furthermore, updating data in a compressed table may require rows to be uncompressed, which defeats the purpose of compression. As a result, tables that require frequent update operations are not suitable candidates for table compression.

Finally, consider the effects of row deletion on the use of table compression. When you delete a row in a compressed table, the database frees up the space occupied by the row in the database block. This free space can be reused by any future insert. However, since a row inserted in conventional mode isn't compressed, it is unlikely that it would fit in the space freed up by a compressed row. High volumes of successive DELETE and INSERT statements may cause fragmentation and waste even more space than would be saved using compression.


Compressing an Existing Uncompressed Table

ALTER TABLE EMP MOVE COMPRESS;

You can also use the ALTER TABLE ... MOVE statement to uncompress a table,

ALTER TABLE EMP MOVE NOCOMPRESS;

Note that the ALTER TABLE ... MOVE operation acquires an EXCLUSIVE lock on the table, which prevents any DML operation on the table while the statement executes. You can avoid this potential problem by using the online table redefinition feature of Oracle9i Database.


Compressing a Partitioned Table

ALTER TABLE EMP MOVE PARTITION EMP3_03 COMPRESS;

Benefits

The biggest reason to use table compression is to save storage space. A table in compressed form will usually occupy less space when compared to its uncompressed form. A compressed table can be stored in fewer blocks results in storage savings, but fewer blocks can mean performance improvements as well. Queries on a compressed table in an I/O bound environment will often complete more quickly, because they need to read fewer database blocks

Use Regular Expressions in SQL

A feature introduced in Oracle Database 10g vastly improves your ability to search and manipulate character data. This feature, regular expressions, is a notation for describing textual patterns. It has long been available in many programming languages and a number of UNIX utilities and now available in Oracle. Oracle's implementation of regular expressions comes in the form of various SQL functions and a WHERE clause operator.

What Is a Regular Expression?

A regular expression comprises one or more character literals and/or metacharacters. In its simplest format, a regular expression can consist only of character literals, such as the regular expression cat. It is read as the letter c followed by the letters a and t and this pattern matches strings such as cat. When you understand the meaning of the various metacharacters, you will see that regular expressions are powerful for isolating and replacing specific textual data. You can apply them in order to validate the formats of phone numbers, zip codes, email addresses, Social Security numbers, IP addresses, filenames and pathnames, and so on. Furthermore, you can locate patterns such as HTML tags, numbers, dates, or anything that fits any pattern within any textual data and replace them with other patterns.

To learn Regular Expression you can go here

Using Regular Expressions With Oracle Database 10g

To utilize the power of regular expressions, you can exploit the newly introduced Oracle SQL REGEXP_LIKE operator and the REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_REPLACE functions. You will see how this new functionality supplements the existing LIKE operator and the INSTR, SUBSTR, and REPLACE functions. In fact, they are similar to the existing operator and functions but now offer powerful pattern-matching capabilities. The searched data can be simple strings or large volumes of text stored in the database character columns. Regular expressions let you search, replace, and validate data in ways you have never thought of before, with a high degree of flexibility.

The REGEXP_LIKE Operator

The following SQL query's WHERE clause shows the REGEXP_LIKE operator, which searches the ZIP column for a pattern that satisfies the regular expression [^[:digit:]]. It will retrieve those rows in the ZIPCODE table for which the ZIP column values contain any character that is not a numeric digit.

SQL> SELECT zipFROM zipcode WHERE REGEXP_LIKE(zip, '[^[:digit:]]');

ZIP
-----
ab123
123xy
007ab
abcxy

This example of a regular expression consists only of metacharacter more specifically the POSIX character class digit delimited by colons and square brackets. The second set of brackets (as in [^[:digit:]]) encloses a character class list. As previously mentioned, this is required because you can use POSIX character classes only for constructing a character list.

For More Understanding go Here

The REGEXP_INSTR Function

This function returns the starting position of a pattern, so it works much like the familiar INSTR function. The main difference between the two functions is that REGEXP_INSTR lets you specify a pattern instead of a specific search string; thus providing greater versatility. The next example uses REGEXP_INSTR to return the starting position of the five-digit zip-code pattern within the string Joe Smith, 10045 Berry Lane, San Joseph, CA 91234. If the regular expression is written as [[:digit:]]{5}, you will get the house number's starting position instead of the zip code's, because 10045 is the first occurrence of five consecutive digits. Therefore, you must anchor the expression to the end of the line, as indicated with the $ metacharacter, and the function will display the starting position of the zip code regardless of the number of digits for the house number.

SQL> SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234','[[:digit:]]{5}$') AS rx_instr FROM dual;

RX_INSTR
----------
45

Writing More Complex Patterns Let's expand on the zip code pattern of the previous example to include an optional four digits. Your pattern may now look like this: [[:digit:]]{5}(-[[:digit:]]{4})?$. If your source string ends in either the 5-digit zip code or the 5-digit + 4 zip-code format, you'll be able to show the pattern's starting position.

SQL> SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234', ' [[:digit:]]{5}(-[[:digit:]]{4})?$') AS starts_at FROM dual;

STARTS_AT
----------
44

In this example the parenthesized subexpression (-[[:digit:]]{4}) is repeated zero or one times, as indicated by the ? repetition operator. Again, attempting to use the traditional SQL functions to accomplish the same result poses a challenge even to SQL experts. To better explain the various components of the regular expression please go Here

The REGEXP_SUBSTRFunction

The REGEXP_SUBSTR function, much like the SUBSTR function, extracts part of a string. In the following example, the string that matches the pattern , [^,]*, is returned. The regular expression searches for a comma followed by a space; then zero or more characters that are not commas, as indicated by [^,]*; and lastly looks for another comma. The pattern will look somewhat similar to a comma-separated values string.

SQL> SELECT REGEXP_SUBSTR('first field, second field , third field',', [^,]*,') FROM dual;

REGEXP_SUBSTR('FIR
------------------
, second field ,

The REGEXP_REPLACE Function

Let's first look at the traditional REPLACE SQL function, which substitutes one string with another. Assume your data has extraneous spaces in the text and you would like to replace them with a single space. With the REPLACE function, you need to list exactly how many spaces you want to replace. However, the number of extra spaces may not be the same everywhere in the text. The next example has three spaces between Joe and Smith. The REPLACE function's parameter specifies that two spaces should be replaced with one space. In this case, the result leaves an extra space where there were three spaces in the original string between Joe and Smith.

SQL> SELECT REPLACE('Joe Smith',' ', ' ')AS replace FROM dual;

REPLACE
---------
Joe Smith

The REGEXP_REPLACE function takes the substitution a step further. The following query replaces any two or more spaces with a single space. The ( ) subexpression contains a single space, which can be repeated two or more times, as indicated by {2,}.

SQL> SELECT REGEXP_REPLACE('Joe Smith','( ){2,}', ' ') AS RX_REPLACE FROM dual;

RX_REPLACE
----------
Joe Smith

For More Understanding go Here

Impotent SQL Queries

1. To see current Oracle Version :

SQL> SELECT version FROM v$instance;

VERSION
-----------------
10.2.0.1.0

2.

Virtual Indexes

For tuning SQL statements often requires the testing of alternate indexing strategies to see the affect on execution plans. Adding extra indexes to large tables can take a considerable amount of time and disk space. The additional indexes are available for use by other sessions, which may affect the performance of other parts of your application that you are not currently testing. This can be problematic when you are trying to identify problems on a production system.

In contrast to conventional indexes, a virtual index has no associated segment, so the creation time and associated disk space are irrelevant. In addition, it is not seen by other sessions, so it doesn't affect the normal running of your system. This article presents a simple example of how virtual indexes are used.


First, we create and populate a table

CREATE TABLE objects_tab AS SELECT * FROM user_objects;

Now we create an primary key on that table. So delete some rows where object_id is null.

DELETE from objects_tab where object_id is null;
COMMIT;

ALTER TABLE objects_tab ADD CONSTRAINT objects_tab_pk PRIMARY KEY (object_id);
EXEC DBMS_STATS.gather_table_stats('EMP', 'objects_tab', cascade=>TRUE);

If we query the table using the primary key, we can see this reflected in the execution plan.

SQL> SET AUTOTRACE TRACEONLY ;
SQL> SELECT * FROM objects_tab WHERE object_id = 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2097082964

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | OBJECTS_TAB_PK | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


If we query the table using a non-indexed column, we see a full table scan

SQL> SELECT * FROM objects_tab WHERE object_name = 'LOGIN';


Execution Plan
----------------------------------------------------------
Plan hash value: 821620785

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| OBJECTS_TAB | 1 | 87 | 11 (0)| 00:00:01 |
----------------------------------------------------------------------------

To create the virtual index on this column, simply add the NOSEGMENT clause to the CREATE INDEX statement.

SQL> CREATE INDEX objects_tab_object_name_vi ON objects_tab(object_name) NOSEGMENT;

Index Created

If we repeat the previous query we can see the virtual index is not visible to the optimizer.

SQL> SELECT * FROM objects_tab WHERE object_name = 'LOGIN';


Execution Plan
----------------------------------------------------------
Plan hash value: 821620785

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| OBJECTS_TAB | 1 | 87 | 11 (0)| 00:00:01 |
----------------------------------------------------------------------------

To make the virtual index available we must set the _use_nosegment_indexes parameter.

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session Altered

If we repeat the query we can see that the virtual index is now used.

SELECT * FROM objects_tab WHERE object_name = 'LOGIN';


Execution Plan
----------------------------------------------------------
Plan hash value: 4006507992

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | OBJECTS_TAB_OBJECT_NAME_VI | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------


The virtual index does not appear in the USER_INDEXES view, but it present in the USER_OBJECTS view.

SQL> SET AUTOTRACE OFF;
SQL> SELECT index_name FROM user_indexes WHERE INDEX_NAME = 'OBJECTS_TAB_OBJECT_NAME_VI';

no rows selected

SQL> SELECT object_name FROM user_objects WHERE object_type = 'INDEX'
AND OBJECT_NAME='OBJECTS_TAB_OBJECT_NAME_VI';

OBJECT_NAME
----------------------------------------------------------------------------------------------------
OBJECTS_TAB_OBJECT_NAME_VI

1 rows selected.

Statistics can be gathered on virtual indexes in the same way as regular indexes, but as we have seen previously, there will be no record of this in the USER_INDEXES view.

SQL> EXEC DBMS_STATS.gather_index_stats('EMP', 'objects_tab_object_name_vi');

PL/SQL procedure successfully completed.

Oracle will prevent us from creating another virtual index with the same column list, but it will allow us to create a real index with the same column list.

SQL> CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT;

CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT
*
ERROR at line 1:
ORA-01408: such column list already indexed


SQL> CREATE INDEX objects_tab_object_name_i ON objects_tab(object_name);

Index created.


Related Links

1. Create INDEX
2. Execution Plan

ALTER TABLE

Specifications of 'ALTER TABLE' are as follows

Adding Foreign key

ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (col1, col2)
REFERENCES table_2 (cola,colb);

Adding unique constraint

The following example adds a unique constraint on the column column_name in the table whose name is table_name. The name of the constraint is constraint_name.

ALTER TABLE table_name
add constraint constraint_name
unique (column_name)

Disabling constraints

Integrity constraints can be disabled with the ALTER TABLE command.

ALTER TABLE table-name disable constraint-specification;
ALTER TABLE table-name disable constraint constraint-name;

Adding new Column

ALTER TABLE foo_table add bar_column char(1);
ALTER TABLE foo_table add (bar_column1 char(1),bar_column2 char(1));

Modifying a column

Renaming a column name

ALTER TABLE some_table rename column column_name to new_column_name;

Changing a column's type

A column's type can be changed if the column is empty within the entire table, otherwise Oracle gives an ORA-01440: column to be modified must be empty to decrease precision or scale.

ALTER TABLE some_table modify (column_name number);

Changing null to not null or vice versa

ALTER TABLE some_table modify (column_name not null);

ALTER TABLE some_table modify col_not_null number null;

Specifying tablespace for index

ALTER TABLE tbl add constraint pk_tbl
primary key (col_1, col_2)
using index tablespace ts_idx

Removing a constraint

ALTER TABLE table_name drop constraint constraint_name;

ORA-02082

Message

ORA-02082: a loopback database link must have a connection qualifier

Cause

Attempt to create a database link with the same name as the current database.

Action:

A loopback database link needs a trailing qualifier, that is if your db_link name is MYDB.ORACLE.COM@INST1 - then '@INST1' is the qualifier and 'MYDB.ORACLE.COM' is the current database name

Related Links
Create database link