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

This paper briefly introduces the main characteristics and operations of table space storage allocation and management in Oracle database.

Physical and logical relations of I table space

Database: database

Table space: tablespace, which is logically divided into system table space, revocation table space, temporary table space and user table space in terms of performance and management. The burden of the system tablespace should be reduced as much as possible, and the user data should not be placed in the system tablespace. After 10g, there is more SYSAUX table space (auxiliary system table space), which is mainly used to record a large number of self-adjustment, optimization analysis data and so on.

Segment: segment, a storage structure in which different types of database objects exist in different segments, such as data segments, index segments, temporary periods, undo segments, etc. Manual / automatic, select automatic

Extent: the smallest unit of space allocated and reclaimed within an extent,Oracle table space, consisting of several contiguous data blocks. The size allocation of the range is specified when the tablespace is created, and when not specified, the default storage parameters of Oracle are used. Data dictionary management / local management, select local management.

Oracle block: the smallest IO unit of a data block,Oracle, consisting of multiple operating system blocks (os block). The master block size is specified by the initialization parameter db_block_size (generally the default is 8K), and the slave block size is specified when the tablespace is created. Set the db_nk_cache_size parameter to 1 to allocate non-standard DB block memory in SGA, such as db_2k_cache_size, db_4k_cache_size, db_32k_cache_size and so on.

Os block: operating system block whose size is determined by the operating system.

Solution: schema database objects are organized into different schemas according to the dependency relationship between users and objects. All objects owned by a database user are called a schema, and the schema name is the same as the user name. All database objects under a user schema are stored through multiple types of segments.

Data files: data file, one for sequential access of large blocks of data, and multiple for random access of small blocks of data.

Setting of storage parameters in II tablespace

1. Settings of file attributes

Large file (bigfile) and small file (smallfile): large file is a feature introduced by Oracle 10g. The large file table space is based on a single data file, and the file size can reach 32TB. The small file tablespace consists of one or more data files. The large file feature is mainly designed for very large databases, in order to overcome the efficiency problems that Oracle may bring about by updating all data file headers when data changes. To use a large file tablespace, use the bigfile or smallfile keyword when creating the tablespace, create [bigfile | smallfile] tablespace myjia datafile'/ jia/test/myjia01.dbf' size 200m. When omitted, the default is the small file tablespace.

Automatic extension (autoextend): autoextend on | off

File status information: online | offline

Data files for tablespaces can be created at the same time

Create tablespace myjia

Datafile'/ jia/test/myjia01.dbf' size 100m autoextend on next 10m maxsize 500m

Verify:

SQL > select TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME='MYJIA'

TABLESPACE_NAME

-

MYJIA

2. The way of space management

Oracle provides two types of parameter settings: zone management and segment space management.

Area management (extent management): determines the storage location of scope allocation information. There are two ways: data dictionary management (dictionary) and local management (local). Choose local management. If system tablespaces are managed locally, creating tablespaces managed by data dictionaries is not allowed.

Local management is divided into

Segment space management (segment space management): specifies how to manage the space within a segment, which can be divided into auto and manual. Oracle recommends using auto management method, while manual method is reserved for backward compatibility, which requires manually setting the usage parameters of segment space, such as pct_free and pct_used.

Limitations of ASSM

1. There is no way to control the storage behavior of independent tables and indexes within tablespace.

two。 AS5M cannot be used for large objects, and a separate tablespace must be created for tables that contain LOB data types.

3. You cannot use ASSM to create a temporary tablespace. This is determined by the short-lived nature of temporary segmentation during sorting.

4. Only locally managed tablespace can be managed using bitmap segmentation.

5 performance problems may occur when using ultra-high capacity DML (such as INSERT, UPDATE, DELETE, etc.).

The difference between District Management and Section Management

Specify scope management and segment space management when creating tablespaces

Create tablespace myjia

Datafile'/ jia/test/myjia01.dbf' size 100m auto extend on next 10m maxsize 500m

Extent management local uniform size 512k

Segment space management auto

-- autoallocate | uniform

Insert data

SQL > alter tablespace lxtab1 add datafile'/ oradata/lxtab/crtab2.dbf' size 20m

SQL > alter tablespace lxtab2 add datafile'/ oradata/lxtab/instab2.dbf' size 20m

Query

SQL > select tablespace_name,contents,status,extent_management,segment_space_management from dba_tablespaces

TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN SEGMEN

SYSTEM PERMANENT ONLINE LOCAL MANUAL

SYSAUX PERMANENT ONLINE LOCAL AUTO

UNDOTBS1 UNDO ONLINE LOCAL MANUAL

TEMP TEMPORARY ONLINE LOCAL MANUAL

USERS PERMANENT ONLINE LOCAL AUTO

UNDOTBS2 UNDO ONLINE LOCAL MANUAL

LXTAB1 PERMANENT ONLINE LOCAL AUTO

LXTAB2 PERMANENT ONLINE LOCAL MANUAL

Description: district management local (local) management has overwhelming advantages, segment management auto and manual have their own advantages

Note:

The values of PCTFREE and PCTUSED can be specified when the table is created or modified after the table is created, but it is important to note that the modified values only affect the modified data operations, not the previous ones.

In order to reduce unnecessary extra uniform size O operations, the optimal size of the uniform size should be the product of the parameter db_block_size and the parameter db_file_multiblock_read_count setting.

3. Setting of internal storage parameters

The non-standard block size of the tablespace can be specified by the keyword blocksize, otherwise it can be set by the standard block size (specified by the db_block_size parameter). To create a tablespace with non-standard block size, you need to allocate the memory area of the non-standard block in SGA in advance, which can be set by the parameter db_nk_cache_size = 1.

Default storage (

[initial n K | M]

[next n K | M]

[minextents n]

[maxextents n | unlimited]

[pctincrease n]

Initial: the number of bytes in the first range allocated by the segment space. When the user creates a schema object, Oracle allocates space for the range.

Next: allocate the number of bytes in the next range.

Minextents: specifies the minimum number of ranges allocated when the object is created, which defaults to 1, meaning that Oracle allocates only the initial range, and the maximum value depends on the operating system. If minextents is greater than 1, Oracle calculates the size of the next range based on the values of initial, next, and pctincrease.

Maxextents: specifies the maximum number of ranges that an Oracle can assign to a segment structure, including the first range. The minimum value is 1, and the default and maximum values depend on the operating system. Unlimited indicates that the number of ranges is automatically allocated as needed.

Pctincrease: specifies the percentage by which the third and subsequent ranges are larger than the previous range. The early default value is 50, which means that each extension is 50% larger than the previous one, and the minimum value is 0, indicating that all ranges after the first range are of the same size. The pctincrease parameter setting is greater than 0 to make small segments in the table space have small ranges, large segments have large ranges, and keep the number of ranges as small as possible.

Use examples

Create tablespace myjia

Datafile'/ jia/test/myji01.dbf' size 100m

Blocksize 4096

Default storage (

Initial 256k

Next 256k

Minextents 2

Maxextents 100

Pctincrease 50)

The non-standard block data buffer db_4k_cache_size needs to be set before this code is created.

Alter system set db_4k_cache_size=1

You can then see the actual buffer size allocated by Oracle

Show parameter db_4k_cache_size

NAME TYPE VALUE

-

Db_4k_cache_size big integer 32M

Starting with Oracle 10g, the tablespace is automatically set to extent management local autollocate and segment space management auto when the tablespace is created, and the create tablespace statement ignores the default storage clause.

Maintenance and management of III tablespace

1. Change the status of the tablespace

The tablespace is offline, such as when doing system recovery, data file shift, and so on. System tablespaces, default temporary tablespaces, and tablespaces containing active rollback segments (the current undo tablespace) cannot be offline.

Alter tablespace... Offline

Offline includes three modes:

Offline normal: by default, all data files are checked, but when there are offline data files, they cannot be offline normally.

Offline immediate: immediately, no checkpoints are made offline for point-in-time recovery.

Offline temporary: temporarily, all online data files do checkpoints, data files can be divided into online and offline, regardless of those not online, media recovery may be required during recovery.

Tablespace online

Alter tablespace... Online

Some tablespaces used to store historical or static data can be set to a read-only state to prevent accidental updates and deletions of the data. Read-only tablespaces need not be backed up periodically, they only need to be backed up once.

Tablespace is set to read-only

Alter tablespace... Read only

Tablespace is set to default readable and writable

Alter tablespace... Read write

2. Rename the tablespace

Alter tablespace myjia rename to mytbsjia

3. Delete tablespace

Drop tablespace... [including contents [and datafiles]]

When the tablespace contains persistent database objects, the specified including contents clause must be displayed. To delete the operating system files on disk at the same time, you can also specify the and datafiles clause, otherwise only the tablespaces will be deleted from the data dictionary.

Once the tablespace is deleted, the data will be lost permanently, so before formally deleting the tablespace, it is best to set it to the state of offline and confirm that it is no longer needed after the time test.

4. Expansion of table space

There are three ways:

1) Open the automatic extension property of the tablespace data file.

Alter database datafile'/ jia/test/myjia01.dbf' autoextend on next 10m maxsize 1000m

2) manually resize existing data files

Alter database datafile'/ jia/test/myjia01.dbf' resize 200m

For temporary tablespace files

Alter database tempfile'/ jia/test/myjia01.dbf' resize 200m

Resize can also reduce the size of the data file, as long as the existing data can be accommodated by the new size.

3) add new data files to the tablespace, such as

Alter tablespace myjia add datafile'/ jia/test/myjia02.dbf' size 200m

For temporary tablespace files

Alter tablespace temp add tempfile'/ jia/test/mes/temp01.dbf' size 200m

Once a tablespace data file is added, it cannot be removed unless the entire tablespace is deleted.

5. Merge free space

Based on the data dictionary management of the tablespace, after a period of use, due to the continuous allocation and release of space, the fragments in the tablespace will gradually increase, and the free space can be merged at this time.

Alter tablespace... Coalesce

Locally managed tablespaces improve this and do not require this operation.

6. Temporary tablespace

It is mainly used to store temporary sort data, etc. The data will first be stored in the sort area of PGA memory, and the size of the sort area is specified by the parameter sort_area_size.

Show parameter sort_area_size

NAME TYPE VALUE

-

Sort_area_size integer 65536

Create a temporary tablespace

SQL > create temporary tablespace mytemp tempfile'/ jia/test/mes/mytemp01.dbf' size 100m

Modify the default temporary tablespace of the database

Alter database default temporary tablespace...

View the database default temporary tablespace

Col property_value for a30

Select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE'

PROPERTY_VALUE

-

TEMP

7. Undo space

The current UNDO table space of the database is specified by the parameter undo_tablespace

Show parameter undo_tablespace

NAME TYPE VALUE

-

Undo_tablespace string UNDOTBS1

Create undo tablespace

Create undo tablespace UNDOTBS2 datafile'/ jia/test/mes/undotbs02.dbf' size 200m autoextend on

Switch the default UNDO table space of the database to the newly created table space

Alter system set undo_tablespace = UNDOTBS2

View the results of changes

Show parameter undo_tablespace

NAME TYPE VALUE

-

Undo_tablespace string UNDOTBS2

IIII data file management

Managing data files requires the following considerations:

1. Number of data files

The operating system limits the number of files that each process can open at the same time.

Oracle limits the number of data files that can be opened per database instance, which is determined by the initialization parameter db_files.

When creating the database, the parameter maxdatafiles determines the size of the part of the control file used to record the data file, which also limits the number of data files that the database can have.

A small number of big data files is better than a large number of small data files, because this can reduce the number of files opened at the same time, and reduce the number of updates to the head of the data file.

2. The size of the data file

Except for the necessary table spaces of several systems, such as SYSTEM, SYSAUX, UNDO, TEMP, etc., the size of the other table spaces is determined according to the needs of the amount of data, and there is no special limit.

3. The location of the data file

The physical location of the data file affects database performance.

You should consider putting data files and index files on separate disks to improve performance.

You should consider putting data files and log files on different disks to avoid competition between reading and writing data files and writing log files.

For security reasons, data files and log files should also be stored on separate disks. If the log group has multiple log members, these members should be placed on different physical disks to ensure the security of log files.

4. Data file of OMF

Data files managed by Oracle, defined file names and paths are managed by Oracle, no longer need to specify data files manually. Files that can OMF include:

Datafiles

Tempfiles

Redo log files

Control files

Block change tracking files

Specify the target file location by setting the parameter db_create_file_dest

Alter system set db_create_file_dest='/u01/oradata'

Maintenance operation of IIIII data file

1. Add data files to the tablespace

Alter tablespace mytbs add datafile'/ jia/test/mytbs02.dbf' size 200m

2. Change the size of the data file. The operation here is similar to the operation described earlier to expand the capacity of the tablespace.

1) by allowing or disabling automatic expansion of data files

Alter database datafile'/ jia/test/mytbs02.dbf' autoextend on

2) change the size of the data file manually

Alter database datafile'/ jia/test/mytbs02.dbf' resize 200m

3. Change the availability of data files

1) data files online

Alter database datafile'/ jia/test/mytbs02.dbf' online

2) data files are offline

Alter database datafile'/ jia/test/mytbs02.dbf' offline [drop]

Data files that are offline when the database is opened need media recovery when they are back online. For databases that are not in archive mode, you need to use the drop option offline, and if you switch logs offline, the files cannot be recovered.

4. Relocate the data file

Method 1: relocate the data file of the tablespace, which requires the tablespace to be offline.

1) alter tablespace... Offline

2) copy the data file to a new location and rename cp as needed

3) alter tablespace... Rename datafile '...' To '...'

Or alter database rename file '...' To '...'

4) alter tablespace... Online

Method 2: relocate database files, which is suitable for tablespaces that cannot be offline, and this method is also suitable for the relocation of online log files.

1) start the database to mount status

2) copy or move the database file to a new location and rename it as needed

3) alter database rename file'.' To '...'

4) Open the database

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.