Re: Tablespaces

From: joel garry <joel-garry_at_home.com>
Date: Thu, 1 Dec 2011 16:03:04 -0800 (PST)
Message-ID: <c38ddbec-686d-4dcf-9648-dfa43e6910aa_at_h21g2000yqg.googlegroups.com>



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,
> too.
>
> --http://mgogala.byethost5.com

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

--
_at_home.com is bogus.
http://www.darkreading.com/database-security/167901020/security/news/232200517/researchers-say-oracle-leaves-databases-needlessly-vulnerable.html
Received on Thu Dec 01 2011 - 18:03:04 CST

Original text of this message