What is a Oracle Database

Oracle is a relational database. In a relational database, all data is stored in two-dimensional tables that are composed of rows and columns. The Oracle Database enables you to store data, update it, and efficiently retrieve it.

Oracle provides software to create and manage the Oracle database. The database consists of physical and logical structures in which system, user, and control information is stored. The software that manages the database is called the Oracle database server. Collectively, the software that runs oracle and the physical database are called the Oracle database system.

Some of Oracle's tools to access the database and create programs are:

SQL*Plus has a command line interface. With it, you can access the database and write stored procedures, you can run SQL commands to retrieve data and you can run scripts of either SQL, PL/SQL or built-in SQL*Plus commands, or a mixture of those three things.

Oracle Developer is a 4GL GUI application Builder. With Developer, you can create forms, reports, and graphics. Oracle*Forms and Oracle*Reports are two components of Oracle Developer. Earlier versions created client-server applications, but the more recent versions create web applications that run under the Oracle Application Server (OAS). OAS is a web-based application server sold by Oracle. OAS is licensed separately and is very expensive (as are its closed source competitors). The current version is OAS 10g.

HTML DB is a fairly new application builder geared toward web development (added to the DB with release 9iR2). HTML DB does not need an application server. This tool runs from the database and can be presented to the web using the Apache web server that comes with the database. Since this is not an additional license, it provides a cheaper way to develop applications. Developer is a feature-rich thick client with all of the normal GUI widgets. HTML DB is HTML-based and is very thin and limited to the HTML provided widgets.

Oracle Enterprise Manager (OEM) is the Enterprise GUI tool to manage the database. From this tool, you can perform any action in the database that is required. This tool is primarily used for administration but can also be provided to developers for application tuning and monitoring. In Oracle 10g, OEM also provides Grid control.

Read next steps at http://Oracleonline.info

A Basic Introduction To SQL

The most basic syntax of SQL starts with a SELECT clause and a FROM clause. A SELECT clause tells the database WHAT you want to select.
A FROM clause tells the database where to find that data.From SQL*Plus, login as whatever user you have available and enter:SELECT table_name FROM all_tables;When I run my query,

I get a list of tables followed by the text "113 rows selected.". The SELECT clause is TABLE_NAME and the FROM clause is all_tables. The semi colon at the end tells Oracle that

you have completed your command. You MUST enter a semi-colon or a slash before Oracle will process you command.This was a very useful command but it could be made more useful. The list of tables was just that, a list.

If we were looking for a specific table, it would be hard with an unordered list like that. Let's try putting some order to it.Enter:

SELECT table_name FROM all_tables ORDER BY table_name /

Ok, there are three differences here. There is no semi colon but there is a slash, "/". The slash also tells Oracle that Oracle DBA have completed a command. The slash must appear ion column 1 on a line by itself. A semi colon can follow anywhere as long as there are no blank lines between it and the command.Type slash on a line by itself and Oracle will re-execute the previous command. Try it now.Ok,

we also added an ORDER BY clause to our command. The ORDER BY tells Oracle to, oddly enough, order the result set. What did it order by? TABLE_NAME.You can scroll through the list and find a specific table. Scroll through it now and find the table, DUAL. If you don't see this table, something is either not configured properly in your database or not configured properly with your user. DUAL is a special table provide by Oracle. It should always have one row and only have one column. That column, DUMMY, should always have the value 'X'.

Enter: SELECT table_name FROM all_tables WHERE table_name = 'DUAL' /


Read complete article at http://Oracleonline.info/what_is_sql.html

SQL Access Advisor

This Article will make you learn how Oracle 11g Sql Access Advisor helps in analyzing indexes, materialized views, and so on as it does in Oracle Database 10g, in Oracle Database 11g SQL Access Advisor also analyzes tables and queries to identify possible partitioning strategies—a great help when designing optimal schema. In Oracle Database 11g SQL Access Advisor can now provide recommendations with respect to the entire workload, including considering the cost of creation and maintaining access structures.

The Problem

Here's a typical problem. The SQL statement below is issued by the application. The query seems resource intensive and slow.

select store_id, guest_id, count(1) cnt
from res r, trans t
where r.res_id between 2 and 40
and t.res_id = r.res_id
group by store_id, guest_id
/

This SQL touches two tables, RES and TRANS; the latter is a child table of the former. Oracle DBA have been asked to find solutions to improve query performance—and SQL Access Advisor is the perfect tool for the job.

Oracle DBA can interact with the advisor either via command line or Oracle Enterprise Manager Database Control, but using the GUI provides somewhat better value by letting Oracle DBA visualize the solution and reducing many tasks to simple pointing and clicking.

To solve the problem in the SQL using SQL Access Advisor in Enterprise Manager, follow the steps below.

The first task is, of course, to fire up Enterprise Manager. On the Database Homepage, scroll down to the bottom of the page where Oracle DBA will see several hyperlinks.

Read full article at http://Oracleonline.info