Re: Questions about using BIGFILE vs SMALLFILE

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 - RHEL6
> Oracle 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

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