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
Rao_SAsked:
Who is Participating?
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
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3002.htm#SQLRF01002


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.
0
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.

ALTER TABLESPACE omf_ts1 ADD DATAFILE;
0
slightwv (䄆 Netminder) Commented:
Try what you had without the "size 10000M".
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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?
0
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;
0

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.
from:
http://docs.oracle.com/cd/B28359_01/server.111/b28310/omf002.htm

DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
DB_RECOVERY_FILE_DEST


>>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.
0
Rao_SAuthor Commented:
I tried the above, it worked...
0
Rao_SAuthor Commented:
Thank you!
0
slightwv (䄆 Netminder) Commented:
>>I tried the above, it worked...

Let's hope it didn't mess anything up...
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.