-
add a datafile for temp tablespace
How would you add a new datafile to this tablespace?
Originally created and successful:
CREATE temporary TABLESPACE DT_TEMP
tempfile '/u02/dbs/RIDEV/temp01.dbf' SIZE 320M
extent management local
uniform size 8M
SQL> select * from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
/u02/dbs/RIDEV/temp01.dbf
1 DT_TEMP 335544320 20480 AVAILABLE
1 NO 0 0 0 327155712 19968
Now I am saying:
1* alter tablespace dt_temp add datafile '/u05/dbs/RIDEV/temp02.dbf' size 1000M
SQL> /
alter tablespace dt_temp add datafile '/u05/dbs/RIDEV/temp02.dbf' size 1000M
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
SQL> c/alter/alter temp/
1* alter temp tablespace dt_temp add datafile '/u05/dbs/RIDEV/temp02.dbf' size 1000M
SQL> /
alter temp tablespace dt_temp add datafile '/u05/dbs/RIDEV/temp02.dbf' size 1000M
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> c/temp/temporary/
1* alter temporary tablespace dt_temp add datafile '/u05/dbs/RIDEV/temp02.dbf' size 1000M
SQL> /
alter temporary tablespace dt_temp add datafile '/u05/dbs/RIDEV/temp02.dbf' size 1000M
*
ERROR at line 1:
ORA-00940: invalid ALTER command
I cant use resize since the filesystem is almost full..
Thanks, ST2000
-
alter tablespace temp add tempfile
look sql reference in tahiti.oracle.com
-
Thanks Pando.. I'm back in business..
-
Just another observation..
After I added another 2GB successfully to this temp tablespace, there is no change in the file system space and secondly when I checked the tablespace listing listing in TOAD, it shows as 100% full.. I checked to see if any users using the temp_segment and it's none..
Thanks, ST2000
-
Just another observation..
After I added another 2GB successfully to this temp tablespace, there is no change in the file system space and secondly when I checked the tablespace listing listing in TOAD, it shows as 100% full.. I checked to see if any users using the temp_segment and it's none..
Thanks, ST2000
-
temp tablespace always show full
in tahiti.oracle.com read concepts about temporary tablespaces
-
You can add files very easily with Enterprise Manager.
David Knight
OCP DBA 8i, 9i, 10g
-
You may want to add Additional Datafile only if there is a real need. Did you get any error? Otherwise it always shows as full.
-- Dilip
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
|