List all indexes in an Oracle tablespace

Sometimes Oracle DBAs need to do some tablespace cleaning and moving indexes from one tablespace to another. If you have hundreds of indexes this may become an awkward task to do manually. This article describes how to use SQL to list all indexes in a given tablespace so that you can automate the object movement.

The view 'dba_indexes' contains useful information about indexes including which tablespaces they belong to. To produce a list of indexes in any given tablespace, one can use the below code:

SELECT * FROM dba_indexes WHERE owner='USER' AND tablespace_name = 'USERS'

You can then feed this list to another script which will do index administration tasks such as rebuild, movement or drop.

No comments: