Managing Table spaces

Note: Here the lmtbsb is used for tablespace name

Creating Tablespaces

Syntax:

CREATE TABLESPACE <TS NAME> DATAFILE 'Path of the datafiles.dbf' SIZE <Size>M

Eg:

CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M

Renaming Tablespaces

Syntax:

ALTER TABLESPACE <OLD TS NAME> RENAME to <NEW TS NAME>;

Eg:

ALTER TABLESPACE users RENAME TO usersts;

Altering a Tablespace

ALTER TABLESPACE <TS NAME>

ADD DATAFILE 'Path of the datafiles.dbf' SIZE <New Size>M

Dropping Tablespaces

DROP TABLESPACE users INCLUDING CONTENTS;

DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;

Viewing Tablespace Information

The following data dictionary and dynamic performance views provide useful information about the tablespaces of a database.

View	Description
V$TABLESPACE	Name and number of all tablespaces from the control file.
DBA_TABLESPACES, USER_TABLESPACES	Descriptions of all (or user accessible) tablespaces.
DBA_TABLESPACE_GROUPS	Displays the tablespace groups and the tablespaces that belong to them.
DBA_SEGMENTS, USER_SEGMENTS	Information about segments within all (or user accessible) tablespaces.
DBA_EXTENTS, USER_EXTENTS	Information about data extents within all (or user accessible) tablespaces.
DBA_FREE_SPACE, USER_FREE_SPACE	Information about free extents within all (or user accessible) tablespaces.
V$DATAFILE	Information about all datafiles, including tablespace number of owning tablespace.
V$TEMPFILE	Information about all tempfiles, including tablespace number of owning tablespace.
DBA_DATA_FILES	Shows files (datafiles) belonging to tablespaces.
DBA_TEMP_FILES	Shows files (tempfiles) belonging to temporary tablespaces.
V$TEMP_EXTENT_MAP	Information for all extents in all locally managed temporary tablespaces.
V$TEMP_EXTENT_POOL	For locally managed temporary tablespaces: the state of temporary space cached and used for by each instance.
V$TEMP_SPACE_HEADER	Shows space used/free for each tempfile.
DBA_USERS	Default and temporary tablespaces for all users.
DBA_TS_QUOTAS	Lists tablespace quotas for all users.
V$SORT_SEGMENT	Information about every sort segment in a given instance. The view is only updated when the tablespace is of the TEMPORARY type.
V$TEMPSEG_USAGE	Describes temporary (sort) segment usage by user for temporary or permanent tablespaces.

Creating a Locally Managed Tablespace

CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

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

Specifying Segment Space Management in Locally Managed Tablespaces

CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Altering a Locally Managed Tablespace

ALTER TABLESPACE lmtbsb ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;

Altering tablespace availability (ONLINE/OFFLINE/READ ONLY/READWITE).

ALTER TABLESPACE lmtbsb online/offline/read only/read write

Creating a Bigfile Tablespace

CREATE BIGFILE TABLESPACE bigtbs DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G

You can specify SIZE in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).

Altering a Bigfile Tablespace

Resize:

ALTER TABLESPACE bigtbs RESIZE 80G;

Autoextend

ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;

Identifying a Bigfile Tablespace

DBA_TABLESPACES

USER_TABLESPACES

V$TABLESPACE

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;

ALTER TABLESPACE lmtemp2 TABLESPACE GROUP group2;

ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';

Tablespace lmtemp3 no longer belongs to any group. Further, since there are no longer any members of group1, this results in the implicit deletion of group1.

Assigning a Tablespace Group as the Default Temporary Tablespace

ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;

Migrating the SYSTEM Tablespace to a Locally Managed Tablespace

EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

Note:

After the SYSTEM tablespace is migrated to locally managed, any dictionary-managed tablespaces in the database cannot be made read/write. If you want to be able to use the dictionary-managed tablespaces in read/write mode, then Oracle recommends that you first migrate these tablespaces to locally managed before migrating the SYSTEM tablespace.

Listing Tablespaces and Default Storage Parameters

SELECT TABLESPACE_NAME "TABLESPACE",INITIAL_EXTENT INITIAL_EXT",NEXT_EXTENT "NEXT_EXT",MIN_EXTENTS "MIN_EXT",   MAX_EXTENTS "MAX_EXT",PCT_INCREASE FROM DBA_TABLESPACES;

Listing the Datafiles and Associated Tablespaces of a Database

SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME FROM DBA_DATA_FILES;

Displaying Statistics for Free Space (Extents) of Each Tablespace

SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,COUNT(*)   "PIECES",MAX(blocks) "MAXIMUM",MIN(blocks) "MINIMUM",   AVG(blocks) "AVERAGE",SUM(blocks) "TOTAL" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME, FILE_ID;