8 Replies Latest reply: Nov 2, 2012 7:16 PM by 972256 RSS

    Unknown query by SYS against DBA_FREE_SPACE, DBA_DATA_FILES always running

    Rapid E-Suite
      I see this unknown query always running and normally takes 20 - 30 % of activities on my database, It normally run longer than 1 hours. I researched about this but can't find information about the query as well. Anybody know what is cause this query always running ?

      SELECT SUM(USED), SUM(TOTAL)
      FROM
      (SELECT /*+ ORDERED */ SUM(D.BYTES)/(1024*1024)-MAX(S.BYTES) USED, SUM(D.BYTES)/(1024*1024) TOTAL
      FROM
      (SELECT TABLESPACE_NAME, SUM(BYTES)/(1024*1024) BYTES
      FROM
      (SELECT /*+ ORDERED USE_NL(obj tab) */ DISTINCT TS.NAME
      FROM SYS.OBJ$ OBJ, SYS.TAB$ TAB, SYS.TS$ TS
      WHERE OBJ.OWNER# = USERENV('SCHEMAID') AND OBJ.OBJ# = TAB.OBJ# AND TAB.TS# = TS.TS# AND BITAND(TAB.PROPERTY,1) = 0 AND BITAND(TAB.PROPERTY,4194400) = 0) TN, DBA_FREE_SPACE SP
      WHERE SP.TABLESPACE_NAME = TN.NAME GROUP BY SP.TABLESPACE_NAME) S, DBA_DATA_FILES D
      WHERE D.TABLESPACE_NAME = S.TABLESPACE_NAME GROUP BY D.TABLESPACE_NAME)