The database has a program which performs a huge sort operation (e.g. My disk was full because the USERS Tablespace was bigger than 17Gb. One of my other tablespaces is out of space so I have to reallocate the remaining size of this tablespace. shrink does not work on a permanent tablespace. 999171 Mar 26, 2013 8:36 AM ( in response to 846231 ) first have to enable row movement alter database enable row movement. Alter table shrink space - Using the alter table shrink space compact command will re-pack the rows, move down the HWM, and releases unused extents. Here are the steps to shrink an Oracle Tablespace: 1. It is not a temporary table-space, I cant lose the data in that tablespace. In this example, the report process may run once or twice a year and there is no need to maintain/keep a huge tempfile.
I need to shrink a tablespace in oracle 11g. Coalesce simply takes contigous free extents and makes them into a single bigger free extent. ALTER TABLESPACE TS1 REDUCE MAX In this case, the keyword MAX is specified as part of the REDUCE clause, indicating that the database manager should attempt to move the maximum number of extents to the beginning of the table space. Bob Watkins explains how in this Oracle tip. Database administrators can now resize Oracle datafiles at the tablespace level using the BIGFILE tablespace feature in 10g. This may cause the TEMP tablespace to grow and occupy most of the space on the file system. Example 1: Reducing an automatic storage table space by the maximum amount possible. The shrink only compacts the data. It is not a reorganization tool. SQL> ALTER DATABASE DATAFILE 72 RESIZE 13229735936; ALTER DATABASE DATAFILE 72 RESIZE 13229735936 * ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value The solution was to purge the tablespace's recycle bin, even if there were no recycled objects near the end of the datafile. ... , the table space or partition must be in the stopped state when the ALTER TABLESPACE statement is executed. Re: How to shrink tablespace? ALTER PARTITION integer
ALTER TABLESPACE ... integer specifies the maximum number of partitions to which the table space can grow or shrink.
alter tablespace shrink;
The ALTER TABLESPACE statement changes the description of a table space at the current server. ALTER TABLESPACE COALESCE; Example: alter tablespace USERS coalesce; 3.
end of the year reporting process). Change the TableSpace Size: It is a command to take any free extents that are right next to some other free extent and make one bigger free extent out of them.