Alter tablespace shrink
This may cause the TEMP tablespace to grow and occupy most of the space on the file system. end of the year reporting process). 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.
I need to shrink a tablespace in oracle 11g.
In this example, the report process may run once or twice a year and there is no need to maintain/keep a huge tempfile. The ALTER TABLESPACE statement changes the description of a table space at the current server.
It is not a temporary table-space, I cant lose the data in that tablespace.
alter tablespace
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.
Re: How to shrink tablespace?
999171 Mar 26, 2013 8:36 AM ( in response to 846231 ) first have to enable row movement alter database enable row movement. ALTER TABLESPACE ... integer specifies the maximum number of partitions to which the table space can grow or shrink.
The database has a program which performs a huge sort operation (e.g. 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. Change the TableSpace Size: It is not a reorganization tool. My disk was full because the USERS Tablespace was bigger than 17Gb. Bob Watkins explains how in this Oracle tip. Example 1: Reducing an automatic storage table space by the maximum amount possible.
The shrink only compacts the data. ALTER TABLESPACE
One of my other tablespaces is out of space so I have to reallocate the remaining size of this tablespace.
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. shrink does not work on a permanent tablespace. Database administrators can now resize Oracle datafiles at the tablespace level using the BIGFILE tablespace feature in 10g. ... , the table space or partition must be in the stopped state when the ALTER TABLESPACE statement is executed.