What is the best way to reorganize a huge tablespace?
Hi Everyone,
We have a data warehouse database with several tablespaces which have wasted disk space. I could not shrink the datafiles, eventhough there are about 50% space usage.
It is 10.2.0.4 enterprise database.
I tried to use OEM tool to shrink segments, which works but does not release any space to OS.
I tried to reorganize a tablespace, but the job runs for days without completion (it is less than 100 GB), even though database does not seems to have much activity.
Tried to reorganize a big table within the tablespace, same thing happened.
I try to export the tablespace, and import it to a test database (just for testing procedures), but i found that the import requires three other tablespaces to be created (associated index tablespaces and claritytool tablespace), eventhough I was not exporting objects from these other tablespaces. Some grant commands also failed, since the test database does not have many users like the source database (I guess these errors does not matter). When I created the required extra tablespaces, errors associated with creating indexes do go away. But no objects were created in these extra tablespaces during the import (impdp). I
We have a data warehouse database with several tablespaces which have wasted disk space. I could not shrink the datafiles, eventhough there are about 50% space usage.
It is 10.2.0.4 enterprise database.
I tried to use OEM tool to shrink segments, which works but does not release any space to OS.
I tried to reorganize a tablespace, but the job runs for days without completion (it is less than 100 GB), even though database does not seems to have much activity.
Tried to reorganize a big table within the tablespace, same thing happened.
I try to export the tablespace, and import it to a test database (just for testing procedures), but i found that the import requires three other tablespaces to be created (associated index tablespaces and claritytool tablespace), eventhough I was not exporting objects from these other tablespaces. Some grant commands also failed, since the test database does not have many users like the source database (I guess these errors does not matter). When I created the required extra tablespaces, errors associated with creating indexes do go away. But no objects were created in these extra tablespaces during the import (impdp). I
0