Tablespace and Datafile Management

Table space and Data file Management

Create SmallFile Tablespace

Create smallfile tablespace safal datafile ‘+data(datafile)’ size 10m autoextend on next 10m maxsize 50m logging extent management local segment space management auto;

Add Datafile to a Tablespace

Alter Tablespace safal add Datafile ‘+data(Datafile)’ size 10m autoextend on next 10m maxsize 50m;

Drop Datafile

Alter tablespace safal drop Datafile <Datafile_number>;

Drop Tablespace

Drop tablespace safal including contents and datafiles;

Change Default Permanent tablespace of a database

Select * from database_properties;

Alter database default tablespace safal;

Create undo tablespace with no retention noguarantee

Create smallfile undo tablespace safal Datafile ‘+data(Datafile)’ size 10m autoextend on next 10m maxsize 50m extent management local retention noguarantee;

Change undo tablespace to a retention guarantee

Alter tablespace safal retention guarantee;

Change default undo tablespace

Alter system set undo_tablespace=SAFAL;

Create Temporary Tablespace

Create smallfile temporary tablespace demo tempfile ‘+data(tempfile)’ size 10m autoextend on next 10m maxsize 50m extent management local uniform size 10k segment space management manual;

Change Default Temporary Tablespace

Alter database default temporary tablespace demo;