adding datafile to tablespace
Can anyone please answer these questions in order of appearance for me?
I used OEM to check out my tablespace and I realixed that:
SIZE is 1000m and (USED) is 997m
1.Does this mean that the tablespace has used it's maxextents that was allocated to it?
2.How do I know if the tablespace has used its maxextents allocated to it.
3. Is it always better to RESIZE the datafiles when adding more space to it OR it's always better to add another datafile to the tablespace?
defrag that tablespace/datafile
I would defrag the tablespace/datafile.
use these two statements. the first will tell you what tablespaces are fragmented:
where percent_extents_coalesced<> 100;
alter tablespace (tablespace_name) coalesce;
see if that helps at all.
you should probably do an export/import as well.