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
Re: Temp Tablespaces Question/Scenario???
Quote:
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.
Quote:
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.
Quote:
Point me to any documents that will help me clear this mystery..
http://tahiti.oracle.com