-
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???
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
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|