Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: out of temp space

    I'm running an ETL, and its exiting because of lack of temp space. How can I check for the available and used space in a schema?
    Thank you in advance.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    query DBA_DATA_FILES, DBA_TEMP_FILES, & DBA_FREE_SPACE

    Alternatively

    For using the RESUME mode we have do the following;

    1. Issue 'GRANT RESUMABLE TO <user>'.

    2. Issue 'ALTER SESSION ENABLE RESUMABLE TIMEOUT <seconds>'

    then fix the space problem & continue from where the process went on hold
    Last edited by anacedent; 02-05-07 at 13:40.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    if I want to see the temp area fill up I use this:
    PHP Code:
    set linesize 250
    col tablespace                 format a10
    col username                   format a8
    col osuser                     format a6
    col sid_pid                    format a10
    col machine                    format a10
    col sid                        format 9990
    col serial
    #                    format 99990
    col spid                       format a5

     compute sum label 
    'TOTAL' of blocks_mb on report;
     break 
    on report
    select t1
    .tablespace
    sumt1.blocks to_numbert3.value ) ) / 1024 1024 blocks_mb
    sumt1.extents tot_extents
    t2.username
    t2.osuser
    t2.status
    t2.SID ||','|| t2.serial# SID_PID
    t4.spid
    t2.sql_address
    t5.hash_value
    from v$sort_usage t1
    v$session t2
    v$parameter t3
    v$process t4
    v$sqltext t5
    where t1
    .SESSION_ADDR t2.SADDR
    and t3.name 'db_block_size'
    and (t2.PROCESS=t4.SPID or t2.paddr t4.addr)
    and 
    t2.sql_address t5.address
    and t5.piece 0
    group by
    t1
    .tablespace
    t2.username
    t2.osuser
    t2.machine
    t2.status
    t2.schemaname
    t2.program
    t2.SID ||','|| t2.serial#
    t4.spid
    t2.sql_address
    t5.hash_value
    order by 2

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •