Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

An Oracle database consists of an infinite number of table Spaces, which are composed of an infinite number of data files that are stored on disk.

With the growth of time and service volume, data files will continue to grow. The default data file is 32 GB, so you need to constantly add data files!

So, here’s the problem! What if you need to add a lot of data files?

The following example uses the LUCIFER table space as an example! OMF is enabled by default.

⭐️ For details about how to enable OMF, see:Oracle OMF parameters

1, add a data file, easy, a line of command done!

alter tablespace LUCIFER add datafile size 30G autoextend off;
Copy the code

2, add 10 data files, trouble point, copy 10 lines can also be done!

alter tablespace LUCIFER add datafile size 30G autoextend off;
alter tablespace LUCIFER add datafile size 30G autoextend off;
alter tablespace LUCIFER add datafile size 30G autoextend off;
alter tablespace LUCIFER add datafile size 30G autoextend off;
alter tablespace LUCIFER add datafile size 30G autoextend off;
alter tablespace LUCIFER add datafile size 30G autoextend off;
alter tablespace LUCIFER add datafile size 30G autoextend off;
alter tablespace LUCIFER add datafile size 30G autoextend off;
alter tablespace LUCIFER add datafile size 30G autoextend off;
alter tablespace LUCIFER add datafile size 30G autoextend off;
Copy the code

3, add 100 data files, headache, copy 100 lines? How about 1,000? 10000 ❓

Of course, just for example, without serious, just to illustrate a concept!

If you need to add more than one tablespace datafile at a time, you can do this with a loop statement and a few lines of code:

begin
  for i in 1.100 loop
    execute immediate 'alter tablespace LUCIFER add datafile size 30G autoextend off';
  end loop;
end;
/
Copy the code

With the above short code, you can create 100 data files. If you need 10000, change 100 to 10000!

If you say you don’t use the OMF parameter, by all means, change it slightly:

begin
  for i in 1.100 loop
    execute immediate 'alter tablespace LUCIFER add datafile ''/oradata/orcl/lucifer'||i||'.dbf'' size 30G autoextend off';
  end loop;
end;
/
Copy the code

Simply concatenate the data file path /oradata/orcl/ with the data file name lucifer and pass in I as the number.

❤️ Remember, this article is about a technique, but also a concept, don’t get bogged down!

This is the end of sharing ~

If you think the article is helpful to you, please like it, favorites it, pay attention to it, comment on it, and support it four times with one button. Your support is the biggest motivation for my creation.

❤️ technical exchange can follow the public number: Lucifer think twice before you do ❤️