Date: Thu, 1 Dec 2011 16:03:04 -0800 (PST)
On Dec 1, 1:44 pm, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> On Thu, 01 Dec 2011 12:19:40 -0800, ExecMan wrote:
> > 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.
> 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,
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.
-- _at_home.com is bogus. http://www.darkreading.com/database-security/167901020/security/news/232200517/researchers-say-oracle-leaves-databases-needlessly-vulnerable.htmlReceived on Thu Dec 01 2011 - 18:03:04 CST