Need help with ORA_02236

I get the following error -->  ORA-02236: invalid file name   with:

alter tablespace IGNITE_DATA
add datafile size 10000M autoextend on maxsize 30000M;

Why is that?

This returns the following:

select file_name,tablespace_name,bytes,status,autoextensible
from dba_data_files where tablespace_name = 'IGNITE_DATA';

FILE_NAME                                                                                                                      TABLESPACE_NAME      BYTES             STATUS AUTOEXTENSIBLE
/odb/cnf10/oradata02/CNFDB04P/datafile/o1_mf_ignite_d_8x6dsq8o_.dbf      IGNITE_DATA                  31,457,280,000      AVAILABLE      NO
/odb/cnf10/oradata02/CNFDB04P/datafile/o1_mf_ignite_d_92yps2sr_.dbf      IGNITE_DATA                  31,457,280,000      AVAILABLE      NO
/odb/cnf10/oradata02/CNFDB04P/datafile/o1_mf_ignite_d_92ypxz6f_.dbf      IGNITE_DATA                  31,457,280,000      AVAILABLE      NO
/odb/cnf10/oradata02/CNFDB04P/datafile/o1_mf_ignite_d_92yq3w86_.dbf      IGNITE_DATA                31,457,280,000      AVAILABLE      NO
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
ORA-02236: invalid file name   with:
 alter tablespace IGNITE_DATA   add datafile size 10000M autoextend on maxsize 30000M;

You are missing the file name.  Unless you are using Oracle Managed files,you need the file name.

See: Adding and Dropping Data Files and Temp Files: Examples

The current file names suggest they were created as Oracle Managed but the error seems to imply the tablespace is no longer Oracle managed.

I'll see if I can find more information.
slightwv (䄆 Netminder) Commented:
I've not done anything with Managed Files so this is a little new for me.

The example in the docs for Oracle Managed just have the add datafile.  No other parameters.  Maybe that is the error.

slightwv (䄆 Netminder) Commented:
Try what you had without the "size 10000M".
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rao_SAuthor Commented:
I tried the above two options, I still get the same error.

How do I give a name to the data file name? I thought that is automatically picked by oracle. The same command works on other databases so I dont know why not on this one?
Mark GeerlingsDatabase AdministratorCommented:
I haven't used Oracle Managed files either.  Without that, you have to add the actual file name (in single quotes and including the full path) to that command, something like this:

alter tablespace IGNITE_DATA
 add datafile '/odb/cnf10/oradata02/CNFDB04P/datafile/o1_mf_ignite_d_92yr4x97_.dbf '
size 10000M autoextend on maxsize 30000M;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
>>The same command works on other databases so I dont know why not on this one?

Then maybe you should wait for another Expert since I haven't messed with Managed Files.

I will leave you with this:
Check the following spfile parameters from a database where it worked and the one where it doesn't.


>>to that command, something like this:

I wouldn't do that.  If it is using Managed Files, it might keep track of the names it creates.  Manually adding one might mess with things.
Rao_SAuthor Commented:
I tried the above, it worked...
Rao_SAuthor Commented:
Thank you!
slightwv (䄆 Netminder) Commented:
>>I tried the above, it worked...

Let's hope it didn't mess anything up...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.