Could somebody clarify whether tablespace expansion procedure in Oracle 11g database requires any downtime or could impact availability of the database? My understanding that this can be done "on the fly" w/o downtime or restricted-mode (at least when we just allocate free space from physical disk)... but I guess there could be some exclusions caveats to this?
There shouldn't be any impact adding datafiles to an existing tablespace.
Easiest is to let oracle manage the files, make sure you have DB_CREATE_FILE_DEST set:
ALTER SYSTEM SET DB_CREATE_FILE_DEST = '%ORACLE_BASE%\oradata\';
Now the only thing to do if your existing datafiles are full or hitting the max autoextend limit is:
alter tablespace USERS add datafile;
where USERS is the name of the tablespace.