Temp Tablespaces Question/Scenario???
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Temp Tablespaces Question/Scenario???

Hybrid View

  1. #1
    Join Date
    Jul 2003
    Location
    Cincinnati
    Posts
    68

    Temp Tablespaces Question/Scenario???

    Hello DBAs:

    I have enclosed the output from my database on Temporary tables.

    I finished copying a database and don't remember adding any temporary tablespaces. One of my users complained that sorting is erroring out and I added a datafile. I wanted to resize the existing datafile, or tempfile but could not find one..

    SVRMGR> connect internal
    Connected.
    SVRMGR> select * from dba_temp_files;
    FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_F AUT MAXBYTES MAXBLOCKS INCREMENT_ USER_BYTES USER_BLOCK
    -------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ---------- --- ---------- ---------- ---------- ---------- ----------
    0 rows selected.
    SVRMGR>
    SVRMGR>
    SVRMGR> select tablespace_name from dba_tablespaces;
    TABLESPACE_NAME
    ------------------------------
    SYSTEM
    RBS
    TEMP
    USERS
    TOOLS
    ...


    SVRMGR> select * from dba_data_files where tablespace_name='TEMP';
    FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_F AUT MAXBYTES MAXBLOCKS INCREMENT_ USER_BYTES USER_BLOCK
    -------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ---------- --- ---------- ---------- ---------- ---------- ----------
    0 rows selected.


    SVRMGR> select count(*) from dba_temp_files;
    COUNT(*)
    ----------
    0
    1 row selected.
    SVRMGR>
    SVRMGR>
    SVRMGR> create temporary tablespace temp tempfile '/data/oracle//u11' size 1000M;
    create temporary tablespace temp tempfile '/data/oracle//u11' size 1000M
    *
    ORA-01543: tablespace 'TEMP' already exists
    SVRMGR>
    SVRMGR> alter tablespace temp add datafile '/data/oracle//u11' size 1000M;
    alter tablespace temp add datafile '/data/oracle//u11' size 1000M
    *
    ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
    SVRMGR>
    SVRMGR> ed
    2>
    3> .
    SVRMGR>
    SVRMGR> alter tablespace temp add tempfile '/data/oracle//u11' size 1000M;
    alter tablespace temp add tempfile '/data/oracle//u11' size 1000M
    *
    ORA-01119: error in creating database file '/data/oracle//u11'
    ORA-27038: skgfrcre: file exists
    SVRMGR>
    SVRMGR>
    SVRMGR> alter tablespace temp add tempfile '/data/oracle//u11/temp02.dbf' size 1000M;
    Statement processed.
    SVRMGR>
    SVRMGR> select * from dba_temp_files;
    FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_F AUT MAXBYTES MAXBLOCKS INCREMENT_ USER_BYTES USER_BLOCK
    -------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ---------- --- ---------- ---------- ---------- ---------- ----------
    /data/oracle//u11/temp02.dbf 1 TEMP 1048576000 128000 AVAILABLE 1 NO 0 0 0 1044381696 127488
    1 row selected.

    *************

    When I queried the dba_tablespaces it showed a Temp TS and I am unable to see the datafile for it. Why??

    Secondly, Am I using system TS by any chance? If so, how can I know that my database is using system TS as Temp TS and how do I see the associated datafile with it?

    Point me to any documents that will help me clear this mystery..

    Thanks, ST2003

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Temp Tablespaces Question/Scenario???

    Originally posted by st2003

    When I queried the dba_tablespaces it showed a Temp TS and I am unable to see the datafile for it. Why??

    You have a temporary tablespace with no tempfiles. The tempfile you have tried to assign first (data/oracle//u11) already exits, but is not assigned to any tablespace.


    Secondly, Am I using system TS by any chance? If so, how can I know that my database is using system TS as Temp TS and how do I see the associated datafile with it?

    Don't know. You'd have to look at dba_users.temporary_tablespace.


    Point me to any documents that will help me clear this mystery..
    http://tahiti.oracle.com
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    You can query dba_users to see if anyone is using the system tablespace as their default or temporary tablespace.

    Code:
    SQL> desc dba_tablespaces
     Name                           
     --------------------------
     TABLESPACE_NAME                
     BLOCK_SIZE                     
     INITIAL_EXTENT                 
     NEXT_EXTENT                    
     MIN_EXTENTS                    
     MAX_EXTENTS                    
     PCT_INCREASE                   
     MIN_EXTLEN                     
     STATUS                         
     CONTENTS                       
     LOGGING                        
     FORCE_LOGGING                  
     EXTENT_MANAGEMENT              
     ALLOCATION_TYPE                
     PLUGGED_IN                     
     SEGMENT_SPACE_MANAGEMENT
    You won't see a datafile in dba_tablespaces. You can query dba_data_files to see the association between datafiles and tablespaces. You can query dba_temp_files to see the association between tempfiles and the temp tablespace.
    Code:
    SQL>  select file_name, tablespace_name
      2  from dba_temp_files;
    
    FILE_NAME                             TABLESPACE_NAME
    ------------------------------------- ----------------
    D:\ORACLE\ORADATA\DB00\TEMP01.DBF     TEMP
    What's with the double slash in "alter tablespace temp add tempfile /data/oracle//u11/temp02.dbf?" And you need a 1GB tempfile? Wow.

  4. #4
    Join Date
    Jul 2003
    Location
    Cincinnati
    Posts
    68
    Hello Jeff:

    **********
    You have a temporary tablespace with no tempfiles. The tempfile you have tried to assign first (data/oracle//u11) already exits, but is not assigned to any tablespace.
    **********

    Sorry.. It does not exist.. I thoroughly checked all my data directories to see if anything is existing other than the datafile I added to Temp TS.

    I basically added a datafile to an existing Temp TS, to which I could not find the datafile or tempfile... How can a TS exist, without no datafiles existing??????

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

    SQL> select temporary_tablespace, count(*) from dba_users group by temporary_tablespace;

    TEMPORARY_TABLESPACE COUNT(*)
    ------------------------------ ----------
    TEMP 141

    --------------------------
    Total# of users : 141
    --------------------------

    It's still a mystery..

    Thanks, ST2003

  5. #5
    Join Date
    Jul 2003
    Location
    Cincinnati
    Posts
    68
    Hi Stecal:

    I have done everything whatever you suggested and you can see most of that that in my posting.. The // is a mistake while copying from the screen.. And in this particlar instance we used to have 12G worth of tempfiles.. Right now, it is 6G since I am monitoring and allocating.

    Thx, ST2003

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