Re: Questions about using BIGFILE vs SMALLFILE

On Sat, 2024-02-17 at 08:23 +0100, Ilmar Kerm wrote:
> Daniel Overby Hansen just recently wrote about this
> topic:https://dohdatabase.com/2023/10/24/why-are-you-not-using-bigfile-tablespaces/
>
> To me it was a major surprise, that people still debate it, I really
> would not want to manage hundreds or thousands of data files for a single
> tablespace.
> But a good link from that thread was that there might still be a case for
> smallfiles due to Doc ID 2089689.1
>
> PS I don't really think in your case the migration effort is worth it.
>
>
> On Fri, 16 Feb 2024 at 23:50, Sandra Becker <sbecker6925_at_gmail.com>
> wrote:
> > OS - RHEL6Oracle EE - 12.2
> >
> > We have a new DBA that is insisting using BIGFILE has been the industry
> > standard for the past 10 years.  Not something I was aware of since my
> > current and last 3 employers chose not to use it.  As team lead, I'm
> > trying to find more information to decide if it's really worth the
> > effort to move all our tablespaces to BIGFILE format before I make any
> > recommendations to my management.
> >
> > First some background on our environment:   
> > * We have an incredibly bad database design that was done some 20 years
> > ago and management will not approve a redesign.  
> > * We do use ASM, which does simplify datafile management for us.
> > * About 80 percent of the application tables belong to a single
> > schema. 
> > Roughly 90 percent of all application tables reside in the same
> > tablespace.  
> > * Thankfully, most indexes are in a separate tablespace.   
> > * Many of the tables have a BLOB, CLOB or spatial data, sometimes all
> > three in the same table.
> > * We are in the middle of a project to rewrite applications and move
> > all
> > data off of on-prem Oracle databases to AWS hosted postgres databases.
> > * New management has expressed the need to accelerate the move off of
> > Oracle  (still expect it to take another 2-3 years even if
> > accelerated).
> > - There will be no more upgrades or patches to the Oracle environment. 
> > (We also no longer have Oracle Support.  Financial decision.).
> > * Any changes cannot incur downtime for the production databases.
> > * Moving to BIGFILE also assumes we'll actually get new ASM storage,
> > not a
> > given due to some budget cuts.
> > I have some questions regarding what's involved and supported.
> > 1. Does BIGFILE support BLOB, CLOB and Geometry (spatial) data?  We
> > have
> > a lot of spatial data as well as BLOBs and CLOBs.
> > 2. Will moving tablespaces from smallfile to bigfile require downtime?
> > 3. None of our tablespaces are over 175 datafiles currently, most are
> > under 20 with minimal growth expected.  Would we really gain anything
> > by making the change?
> > 4. Would BIGFILE on ASM really be any better than our current SMALLFILE
> > on ASM given the project to move off of Oracle completely?
> > Since the major projects are to get us off of Oracle sooner rather than
> > later, is this really where we should be focusing our time and energy? 
> > My inclination is to say no, the team should be focusing on learning to
> > administer and monitor the AWS postgres, as well as the MSSQL
> > servers/databases that will not be migrated to postgres.  I've been
> > reading some articles, but wanted to get the feedback from people who
> > have done the move from smallfile to bigfile.
> >
> > Thank  you for your feedback.
> >
> > --
> > Sandy B.
> >
>
>
> --
> Ilmar Kerm

Hi Ilmar,
Small file tablespace allows me to move a portion of it to another storage unit. I cannot do partial move with the BIGFILE tablespace. Also, I am not "managing" thousands of data files, they don't need any management. The pertinent information would be what Oracle uses for their tests. Are they using bigfile tablespaces or not?
Regards

-- 
Mladen Gogala
Database SME
https://dbwhisperer.wordpress.com


--
http://www.freelists.org/webpage/oracle-l