Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to achieve UNDO TABLESPACE in the database, the article is very detailed, has a certain reference value, interested friends must read it!

I. official files

BOOK → SQL Language Reference → 16 SQL Statements: CREATE SYNONYM to CREATE TRIGGER → CREATE TABLESPACE

Second, basic grammar

CREATE [BIGFILE | SMALLFILE] UNDO TABLESPACE

[data file statement]

[scope management statement]

[consistency protection]

3. Data file size

1. The path name of the data file: DATAFILE datefile_spec1 [, datefile_spec2].

2. Initial size of the file: [SIZE INTEGER [K | M | G | T | P | E]]

3. Reuse: [REUSE]

4. Indicate whether to expand automatically: [AUTOEXTEND]

A) do not automatically extend: OFF

B) automatic extension: ON

i. Indicates the expanded size after the data file is full: [NEXT INTEGER [K | M | G | T | P | E]]

ii. Maximum expansion to: [MAXSIZE]

Represents infinite tablespaces: [UNLIMITED]

Indicates the maximum size of the data file: INTEGER [K | M | G | T | P | E]]

IV. Scope management statements

Illustrates how tablespaces manage scopes. Once you declare this clause, you can only change these parameters through migration.

1. If you want tablespaces to be managed locally, declare the local option, which is managed by bitmaps.

EXTENT MANAGEMENT LOCAL

A) specifies that the tablespace automatically allocates the range, and the user cannot specify the size of the range. Only versions above 9.0 have this feature.

AUTOALLOCATE

B) uniform specifies the fixed size of the range of the tablespace, which defaults to 1m.

UNIFORM [SIZE INTEGER [K | M | G | T | P | E]]

2. If extent_management_clause,oracle is not set, it will set a default value for him.

A) compatible is less than 9.0.0: if the initialization parameter, the system creates a dictionary to manage the tablespace.

B) compatible greater than 9.0.0:

i. If you do not specify default storage_clause,oracle to create an automatically allocated local management tablespace.

ii. If you specify default storage_clause

If mininum extent is specified, oracle determines whether mininum extent, initial, and next are equal, and whether pctincrease is equal.

If the above conditions are met, oracle creates a locally managed tablespace, and extent size is initial.

If the above conditions are not met, oracle will create a locally managed table space that is automatically allocated.

If no mininum extent is specified. Initial, then oracle determines whether next is equal and whether pctincrease is equal or not.

Create a locally managed tablespace and specify uniform if oracle is satisfied.

Otherwise, oracle will create a locally managed table space that is automatically allocated.

Note: local management tablespaces can only store persistent objects. If you declare local, you cannot declare default storage_clause,mininum extent, temporary.

V. consistency protection

If you tend to ensure data consistency, that is, to focus on queries, then you need to use ALTER TABLESPACE undotbs1 RETENTION GUARANTEE to ensure consistency, that is, whether you have enough space or not, you can not use the inactive state zone, which may lead to database hang living because there is no available undo space, but you can ensure the consistency of all queries with query execution time within the undo_ retention value.

RETENTION

GUARANTEE

NOGUARANTEE

VI. Common sentences

CREATE UNDO TABLESPACE UNDOTBS1

DATAFILE'+ ARCHDG1' SIZE 30g

EXTENT MANAGEMENT LOCAL

VII. UNDO usage

SELECT TABLESPACE_NAME

SUM (ACTIVE_MB) ACTIVE_MB

SUM (UNEXPIRED_MB) UNEXPIRED_MB

SUM (EXPIRED_MB) EXPIRED_MB

FROM (SELECT TABLESPACE_NAME

DECODE (STATUS, 'ACTIVE', SIZE_MB) ACTIVE_MB

DECODE (STATUS, 'UNEXPIRED', SIZE_MB) UNEXPIRED_MB

DECODE (STATUS, 'EXPIRED', SIZE_MB) EXPIRED_MB

FROM (SELECT TABLESPACE_NAME

(SUM (BLOCKS) * 8) / 1024 SIZE_MB

STATUS

FROM DBA_UNDO_EXTENTS

GROUP BY TABLESPACE_NAME, STATUS))

GROUP BY TABLESPACE_NAME

ORDER BY TABLESPACE_NAME

The above is all the contents of the article "how to implement UNDO TABLESPACE in the database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.