Re: Questions about using BIGFILE vs SMALLFILE

And another one that blocks a tablespace_shrink:

Procedure exited because it can't move an object: Failed Move DDL: alter table "TEST_USER"."SCRIPT_HANDLES" move online Failed Reason: ORA-14808: table does not support ONLINE MOVE TABLE because of the presence of domain index
...

Regards
Jonathan Lewis

On Sat, 17 Feb 2024 at 07:24, Ilmar Kerm <ilmar.kerm_at_gmail.com> 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 - 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