Managing Database Objects in Groups
The typical database contains a large number of tables, indexes, and other objects. Dealing with these objects on a one-by-one basis quickly becomes a complex and unmanageable task. One way that I've found to reduce this complexity is to deal with database objects in groups, or sets. For that purpose, the technique of using SQL to write SQL is the most powerful weapon in my arsenal. If I can construct a SELECT statement that returns a set of objects that I need to work on, then I have a good chance of leveraging that SELECT statement to generate the commands needed to manipulate those objects.
Recently, I was faced with the task of moving some 800+ indexes from one set of tablespaces to another. These indexes were owned by seven different users. The problem was that all the tables and indexes for each user were stored in each user's default tablespace. My task was to create an index tablespace for each user, and move each user's indexes to that new tablespace.
Creating the new tablespaces was easy. I did that by hand, and adopted the rather simple naming convention of USERNAME_INDEXES. The next task was a bit more difficult. I needed to move each index. To do that, I decided to use the ALTER INDEX REBUILD command. For each index, I would need to issue a command like this:
ALTER INDEX index_name REBUILD TABLESPACE username_indexes;
I wanted to issue the same command against every index, so that made this a good time to think about operating on the entire set at once. I came up with the following SQL SELECT statement to generate the ALTER INDEX REBUILD commands for each index:
SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD TABLESPACE ' || owner || '_INDEXES;' FROM all_indexes WHERE ((owner in ('username','username'...)));
As you can see, I queried the all_indexes data dictionary view to find the indexes owned by the users in question. The SELECT statement returns a computed expression that evaluates to the command that I wanted to execute. Once I had the statement figured out, I only needed to spool the output to a file, and then execute that file.
Now in truth, I didn't really want to execute the same command on all 800+ indexes. There were 40 or 50 that were large enough that I didn't want them lumped in with the others. But it was a simple matter to edit my generated script file and delete the ALTER INDEX commands for those indexes. Then, I executed the script.
One caveat that's relevant to this example. You might want to think twice about unleashing 800 ALTER INDEX REBUILD commands against your database. At least think about when you want to do it, and how long it will take. In my case, we were preparing a database for production. It wasn't being used yet, and contained minimal data.
So that's it. Instead of dealing with 800 indexes one at a time, I was able to deal with 800 indexes all at one time--a much more manageable task.