3 Replies Latest reply: Apr 12, 2012 8:06 AM by 928283 RSS

    DBA_FREE_SPACE is showing empty (nothing is being shown)

    928283
      Good Morning,

      I get the ORA-01652 error. I go to view DBA_FREE_SPACE and it shows:

      ~
      ~
      ~
      ~
      ~
      ~
      ~
      ~
      "DBA_FREE_SPACE" [New File]

      I need to extend my temp segment by 128 in the tablespace. But, I would like to view it first. Has anyone had this issue before?

      Thank You
        • 1. Re: DBA_FREE_SPACE is showing empty (nothing is being shown)
          sybrand_b
          If there is no free space in a tablespace, dba_free_space doesn't produce results.

          -------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: DBA_FREE_SPACE is showing empty (nothing is being shown)
            Fran
            Error: ORA-01652: unable to extend temp segment by string in tablespace string
            Cause: Failed to allocate an extent for temporary segment in tablespace.
            Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated

            what query is creating this issue?In first place, you have to know what query is the problem before add more space in temp tablespace, to see it execute:


            SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
            S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
            COUNT(*) sort_ops,T.segtype
            FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
            WHERE T.session_addr = S.saddr
            AND S.paddr = P.addr
            AND T.tablespace = TBS.tablespace_name
            GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
            S.program, TBS.block_size, T.tablespace,T.segtype
            ORDER BY mb_used,sid_serial;

            HTH

            Edited by: Fran on 12-abr-2012 5:42
            • 3. Re: DBA_FREE_SPACE is showing empty (nothing is being shown)
              928283
              I replicated the error on my intranet site and viewed the log files. Do I need to be in the database directory to excute that query? I'm not a Oracle expert. I just took over this database and learning Oracle at the same time.

              Thank You