Wednesday, April 23, 2008

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

Tuesday, April 22, 2008

Installing Oracle on Linux

This article is a comprehensive guide for installing Oracle9i Release 2 (9.2.0.4.0) on the Red Hat Linux Fedora Core 2 operating environment. Also included in this article is a detailed overview for applying the Oracle9i (9.2.0.5.0) patchset. Keep in mind the following assumptions throughout this article:

When installing Red Hat Linux Fedora Core 2, I install ALL components. (Everything). This makes it easier than trying to troubleshoot missing software components.

As of March 26, 2004, Oracle includes the Oracle9i database software with the 9.2.0.4.0 patchset already included. This will save considerable time since the patchset does not have to be downloaded and installed. We will, however, be applying the 9.2.0.5.0 patchset.

Although it is not required, it is recommend to apply the 9.2.0.5.0 patchset.

The post installation section includes steps for configuring the Oracle Networking files, configuring the database to start and stop when the machine is cycled, and other miscellaneous tasks.

Finally, at the end of this article, we will be creating an Oracle 9.2.0.5.0 database named ORA920 using supplied scripts. Swap Space Considerations

Ensure enough swap space is available.

Installing Oracle9i requires a minimum of 512MB of memory. (An inadequate amount of swap during the installation will cause the Oracle Universal Installer to either "hang" or "die")
To check the amount of memory / swap you have allocated, type either:
# free
- OR -
# cat /proc/swaps
- OR -
# cat /proc/meminfo grep MemTotal


Read the full article at http://Oracleonline.info/install_on_linux.html

Transaction Management with LogMiner and Flashback Data Archive

LogMiner is an often ignored yet very powerful tool in the Oracle Database. It is used to extract DML statements from the redo log files—the original SQL that caused the transaction and even the SQL that can undo the transactions. In Oracle Database 11g, however, Oracle Enterprise Manager has a graphical interface to extract transaction from the redo logs using LogMiner, which makes it extremely easy to use the tool to examine and rollback transactions. (Note: As in previous versions, you can continue to use the DBMS_LOGMNR package to perform command line-driven log mining if you wish.)

Let's see an example how this is done. To enable log mining, Oracle DBA need only minor supplemental logging enabled for the database or at least the table. Flashback Transaction requires primary key logging. To enable it for the entire database, issue the following commands:

SQL> alter database add supplemental log data; Database altered. SQL> alter database add supplemental log data (primary key) columns; Database altered.Now, consider the following statements issued by an application against your database:
SQL> insert into res values (100002,sysdate,12,1);

1 row created.

SQL> commit;

Commit complete.
SQL> update res set hotel_id = 13 where res_id = 100002;
1 row updated.

SQL> commit;

Commit complete.
SQL> delete res where res_id = 100002;

1 row deleted.

SQL> commit;

Commit complete.

Read on full article at http://Oracleonline.info/

Sunday, April 20, 2008

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

Saturday, April 19, 2008

Database Replay - New feature of Oracle 11g

What is your biggest concern when you need to make a change in the database—be it some minor change, such as altering initialization parameters and database attributes, or major but inevitable ones such as applying patchsets? What about for your upgrade to Oracle Database 11g itself?

For me, the biggest concern is the risk of the change "breaking" something. Even the most minor changes can have a domino effect, eventually producing a visible impact.
To minimize this risk, most shops make the change in a control environment similar to the production one, apply a workload similar to your production system's, and observe the impact. It's rather trivial, at least technologically speaking, to replicate your production system but reproducing the workload is a different story. That's easier said than done.

Most organizations attempt to do that using some third-party load generation tool that can run automatically to simulate real user activity. Although this approach may be adequate in most cases, it's never a truly faithful reproduction of your production database workload. These third-party tools merely execute a pre-written query several times with different parameters; you have to supply the query to the tool and give it a range of parameters that it can use randomly. This is not a representative workload of your production system but rather merely the running of a small part of your production workload executed several times—resulting in as little as 1 percent of your application code being tested. Worst of all, these tools require you to supply all the queries from the production workload yourself, which can take weeks or months for small applications or even up to a year for complex ones.

If you could, wouldn't it be a better approach to record all database operations—DML-related and otherwise—inside the database itself, and then replay them in the very sequence they occurred?

Enter Database Replay
In Oracle Database 11g, your wish is granted, and then some. The new Database Replay tool works like a DVR inside the database. Using a unique approach, it faithfully captures all database activity beneath the level of SQL in a binary format and then replays it either in the same database or in a different one (which is exactly what you would want to do prior to making a database change). You can also customize the capture process to include certain types of activity, or perhaps exclude some.

Database Replay delivers half of what Oracle calls Oracle Database 11g's "Real Application Testing" option; the other half is provided by another tool, SQL Performance Analyzer. The main difference between these two tools is the scope involved: whereas Database Replay applies to the capture and replay of all (subject to some filtering) activities in a database, SQL Performance Analyzer allows you to capture specific SQL statements and replay them. (You can't see or access specific SQLs captured in Database Replay, while in SQL Performance Analyzer you can.) The latter offers a significant advantage for SQL tuning because you can tweak the SQL statement issued by an application and assess its impact. (SQL Performance Analyzer is covered in a forthcoming installment in this series.)

Read more about this at http://Oracleonline.info/Oracle_11g_data_replay.html

Thursday, April 17, 2008

Learn Oracle - Become oracle DBA in 10 simple steps

I have created Learn Oracle website for anyone who wants to Learn Oracle and perform common day-to-day administrative tasks with the Oracle Database. Prior knowledge or experience with managing databases is not required. The only requirement is a basic knowledge of computers.The website is targeted toward the following groups of Oracle users:

-Developers wanting to acquire part-time DBA skills
-Anyone managing departmental servers
-Database administrators managing an Oracle database for small or medium business (SMB)

The website is equally useful for enterprise DBAs. It provides recommended best practices and describes efficient ways of performing administrative tasks with Oracle Enterprise Manager as the primary interface.

You can access the website at http://Oracleonline.info/