Oracle Database 10g Release 2: New Feature Highlights

Released in January 2004, Oracle Database 10g is the first relational database designed for Grid comp uting, delivering information with the highest quality of service, for the lowest possible cost. Only Oracle Database 10g delivers the clustering, workload management, and data center automation required for enterprise grid computing
as well as the ease of use required for small businesses. With Release 2, Oracle’s focus on improving efficiencies and reducing the cost of information management continues. This data sheet highlights some of the new features in Oracle Database 10g Release 2.

For more information, visit…

New Features in Oracle 10g for SQL*Plus and iSQL*Plus
By
James Koopmann

Don’t wait for your shop to get an Oracle 10g database up and running. Download the client software and start using these great new features that will make a few of your everyday tasks a bit more bearable.

Continue reading

Advertisements

Umm, I forgot my password

Probem solved, with the following convenient password reset procedure found in a large production database, with EXECUTE granted to PUBLIC and a handy public synonym:

CREATE OR REPLACE PROCEDURE 
    reset_user_password(p_username IN VARCHAR2) 
AS 
BEGIN 
    execute immediate 
    ' ALTER USER ' || upper(p_username) || 
    ' IDENTIFIED BY '||upper(p_username); 
END; 
/

Many thanks to Robert De Laat for this submission.

By Ankur Bhatia Posted in Oracle

Joins Explained

I must first give credit to Padders for spotting this article a couple of months back, and to Adrian for reminding me about it. He was poised to blog it when Thai pointed out that it had recently been added to Database Debunkings’ “To Laugh Or Cry” hall of shame. But of course there is no honour among bloggers and it’s damned funny, so here it is. Remember to print out the following definitions and keep them by your desk in case you forget what a complex right inner join is:

Don’t use joins
Joins become a problem with Oracle and SQL Server DBMSs because the two systems have fundamentally different approaches to the concept. Basically, joins don’t always work the same on every DBMS, and you can end up with unexpected result sets. Before we talk about the differences and how to get around them, you should understand the basic types of join clauses:

  • Join
    Joins are powerful SQL commands for creating tables from data that is retrieved from multiple sources.
  • Equi-Join
    Equi-Joins are formed by retrieving all the data from two separate sources and combining it into one, large table.

             

            Example

               

  • Inner/Outer Join
    Inner Joins are joined on the inner columns of two tables. Outer Joins are joined on the outer columns of two tables.

                        

          Outer Join Operator is plus sign (+).

            

          Example

               

  • Left/Right Join
    Left Joins are joined on the left columns of two tables. Right Joins are joined on the right columns of two tables.
  • Compound/Complex Joins
    There are also other kinds of joins—left/inner, left/outer, right/inner, and right/outer.

The important thing to remember about joins is to not use them if you want your application to work well with different database servers.
I love the way that it’s exactly the kind of thing you would make up, if you were 12 and had absolutely no idea how to answer a homework question.

While I’m at it I can’t help quoting a bit more, this time about the well-known ADD command:
ANSI SQL: CREATE, DROP, ADD, UPDATE, DELETE, INSERT, SELECT
As a rule, the only commands you should use are:

  • CREATE and DROP for Data Definition Language (DDL).
  • ADD, UPDATE, DELETE and INSERT for Data Manipulation Language (DML).
  • SELECT for data retrieval.
By Ankur Bhatia Posted in Oracle