Tablespace and Data Files ·

Table spaces and data files

Table space is a logical part of a database, physically, the database data is present in the file, logically speaking, the database is in the table space, the table space consists of one or more data files.

Oracle logical structure includes table spaces, segments, blocks

Oracle can include many table spaces. The tablespace is actually composed of data files.

1) You can control the disk space occupied by the database through the tablespace

2) DBAs can deploy different data types to different locations, helping to improve IO performance while facilitating management operations such as backup and recovery.

Establish tablespace usage

Create Tablespace

Create tablespace data01 datafile ' e:\test\data01.dbf ' size 20m uniform (area size is $) size 128k;

Create a table and put him in the table space.

Add tablespace data01 after creating the table; it's all right.

The role of Table spaces:

When a tablespace is created, the tablespace is in the online state, and the tablespace is accessible, and the table space is readable and writable. However, in the case of system maintenance or data maintenance, it may be necessary to change the state of the tablespace, which is normally operated by a privileged user or DBA.

1) Make Table space offline

Alter tablespace table space name offline.

2) Make Table space online

Alter tablespace table Space Online;

3) Read-only table space

Alter tablespace table space Read only;

4) Readable and writable

Alter tablespace table Space Read write;

5) Display all tables of the tablespace

Select * from all_tables where tablespace_name = ' sp001 ';

6) Know the table name and see what table space it belongs to

Select Tablespace_name, table_name from user_tables WHERE table_name = ' EMP ';

7) Delete Table space

Drop tablespace ' table space ' including contents and datafiles;

8) Extended Table space

A) manually increase

Alter database datafile ' e:test.dbf ' resize 200m;

b) set to automatically increase

ALTER DATABASE datafile '/HOME/ORACLE/TS01.DBF ' autoextend on next 5m MaxSize Unlimited;

Tablespace and Data Files ·