Oracle Logbook

April 30, 2008

Oracle 12v the next generation

Filed under: Uncategorized — Helio Dias @ 4:17 pm

I been thinking about the Oracle letters ,i (8i and 9i) then (10g and 11g)

So seems to me that Oracle 12 will have a new letter, and if you check the release 11g you´ll see a lot of reference about virtual things (Index, RMAN Virtual catalog) , and more…

Two months ago i installed a lot of Oracle instances on vmware environment (Win2003) . Why i didn´t simply install all instances on a real machine? MANAGEMENT is the answer, how can guarantee that one instance wouldn´t using resources of whole machine? I know i can use resource manager to help with this, but on a Virtual environment is much more easy, you just create you Virtual Machine with the resource , and voila.

So i believe that Oracle 12 will be the first v, fully integrated with Oracle VM.

Let´s wait.

April 29, 2008

Oracle on Vmware

Filed under: Uncategorized — Helio Dias @ 6:37 pm

Amazing is the only word to describe the performance of Oracle runing
on VmWare ESX.
The environment:
Dell Quad Core, 16GB RAM, clock 3GB
Storage: Hitachi

4 Luns
Windows 2003, Oracle 10g

I did this query to put the machine to work hard.

SQL> select count(*) from dba_objects t1,dba_objects;


Elapsed: 00:02:8.21

But as we can see, it was not hard enought.

Then the unbelievable part:

We started the query again AND we started the VMotion (The Vmotion duration: 1 minute)
But the Oracle lost just 12 seconds.

SQL> select count(*) from dba_objects t1,dba_objects;


Elapsed: 00:02:20.76

Now we are testing 15 instances, with no kind of trouble.

The article below will help you to understand why it run so good

Ten Reasons Why Oracle Databases Run Best on VMWare

April 13, 2008

OCE – Oracle Certified Expert SQL Approved

Filed under: Uncategorized — Helio Dias @ 2:39 am

yeah, i passed today.

And i recommend this certification, cause is a good way to show that you are a little bit more than a database administrator.

Read more about Oracle database SQL certified expert program on:

Oracle Expert Program

Listen to a podcast about the Oracle Certified Expert Program

The Oracle Certified Expert program is an exciting new type of specialization certification program from Oracle, which grants credentials that recognize competency in specific technologies, architectures or domains not currently covered in the path-based Certified Associate and Certified Professional credentials.

Some of the parameters are:

  1. Niche oriented – based upon specific products or technologies rather than broad job roles.
  2. Credentials are independent of the current OCA, OCP, OCM hierarchy.
  3. Typically comprises a single comprehensive exam, rather than a series of exams.
  4. Competencies falling under the umbrella of the Expert program may range from foundational skills to mastery of advanced technologies.
  5. May have prerequisites such as an online exam, previous certification or training course attendance.

Oracle Database SQL Expert Exam

April 11, 2008

Grouping with Rollup

Filed under: Uncategorized — Helio Dias @ 4:41 pm

By James F. Koopmann,

Oracle Grouping with the ROLLUP Operation

Grouping and summing at multiple levels often takes an application to perform. Take a look at the ROLLUP operation within Oracle and perform these operations in a single SQL statement.

Grouping and in particular gathering aggregates across groups often brings confusion to many practitioners. This does not need to be the case if approached from a systematic fashion. This article will approach gathering aggregates from a simple GROUP BY operation and then extend into Oracle’s higher level grouping operations. In particular, the ROLLUP operation, which allows us to group and aggregate at different levels in a collection of similar rows. By similar I mean that two or more rows must have a common column or set of columns so that we can group them together for performing some form of aggregate function. In this article, I will use the SUM function for aggregating.

Once again, in this article, we revisit the DOG_ORIGIN table. This time however, I have added a new column entitled ‘POPULATION’ that holds a fictitious value for the number of a particular breed in their country of origin. These are just made up numbers so please do not hold me to them. To introduce this table again, Table 1. has the values as I have entered them.


Country Breed Breed_size Population
Germany German Shepherd Dog Big 12000
Germany Dobermann Big 8000
Germany Rottweiler Big 9000
USA Siberian Husky Medium 5000
USA Alaskan Malamute Medium 3000
USA American Bulldog Big 8000
Switzerland Bernese Mountain Dog Big 2000
Switzerland Saint Bernard Dog Big 2000
Switzerland Entlebuch Cattle Dog Medium 2000
Australia Australian Cattle Dog Medium 6000
Australia Jack Russell Terrier Small 7000

Early practitioners of SQL often are only introduced to answering questions like ‘How many dogs are in America? or ‘How many dogs are there in the world?. These two answers can quickly be answered by the following SQL. These two SQL statements simply investigate each row in the DOG_ORIGIN table and add together the population. In the case where country = ‘USA’ only the dogs in the USA are added together. For the second SQL statement, every row is added together, to give us the grand total of all dogs in the world.

SQL > SELECT SUM(population) FROM dog_origin WHERE country = 'USA';


SQL > SELECT SUM(population) FROM dog_origin;


Over time we soon learn that in order to gather aggregate values for each of the groupings we are interested in, in this case the country column, we can use the GROUP BY operation. This allows us to process and aggregate all grouping of a column without having to issue a separate SQL statement for each grouping we are interested in. Most of us have already used this in the past but it is introduced here because the ROLLUP operation is an extension of the GROUP BY statement. In TABLE 2., the simple question and answer of ‘How many dogs are there within each country? has been given. This can quickly be accomplished by using the simple GROUP BY operation on the country column and then adding the SUM function around the population column. This SQL statement groups all the rows within each country and ‘rolls’ that value up into a single result row that has the accumulated population for that country.

Table 2.
Simple GROUP BY operation

Simple GROUP BY operation
SQL>  select country,sum(population)
        from dog_origin
       group by country;

--------------- ---------------
Australia                 13000 <- group by country
Germany                   29000 <- group by country
Switzerland                6000 <- group by country
USA                       16000 <- group by country

Since the ROLLUP operation is just an extension of the GROUP BY operation we should be able to compose a statement that uses the ROLLUP operation and get an answer that resembles our previous simple GROUP BY operation. To understand how we might be able to do this we must first look at the ROLLUP option and its’ syntax.

The ROLLUP operation works on a set of columns you want to group. Just like the GROUP BY operation but aggregates a summary row for each group of columns supplied in its clause. From the most detailed to a grand total and has the following basic syntax format of:

GROUP BY ROLLUP ([columns of interest separated by commas])

The best way to explain how this will work is to supply a simple example. If you had 4 columns of interest in you ROLLUP operation say (c1, c2, c3, c4) Oracle will actually produce 5 different groupings (one more than the number of columns you provide). You can almost think of this as Oracle performing 5 different GROUP BY operations on your behalf.

Those grouping would be:

(c1, c2, c3, c4)
(c1, c2, c3)
(c1, c2)
(t1) where t1 is a GROUP BY across all rows

So if we translate the previous simple GROUP BY operation into a ROLLUP operation the statement becomes one like that in TABLE 3. As you can see we have basically the same result set but the totals were generated by the ROLLUP operation instead of the GROUP BY. This is noted by the ‘rollup by country’ notation I have added to the result set. The ROLLUP operation also provides a grand total line, which gives us the total dog population across all countries. If we relate this to the column example above we have told Oracle to perform a GROUP BY on (c1)country and (t1)grand total.

Simple ROLLUP operation to mimic a simple GROUP BY operation

SQL> select country,sum(population)
       from dog_origin
      group by rollup (country);

----------- ---------------
Australia             13000 <- rollup by country
Germany               29000 <- rollup by country
Switzerland            6000 <- rollup by country
USA                   16000 <- rollup by country
                      64000 <- grand total given

To go to the next level and show the real power of the ROLLUP operation we need to introduce at least one more column in the grouping. To do this we will add in the BREED_SIZE. The question now becomes ‘What is the population of dogs given the different breed sizes?. This is now a very simple SQL statement where all we need to do is add BREED_SIZE into the ROLLUP operation. Table 4. shows the SQL statement and the result set. Oracle now performs GROUP BY operations on (c1,c2)country & breed size, (c1)country, and (t1)grand total. I have added a rollup comment on the end of each row for clarification. So now, we can see the population by dog size in each country as well as the total number of dogs in each country and in the world.

Table 4.
Show population across breed size

SQL> select country,breed_size,sum(population)
       from dog_origin
      group by rollup (country,breed_size);

--------------- ---------- ---------------
Australia       Medium                6000 <- rollup by country & breed_size
Australia       Small                 7000 <- rollup by country & breed_size
Australia                            13000 <- rollup by country
Germany         Big                  29000 <- rollup by country & breed_size
Germany                              29000 <- rollup by country
Switzerland     Big                   4000 <- rollup by country & breed_size
Switzerland     Medium                2000 <- rollup by country & breed_size
Switzerland                           6000 <- rollup by country
USA             Big                   8000 <- rollup by country & breed_size
USA             Medium                8000 <- rollup by country & breed_size
USA                                  16000 <- rollup by country
                                     64000 <- grand total line

Another hybrid of the GROUP BY..ROLLUP operation is the ability to do partial rollups. The GROUP BY…ROLLUP clause actually has the following syntax. You can move columns between the GROUP BY list and the ROLLUP list to produce different, or partial, aggregated values.

GROUP BY (column[s]) ROLLUP (column[s])

So if we extend our previous column example where we used (c1,c2,c3,c4) we actually have at our disposal the following options when developing our SQL statement. Notice that as we move columns from the ROLLUP list, in the end, all we have left is a simple GROUP BY operation.

Those grouping would be:

group by rollup  (c1,c2,c3,c4) - full rollup option
group by  c1,rollup (c2,c3,c4) - removes grand total line (t1)
group by  c1,c2,rollup (c3,c4) - removes (t1) and group by (c1)
group by  c1,c2,c3,rollup (c4) - removes (t1) and group by (c1) & (c1,c2)
group by (c1,c2,c3,c4)         - removes (t1) and group by (c1),(c1,c2),&(c1,c2,c3)

For instance if we wanted to remove the grand total line from our dog origin example and only wanted to show detail for the dog population by country and breed size we would have the following SQL in Table 5.

Table 5.
Removal of grand total line

SQL >  select country,breed_size,sum(population)
         from dog_origin
        group by country, rollup (breed_size);

--------------- ---------- ---------------
Australia       Medium                6000
Australia       Small                 7000
Australia                            13000
Germany         Big                  29000
Germany                              29000
Switzerland     Big                   4000
Switzerland     Medium                2000
Switzerland                           6000
USA             Big                   8000
USA             Medium                8000
USA                                  16000

April 3, 2008

Creating RMAN Recovery Catalog

Filed under: Uncategorized — Helio Dias @ 4:23 pm

Create a tablespace e.g.  RMANTBS (RMAN alone is reserved word)

Create a user RMAN

Grant the user with connect,resource and recovery_catalog_owner

Then into the RMAN on catalog instance (RMAN catalog rman/password@connection)

create catalog tablespace RMANTBS   (don´t use dots or any sign)

Then into the RMAN on target instance

rman catalog rman/password@cataloginstance target rman/rman@targetinstance

register database

And if you deleted some archives then

crosscheck archivelog all (to syncronize it)

backup database plus archivelog  (make a complete set of backup, including redos generated during backup)

Create a free website or blog at