Resizing Datafile:
SQL> alter database datafile ' /oracle/PRD/sapdata1/sr3usr_1/sr3usr.data8 ' resize 20000M;
===============================================================================================
Renaming tablespaces:
You must follow the rules when renaming a tablespace:
You must set compatibility level to at least 10.0.1.
You cannot rename the SYSTEM or SYSAUX tablespaces.
You cannot rename an offline tablespace.
You cannot rename a tablespace that contains offline datafiles.
Renaming a tablespace does not changes its tablespace identifier.
Renaming a tablespace does not change the name of its datafiles.
Tablespace Rename Benefits
Tablespace rename provides the following benefits:
It simplifies the process of tablespace migration within a database.
It simplifies the process of transporting a tablespace between two databases.
You can rename a permanent or temporary tablespace using the ALTER TABLESPACE RENAME statement. Just remember that you cannot rename SYSTEM or SYSAUX tablespace:
SQL> alter tablespace USERS rename to PSAPUSER;
===============================================================================================
Renaming datafiles of a single offline tablespace:
While the database is open, put the tablespace offline, rename the datafile at the operating system level, rename the datafile at the database level and finally take the tablespace online again.
SQL> alter tablespace USERS rename datafile ' /oracle/PRD/sapdata1/sr3usr_1/sr3usr.data1 ' to '/oracle/PRD/sapdata1/sr3usr_1/users01_renamed.dbf';
===============================================================================================
