Use ASH for troubleshooting a long running query

You wont believe it how useful ASH (Active Session History) can be.

Step 1: Find the sql_id of the long_running query (the following lists the top 100 most -busy queries. I.e., queries that are in the most ASH samples either on cpu or actively waiting on something) :

[Update]: In this newer post we describe how one can use ASH and SQL Monitoring to tune a complex query that does not even finish!

Two ways to make a table read-only

Here are two ways to make a table read-only:
First lets do it the 11g way:


nikos@NIKOSDB> create table lala (x int, y int)
  2  /

Table created.

nikos@NIKOSDB> insert into lala values (1,1)
  2  /

1 row created.

nikos@NIKOSDB> insert into lala values (2,2)
  2  /

1 row created.

nikos@NIKOSDB> commit
  2  /

Commit complete.

Moving a table and rebuilding the indexes at the same time

After moving a table (ALTER TABLE XXX MOVE ), because the physical location of the rows changes, i.e., the rowids of the records change, all indexes on the table will become "UNUSABLE". This means that we have to manually rebuilt them (ALTER INDEX YYY REBUILD).

Is it  possible to move a table and rebuild the indexes at the same time with a single move statement? (Note: I am not considering the case of using the DBMS_REDEFINITION package for on-line table redefinition)

Hello World!

Hello, World!

This is the fist blog post in Oracle Data Warehousing Stories. This is a blog mainly intended for personal use where little bits from my everyday Oracle Data Warehousing experiences will be published, as well as Oracle-related stuff from my readings, other blogs etc. It is just a place to gather all this info and have it easily accessible as well as share it with anyone else who cares.

As Tom Kyte says, you can learn or relearn something new about Oracle everyday. I am no Oracle guru, nor interested to become one,  I just love walking on this Oracle learning path everyday ...

Cheers.