Skip to Main Content
  • Questions

  • How to reduce the size of a tablespace?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Andres Abraham.

Asked: March 22, 2017 - 11:48 pm UTC

Last updated: March 25, 2017 - 3:45 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

today I could purge a "table space" that apparently occupied 70 gb, but actually used 3 gb, therefore now that "table space" measures 70 gb with only 3 gb occupied, then I want to minimize the "table space" at least 5 gb, but not let me make the operation,

"ORA-03297: file contains used data beyond requested RESIZE value..."

How do I accommodate the information in order to reduce the "table space"?

what I have tried is to create a new "table space", and move all the objects in the "table space" old to the new, but have not found the way to move all objects.

the "table space" is as the name "APPS_TS_MEDIA"

Thank you.

and Connor said...

"what I have tried is to create a new "table space", and move all the objects in the "table space" old to the new, but have not found the way to move all objects."

That is the way do it, its just a case of handling the various objects:

tables:
alter table T move tablespace NEW_TS

indexes:
alter index IX rebuild tablespace NEW_TS

lobs:
alter table t move lob(y) store as ( tablespace NEW_TS )

and so forth for each object in the tablespace.

You can even move them to the same tablespace - objects when moved tend to gravitate toward the front of the datafiles.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

works, but ,,,

Andres Abraham Bonilla Gomez, March 23, 2017 - 8:39 pm UTC

I have tried a 'query' dynamic, something like this.

select 'ALTER '||segment_type||' '||owner||'.'||segment_name||' REBUILD TABLESPACE APPS_TS_MEDIA_2;' from dba_extents a where a.tablespace_name='APPS_TS_MEDIA' AND A.SEGMENT_TYPE='INDEX' group by owner,segment_type,segment_name;

this to index them, do something similar for tables and lobs.

but I mark error on the tablespace privilege, tablespace created it with sys.
"ORA-01950: no privileges on tablespace 'APPS_TS_MEDIA_2'"

any form of "sys" to move all objects?
Connor McDonald
March 25, 2017 - 3:45 am UTC 

C:\Users\hamcdc>oerr ora 1950
01950, 00000, "no privileges on tablespace '%s'"
// *Cause:  User does not have privileges to allocate an extent in the
//          specified tablespace.
// *Action: Grant the user the appropriate system privileges or grant the user
//          space resource on the tablespace.


You need to alter the user to have a quota on the target tablespace

SQL> alter user X quota 5G on USERS;
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.