Index creation process stuck

ISSUE ON index creation process stuck:

ISSUE:BUT for index execution process stuck issue

Root cause: Tablerspace usage of schema(UCM schema in my case it is dev_ocs tablespacename also dev_ocs)

Solution :I added datafile to dev_ocs tablespace with command,

But before that we have to check table space info and usage:

For usage:

Command,

SQL> select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free" from (select tablespace_name,round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name;

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

FILE_SIZE_MB AUT INCREMENT_BY

------------ --- ------------

SYSTEM

/u01/app/oracle/oradata/gmkp11/system01.dbf

         710 YES         1280

SYSAUX

/u01/app/oracle/oradata/gmkp11/sysaux01.dbf

         690 YES         1280

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

FILE_SIZE_MB AUT INCREMENT_BY

------------ --- ------------

UNDOTBS1

/u01/app/oracle/oradata/gmkp11/undotbs01.dbf

         340 YES          640

USERS

/u01/app/oracle/oradata/gmkp11/users01.dbf

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

FILE_SIZE_MB AUT INCREMENT_BY

------------ --- ------------

           5 YES          160

EXAMPLE

/u01/app/oracle/oradata/gmkp11/example01.dbf

         100 YES           80

DEV_IPM

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

FILE_SIZE_MB AUT INCREMENT_BY

------------ --- ------------

/u01/app/oracle/oradata/gmkp11/DEV_ipm.dbf

         200 YES        51200

DEV_MDS

/u01/app/oracle/oradata/gmkp11/DEV_mds.dbf

         100 YES         6400

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

FILE_SIZE_MB AUT INCREMENT_BY

------------ --- ------------

DEV_OCSSEARCH

/u01/app/oracle/oradata/gmkp11/DEV_ocssearch.dbf

         105 YES         6400

DEV_ORAIRM

/u01/app/oracle/oradata/gmkp11/DEV_orairm.dbf

         100 YES         3840

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

FILE_SIZE_MB AUT INCREMENT_BY

------------ --- ------------

DEV_URMSERVER

/u01/app/oracle/oradata/gmkp11/DEV_urmserver.dbf

         500 YES         6400

DEV_OCS

/u01/app/oracle/oradata/gmkp11/DEV_ocs.dbf

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

FILE_SIZE_MB AUT INCREMENT_BY

------------ --- ------------

        1250 YES         6400

DEV_OCS

/u01/app/oracle/oradata/gmkp11/DEV_ocs01.dbf

        1024 YES       131072

12 rows selected.

SQL> alter tablespace DEV_OCS add datafile'/u01/app/oracle/oradata/gmkp11/DEV_ocs01.dbf' size 1024M autoextend on next 1024M;

To check tablespace usage :

SQL> select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free" from (select tablespace_name,round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name;

Tablespace                        Used MB    Free MB   Total MB  Pct. Free

------------------------------ ---------- ---------- ---------- ----------

SYSAUX                                647         43        690          6

DEV_OCS                              2110        164       2274          7

UNDOTBS1                              828        242       1070         23

USERS                                   3          2          5         40

SYSTEM                                700         10        710          1

EXAMPLE                                78         22        100         22

DEV_ORAIRM                              0        100        100        100

DEV_URMSERVER                           5        495        500         99

DEV_IPM                                 0        200        200        100

9 rows selected.

SQL>

Then run index creation again .