top of page
CerebroSQL

Oracle:

ALTER TABLESPACE

ALTER TABLESPACE tablespace
{ DEFAULT [ table_compression ] [ storage_clause ]
| MINIMUM EXTENT size_clause
| RESIZE size_clause
| COALESCE
| SHRINK SPACE [ KEEP size_clause]
| RENAME TO new_tablespace_name
| { BEGIN | END } BACKUP
| datafile_tempfile_clauses
| tablespace_logging_clauses
| tablespace_group_clause
| tablespace_state_clauses
| autoextend_clause
| flashback_mode_clause
| tablespace_retention_clause
} ;

datafile_tempfile_clauses ::=
{ ADD { DATAFILE | TEMPFILE }
[ file_specification [, file_specification ]... ]
| DROP {DATAFILE | TEMPFILE } { 'filename' | file_number }
| SHRINK TEMPFILE { 'filename' | file_number } [KEEP size_clause]
| RENAME DATAFILE 'filename' [, 'filename' ]...
TO 'filename' [, 'filename' ]...
| { DATAFILE | TEMPFILE } { ONLINE | OFFLINE }
}
tablespace_logging_clauses ::=
{ logging_clause
| [ NO ] FORCE LOGGING
}
tablespace_group_clause::=
TABLESPACE GROUP { tablespace_group_name | '' }
tablespace_state_clauses::=
{ { ONLINE
| OFFLINE [ NORMAL | TEMPORARY | IMMEDIATE ]
}
| READ { ONLY | WRITE }
| { PERMANENT | TEMPORARY }
}
autoextend_clause ::=
AUTOEXTEND
{ OFF
| ON [ NEXT size_clause ]
[ maxsize_clause ]
}
maxsize_clause::=
MAXSIZE { UNLIMITED | size_clause }
flashback_mode_clause ::=
FLASHBACK { ON | OFF }
tablespace_retention_clause::=
RETENTION { GUARANTEE | NOGUARANTEE }

example

ALTER TABLESPACE tbs_01 BEGIN BACKUP;
ALTER TABLESPACE tbs_01 END BACKUP;

ALTER TABLESPACE tbs_02 OFFLINE NORMAL;
ALTER TABLESPACE tbs_02
RENAME DATAFILE 'diskb:tbs_f5.dbf'
TO 'diska:tbs_f5.dbf';
ALTER TABLESPACE tbs_02 ONLINE;

ALTER TABLESPACE tbs_03
ADD DATAFILE 'tbs_f04.dbf'
SIZE 100K
AUTOEXTEND ON
NEXT 10K
MAXSIZE 100K;

ALTER TABLESPACE tbs_03 DROP DATAFILE 'tbs_f04.dbf';

ALTER TABLESPACE temp_demo ADD TEMPFILE 'temp05.dbf' SIZE 5 AUTOEXTEND ON;

ALTER TABLESPACE temp_demo DROP TEMPFILE 'temp05.dbf';

ALTER TABLESPACE temp_demo SHRINK SPACE;

ALTER TABLESPACE omf_ts1 ADD DATAFILE;

ALTER TABLESPACE tbs_03 NOLOGGING;

ALTER TABLESPACE undots1 RETENTION NOGUARANTEE;
ALTER TABLESPACE undots1 RETENTION GUARANTEE;

bottom of page