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.
Increase the parameter, db_files in init.ora and bounce the db.
That limit is setup when you create your database. I think the only way to change it is to recreate your control file with a larger MAXDATAFILES entry.
Ah, I was assuming db_files had already topped out...
Hmm, strange. I did that once before.
You may have to recreate the control file with more number of datafiles.
I guess you can issue "alter database backup controlfile to trace noresetlogs" and edit the .trc file created accordingly.
(the .trc file is usually created in the UDUMP directory)
Startup the DB in NOMOUNT and execute the edited tracefile.
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.
I don't see any easy way of accomplishing this.
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...
can not reuse the controlfile
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.
Click Here to Expand Forum to Full Width