Skip navigation.

Blogs

Common errors seen when using OUTER-JOIN

Kevin Meade's picture

OUTER-JOIN is a very handy feature of SQL. But its value at solving certain classes of SQL query problems aside, it is also one of the most error prone Oracle SQL extensions we can put to use. Even advanced developers can make these mistakes. So let us discuss the error prone nature of this feature, and how to fix it.

Database Design Guidelines and Rules of Thumb

Thomas B. Cox's picture

This article discusses the various types of primary keys and list the advantages and disadvantages they provide.

What's blocking my lock?

Natalka Roshak's picture

If you've ever gotten a phone call from an annoyed user whose transaction just won't go through, or from a developer who can't understand why her application sessions are blocking each other, you know how useful it can be to identify not just whose lock is doing the blocking, but what object is locked. Even better, you can identify the exact row that a session is waiting to lock.

External Tables

Natalka Roshak's picture

External Tables let you query data in a flat file as though the file were an Oracle table. In 9i, only read operations were permitted; in 10g, you can also write out data to an external table, although you can't write to an existing table.

Engineering Better PL/SQL

Bert Scalzo's picture

PL/SQL is a great language. It's relatively simple to learn, is well integrated with the Oracle database, and can often be the most efficient way to perform complex or large scale database operations. In fact PL/SQL is so useful, it's difficult to believe that its origin is SQL*Forms -- and that PL/SQL was once was an optional cost add-on to the database. Working with Oracle over the past few decades has come a long way, and PL/SQL had evolved into a mature, robust and highly functional database language.

It's time to harden and patch

Natalka Roshak's picture

There have been two high-profile Oracle security flaws in the last few months. The first, which everyone reading this article has probably heard of, is the Voyager worm. The second, which is slightly less well-known, is a very severe security hole that lets anyone with a valid logon to an Oracle database -- including an unprivileged account with nothing but CONNECT privs -- execute arbitrary code as SYS. In this article, I'll look at the two security flaws and outline the steps you need to take to protect your databases from them.

Top-n query tuning in huge OLTP applications

Marcin Miller's picture

Top-n Query is one of the more advanced SQL problems. How does one retrieve N first (or least) rows from a record set? For example, how does one find the top five highest-paid employees in a given department?

10gR2 New Features: RAC Enhancements

Natalka Roshak's picture

10gR1 revamped Oracle clustered database management and features. 10gR2 builds on this success with a long list of improvements and enhancements. Oracle has streamlined the installation process and provided more filesystem options, made some performance and monitoring improvements, and improved manageability with a half-dozen administration enhancements. This article will take a look at the major changes.

Analyzing Materialized Views for Fast Refresh

Ellis Miller's picture

This article shows how materialized views can be analyzed and optimized to ensure they can be FAST REFRESHed. As tools, the DBMS_MVIEW.explain_mview procedure and the MV_CAPABILITIES_TABLE are used. In this particular case, refresh time was reduced from more than 14 hours to less than 2!

Creating Custom Aggregate Functions in C/ C++

The aim of this article is to describe the process of creating a user-defined aggregate function. Oracle 9i was used to prepare and test the function; some parts of the code may not work with Oracle versions older than 9i. This article gathers information that is needed to write the aggregate function in one place, and presents a clear step-by-step descripion of the process.