DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: exceed limit of datafile numbers

  1. #1
    Join Date
    Sep 2000
    Posts
    31

    Unhappy

    Hi Guys,
    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.

    Jeanie :)

  2. #2
    Join Date
    Apr 2000
    Location
    Baltimore, MD
    Posts
    759
    Increase the parameter, db_files in init.ora and bounce the db.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Ah, I was assuming db_files had already topped out...
    Jeff Hunter

  5. #5
    Join Date
    Apr 2000
    Location
    Baltimore, MD
    Posts
    759

    Angry

    Hmm, strange. I did that once before.

  6. #6
    Join Date
    Aug 2000
    Posts
    194
    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.

  7. #7
    Join Date
    Sep 2000
    Posts
    31
    Hi guys,
    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.

    Jn1971 :)

  8. #8
    Join Date
    Aug 2000
    Posts
    194
    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.

    Note:

    By
    "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?

  9. #9
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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...


  10. #10
    Join Date
    Sep 2000
    Posts
    31

    Unhappy can not reuse the controlfile

    Hi guys,
    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.

    Jn1971 :(

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width