Tablespace Management in Oracle - II (LMT and DMT)

Tablespace Management - II

Creating the locally managed tablespace (LMT) with uniform extent size 

Sql> CREATE TABLESPACE fmts DATAFILE '/u02/user18/test/data/fmts01.dbf' SIZE 50m

         

EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 100k

FMT with ASSM (Automatic Segment Space Management)

Sql> CREATE TABLESPACE fmts DATAFILE  '/u02/user18/test/data/fmts01.dbf' SIZE 50m EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 100k  SEGMENT   SPACE MANAGEMENT auto

Converting Localy Managed Tablespace to Dictionary Managed Tablespace

Sql>exec dbms_space_admin.tablespace_migrate_from_local(‘FMTS’);

Creating the dictionary managed tablespace

Sql> CREATE TABLESPACE dmts DATAFILE ‘/u02/user18/test/dmts01.dbf' SIZE 50m EXTENT MANAGEMENT dictionary DEFAULT STORAGE (INITIAL 50k NEXT 50k MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

Converting Dictionary Managed Tablespace to Localy Managed Tablespace

Sql>exec dbms_space_admin.tablespace_migrate_to_local ('DMTS');

Creating bigfile tablespace

Sql>CREATE BIGFILE TABLESPACE bigtbs DATAFILE ‘/u02/user18/test/data/bigtbs01.dbf’ SIZE   50g

Creating temporary tablespace

Sql>CREATE TEMPORARY TABLESPACE fmtemp TEMPFILE ‘/u02/oracle/data/fmtemp01.dbf' SIZE 20m EXTENT MANAGEMENT local UNIFORM SIZE 16m;

Changing default temporary tablespace of database

Sql>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;

Creating temporary tablespace group

Sql>CREATE TEMPORARY TABLESPACE temp TEMPFILE  '/u02/user18/test/data/temp01.dbf' size 50m TABLESPACE GROUP tmpgrp;

Removing a tablespace from a group :

Sql>ALTER TABLESPACE fmtemp3  TABLESPACE GROUP '';

Changing member of a tablespace group

Sql>ALTER TABLESPACE fmtemp2 TABLESPACE GROUP group2;

Assigning a Tablespace Group as the Default Temporary Tablespace

Sql>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE group2;

 Views for temporary tablespace

      v$tempfile

      dba_temp_files

      dba_tablespace_groups 

Creating Undo TBS

Sql>CREATE UNDO TABLESPACE undotbs_02 DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' SIZE 2M REUSE AUTOEXTEND ON;

Sql>ALTER TABLESPACE undotbs_01 ADD DATAFILE '/u01/user18/test/undo0102.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

Switching Undo Tablespaces

Sql>ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02   SCOPE=both;

Views for undo tablespace

          V$undostat   

V$rollstat       

V$transaction

dba_undo_extents

View for Sysaux tablespace:

V$sysaux_occupants