Kaley's (mostly) Oracle Database Blog
Hi! I'm Kaley Crum, Oracle DBA.
Welcome to my (mostly) Oracle database blog.

Author: Kaley

Are You Getting “Real” Read Consistency in Oracle updates? (Part II)

Earlier, I ran an experiment to see if we got what I considered to be true read consistency on a table.  Initially, admittedly, I didn’t think it was possible without first locking the entire table.  And I still believe that’s the case with updates I envisioned them initially.

I was, however, thrown for a loop when I discovered the SCN of the update that was being performed was after the SCN of an update that had been started after the update, which made me devise the theory that if an update reaches a row that has been updated since the start of the statement, the read consistency of the row is foiled and the query has to start back at the beginning to achieve true read-consistency.

Today, I’ll prove that theory either true or false.

Continue Reading

Are You Getting “Real” Read Consistency in Oracle Updates? (Part I)

I read one of Tom Kyte’s books awhile back, and he mentioned that in Oracle, everything is done with read consistency.  Everything from SELECTs, INSERTs, UPDATEs, to DELETEs…the whole 9 yards is done with a read consistency that looks at what the table looked like at the beginning of the query.  But is this true for update statements, where you have to both read and modify the data?

Continue Reading

DBMS_PARALLEL_EXECUTE Example

You may know that pulling data over a database link is serial, which is a great opportunity to take advantage of DBMS_PARALLEL_EXECUTE.

Here’s an example of how to pull something over a database link in parallel.

 

 

Oracle Java Procedure Example

I had a book tell  me that Java procedures couldn’t be scheduled using DBMS_SCHEDULER, which I didn’t buy because I had just read in another book that you *could* schedule Java procedures using DBMS_SCHEDULER.  I tried it for my self…turns out, you can, in fact, schedule Java procedures.  Here’s the proof (from Oracle 11.2).

 

Query to Examine Blocking

 

See All Grants Necessary for Selecting From a View

Ran into a frustrating problem where schema “A” couldn’t select from a view that schema “B” owned.  This was because schema “B” referenced tables that were owned by schema “C,” and schema “B” was granted “SELECT” privileges only on those tables, where it should have been granted “SELECT…WITH GRANT OPTION.”

This is a bit like killing a fly with a sledgehammer, so it should be used with thoughtful consideration to the exact privileges that are being granted.  But this SQL should list out all the necessary grants for a user to be able to select from a view.  Just fill in the VIEW_OWNER, VIEW_NAME, and GRANT_SELECT_TO pieces.

 

Grabbing the “Most Recent Record”

Suppose you have a table full of tweets.

You would like a view that will show you the most recent tweet of every username, and you want it done fast and efficiently!  (Suppose that the table has millions of rows)

What’s the best way to get this information?  I don’t know.  But I think I’ve got a way that’s pretty fast.

Continue Reading