ORATraining Blog

Using SQL*Plus

SQL*Plus is a command-line tool that provides access to the Oracle RDBMS.

SQL*Plus enables you to:

• Enter SQL*Plus commands to configure the SQL*Plus environment
• Startup and shutdown an Oracle database
• Connect to an Oracle database
• Enter and execute SQL commands and PL/SQL blocks
• Format and print query results

SQL*Plus is available on several platforms. In addition, it has a web-based user

interface, iSQL*Plus.

SQL*Plus is a client terminal software allowing users to interact with Oracle server to manipulate data and data structures. Users type in SQL statements in SQL*Plus that send statements to Oracle server. Oracle server then validates and executes the statements on its databases. The query results are returned to SQL*Plus and displayed to the user.  Besides sending SQL statements to the server, SQL*Plus also saves them into a local buffer and allow users to view and change the statements. The following figure illustrates the process. Read more…

Oracle Data Export and Import utility

The Import and Export utilities work together; Export sends database definitions and actual data to an export file and Import can read the file to perform many different tasks. You can use Export and Import for many important database tasks, such as restoring a table, generating CREATE scripts, copying data among Oracle databases, migrating among Oracle versions, and moving tables from one schema to another.

Oracle’s export (exp) and import (imp) utilities are used to perform logical database backup and recovery. When exporting, database objects are dumped to a binary file which can then be imported into another Oracle database.

These utilities can be used to move data between different machines, databases or schema. However, as they use a proprietary binary file format, they can only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database.

Various parameters are available to control what objects are exported or imported. To get a list of available parameters, run the exp or imp utilities with the help=yes parameter i.e. “exp help=yes” or “imp help=yes“.

The export/import utilities are commonly used to perform the following tasks: Read more…

SQL*Loader

SQL*Loader is a high-speed data loading utility that loads data from external files into tables in an Oracle database. SQL*Loader accepts input data in a variety of formats, can perform filtering, and can load data into multiple Oracle database tables during the same load session. SQL*Loader is an integral feature of Oracle databases and is available in all configurations.

Commands and Parameters

SQL*Loader can be invoked in one of three ways:

sqlldr

sqlldr keyword=value [keyword=value ...]

sqlldr value [value ...]

Valid Keywords/Parameters: Read more…

/*+ hint */

/*+ hint(argument) */

/*+ hint(argument-1 argument-2) */

All hints except /*+ rule */ cause the CBO to be used. Therefore, it is good practise to analyze the underlying tables if hints are used (or the query is fully hinted.

There should be no schema names in hints. Hints must use aliases if alias names are used for table names. So the following is wrong:

select /*+ index(scott.emp ix_emp) */ from scott.emp emp_alias

better:

select /*+ index(emp_alias ix_emp) */ … from scott.emp emp_alias

Why using hints

Read more…

(Based on Oracle Metalink note: 783188.1 as on 3-Mar-2009)

This document is divided into the following sections:

• Section 1: Terminology
• Section 2: Supported Scenarios
• Section 3: References

The most up-to-date version of this document can be obtained in Note 783188.1.

Section 1: Terminology

Cloning an Oracle E-Business Suite system that uses an Oracle Real Application Clusters (Oracle RAC) enabled database involves numerous different technology components and steps, and would therefore be a complex, error-prone process if carried out manually.

To speed up, simplify, and enhance reliability of the process, Oracle offers the following tools for use in cloning RAC Enabled Oracle E-Business Suite systems:

• Rapid Clone – Command-line tool
• Oracle Application Management Pack for Oracle E-Business Suite (AMP) – GUI-based tool used for automated end-to-end cloning

This document provides details of the RAC scenarios that are currently certified for cloning using these tools. Other scenarios are planned as shown.

Note: Scenarios not shown are not supported.

Read more…