Shulou(Shulou.com)06/01 Report--

Tablespaces and data files

1. Tablespace introduction

1)。 The relationship between tablespace and data file

In Oracle database, the relationship between table space and data file is very close, which depends on each other, that is to say, data file must be created when table space is created, and table space must be specified when table space is increased.

The highest logical layer in Oracle disk space management is the table space (TABLESPACE), and its next layer is the segment (SEGMENT), which can only exist in one table space. The next layer of the segment is the extent, a segment can have multiple extents, each extent can only exist in one data file. If a segment spans multiple data files, the extents of each data file are independent of each other under this segment. The next layer of the disk area is the data block, which is the lowest logical partition in disk space management. A set of data blocks form a disk area.

Relationship between database, tablespace, data file, segment, disk area, data block and operating system quality inspection

2)。 Tablespace

The tablespace information is stored in the DBA_DATA_FILES data dictionary.

Tablespace

Description

EXAMPLE

If you select instance Scheme during installation, this tablespace holds the data for each sample

SYSTEM

Store data dictionaries, including tables, views, definitions of stored procedures, etc.

SYSAUX

The auxiliary space of the SYSTEM tablespace. It is mainly used to store data objects other than data dictionaries, which can reduce the load on SYSTEM tablespaces

TEMP

Storing information about tables and indexes processed by SQL statements, such as data sorting, takes up this space

UNDOTBLS1

Table space where undo data is stored

USERS

It is usually used to store data objects used by application systems.

two。 Create a tablespace

In order to simplify the management of tablespaces and improve system performance, Oracle recommends that different categories of data be stored in different tablespaces, so administrators need to create different types of tablespaces according to specific conditions after creating the database. The size of tablespaces can be increased after creation, and the maximum size of Oracle tablespace data files is 32G. If you need a larger tablespace, you can create other data files for this tablespace.

Table space creation and modification statements:

-- create a TEST tablespace with a data file of TEST01.dbf and store it in the C:\ tablespace path with a size of 2048m

Create tableSpace TEST datafile'C:\ tablespace\ TEST01.dbf' SIZE 2048M

-- add the data file TEST02.dbf to the tablespace with a size of 2G:

Alter tablespace HDAPP add datafile'C:\ tablespace\ TEST02.dbf 'size 2048m

-- increase the original data file size of the tablespace and modify it to 4096m

Alter database datafile'C:\ tablespace\ TEST01.dbf 'resize 4096m

-- modify the size of the TEST01.dbf data file to grow automatically, with an increase of 1024m at a time, up to a maximum of 20g

ALTER DATABASE DATAFILE'C:\ tablespace\ TEST01.dbf 'AUTOEXTEND ON NEXT 1024M MAXSIZE 20480M; the tablespace auto-extension attribute can be queried and turned off

-- whether the query is automatically extended:

Select file_name,autoextensible,increment_by from dba_data_files

-- turn off the extension:

Alter database datafile/TEMPFILE 'C:\ tablespace\ TEST02.dbf 'autoextend off; delete tablespace statement:

-- Delete tablespaces:

Related statements of DROP TABLESPACE TEST INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; tablespace query

-- Tablespace size query:

SELECT a.tablespace_name Tablespace name

Total Tablespace size

Free "tablespace remaining size"

(total-free) "Tablespace usage size"

Total / (1024 * 1024 * 1024) "Table Space size (G)"

Free / (1024 * 1024 * 1024) "Tablespace remaining size (G)"

(total-free) / (1024 * 1024 * 1024) "Tablespace usage size (G)"

Round ((total-free) / total, 4) * 100 "usage%"

FROM (SELECT tablespace_name, SUM (bytes) free

FROM dba_free_space

GROUP BY tablespace_name) a

(SELECT tablespace_name,SUM (bytes) total

FROM dba_data_files

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

-- query the tablespace physical address

Select c.tablespace_name tablespace name, c.file_name physical file address, SUM (bytes) / 1024Universe 1024Universe 1024 "Tablespace size (G)"

From dba_data_files c

Group by c.tablespace_name,c.file_name

Order by c.tablespace_name,c.file_name

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.