0

In Oracle 18c there are some tablespaces with many (a lot, really) datafiles. Not sure why the previous DBA implemented it this way. But anyway I would like to ask if it is possible to alter the tablespace to use bigsize datafiles . Some miracle like that ... Maybe exporting all tables and indexes using datapump . Drop the object and then create a different object with tablespace using one bigfile datafile. Then import the data to it . But it seems it would not work . I don't know. Maybe a couple of magical commands to alter tablespace ? Any advice would be nice Thank you very much

3
  • I think this belongs on the dba site. But yes, in general you can alter all of the objects in your tablespace and move them to a different tablespace (e.g. a new one with larger datafile sizes).
    – kfinity
    Sep 23, 2021 at 20:46
  • No need for export/import. Just create a new TS as bigfile, then migrate all the segments from the old to the new -- ALTER TABLE MOVE for the tables and rebuild the indexes. Might want to follow up with investigating and changing any user's default TS that currently use the one you want to replace.
    – EdStevens
    Sep 23, 2021 at 23:50
  • Appreciate that . Thanks a lot for the explanation. Have a nice day Sep 24, 2021 at 11:45

1 Answer 1

0

The reason for multiple datafiles is recoverability. It was until v12 that a tablespace was backed up and restored per datafile. So with a very large bigfile tablespace it could take a long time to perform a backup and even worse, to restore that.

If you want to use bigfile tablespaces, just move your partitions, tables and indexes to the new tablespace. Review your default tablespaces for users and partitions. Check and test your recovery plan and make sure you can restore such a tablespace in the allowed downtime, in case a sad event occurs.

you might want to take a look at multi section backups

3
  • I see. That explains it. But in current situation client is using Rubrik as a backup system, so this is why I am considering the move. Thanks a lot for helping me . Appreciate very much. Sep 24, 2021 at 11:45
  • Don't think that rubric is smarter than RMAN. It uses rman to do the work. So the fact that rubric is used does not mean the recoverability problem is solved. There is no magic involved. With a little luck you can configure multi section backups with it. See docs.oracle.com/database/121/VLDBG/…
    – user953
    Sep 24, 2021 at 11:52
  • Appreciate the insight . Yes , it looks like it does use Rman. But what I have to do is to consolidate datafiles in a smaller number of files so they can be 'integrated' into Rubrik. Well, Thanks a lot for the help. Sep 24, 2021 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.