Oracle 10 g - Unable to free up space in tablespace

The tablespace in Oracle 10g is virtually 100% made use of.

Dimension (MEGABYTES) = 571,768 .0 Used (MEGABYTES) = 571,534 .0

I simply removed (and also devoted) hundreds of documents in a table that comes from a schema related to that tablespace. Remarkably, no room was liberated according to the Tablespaces web page on Enterprise Manager.

Inquiry: exists anything that I require to do to compel Oracle to release the room representing the removed documents?

2022-06-07 14:35:09
Source Share
Answers: 3

Usually it is far better to create a short-lived table as create table temp_table name as select * from table_you_deleted_from ; after that go down the indexes on the initial table disable constraisnt indicating that table and afterwards go down and also recreate this table once more as create table table_you_deleted_from name as select * from temp_table ; recreate you indexes and also enable the restraints.

Below you primarily lowered your table dimension and also index tree size, i.e. much faster retrival moving forward.

2022-07-25 14:15:14

The web page you are considering in Enterprise Manager is likely reporting vacuum by contrasting DBA_DATA_FILES (which informs you the complete dimensions of the numerous documents alloted to a tablespace) with DBA_SEGMENTS (which informs you the complete dimension of all the sectors alloted to things in the tablespace). This will certainly not transform merely due to the fact that you removed some information.

When you delete information, you liberate room within the blocks and also levels alloted to a certain object. So if you delete 100 megabytes well worth of information from the FOO table (and also linked FOO indexes), the dimension of the FOO sector would certainly not decrease. Yet there would certainly currently be room because sector that would certainly suit an additional 100 megabytes well worth of inserts right into FOO. If you are removing room from FOO in order to liberate room for various other sectors in the tablespace, you would certainly require to restructure FOO after removing the information - - this is a rather entailed procedure that likely calls for downtime along with a reasonable little screening. Unless you are completely lowering the dimension of an object, it is usually not suggested - - if FOO is at some point visiting an additional 100 megabytes of inserts, it is not valuable to reduce the FOO sector just to have it expand once more.

You can make use of the DBMS_SPACE plan to see just how much room is readily available in various sectors. That will certainly inform you concerning vacant blocks along with blocks that remain in numerous states of being loaded (0 - 25% complete, 25 - 50% complete, 50 - 75% complete, and also 75 - 100% complete). When you delete information, you will usually create an increase in the variety of vacant and also partly vacant blocks alloted to a table.

2022-06-07 15:01:26

modify table table_name enable row activity ;

modify table table_name reduce room ;

2022-06-07 14:59:17