BABU MADHAV INSTITUTE OF INFORMATION TECHNOLOGY, UTU
[Link].(IT)
Semester-V
Database Administration Essentials
Practical Tutorial: 02
~: Manage Tablespace in Database :~
1) To create new tablespace:
Syntax:
create tablespace <TablespaceName>
datafile ' <DataFileName>.dbf '
size <Size>M/G;
2) To create new Smallfile tablespace:
Syntax:
create smallfile tablespace <TablespaceName>
datafile ' <DataFileName>.dbf '
size <Size>M/G;
3) To create new Bigfile tablespace:
Syntax:
create bigfile tablespace <TablespaceName>
datafile ' <DataFileName>.dbf '
size <Size>M/G;
4) To rename tablespace:
Syntax:
alter tablespace <ExistingTablespaceName> rename to <NewTablespaceName>;
5) To add a new data file in a particular existing tablespace:
Syntax:
alter tablespace <ExistingTablespaceName>
add datafile ' <NewDataFileName>.dbf '
size <Size>M/G;
6) To resize an existing data file in a particular tablespace:
Syntax:
alter database
datafile ' <ExistingDataFileName>.dbf '
resize <NewSize>M/G;
BHAVIK SARANG | UKA TARSADIA UNIVERSITY PAGE 1
BABU MADHAV INSTITUTE OF INFORMATION TECHNOLOGY, UTU
[Link].(IT)
7) To drop a particular existing data file from a particular tablespace:
Syntax:
alter tablespace <ExistingTablespaceName>
drop datafile ' <ExistingDataFileName>.dbf ';
8) To drop an empty tablespace from database:
[For tablespaces without containing table(s)]
Syntax:
drop tablespace <ExistingTablespaceName>;
9) To drop a non-empty tablespace from database:
[For tablespaces containing table(s)]
Syntax:
drop tablespace <ExistingTablespaceName> including contents;
[Including Contents – It is optional. If you specify Including Contents, all table space
content will be deleted. If there are objects in the table space and you do not specify
Including Contents, you will receive an error message.]
10) To drop a tablespace, whose tables are referenced by referential constraints:
Syntax:
drop tablespace <ExistingTablespaceName>
including contents [and | keep] datafiles
cascade constraints;
[And Datafiles – It is optional. It will delete the associated operating system files. If you
are using files managed by Oracle, you can omit the “and datafiles” option, because
Oracle will automatically remove the linked operating system files.]
Keep Datafiles – It is optional. If specified, the linked operating system files will not be
removed. When using files managed by Oracle, if you want to keep operating system
binaries, you must specify the “keep datafiles” option.
Cascade Constraints – It is optional. If you specify “cascade constraints”, all reference
integrity restrictions will be removed, which will meet the following criteria: reference
integrity restriction from a table outside the tablespace_name that refers to a primary key
or a unique key in a table that is inside the tablespace_name.
11) To take a particular tablespace offline or online:
Syntax:
alter tablespace <ExistingTablespaceName> offline;
alter tablespace <ExistingTablespaceName> online;
BHAVIK SARANG | UKA TARSADIA UNIVERSITY PAGE 2
BABU MADHAV INSTITUTE OF INFORMATION TECHNOLOGY, UTU
[Link].(IT)
12) To take an individual datafile of a particular the tablespace offline or online:
Syntax:
alter database datafile ' <ExistingDataFileName> ' offline;
alter database datafile ' <ExistingDataFileName> ' online;
13) To make a particular tablespace read only:
Syntax:
alter tablespace <ExistingTablespaceName> read only;
14) To make a particular tablespace read write:
Syntax:
alter tablespace <ExistingTablespaceName> read write;
~: Manage Temporary in Tablespace :~
15) To change the default temporary tablespace:
[When you create a user without specifying a temporary tablespace, Oracle assigns the
default temporary tablespace TEMP to the user.]
Syntax:
alter database default temporary <ExistingTemporaryTablespaceName>;
16) To create a temporary file in a temporary tablespace:
Syntax:
create temporary tablespace <TemporaryTablespaceName>
tempfile ' <TemporaryFileName>.dbf '
size <Size>M/G;
17) To add a new temporary file in a particular existing temporary tablespace:
Syntax:
alter tablespace <ExistingTemporaryTablespaceName>
add tempfile ' <NewTemporaryFileName>.dbf '
size <Size>M/G;
18) To resize an existing temporary file in a particular temporary tablespace:
Syntax:
alter database
tempfile ' <ExistingTemporaryFileName>.dbf '
resize <NewSize>M/G;
BHAVIK SARANG | UKA TARSADIA UNIVERSITY PAGE 3
BABU MADHAV INSTITUTE OF INFORMATION TECHNOLOGY, UTU
[Link].(IT)
19) To drop a particular existing temporary file from a particular temporary
tablespace:
Syntax:
alter tablespace drop tempfile ' <ExistingTemporaryFileName>.dbf ';
drop tablespace <ExistingTablespaceName> including contents and datafiles;
20) To change the destination/ location of the data / temp files:
Syntax:
alter system set
db_create_file_dest = ' <Destination/LocationPath> ' scope=both;
~: Obtaining Tablespace Information :~
21) To view the list of (or information about) tablespaces available/ created by the user
in database:
Query:
select tablespace_name from dba_tablespaces;
select * from dba_tablespaces;
select * from v$tablespace;
select tablespace_name from dba_data_files;
22) To view the list of (or information about) data files available/ created by the user in
oracle:
Query:
select file_name from dba_data_files;
select tablespace_name, file_name from dba_data_files;
select tablespace_name, file_name, bytes/1024/1024 MB from dba_data_files;
select * from dba_data_files;
select * from v$datafile;
23) To view the list of (or information about) data files available/ created by the user in
a particular tablespace:
Syntax:
select tablespace_name, file_name from dba_data_files
where tablespace_name = ' <TablespaceName> ';
24) To find the current default temporary tablespace:
Query:
select property_name, property_value
from database_properties
where property_name='default_temp_tablespace';
BHAVIK SARANG | UKA TARSADIA UNIVERSITY PAGE 4
BABU MADHAV INSTITUTE OF INFORMATION TECHNOLOGY, UTU
[Link].(IT)
25) To view the list of (or information about) temp files available/ created by the user in
oracle:
Query:
select * from dba_temp_files;
select file_name from dba_temp_files;
select * from v$tempfile;
26) To view the list of (or information about) free spaces available in data files:
Query:
select * from dba_free_space;
27) To view the space allocation in a temporary tablespace:
Query:
select * from dba_temp_free_space;
28) To view the list of (or information about) free spaces available in temp files:
Query:
select * from v$temp_space_header;
~: Manage Table :~
29) To create table by applying constraint(s) in tablespace:
[By default, the tables are created in Default tablespace of the user, so to create
tables in a specify tablespace following syntax is been used.]
Syntax:
Create table <TableName>
( <ColumnName1> <DataType> (<Size>) <ColumnConstraint>,
<ColumnName2> <DataType> (<Size>) <ColumnConstraint> )
tablespace <TablespaceName> ;
~: Extending Space :~
30) To extend the size of tablespace:
Syntax:
create tablespace <TablespaceName>
datafile ' <DataFileName>.dbf '
size <Size>M/G
AUTOEXTEND ON;
[AUTOEXTEND ON – will automatically extend the size of the datafile when needed.]
create tablespace <TablespaceName>
datafile ' <DataFileName>.dbf '
size <Size>M/G
BHAVIK SARANG | UKA TARSADIA UNIVERSITY PAGE 5
BABU MADHAV INSTITUTE OF INFORMATION TECHNOLOGY, UTU
[Link].(IT)
AUTOEXTEND <Size>M/G;
create tablespace <TablespaceName>
datafile ' <DataFileName>.dbf '
size <Size>M/G
AUTOEXTEND ON next <Size>M/G maxsize <Size>M/G;
BHAVIK SARANG | UKA TARSADIA UNIVERSITY PAGE 6