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

Useful Queries and Code

Summing Interval Data Types

Something I’ve occasionally thought would be really nice is if Oracle had a good way of summing up interval data types, much the same way that you can SUM() number data types.  Occasionally I’ve designed processes that record start and stop timestamps of operations.  If you want to know how long the operation lasted, you could do the end timestamp minus the start timestamp to get a DAY TO SECOND INTERVAL data type.

If I want to know the total runtime of a set of operations, there’s not a nice, slick, easy way to do this.

Usually, I get away with some jankedy code like this:

First, you gotta cast the timestamps as dates (meaning you lose any fractional-seconds).

Then you subtract the start date from the end date.  That gives you a number, which represents the number of days (possibly fractional).

Oracle then allows you to sum up those numbers.

If you want the number back in interval format, you can supply it to a NUMTODSINTERVAL() function, passing in ‘DAY’ as the second argument.

It sucks, it’s not smooth, it loses precision, the maintaining developer has to double-take to try to figure out what it is that I’m doing.  Wouldn’t it be nice if there was some smooth way to just add up intervals?

Well, after giving it some thought, I decided to create my own…

Continue Reading

Quickly Creating a Billion-Row Test Table

Lots of times, when I’m testing theories, I need a large test table to see how things scale.  Here’s a script that will quickly create such a table with a primary key/unique index, along with a bitmap index.  The table has 10 partitions (this is necessary to allow 10 concurrent “append” inserts).  DBMS_PARALLEL_EXECUTE is used to build the table in parallel, and DBMS_PCLXUTIL is used to build the bitmap index in parallel.

Continue Reading

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.

 

See Privileges Granted Through Roles

Here’s a couple of queries that can be used to see privileges granted through roles.

System Privileges:

Object Privileges:

 

Oracle Drop If Exists Procedure

One of the things I’ve always wanted in Oracle was drop if exists functionality.  Inevitably, I’ll be creating a script that creates a bunch of tables and, for one reason or another, I’ll need to re-run the script because a data type in the table has changed or the length of a field needs to be tweaked, so I start dropping tables and I’ll miss one, or I’ll miss and index, and as a result the script dies halfway through with a ORA-00955 name is already used by an existing object error.

The solution, I’ve determined, was to roll my own drop_if_exists() procedure.

Continue Reading

See All Tables Foreign-Keyed to a Parent Table in Oracle

The below SQL query will find all children tables of any specified parent table.  If you do not have access to DBA_CONSTRAINTS/DBA_CONS_COLUMNS data dictionary view, use ALL_CONSTRAINTS/ALL_CONS_COLUMNS instead.  Or ask your DBA very nicely for the SELECT ANY DICTIONARY system privilege (or SELECT_CATALOG_ROLE).

 

Creating a Database Link in Another User’s Schema

Awhile back, I was tasked with creating a database link in another user’s schema, when I wasn’t able to login as the user.  I searched the internet–the majority of articles said that it couldn’t be done, and they encouraged people to create public database links instead of private database links.

Well, I found a working method–here it is.  Just replace INSERT_USERNAME  with your intended target schema.

 

Copying a User in Oracle

When you don’t care so much about copying the contents of the schema…and you’re more concerned about just copying a user’s account, this PL/SQL block will allow you to make a user account and model it after an existing account.  (If you are more interested in copying the schema contents…tables, indexes, etc., check out this blog post: How to Copy a Schema in Oracle)

 

Searching for Junction Tables in Oracle

If you have two tables that can be joined in a many-to-many fashion (for example, if you have a MOVIES table and an ACTORS table), and you want to find the table that joins the two, it’s easy if the architect used foreign keys.  Below is a modified version of the query used to search for child tables of a given parent table in a foreign-key relationship.  As always, if you don’t have permission to select from DBA_CONSTRAINTS, ask your DBA nicely for the SELECT ANY DICTIONARY system privilege or the SELECT_ANY_CATALOG role, or use ALL_CONSTRAINTS instead.