By default each database will have five
tablespaces in oracle 10g.
- System : Tablespace-contains data dictionary of database
- Sysaux : Contains database statistics
- Undo : Conatins Pre Image data
- Temporary : Temporary oprations are performed in this if pga is not enough
- Users : Default tablespace for all DB users / Application schemas
Creating
tablespace :
Sql> create
tablespace USER1 datafile '/u01/home/prod1/user.dbf' size 10m autoextend on
maxsize 100m default storage (next 10m);
To
check tablespace information
Sql> select
tablespace_name from dba_tablespaces;
Adding a datafile to a tablespace
Sql> alter
tablespace USER1 add datafile
‘/u02/user/oratab/prod/uuser2.dbf’ size 100m autoextend on;
Deleting
a datafile from a tablespace
Sql> alter
tablespace USER1 drop datafile
'/u01/home/prod1/user.dbf';
Droping
a tablespace
Sql> drop
tablespace USER1;
// it will drop
tablespace logically at database level.
Sql> drop
tablespace USER1 including contents and datafiles;
// it will drop
tablespace logically(database level) and physically(o/s level)
Reusing
orphan datafile
Sql> create
tablespace USER1 datafile '/u01/home/prod1/user.dbf' reuse;
Resize
a Datafile
Sql> alter database datafile
'/u01/home/prod1/user.dbf' resize 50m;
Making a tablespace as read only
Sql> alter
tablespace user1 read only;
Sql> select
tablespace_name,status from dba_tablespaces;
Sql> alter
tablespace user1 read write;
Making
a tablespace offline
Sql> alter
tablespace user1 offline; // Users can not access this tablespace in this
state.
Sql> alter
tablespace user1 online;
Renaming
of tablespace
Sql> alter
tablespace user1 rename to employes1;
Steps
for Renaming a datafile in tablespace
Steps:-
1.make
tablespace offline
Sql> alter tablespace user1 offline;
2. at os level
rename the datafile
$cd /u01/app/oracle/data/
$mv user1.dbf user00.dbf
3. Update the
controlfile for this datafile.
Sql> alter database rename file ‘/u01/app/oracle/data/user1.dbf’
to
‘/u01/app/oracle/data/user00.dbf’;
4. Online the
tablespace
Sql> alter tablespace user1 online;
Select tablespace_name, file_name from dba_data_files;
Moving
table (‘emp’) from one tablespace to another tablespace (users)
Sql> alter
table emp move tablespace users;
Moving
index from one tablespace to another tablespace
Sql> alter
index emp_indx rebuild tablespace user;
To
check database size
Select sum(bytes)/1024/1024 “size in MB” from
dba_data_files
To
check free space in database
Select
sum(bytes)/1024/1024 from dba_free_space;
Views:-
V$tablespace V$datafile
Dba_tablespaces User_tablespaces
Dba_data_files Dba_segments
Dba_extents sm$ts_free
Sm$ts_used sm$ts_avail
