Oracle : TABLESPACE MANAGEMENT

TABLESPACE MANAGEMENT

WHAT IS TABLESPACE ?

IT’S A LOGICAL LINK BETWEEN USER AND DATAFILES.IT IS LOGICAL STORAGE OF DATA

 

DICTIONARY: EXTENTS INFORMATION WILL GET FROM BASE TABLES

LOCAL: EXTENTS INFORMATION WILL GET FROM DATAFILE HEADERS

TABLESPACE MANAGEMENT

 

HOW TO CREATE THE TABLESPACE

SYS>> CREATE TABLESPACE <TABLESPACENAME>

DATAFILE  ‘LOCATION’  SIZE 2M;

Sys>>create tablespace  ts1

Datafile  ‘/u01/app/oracle/product/11.2.0/db_1/orcl/ts1.dbf’  size 2m;

Tablespace created

To check whether tablespace is created or not

Desc dba_tablespaces

Sys>> select tablespace_name from dba_tablespaces;

How to make tablespace offline

alter tablespace ts1 offline;

(Generally we make offline for recovery purpose)

How to make tablespace online

Alter tablespace ts1 online;

How to make tablespace read only

Alter tablespace ts1 read only;

How to make tablespace read write

Alter tablespace ts1 read write;

To check tablespaces status

Select tablespace_name,status from dba_tablespaces;

To check tablespacename along with datafiles

Desc dba_data_files

Sys>> select tablespace_name,file_name ,file_id from dba_data_files;

Tablespace _name   file_name                                               file_id

Ts1                        /disk1/oradata/dayaker/ts1.dbf         5

How to add a datafile to existing tablespace

alter tablespace ts1 add

datafile   ‘/disk1/oradata/dayaker/ts2.dbf’   size 4m;

Sys>> select tablespace_name,file_name ,file_id from dba_data_files;

Tablespace _name   file_name                                               file_id

Ts1                          /disk1/oradata/black/ts1.dbf           5

Ts1                        /disk1/oradata/black/ts2.dbf             6

How to drop one datafile from a tablespace

Alter tablespace ts1 drop datafile

‘/disk1/oradata/black/ts2.dbf’;

Note: we cannot drop first datafile from tablespace

How to resize the datafile

Alter database datafile 5 resize 4m;

Alter database datafile ‘/disk1/oradata/black/ts2.dbf’  resize 2m;

 

To check the size of ur datafiles

Select tablespace_name,file_name,bytes/(1024*1024) from dba_data_files;

To check whether datafile is autoextend  on or off

Select file_name,autoextensible from dba_data_files;

How to drop a tablespace

1)Drop tablespace ts1;

2)Drop tablespace ts1 including contents;

3)Drop tablespace ts1 including contents and datafiles;

1) it will drop only the logical tablespace still datafiles are exists at o/s level

2) it will drop tablespace and also it will delete the contents from datafile but not the datafile

3) it will drop tablespace as well as datafile from o/s level

 

How to see the contents of tablespace

Select tablespace_name,contents from dba_tablespaces;

How to see whether the tablespace is plugged in or not

Select tablespace_name,plugged_in from dba_tablespaces;

If plugged in column value is yes it is transportablespace if it NO

then it is a local tablespace

how to see th extent management of tablespace

select tablespace_name,extent_management  from dba_tablespaces;

here we have two types of extent management

• Dictionary: extents information will get from base tables.
• Local: extents information will get from datafile headers.

How to create bigfile tablespace

Create bigfile tablespace bts

Datafile ‘/disk1/oradata/black/bts.dbf’   size 3m;

To check tablespace is bigfile or smallfile tablespace?

Select tablespace_name,bigfile from dba_tablespaces;

Yes: it is a bigfile tablespace

No:it is a smallfile tablespace

Bigfile tablespace can have only one datafile not more than one

Where as smallfile can have one or more than one.

How to resize the bigfile tablespace

Alter tablespace bts resize 5m;

We can resize at tablespace level which is not possible with small file.

How to rename the tablespace

Alter tablespace <oldtablespacename> rename to <newtablespacename>;

How to rename the datafile

Step 1 : make the tablespace offline

Step 2 : copy the datafile at o/s level

Step 3 : rename the datafile at oracle level

Step 4 : make the tablespace online

Demo for renaming datafile

Alter tablespace ts1 offline;

Exit

Cd /disk1/oradata/dayaker

Cp ts1.dbf tsnew.dbf

Sqlplus / as sysdba

Alter tablespace ts1 rename datafile

‘/disk1/oradata/black/ts1.dbf’ to ‘/disk1/oradata/black/tsnew.dbf’;

Alter tablespace ts1 online;

How to display tempfiles information

Select tablespace_name,file_name from dba_temp_files;

How to create new temporary tablespace

Create temporary tablespace tempnew

Tempfile ‘/disk1/oradata/black/tempnew.tmp’  size 4m;