Managing Oracle tablespace and datafiles

It is the best practise to separate user data from system data in an oracle database. Doing so enhances the performance of the database.
Oracle manages its datafiles in logical space known as Tablespace. A tablespace can have multiple datafiles but a single datafile can belong to one and only one tablespace. Datafiles are the physical files that stores data which are divided again into logical segment and extents. It should be noted that autoextend clause extend
the datafile automatically as and when needed but the auto extension of the size of datafile depend on the maximum file size limit of the operating system and it also degrade performance of the database. Locally managed extents maintain its data state information in its own header(bitmapped). This avoids the throughput in the data dictionary, which increases performance as well.

It is the best approach to separate user datafile and user data index file in separate tablespace. Temporary tablesapce are used for sorting operation required during query executions.

--user data holder tablespace and datafiles
CREATE TABLESPACE TABLESPACE_NAME
DATAFILE '%ORACLE_BASE%\ORADATA\SID\DATAFILE01.DBF' SIZE 100M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

--add more datafile to a tablespace

ALTER TABLESPACE TABLESPACE_NAME ADD DATAFILE '%ORACLE_BASE%\ORADATA\SID\DATAFILE02.DBF' SIZE 100M AUTOEXTEND ON;

--user index tablespace and datafiles
CREATE TABLESPACE TABLESPACE_NAME_INDX
DATAFILE '%ORACLE_BASE%\ORADATA\SID\DATA_INDX01.DBF'
SIZE 100M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

--user temporary tablespace and datafiles
CREATE TEMPORARY TABLESPACE TABLESPACE_TEMP
TEMPFILE '%ORACLE_BASE%\ORADATA\SID\TEMP01.DBF'
SIZE 20M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;