Can You Alter a Datafile From the CDB$ROOT?

Published: Thu, 05/20/21

What do you think the answer is?

Some of you might be saying YES, of course you can, some of you might be saying NO, you can’t…and you know why there would be two different answers? Because I didn’t define what datafile I am referring to! Am I referring to a datafile that belongs to the CDB$ROOT, or am I referring to a datafile that belongs to a PDB?

So the answer to my question is “It depends!“

When I was in “DBA school”, I had an awesome teacher, and his answer to most of the questions was “It depends!” And it really does, because we can find ourselves in so many circumstances that a clear yes or no answer cannot be given. It depends means, you need to get more information before you can give an accurate answer.

But I am going off topic here, so let’s return to my original question, can you alter a datafile from the CDB$ROOT?

If you are altering a datafile or even a tablespace, as in changing the size, turning autoextend on, for a datafile that belongs to the CDB$ROOT, then the answer is YES, you can do it. On the other hand, if you are altering a datafile that belongs to a PDB, then you cannot alter it, when connected to the CDB$ROOT. You will receive an error that such file doesn’t exist. Let me show you:

sqlplus / as sysdba

show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL>select con_id, file_name from cdb_data_files order by 1,2;

CON_ID FILE_NAME

------ ------------------------------------------------------------

1 C:\ORACLE\ORADATA\TESTDB\SYSAUX01.DBF

1 C:\ORACLE\ORADATA\TESTDB\SYSTEM01.DBF

1 C:\ORACLE\ORADATA\TESTDB\UNDOTBS01.DBF

1 C:\ORACLE\ORADATA\TESTDB\USERS01.DBF

3 C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\UNDOTBS01.DBF

3 C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSAUX.DBF

3 C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSTEM.DBF

3 C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\USERS01.DBF

--alter file that belongs to CDB$ROOT

SQL>alter database datafile 'C:\ORACLE\ORADATA\TESTDB\SYSAUX01.DBF'

autoextend on next 1M maxsize 2G;

Database altered.

--alter file that belongs to a PDB

SQL>alter database datafile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\USERS01.DBF'

autoextend on next 1M maxsize 2G;

alter database datafile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\USERS01.DBF'

autoextend on next 1M maxsize 2G

*

ERROR at line 1:

ORA-01516: nonexistent log file, data file, or temporary file

"C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\USERS01.DBF" in the current container

The lesson here is, just because you can see the datafiles of the PDBs from within the CDB$ROOT, through the cdb_data_files view, it doesn’t mean you can alter the datafiles in the PDB. However the solution is, to connect to the appropriate PDB and alter the datafile from within the PDB!

SQL>show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 TESTDBPDB READ WRITE NO

SQL>alter session set container=TESTDBPDB;

Session altered.

SQL>alter database datafile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\USERS01.DBF'

autoextend on next 1M maxsize 2G;

Database altered.

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the May special, only $16.99 CAD!

Did you know you can test the course for 30 days with no risk?

Remember - sharing is caring...

Forward this email to a friend!

You're the best!

Sincerely,

Diana Robete

diana@dbaparadise.com