I tried to create a new tablspace and of course a datafile but i got a error :
cannot add any more database files : limit of 30 exeeded.
Please tell me what i should do? Oracle version 7.1.4 and Unix os. Thanks in advance.
I appreciate your help very much. I'd like to ask you this:
I found out that there are 2 unused datafiles (contains no file) on our database. Could they be deleted or removed? If so, please tell me how. Thanks thousands times.
If the tablespace has just the datafiles you want to remove and/or you do not care about the data in that tablespace you can drop the tablespace and recreate it.
(in such a situation, you may use "drop tablespace table_space including contents")
The otherway is to do an export of the objects from the tablespace where the datafile resides, drop the tablespace, recreate it accordingly, import the object/data.
"I found out that there are 2 unused datafiles (contains no file) on our database", do you mean that the files are not available on the OS filesystem but the entry is there in the DB?
I would advise you to go and change your control file with new maxdatafiles limit than try dropping unused datafiles. The pain you will be taking for dropping datafiles(unused) will be much more than recreating controlfile, even from the risk perspective and flexibility.
What if tomorrow you run outta space again ? that time you will not be left with unused datafiles to drop and accomadate new datafiles. So, Best bet is change maxdatafiles limit by recreating control file.
If you want steps search for 'rename database', you have bunch of postings on how to recreate controlfile.
Remember! you don't want to rename database but redefine maxdatafiles limit while following the steps in postings...
I tried to recreate our controlfile but i got an error: can not reuse the old controlfile b'cause its size (100) is smaller than needed (130). Then i bounced the database successfully but when i tried to connect to it, i got the error:
ORA-01033:ORACLE initialization or shutdown in progress.
I waited and waited and tried to access the database again but unsuccessfully. Please tell what i did wrong and what i should do to fix the problem. Thanks alot in advance.