I have one tablespace which is almost 90% full. I am still thinking about what is the best method to increase the size. Do I have to autoextend or to add a new datafile? What is the difference between adding new datafile and autoextend? I still have 34% free space where the datafile is located. Our Oracle is in Unix. Thank you in advance.
For most databases, there is little difference between adding a new datafile or extending an existing one. There are a few things to keep in mind though.
The maximum datafile size is based on your DB_BLOCK_SIZE. If your DB_BLOCK_SIZE is 8KB, then the max datafile size is 32GB. If 16KB, then the max is 64GB. If your datafile reaches this limit, you have no choice but to add a new datafile. (The preceeding assumes you are not using a BIGFILE tablespace.)
Certain backup strategies can limit your datafile size. While it is rare these days, your backup tapes may be small. For instance, if your backup tapes can only hold 50GB, then you'll want a maximum datafile size to be small enough so as to fit on a tape, and normally allow multiple files on the tape. For instance, you may want to be able to fit five 10GB files on this tape. In that case, your max datafile size would be a self-imposed 10GB. These situations can be rare if backing up to today's higher capacity tapes. But if you are backing up to DVDs, you may want to impose a 4GB limit on your datafiles.