1 person found this helpful
CREATE TABLESPACE tablespacename DATAFILE 'Path'
AUTOEXTEND ON MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
if i create the tablespace with following statement, upto what extend the datafile size can grow (is it OS dependent)?
Is it OS dependent ? Probably not, maybe yes, it depends on your OS. See Comparison of file systems - Wikipedia, the free encyclopedia
If my tablespace is nearly 95% occupied, then do i need to manually resize the datafile?
If 95% means 95% of maximum extension, and you think the remaining 5% is not enough, then yes (consistently with the above) : you can increase MAXSIZE up to UNLIMITED, which means 4 million database blocks. You can always add another datafile, as long as you don't exceed the maximum number of datafiles per tablespace (normally 1022).