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