TABLE SPACE MANAGEMENT

To create table space raw/ empty storage or mount point will be given in terabytes

 

In one table space we can create 1024 data files

One data file size is 32GB

 

In table space it contains tables, segments, extents, blocks, rows, data

In this the tables and data are physical remaining all are logical

Table space is also logical

 

The default block size is 8KB

We can change the block size manually to 12kb and 16kb

 

To create table space use this command

We should give CRD files location only to create table space

 

Create tablespace tablespace name datafile ‘/mount point /directory /data file/.table space name.dbf’ size2g;

 

For eg: employee table

Emp id	Emp name	Emp Sal	Emp bon	Emp OT
150	Mega star	40,000	2000	8000
786	Power star	56,000	3000	4000
111	Super star	48,000	2600	6000

 

Command to create table:

 

Create table emp (emp id num (20), emp sal integer (20), e name varchar 2(20) );

Here integer means numbers, varchar means spelling

Semantic check means error in table space

Syntax check means error in spelling

 

To see our user:  fire this command

 

Select username, account_status, default_table space, temporary _ tablespace, profile from dba_users;

 

Sql> col username for a20;

        Col account_status for a20;

 

Then it shows how many table spaces exists in our user

 

System

Sysax

Temp

Users

Undo

 

These are default table sapce

 

Command:

 

Select name, open_mode, log_mode, database_role, controlfile_type from v$database;

 

V$database means it gives the full information of database

 

Command output:

 

Name            open mode               log mode           control type

Modi            Read/write              Archive log           primary

 

If open mode is in read/write mode then it is primary

 

Select * from v$data base ( to view the table space in our user)

Data file addition:

 

We can add n number of data files in data base

We shouldn’t remove any data file either it is personal file also

 

To add data file command:

 

Alter tablespace tablespace name add datafile ‘/path/path/path/path /path/.dbf’ size 2g;

 

To resize file  command:

 

Alter tablespace tablespace name resize datafile ‘/path/path/path/ path/.dbf’ resize 4g;

 

Alter tablespace tablespace name autoextend datafile/path/location. dbf' 32G

 

To move file location:

 

Alter database move /path/path/path/abc 01.dbf to /backup/path/fasak/ xyz 01.dbf.size2g;

 

After filling 75% data in table space it shows threshold.

It may be 80% or 90% depends upon the company

 

( ; ) semi column are mandatory in sql commands