Index managment

In this article, I’ll show you various ways to move an index to another tablespace when you need to reorganize database objects or free some space.

Move an index on a different tablespace

Simple and efficient. Will work flawlessly for small indexes (> 200-300 Mb)

Alter index index_name rebuild
tablespace new_tablespace_name;

Alter index ITEM_SELL rebuild online tablespace ACCOUNT compute statistics;

Change initial and next index storage parameters

Here’s how to change index storage parameters without having to rebuild the object entirely.

ALTER INDEX index_name
 REBUILD STORAGE (
   INITIAL 1M
   NEXT 1M
 );

Rename an index

ALTER INDEX index_name RENAME TO new_name;

Rebuild multiple indexes using PARALLEL

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE PARALLEL 8;'
FROM DBA_INDEXES;
-- And execute the output.

Alter index OWNER.INDEX_NAME rebuild online parallel 8;

Of course, you can change the parallel value if needed. Then you might need to change the parallel parameter for the indexes you’ve just rebuilt.

SELECT DEGREE, 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' NOPARALLEL;'
FROM DBA_INDEXES
WHERE DEGREE > '1';

In case of trouble during the index rebuild process

If you ever get into trouble while rebuilding indexes and encounter the error “ORA-08104: this index object 75350 is being online built or rebuilt”, just get the OBJECT_ID of the index causing trouble, and use the following script to unlock it.

DECLARE
RetVal BOOLEAN;
OBJECT_ID BINARY_INTEGER;
WAIT_FOR_LOCK BINARY_INTEGER;
BEGIN
OBJECT_ID := 63556;
WAIT_FOR_LOCK := NULL;
RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN (OBJECT_ID);
COMMIT;
END;
/

If this doesn’t work, use this script to rebuild and clean all invalid indexes.

begin
isclean :=false;
while isclean=false
loop
isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
dbms_lock.sleep(10);
end loop;
end;
/

Tags:

Forcing ASMM component to shrink

#oracle #database

Here's the way to force the shared pool to shrink dynamically. Documentation states that ASMM can only increase shared pool, and can't shrink. When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this va... Read more

20 Mar 2019 - 2 minute read

How to extract Oracle user DDL

#database #oracle

In some cases, you need to duplicate an Oracle user, along with all it's privileges. Doing this manually can be tedious. Hopefully, we can use the data dictionary to extract the data we need and dump it into a SQL file to modify and replay. Here's how to do it: ```sql set head off set pages 0 set long 9999999 spool user_script.sql SELECT DBMS... Read more

20 Feb 2018 - 1 minute read

Restart a hung Oracle database

#oracle

On rare occasions, mostly during a high server load peak or a process failure, an Oracle instance may not accept any connection. Either from regular users as well as SYSDBA. This situation is called a **hung database** and must be quickly resolved as the database isn't accessible for your users anymore. As you cannot connect to the hung database... Read more

20 May 2017 - 1 minute read