Rao_S
asked on
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,stat us,autoext ensible
from dba_data_files where tablespace_name = 'IGNITE_DATA';
FILE_NAME TABLESPACE_NAME BYTES STATUS AUTOEXTENSIBLE
/odb/cnf10/oradata02/CNFDB 04P/datafi le/o1_mf_i gnite_d_8x 6dsq8o_.db f IGNITE_DATA 31,457,280,000 AVAILABLE NO
/odb/cnf10/oradata02/CNFDB 04P/datafi le/o1_mf_i gnite_d_92 yps2sr_.db f IGNITE_DATA 31,457,280,000 AVAILABLE NO
/odb/cnf10/oradata02/CNFDB 04P/datafi le/o1_mf_i gnite_d_92 ypxz6f_.db f IGNITE_DATA 31,457,280,000 AVAILABLE NO
/odb/cnf10/oradata02/CNFDB 04P/datafi le/o1_mf_i gnite_d_92 yq3w86_.db f IGNITE_DATA 31,457,280,000 AVAILABLE NO
alter tablespace IGNITE_DATA
add datafile size 10000M autoextend on maxsize 30000M;
Why is that?
This returns the following:
select file_name,tablespace_name,
from dba_data_files where tablespace_name = 'IGNITE_DATA';
FILE_NAME TABLESPACE_NAME BYTES STATUS AUTOEXTENSIBLE
/odb/cnf10/oradata02/CNFDB
/odb/cnf10/oradata02/CNFDB
/odb/cnf10/oradata02/CNFDB
/odb/cnf10/oradata02/CNFDB
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.
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;
Try what you had without the "size 10000M".
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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.
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_
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.
ASKER
I tried the above, it worked...
ASKER
Thank you!
>>I tried the above, it worked...
Let's hope it didn't mess anything up...
Let's hope it didn't mess anything up...
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.