Changing tablespace increment size

Question

One of our datafiles hit the max of 32G. So, we created a 2nd datafile in that tablespace.

However, we forgot to give a default NEXT size. So, file 1 has a NEXT of 50MB. And file 2 has a NEXT of 8k.

These are locally managed, so, I am guessing the only thing to do is create a new tablespace, and move all the objects. Or is there another solution? One question: How do I move TYPES? Do I need to drop & recreate those? Which will invalidate a ton of things.......

Any suggestions? Can I isolate the objects in just that datafile?

Thanking you.

instead of adding 2 data file you can create " BIG FILE" which can grow very big size. and BIGFILE supports chan NEXT value ALTER TABLESPACE my_TBS_name AUTOEXTEND ON NEXT 20G; — Ramki

peter.hrasko.sk peter.hrasko.sk · Accepted Answer · 2017-05-29T08:35:34

You should always consult Oracle documentation first. What you ask for is a straightforward, single-SQL-command-involving action. The crucial piece of knowledge you missed is that you do not alter a tablespace, you alter a datafile.

Proof of concept

First, I'll just query my example tablespace for its block size, as I'll use the value for proving that my datafile was altered correctly.

SQL> select tablespace_name, block_size
SQL> from dba_tablespaces
SQL> where tablespace_name = 'EXAMPLE';

TABLESPACE_NAME                BLOCK_SIZE
------------------------------ ----------
EXAMPLE                              8192

OK, the tbs uses a block size of 8KB.

Now, what my example data files look like?

SQL> select file_name, file_id, tablespace_name, autoextensible, increment_by * &example_tbs_block_size_b / 1048576 as increment_by_mbytes
SQL> from dba_data_files
SQL> where tablespace_name = 'EXAMPLE';

FILE_NAME                          FILE_ID    TABLESPACE_NAME AUTOEXTENSIBLE INCREMENT_BY_MBYTES
---------------------------------- ---------- --------------- -------------- -------------------
D:\ORA\MY_CDB\MY_PDB\EXAMPLE01.DBF         10 EXAMPLE         YES                              1

OK, I see just a single data file with the autoextend of 1MB.

Now alter the datafile...

SQL> alter database datafile 10 autoextend on next &target_autoextend maxsize unlimited;

Database altered

And recheck the tbs datafiles once again

SQL> select file_name, file_id, tablespace_name, autoextensible, increment_by * &example_tbs_block_size_b / 1048576 as increment_by_mbytes
SQL> from dba_data_files
SQL> where tablespace_name = 'EXAMPLE';

FILE_NAME                          FILE_ID    TABLESPACE_NAME AUTOEXTENSIBLE INCREMENT_BY_MBYTES
---------------------------------- ---------- --------------- -------------- -------------------
D:\ORA\MY_CDB\MY_PDB\EXAMPLE01.DBF         10 EXAMPLE         YES                              8

And, voilá, I have an autoextend of 8MB.

Changing tablespace increment size

1 Answers