asked on
adding datafile to TEMP tablespace with tablespace group clause oracle 11g
This is the existing TEMP tablespace create statement
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
'file1' SIZE 2040K AUTOEXTEND ON NEXT 512M MAXSIZE 20480M,
'file2' SIZE 2040K AUTOEXTEND ON NEXT 512M MAXSIZE 20480M,
'file3' SIZE 527352K AUTOEXTEND ON NEXT 512M MAXSIZE 20480M,
'file4' SIZE 2040K AUTOEXTEND ON NEXT 512M MAXSIZE 20480M,
'file5'. SIZE 2040K AUTOEXTEND ON NEXT 512M MAXSIZE 20480M
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
I want to add a file 'file6'
The database is 11g
I want a symtax for adding a new datafile 'file6'
Thanks very much
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
'file1' SIZE 2040K AUTOEXTEND ON NEXT 512M MAXSIZE 20480M,
'file2' SIZE 2040K AUTOEXTEND ON NEXT 512M MAXSIZE 20480M,
'file3' SIZE 527352K AUTOEXTEND ON NEXT 512M MAXSIZE 20480M,
'file4' SIZE 2040K AUTOEXTEND ON NEXT 512M MAXSIZE 20480M,
'file5'. SIZE 2040K AUTOEXTEND ON NEXT 512M MAXSIZE 20480M
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
I want to add a file 'file6'
The database is 11g
I want a symtax for adding a new datafile 'file6'
Thanks very much
Oracle Database
Last Comment
Greens8301
alter tablespace temp add tempfile 'file6' SIZE 2040K AUTOEXTEND ON NEXT 512M MAXSIZE 20480M tablespace group '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ASKER
I get the following error
sys> alter tablespace temp add tempfile 'file6' ' SIZE 2040m
2 AUTOEXTEND ON NEXT 512M MAXSIZE 20480M tablespace group '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
AUTOEXTEND ON NEXT 512M MAXSIZE 20480M tablespace group '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
sys> alter tablespace temp add tempfile 'file6' ' SIZE 2040m
2 AUTOEXTEND ON NEXT 512M MAXSIZE 20480M tablespace group '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
AUTOEXTEND ON NEXT 512M MAXSIZE 20480M tablespace group '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
Why do you need another file? Assuming you maxed out the 5 that you already have, that is 100G of temp space. What are you doing that needs to consume more that 120G of temp?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
alter tablespace temp add tempfile 'file6' SIZE 2040K AUTOEXTEND ON NEXT 512M MAXSIZE 20480M tablespace group 'tablespace_group_name' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
put the tablespace group name inside single quote for 'tablespace_group_name'
put the tablespace group name inside single quote for 'tablespace_group_name'
ASKER
Excellent
alter tablespace temp add tempfile 'file5' SIZE 2040K AUTOEXTEND ON NEXT 512M MAXSIZE 20480M tablespace group '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;