on 09-13-2014 9:18 AM
Dear all,
I would like to activate the PSAPTEMP to autoextend before start the index / table compression.
Is the below command OK?
brspace -f tsextend -t PSAPTEMP -s 5120 -a yes -i 100 -m 15360 -f <sapdata_dir>
My question what should be the value of <sapdata_dir>?
I run the following script -->select * from dba_data_files where tablespace_name='PSAPTEMP'
However there is no entry.
If i use the command as below , could i run it when the database is open?
ALTER DATABASE DATAFILE '<path_and_name>' AUTOEXTEND ON;
Thank you so much!
brspace -f tsextend -t PSAPTEMP -s 5120 -a yes -i 100 -m 15360 -f <sapdata_dir>
My question what should be the value of <sapdata_dir>?
You can specify -f sapdata02 after PSAPTEMP in the above command which is for /oracle/SID/sapdata02
I run the following script -->select * from dba_data_files where tablespace_name='PSAPTEMP'
However there is no entry.
For PSAPTEMP you need to query DBA_TEMP_FILES or V$TEMPFILE
If i use the command as below , could i run it when the database is open?
ALTER DATABASE DATAFILE '<path_and_name>' AUTOEXTEND ON;
It should be ALTER DATABASE TEMPFILE and NOT DATAFILE and also you need to specify the max size and increment value.
Eg: alter database tempfile '<path_and_name>' autoextend on next 500M maxsize 30000M;
Regards
RB
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Reagan B,
Thank you for the detailed responses.
However
Still further question about the datafile , how can i decide which datafile dir to fill in ? and i have to fill in just one single datafile_dir or i could do several
I run the scripts below
select substr(file_name,1,50), AUTOEXTENSIBLE from dba_data_files
results shows a list of >30 datafiles with all auto extende marked "YES"
Does it mean i can use any datafile for tablespace extension?
Or I should seperate the datafile group by different tablespace, ie, sapdata1/2/3/4 for PASAPSR3 and sapdata5/6 for PSAPSYS and sapdata7 only for PSAPTEMP?
Thank you so much!
Kate
When you extend a tablespace a new datafile is added at the OS level.
Eg: brspace -c force -o process,time -f tsextend -t PSAPSR3 -f sapdata02 -s 1024 -a yes -m 32000 -i 128
The above command will add a new datafile in the sapdata02 location (exactly at /oracle/SID/sapdata02).
If you are planning to alter the size of an existing datafile / tempfile or enable auto extend ON then the command is different.
Eg: brspace -c force -u / -f dfalter -a fixsize -t PSAPTEMP –file '/oracle/SID/sapdata02/temp_2/temp.data2' -i 100 -m 30000
The above command will enable AE mode and will set the Max Size to 30000 M and increment of 100 M for the temp file /oracle/SID/sapdata02/temp_2/temp.data2
You can create or extend tablespace in any sapdata file systems as long as there is enough space.
Regards
RB
Hi Reagan B,
Thank you again for your answer, however there must be some misunderstanding...
Let me put my question again:
in my system the current PSAPTEMP directory is as follows
/oracle/TPV/sapdata1/temp_1/temp.data1
/oracle/TPV/sapdata4/temp_2/temp.data2
/oracle/TPV/sapdata1/temp_3/temp.data3
/oracle/TPV/sapdata2/temp_4/temp.data4
/oracle/TPV/sapdata3/temp_5/temp.data5
/oracle/TPV/sapdata4/temp_6/temp.data6
Could you tell me how to construct the scripts so that the extended PSAPTEMP will follow the structure as above instead of only store the data in th datefile sapdata2?
Thank you!
Well these are not just directories. They do contain the tempfiles assigned to PSAPTEMP.
If you extend PSAPTEMP then there will be a new tempfile created.
Suppose if you run brspace -c force -o process,time -f tsextend -t PSAPTEMP -f sapdata3 -s 1024 -a yes -m 31744 -i 128 this will create a new tempfile called /oracle/TPV/sapdata3/temp_7/temp.data7 with auto extend enabled.
Is this what you are looking for ?
Hello Reagan B,
Yes, that is quite close to what I'm looking for.
Thank you.
And do you know how to config in the script fo tsextend to make the new tempfile also to be created in sapdata2, sapdata1 and sapdata4 , that is the new tempfile will be created among the sapdata1/2/3/4 one by one?
Thank you and that's my last question.
I would create a file at the OS level called TSCreate.sh and have these commands added to it.
brspace -c force -o process,time -f tsextend -t PSAPTEMP -f sapdata1 -s 1024 -a yes -m 31744 -i 128
brspace -c force -o process,time -f tsextend -t PSAPTEMP -f sapdata2 -s 1024 -a yes -m 31744 -i 128
brspace -c force -o process,time -f tsextend -t PSAPTEMP -f sapdata3 -s 1024 -a yes -m 31744 -i 128
brspace -c force -o process,time -f tsextend -t PSAPTEMP -f sapdata4 -s 1024 -a yes -m 31744 -i 128
Execute the script as orasid.
Good Luck
RB
Hi Kate,
It would be best to opt brtools option to extend the table space and follow the instructions for same.
With use of brtools you can set table space for auto extend mode.
Hope this will be helpful for you.
Also if you need to extend it via command than use below to extend
alter tablespace PSAPTEMP add tempfile '<datafilepath>' size 5000M autoextend off
Hope this will help you.
Regards,
Gaurav
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.