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

Examples/Deep Dives/Reference


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).


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

Which Partition Is My Row In?

I recently worked on a project where I have an interval-partitioned table that I insert into daily.  After the insert, I wanted to gather statistics on the partition that I’d just inserted to.  I thought back through some recent reading and remembered that the ROWID of an object contains the following elements:

  • Letters 1-6 = the OBJECT_ID
  • Letters 7-9 = the relative FILE_ID
  • Letters 10-15 = the BLOCK within the file
  • Letters 16-18 = the ROW within the block

I knew there was a package called DBMS_ROWID (although I had never used it) but I guessed there would be a procedure that I might could acquire an OBJECT_ID for a given ROWID.

And I was right.

Continue Reading

ORA-01031 Insufficient Privileges Reference

Trying to Creating a View?

You need the CREATE VIEW system privilege to create a view in your own schema, or the CREATE ANY VIEW system privilege to create a view in someone else’s schema.  These privileges can be inherited through roles.

You also need to be granted SELECT, INSERT, UPDATE, and DELETE on all tables referenced by the view, or the corresponding SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, and DELETE ANY TABLE system privileges, and these privileges may not be obtained through roles.

Additionally, in order to grant other users access to your view, you must have received object privileges to the base tables with the WITH GRANT OPTION, or the appropriate system privileges with the WITH ADMIN OPTION.


Trying to Truncate a Table?

If the table you’re trying to truncate is not in your own schema, you must be granted the DROP ANY TABLE privilege.  This privilege may be inherited through a role.


Trying to do something in a Procedure, Function, or a Trigger?

If the procedure or function is created with definer’s rights (which is the default) then any privilege obtained through a role is “null and void.”  Any privileges you’re trying to take advantage of must be granted to you directly (i.e. not through a role).  For example, if you create a new user and only grant the user the CONNECT and RESOURCE roles, you’ll see that if you login as the newly created user, you’ll be able to create a table; however, if the newly created user tries to create a procedure that creates a table, you’ll get “ORA-01031: insufficient privileges” because you’ve not been granted the CREATE TABLE system privilege directly–it’s been granted to you through a role.  Try granting the CREATE TABLE privilege directly to the owner of the procedure/function.

Triggers always have definer’s rights, so the above is always true for triggers.

If the procedure is created with invoker’s rights, it’s true that an invoker of the function may take advantage of roles; however at compile time the owner of the procedure must have been granted the necessary permissions on the underlying objects if static SQL is being used.  Again, these permissions cannot be inherited through roles–this is necessary for compile-time validation.  One way to avoid this is to use dynamic SQL (execute immediate), so that the dependency is not checked at the function’s compile time.


Are you used to working with a version of Oracle prior to 10.2?

Note that if you’re accustomed to working on Oracle version that’s pre-10.2, you were automatically granted the ALTER SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE CLUSTER, CREATE DATABASE LINK, and CREATE SEQUENCE system privileges as part of the CONNECT role, but starting in Oracle 10.2 and beyond, the CONNECT role only has the CREATE SESSION.  So granting someone CONNECT will no longer allow them to create tables, views, etc.  More information here.  Note that using the CONNECT role nowadays is deprecated by Oracle.