Oracle alter table space syntax (found by Google)

(There is an "Enterprise Manager Console" in the Windows Oracle Start Menu, which allows you to directly edit tablespaces and files)
Alter Database

Open an existing database, and/or modify associated files.

Syntax:

   ALTER DATABASE database_name options

Options:

open/mount options:   MOUNT   MOUNT STANDBY DATABASE   MOUNT CLONE DATABASE   MOUNT PARALLEL   MOUNT STANDBY DATABASE   CONVERT   OPEN [READ ONLY]   OPEN [READ WRITE] RESETLOGS|NORESETLOGS [MIGRATE]   ACTIVATE STANDBY DATABASE   [NATIONAL] CHARACTER SET char_setarchivelog options:   ARCHIVELOG   NOARCHIVELOGbackup and recovery options:   BACKUP CONTROLFILE TO 'filename' [REUSE]   BACKUP CONTROLFILE TO TRACE [RESETLOGS] [AS 'filename' [REUSE]]   CREATE STANDBY CONTROLFILE AS 'filename' [REUSE]   RECOVER recover_clause   RECOVER MANAGED STANDBY standby_recover_clause   END BACKUPDatafile options:   CREATE DATAFILE 'filename' AS filespec   DATAFILE 'filename' ONLINE   DATAFILE 'filename' OFFLINE [DROP]   DATAFILE 'filename' RESIZE int K | M   DATAFILE 'filename' AUTOEXTEND OFF   DATAFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M | UNLIMITED]   DATAFILE 'filename' END BACKUP   RENAME FILE 'data_file_name' TO 'data_file_name'   TEMPFILE 'filename' ONLINE   TEMPFILE 'filename' OFFLINE   TEMPFILE 'filename' DROP [INCLUDING DATAFILES]   TEMPFILE 'filename' RESIZE int K | M   TEMPFILE 'filename' AUTOEXTEND OFF   TEMPFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M | UNLIMITED]redo log options:   ADD LOGFILE [THREAD int] [GROUP int] filespec   ADD LOGFILE MEMBER 'filename' [REUSE] TO GROUP int   ADD LOGFILE MEMBER 'filename' [REUSE] TO 'filename'   DROP LOGFILE GROUP int   DROP LOGFILE ('filename')   DROP LOGFILE MEMBER 'filename'   RENAME FILE 'redolog_file_name' TO 'redolog_file_name'   CLEAR [UNARCHIVED] LOGFILE GROUP int [UNRECOVERABLE DATAFILE]   CLEAR [UNARCHIVED] LOGFILE ('filename') [UNRECOVERABLE DATAFILE]Parallel server options:   CREATE STANDBY CONTROLFILE AS 'filename' [REUSE]   SET DBLOW = 'text'   SET DBHIGH = 'text'   SET DBMAC = ON | OFF   ENABLE [PUBLIC] THREAD int   DISABLE THREAD intBackwards compatibility options:   RENAME GLOBAL_NAME TO database [domain]   RESET COMPATIBILITY

Any option above that could des a 'filename' can be extended to cover multiple files using the Syntax:('Filename1','Filename2 ')

'Filename'[Offline] Drop will only work on a tablespace consisting of a single datafile, for a tablespace comprising multiple datafiles you can only drop a datafile by dropping the entiretablespace.

Some of the commands above can only be used when the database is in a particle state:

Mount, convert-require that the DB is not mounted.
Archivelog, noarchivlog, recover-require that the DB is mounted but not open (must be Mount exclusive-not mount parallel ).
Enable, disable, rename global_name, reset, set-require that the DB is open.

All other options will work with the DB mounted, open or closed as long as none of the files involved are 'in use'

database_nameIs defined when the database is created-it is normally set to the same as the database Sid.

Examples

Resize datafile:

SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' RESIZE 500m;

Take datafile offline:

SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' OFFLINE;

Database altered.

Take datafile online, when doing this, file recovery is needed to update the timestamp in the offline datafile header .:

SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE; ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE * ERROR at line 1: ORA-01113: file 16 needs media recovery ORA-01110: data file 16: 'F:\ORADATA\LIVE\Mydb02.ORA'

SQL> RECOVER DATAFILE 'F:\oradata\live\Mydb02.ora'; Media recovery complete.

SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE;

Database altered

Change the database character set to utf8, this must be done with no users connected (restricted session)

SQL> SHUTDOWN IMMEDIATE;   -- or NORMAL    <do a full backup>SQL> STARTUP MOUNT;SQL> ALTER SYSTEM ENABLE RESTRICED SESSION;SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;SQL> ALTER DATABASE OPEN;SQL> ALTER DATABASE CHARACTER SET UTF8;SQL> SHUTDOWN IMMEDIATE;   -- or NORMALSQL> STARTUP;

"You know, the very powerful and the very stupid have one thing in common, they don't alter their views to fit the facts, they alter the facts to fit the views, which can be uncomfortable, if you happen to be one of the facts that needs altering. "-doctor
Who

Related commands:

Database-Create Database
Recover
Shutdown
Startup
Alter tablespace
Alter System

Related views:

Global_name dba_data_files v $ controlfile v $ Database
V $ datafile v $ log V $ logfile