So, one tries to resize a datafile and receives the ORA-03297 error.
In our case, we have nearly 2000 tables in the tablespace.

Short of moving them all elsewhere, or doing an export / import, is
there any way to see WHAT objects may be above the size limit we are
trying to shrink to, and move only those objects? I'm not sure how to
actually map that out.

Just curious.

http://jonathanlewis.wordpress.com/2...nk-tablespace/
http://jonathanlewis.wordpress.com/tablespace-hwm/

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com


"ExecMan" <artmerar (AT) yahoo (DOT) com> wrote

On Thu, 01 Dec 2011 12:19:40 -0800, ExecMan wrote:

As Jonathan has pointed out, you can find out the requested information
from DBA_EXTENTS view. Are you really sure that you want to do that? If
that is a used and alive tablespace, the space is likely to be needed
again, in which case you will take a performance hit, possibly a big one,
too.




--
http://mgogala.byethost5.com

On Dec 1, 3:12*pm, "Jonathan Lewis" <jonat... (AT) jlcomp (DOT) demon.co.uk>
wrote:
Good article. Very helpful. What does a command like this do?

alter table SHOPCART_SESSIONS move;

It runs, but what does it do to the table? Rebuild it?

On Thu, 01 Dec 2011 13:45:35 -0800, ExecMan wrote:

Yes.



--
http://mgogala.byethost5.com

On Dec 1, 1:44*pm, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:
I thank Jonathan again, I just used that script to determine it would
be easier just to expdp/blow away ts and files/recreate files/impdp
the whole schema than try to script moving 1000 tables. It was the
kind of thing where 3 thousand tables/indices were imported, then
months later the users finally agreed they didn't need 90% of the
data, which is in the top 10 tables/indices by size. They happen to
be in different parts of the middle of the alphabet, so when they were
originally imported, they got round-robinned around dozens of data
files, truly swiss-cheesing them when truncated, and putting all the
alphabetically subsequent tables in the top end of the data files.

100G here, 100G there, pretty soon you are moving the %used on the
production san. Users have no clue what they are asking for in the
background with what-if databases, especially when most of their work
is in Excel.

jg
--
@home.com is bogus.
http://www.darkreading.com/database-...ulnerable.html

ExecMan wrote,on my timestamp of 2/12/2011 8:45 AM:

It moves it into the same tablespace. IOW: it recompacts the table into the
first available free space in the original tablespace.