Oracle table space Creation, modification, deletion, and some parameter interpretation

CREATE TABLE Space

Method 1:
The code is created as follows:
PL/SQL

Create a tablespace (typically a table space with n stored data and an index space):
Create tablespace table space name
DataFile ' Path (to build the path first) \***.dbf ' size *m
Tempfile ' path \***.dbf ' size *m
Autoextend on-Automatic growth
Extent Management Local
Sql>/
The tablespace has been created.



Method 2
Use Sqlplus, as follows:
Sqlplus/as SYSDBA
Sql>create tablespace tablespace name datafile ' file path and file name ' size 500m;

Method 3
Created by script, as follows:
Create tablespace Table Space name
DataFile ' e:\database\oracle\demo.dbf '
Size 32m
Autoextend on
Next 32m MaxSize 1024m
Extent management Local;

　　

To grant a user the use of table space SQL

Unlimited usage of the tablespace
Alter user username quota unlimited on table space;
Occupy a certain size
or alter user username quota *m on table space;

Modify the user table space

Querying all user table spaces
Select Username,default_tablespace from User_users;
Modify the user tablespace (carry all tables under the user)
Alter USER username default tablespace table space name;

To Delete a table space sql
Drop tablespace tablespace name including contents and Datafiles

In the process of creating tablespace tablespace using the command line, several aspects of the parameters are included.

Data file parameters: Includes table space corresponding data file name, initial size and changeable rules;

Extent (partition) Allocation rule scheme: the allocation method and strategy used when table space is allocated to segment objects;

Segment (segment) object Management method: Divided into automatic segment object management or manual segment object management;

The following are described separately:

1. Data file datafile Parameters

A tablespace is the only level in the logical structure of a table, segment, zone, block, or Oracle four layer that corresponds to a specific physical file. A table space can correspond to multiple files on a different hard disk, and a file can belong to only one table space.

When a table space is created, at least one data file is generated as a place where tablespace information is saved. If you do not specify datafile words when creating tablespace, Oracle automatically creates a data file based on OMF.

The word for the specified data file is using datafile as the keyword, followed by file path, initial size, expansion mode, and each additional space size.

　　　　　　　　

Size clause: Specifies the initial size of the generated data file, which is typically 100m by default. For a mature system deployment porting effort, it is usually possible to determine the fixed size of the file. Avoid the frequent file expansion, causing performance changes;

Autoextend switch and Next clause: The file size change switch is implemented by Autoextend. If set on, the file is allowed to be dynamically extended, and the file is filled with the size specified next. If set to OFF, the file is not extended;

Next clause: When the file is set to extensible, next is the stride size for each expansion. If the data file is constantly increasing in mass, set a larger next value for good;

MAXSIZE clause: Maximum file size;

2. Extent partition allocation scheme

The logical structure contained within the Tablespace tablespace is the segment segment object. A segment object is typically associated with an object in each schema of the database. An important aspect of table space management is the allocation of new extent to segment objects. A segment will correspond to one or more extent objects. The extent area is a contiguous set of block blocks.

From the perspective of Oracle development, there are two stages in the extent allocation scheme. First appeared is dictionary Mangage tablespace, is the data dictionary management way. A data table is maintained in the database, which is accessed every time the extent allocation is managed. DMT represents an era, accompanied by a deadly flaw.

An important indicator of a database is the degree of parallelism. In the case of parallel bottlenecks, there is a flaw in the system architecture. DML is such a problem that the data table is prone to bottlenecks when there are a large number of allocation requests in the database table space. As a result, DML is replaced by a new allocation method locally mangage tablespace (LMT).

The current Oracle database is using the LMT method by default. LMT, in a nutshell, is to use bit-charting techniques to record the methods and records of the allocation extent on the file header of the data file. Thus, the distribution pressure of different files is spread from one data table to multiple files.

The current Oracle database is capable of supporting both LMT and DMT. However, the new system will generally use LMT. LMT policy, there is an issue with allocating extent size.

Each time the extent allocation strategy, there is the system automatic and uniform size uniform. Size two methods.

Automatic allocation automatically allocates size. The size of each allocation extent is determined automatically by the system. The advantage of this approach is that each time the extent size is more flexible. But the disadvantage is also very serious, is to cause a lot of storage fragmentation.

Uniform. Size is uniform. The size of each allocated extent is fixed, which can largely avoid fragmentation problems. Default uniform. Size is 1M.

3, Duan segment management strategy

Finally, the segment Management policy in table space creation parameters is introduced. The segment corresponds to a database-retained object information, such as data segments, index segments, and rollback segments. Segment space management corresponds to the Segment spatial management strategy, currently has auto and manual two kinds of ways.

ASSM (Auto) mode: ASSM represents the new trend of technology, 10g corresponding to the shrink space function, is based on ASSM technology, effectively reduce HWM, avoid excessive space waste. In the past need to design pctfree, pctlist and other parameters, but also the use of automated methods for management;

Manual (Manual) mode: corresponds to Automatic ASSM. Unlike ASSM, each segment object can have independent storage settings parameters;

In the segment management strategy, the ASSM strategy is generally selected at present. But it does not mean that ASSM is omnipotent, there are some limitations.

EXP username/password @orcl file=d:\daochu.dmp full=y

Oracle table space Creation, modification, deletion, and some parameter interpretation