Temporary Tablespaces

Creating a Locally Managed Temporary Tablespace

CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf' SIZE 20M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

Altering a Locally Managed Temporary Tablespace

ALTER TABLESPACE lmtemp ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE;

ALTER TABLESPACE lmtemp TEMPFILE OFFLINE;

ALTER TABLESPACE lmtemp TEMPFILE ONLINE;

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;

Resizing a Temporary File

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;

Delete a Temporary file

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;

Creating a Tablespace Group

CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf'SIZE 50M TABLESPACE GROUP group1;

ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;

Changing Members of a Tablespace Group

CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE '/u02/oracle/data/lmtemp301.dbf'SIZE 25M TABLESPACE GROUP group1;

Adds a Tablespace to an Existing Group

ALTER TABLESPACE lmtemp2 TABLESPACE GROUP group2;

Remove a Tablespace from a Group

ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';

Assigning a Tablespace Group as the Default Temporary Tablespace

ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;

Specifying Nonstandard Block Sizes for Tablespaces

CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K BLOCKSIZE 8K;