cancel
Showing results for 
Search instead for 
Did you mean: 

question about activate the PSAPTEMP to autoextend

Former Member
0 Kudos

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!

Accepted Solutions (1)

Accepted Solutions (1)

Reagan
Advisor
Advisor
0 Kudos

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

Former Member
0 Kudos

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

Reagan
Advisor
Advisor
0 Kudos

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

Former Member
0 Kudos

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!




Reagan
Advisor
Advisor
0 Kudos

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 ?

Former Member
0 Kudos

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.

Reagan
Advisor
Advisor
0 Kudos

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

Former Member
0 Kudos

Thank you Reagan

Answers (1)

Answers (1)

former_member182657
Active Contributor
0 Kudos

Hi Kate,

It would be best to opt brtools option to extend the table space and follow the instructions for same.

In addition follow SCN doc

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

Former Member
0 Kudos

Hi Gaurav,

Thank you so much , i will check later the post.