Tablespace Management - I
By default each database will
have five tablespaces in oracle 10g.
1.
System :
Tablespace-contains data dictionary of database
2.
Sysaux :
Contains database statistics
3.
Undo : Contains
Pre Image data
4.
Temporary :
Temporary oprations are performed in this fm if pga is not enough
5.
Users : Default
tablespace for all DB users / Application schemas
Creating tablespace :
Sql>
create tablespace tbs1 datafile '/u01/home/prod/tsb.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 fm add datafile
‘/u02/user18/demo/data/fm02.dbf’ size 100m autoextend on;
Deleting a datafile from a tablespace
Sql>
alter tablespace fm drop datafile
‘/u02/user18/demo/data/fm02.dbf’;
Droping a tablespace
Sql>
drop tablespace fm; à it will drop tablespace and datafile at database level.
Sql>
drop tablespace fm including contents and datafiles; à it will drop tablespace
logically(database
level) and physically(o/s level)
Reusing orphan datafile
Sql>
create tablespace fm2 datafile ‘/u01/user18/demo/data/fm01.dbf reuse;
Making a
tablespace as read only
Sql>
alter tablespace fm read only;
Sql>
select tablespace_name,status from dba_tablespaces;
Sql>
alter tablespace fm read write;
Making a tablespace offline
Sql>
alter tablespace fm offline; à Users can not access this tablespace in this state.
Sql>
alter tablespace fm online;
Renaming of tablespace
Sql>
alter tablespace fm rename to fm1;
Renaming a datafile in tablespace
Steps:-
1.make
tablespace offline
Sql> alter tablespace fm offline;
2.
at os level rename the datafile
$cd /u01/user18/demo/data/
$mv fm01.dbf fm03.dbf
3. Update
the controlfile for this datafile.
Sql> alter database rename file
‘/u01/user18/demo/data/fm01.dbf’ to
‘/u01/user18/demo/data/fm03.dbf’;
4. Online
the tablespace
Sql> alter tablespace fm online;
Select tablespace_name, file_name from dba_data_files;
Relocating a datafile in tablespace
Steps:-
1.make
tablespace offline
Sql> alter tablespace fm offline;
2.
at os level rename the datafile
$cd /u01/user18/demo/data/
$mv fm03.dbf ../fm03.dbf
3.
update the controlfile for this datafile.
Sql>
alter database rename file ‘/u01/user18/demo/data/fm03.dbf’ to
‘/u01/user18/demo/fm03.dbf’;
4.
online the tablespace
Sql> alter tablespace fm online;
Sql>Select tablespace_name,file_name from
dba_data_files;
Moving table from on tablespace to another tablespace
Sql>
alter table emp move tablespace fm;
Moving index from on tablespace to another tablespace
Sql>
alter index emp_indx rebuild tablespace fm;
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
No comments:
Post a Comment