torturedmind
Programmer
Hi,
We had a very large amount of data that were transferred from a simple Novell file server to Oracle DB. In the Oracle side, we have a permanent tablespace and a temporary tablespace. This temporary tablespace is NOT the default TS. In it, there are 2 data files called CIM_TMP1.DBF and CIM_TMP2.DBF each having a size of 512MB. During the transfer, one of these data files (CIM_TMP2.DBF) bloated to a size of +33GB because it was set to autoincrement. But viewing the current info of the said data file in the Oracle OEM, only 4MB is being used. Now, I want to reclaim the unused space by resizing it back to 512MB but Oracle won't let me saying that the target size is much smaller compared to its current content or something like that. I read somewhere that, since it is a temporary tablespace, I may just drop the temporary data file (NOT the entire tablespace) using ALTER TABLESPACE TEMPFILE...DROP INCLUDING DATAFILES command.
My question is: How much risk is involved in this procedure? I just want to have other DBA or expert knowledge. TIA
kilroy
philippines
"Once a king, always a king. But being a knight is more than enough."
We had a very large amount of data that were transferred from a simple Novell file server to Oracle DB. In the Oracle side, we have a permanent tablespace and a temporary tablespace. This temporary tablespace is NOT the default TS. In it, there are 2 data files called CIM_TMP1.DBF and CIM_TMP2.DBF each having a size of 512MB. During the transfer, one of these data files (CIM_TMP2.DBF) bloated to a size of +33GB because it was set to autoincrement. But viewing the current info of the said data file in the Oracle OEM, only 4MB is being used. Now, I want to reclaim the unused space by resizing it back to 512MB but Oracle won't let me saying that the target size is much smaller compared to its current content or something like that. I read somewhere that, since it is a temporary tablespace, I may just drop the temporary data file (NOT the entire tablespace) using ALTER TABLESPACE TEMPFILE...DROP INCLUDING DATAFILES command.
My question is: How much risk is involved in this procedure? I just want to have other DBA or expert knowledge. TIA
kilroy
philippines
"Once a king, always a king. But being a knight is more than enough."