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

This article is about how to use CREATE TABLESPACE in the database. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I. official files

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

Second, basic grammar

CREATE [BIGFILE | SMALLFILE] TABLESPACE

[data file statement]

[maximum cell size]

[block size]

[log statement]

[force logging]

[encrypted statement]

[storage statement]

[online]

[scope management statement]

[segment management statement]

[flashback mode statement]

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]

Do not automatically extend: OFF

Automatic extension: ON

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

Maximum expansion to: [MAXSIZE]

Represents infinite tablespaces: [UNLIMITED]

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

4. Minimum extension size of data file

Point out the minimum value of the range in the table space and ensure that the range in the table space is an integral multiple of this value.

MINIMUM EXTENT INTEGER [K | M | G | T | P | E]

5. Block size

This parameter sets the size of a non-standard block. If you want to set this parameter, you must set db_block_size, at least one db_nk_block_size, and the declared integer must be equal to db_nk_block_size, which defaults to 8k blocks

BLOCKSIZE INTEGER K

VI. Log statement

This clause declares the log properties of all user objects on this tablespace, including tables, indexes, partitions, materialized views, indexes on materialized views, and partitions.

1 、 LOGGING

2 、 NOLOGGING

3 、 FILESYSTEM_LIKE_LOGGING

7. Mandatory logging

Use this clause to indicate that the tablespace enters mandatory logging mode. At this point, the system records all changes to objects on the tablespace, except for temporary changes. This parameter is higher than the object's nologging option.

Note: setting this parameter database is not open and is in read-write mode.

FORCE LOGGING

VIII. Encrypted statement

Used when creating encrypted tablespaces, with default storage (encrypt)

ENCRYPTION [USING']

1 、 AES256

2 、 AES192

3 、 AES128

4 、 3DES168

IX. Storage statement

Default storage statement

DEFAULT

1 、 [table_compression]

Basic compression

OLTP compression

Warehouse compression (Hybrid Columnar Compression)

Archive compression (Hybrid Columnar Compression)

2 、 [storage_clause]

Use this statement to set storage parameters for all objects in the tablespace. This statement is used for dictionary-managed tablespaces. You can specify the following storage parameters in the statement: COMPRESS,INITIAL,NEXT,MINEXTENTS,MAXEXTENTS, and PCTINCREASE.

Whether it is online or not

1. Make the tablespace effective immediately after it is created. This is the default: ONLINE

2. Invalidate tablespaces after creation: OFFLINE

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:

Then follow the settings as follows

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.

Section management statement

Segment space management

SEGMENTSPACE MANAGEMENT

1. AUTO: automatic management

2. MANUAL: manual management

Flashback mode statement

Flashback mode

FLASHBACK

1 、 ON

2 、 OFF

XIV. Common sentences

CREATE TABLESPACE TOOLS

DATAFILE'+ DATADG1' SIZE 100m

EXTENT MANAGEMENT LOCAL

UNIFORM SIZE 128k

SEGMENT SPACE MANAGEMENT AUTO

Thank you for reading! This is the end of the article on "how to use CREATE TABLESPACE in the database". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.