Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1527
  • Last Modified:

How to really clean the tablespace?

Why cleaned up some big tables, but it still showed the tablespace space was used up?
0
luyan
Asked:
luyan
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean, you DELETED the rows?
in which case, the blocks assigned to the tables do NOT get returned as free space to the tablespace, but only as free space inside the table.
in order to reclaim space in such a case is to recreate the table, or use TRUNCATE instead of DELETE (assuming you delete all the rows).
in case you do NOT delete all the rows from a table, you need to recreate the table with an intermediate step of a temp table holding the rows to be kept.
0
 
Mark GeerlingsDatabase AdministratorCommented:
If you move or drop a table, then *ALL* of the space formerly used by the table gets returned to the tablespace as "free" space.

If you truncate a table, then *SOME* of the space formerly used by the table gets returned to the tablespace as "free" space, *IF* the table had grown to multiple extents.  But the "initial" extent remains allocated to the table, even though it is now empty.

If you just delete some (or even all) records then *NO* space gets returned to the tablespace as "free" space.
0
 
LindaCCommented:
The answer to your two questions are the following:

Why cleaned up some big tables?

In this way you will have the possibility of rebuilding the table, rebuilding the tablespace and have more space on the server for future used.  Also in the rebuidling of the tables your queries will run faster as they will have fewer data to look upon.

but it still showed the tablespace space was used up?

Yes, this is true because of the highwatermark.
The used space you will not be able to see it "shrink" unless you rebuild the tablespace.  How?

Export all shemas in the tablespaces schema_dat and schema_indx.

Now issue this inside the server:   drop tablespace <shcema_dat> including contents and datafiles;
drop tablespace schema_indx including <schema_indx>  including contents and datafiles;

Create the tablespace so that oracle will manage the segments space automatically in this way:

For data: Notice the next extent of 125 mb

CREATE  TABLESPACE "<schema_dat" DATAFILE '<datafile path and name>' SIZE 126M AUTOEXTEND ON NEXT 125M MAXSIZE 5001M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

For indexes:  Notice the next extent of 40 mb only.

CREATE  TABLESPACE "<schema_INDX>" DATAFILE '<schema_indx>' SIZE 41M AUTOEXTEND ON NEXT 40M MAXSIZE 2001M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

Alter the schemas quota unlimited on each tablespace.  Notice:  Do not issue the "unlimited tablespace privilige" as this two are not the same.

Import fromuser touser each schema.

Now your problem is resolved.

Please give the points to your favorite answer (s) as we need them.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now