on 09-16-2014 5:45 AM
Dear expert,
I extended the PSAPTEMP from 30G to 120G (autoextended switch on by alter temfile) due to rebuild big indexes
Now the operation is finished , and i would like to resize it back to 30G
ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/QAT/temp.data2' SIZE 30720M REUSE AUTOEXTEND ON NEXT 10240 MAXSIZE 30720M;
ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/QAT/temp.data3' SIZE 30720M REUSE AUTOEXTEND ON NEXT 10240 MAXSIZE 30720M;
ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/QAT/temp.data4' SIZE 30720M REUSE AUTOEXTEND ON NEXT 10240 MAXSIZE 30720M;
I need to resize it or i need to drop the 3 added tempfile?
Could you share me the command?
Just one more question: there is for table OLTP compression, it will not ues the tempfile right?
Thank you!
Kate
Hello
You can shrink the 3 datafiles to 10G .
ALTER DATABASE TEMPFILE '/oracle/QAT/temp.data2' resize 10G;
ALTER DATABASE TEMPFILE '/oracle/QAT/temp.data3' resize 10G;
ALTER DATABASE TEMPFILE '/oracle/QAT/temp.data4' resize 10G;
If you are on version 11g you can also do it using
alter tablespace PSAPTEMP shrink space keep 30G;
If you are planning to perform compression, tempfile should be big enough.
1109743 - Use of Index Key Compression for Oracle Databases
Please make sure that your PSAPTEMP Temporary Tablespace is at least as big as the biggest index to rebuild
Regards
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Fidel ,
Then we can try to add new TEMP file and remove the old one instead of so we can change TEMP Tablespace size after that
- Connect to SQLPLUS via SYSDBA authorization.
- RUN following syntax for creating new TEMPFILE with value of 1000MB
alter tablespace temptablespace add tempfile ‘’ size 1000M autoextend on;
example ;
alter tablespace PSAPTEMP add tempfile ‘G:\ORACLE\SID\SAPDATA1\TEMP_10\TEMP.DATA10′ size 1000M autoextend on;
Drop TEMPFILE procedure
- Connect to SQLPLUS via SYSDBA authorization.
- RUN following syntax for first getting OFFLINE to releated TEMPFILE and then DROP with releated Datafiles on OS level
alter database tempfile ‘disk location of TEMPFILE ’ offline ;
alter database tempfile ‘disk location of TEMPFILE ’ drop including datafiles;
example ;
alter database tempfile ‘G:\ORACLE\SID\SAPDATA1\TEMP_10\TEMP.DATA10′ offline;
alter database tempfile ‘G:\ORACLE\SID\SAPDATA1\TEMP_10\TEMP.DATA10′ drop including datafiles;
I hope it helps...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello All ,
Also you can use the SHRINK option for other SAP tables..
Just open sqlplus and use "row movement" and "shrink space" commands..(You can use it online)
Shrink Oracle TablesYou must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Very nice, good answer but unrelated to the OP.
But it would be great that you read the question before you answer
She is talking about a tablespace, not tables
She is talking about the TEMPORAL tablesplace, which should be temporal and no table should be there, making your answer totally out of place
Hi Kate,
I am not sure on resizing the tablespace but you can try the below option.
Export all tables from the tablespace
** Don't execute the command please follow through Brtools**
brspace -u / -f tbexport -s <Tablespace> -t "*" -u /dumpdir
2)Delete those tablespaces and recreate.
Before deleting note down the tablespace used size and then create new one based on the usedsize.
3)Recreate the tablespace and import the table contents
Regards,
Karthik
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
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.