Temp files !!
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Temp files !!

  1. #1
    Join Date
    Jul 2003
    Posts
    323

    Temp files !!

    SQL> desc dba_data_files

    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    FILE_NAME VARCHAR2(513)
    FILE_ID NUMBER
    TABLESPACE_NAME VARCHAR2(30)
    BYTES NUMBER
    BLOCKS NUMBER
    STATUS VARCHAR2(9)
    RELATIVE_FNO NUMBER
    AUTOEXTENSIBLE VARCHAR2(3)
    MAXBYTES NUMBER
    MAXBLOCKS NUMBER
    INCREMENT_BY NUMBER
    USER_BYTES NUMBER
    USER_BLOCKS NUMBER


    SQL> select file_name
    2 from dba_data_files
    3 where tablespace_name='TEMP';

    FILE_NAME
    --------------------------------------------------------------------------------
    /db07/oradata/DMG/DMGDWHD/temp01.dbf
    /db09/oradata/DMG/DMGDWHD/temp02.dbf
    /dba/DMG/oradata/DMG/temp03.dbf

    SQL> desc dba_temp_files
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    FILE_NAME VARCHAR2(513)
    FILE_ID NUMBER
    TABLESPACE_NAME NOT NULL VARCHAR2(30)
    BYTES NUMBER
    BLOCKS NUMBER
    STATUS CHAR(9)
    RELATIVE_FNO NUMBER
    AUTOEXTENSIBLE VARCHAR2(3)
    MAXBYTES NUMBER
    MAXBLOCKS NUMBER
    INCREMENT_BY NUMBER
    USER_BYTES NUMBER
    USER_BLOCKS NUMBER

    SQL> select file_name from dba_temp_files;

    no rows selected

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

    Why do the temp files not show up in the 2nd view ?



  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Are the temp files of type temporary? You can name a tablespace TEMP but it doesn't mean it is a temp tablespace unless you specify that.

    default temporary tablespace orcl_temp tempfile 'c:\ora9i\oradata\orcl\orcl_temp.dbf'
    size 5m autoextend on
    Last edited by stecal; 05-25-2005 at 02:58 PM.

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131
    You probably didn't specify TEMPFILE when you created the temp tablespace. Pull the temp tablespace DDL and post it.
    I remember when this place was cool.

  4. #4
    Join Date
    Jul 2003
    Posts
    323
    Tx. for confirming what I was thinking - just inherited this rather messy dev. db. - no way to get the DDL the dba maintaining this left
    and no one bothered to get even the app. schema passwords !!


  5. #5
    Join Date
    Oct 2002
    Posts
    807
    See Note 160426.1 for detailed explanation.

  6. #6
    Join Date
    Jul 2003
    Posts
    323
    My next q:-

    SQL> select tablespace_name from v$sort_segment;

    TABLESPACE_NAME
    -------------------------------
    TEMP

    --------------------------------------------------------------------
    So it show up here but not in dba_temp_files !!



  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    http://download-west.oracle.com/docs...pace.htm#10561


    Temporary Datafiles
    Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles, with the following exceptions:

    Tempfiles are always set to NOLOGGING mode.
    You cannot make a tempfile read-only.
    You cannot rename a tempfile.
    You cannot create a tempfile with the ALTER DATABASE statement.
    When you create or resize tempfiles, they are not always guaranteed allocation of disk space for the file size specified. On certain file systems (for example, UNIX) disk blocks are allocated not at file creation or resizing, but before the blocks are accessed.


    --------------------------------------------------------------------------------
    Caution:
    This enables fast tempfile creation and resizing; however, the disk could run of space later when the tempfiles are accessed.

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


    Tempfile information is shown in the dictionary view DBA_TEMP_FILES and the dynamic performance view V$TEMPFILE, but not in DBA_DATA_FILES or the V$DATAFILE view.
    I remember when this place was cool.

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,014
    Originally posted by cruser3
    My next q:-

    SQL> select tablespace_name from v$sort_segment;

    TABLESPACE_NAME
    -------------------------------
    TEMP

    --------------------------------------------------------------------
    So it show up here but not in dba_temp_files !!


    If you look at dba_users what is the temporary tablespace? I would guess that it is temp. You should create a new temp tablespace and point all of the users to that tablespace. See what is in temp, and as long as it doesn't contain actual data, then drop it. Make sure that no one is using system for their temporary tablespace.
    this space intentionally left blank

  9. #9
    Join Date
    Jan 2001
    Posts
    3,131
    I'm guessing it is not LMT.

    This is under CREATE TABLESPACE parameters...


    TEMPORARY
    Specify TEMPORARY if the tablespace will be used only to hold temporary objects, for example, segments used by implicit sorts to handle ORDER BY clauses.

    Temporary tablespaces created with this clause are always dictionary managed, so you cannot specify the EXTENT MANAGEMENT LOCAL clause. To create a locally managed temporary tablespace, use the CREATE TEMPORARY TABLESPACE statement.


    --------------------------------------------------------------------------------
    Note:
    Oracle Corporation strongly recommends that you create locally managed temporary tablespaces containing tempfiles by using the CREATE TEMPORARY TABLESPACE statement. The creation of new dictionary-managed tablespaces is scheduled for desupport.
    I remember when this place was cool.

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by cruser3
    My next q:-

    SQL> select tablespace_name from v$sort_segment;

    TABLESPACE_NAME
    -------------------------------
    TEMP

    --------------------------------------------------------------------
    So it show up here but not in dba_temp_files !!


    because it doesnt use tempfiles?

Posting Permissions

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



Click Here to Expand Forum to Full Width