Oracle tablespace & amp; datafile -- oracle tablespace

 

When learning about the basic architecture of Oracle databases, I have learned about the storage structure of Oracle. Logically, Oracle data is stored in tablespaces and physically stored in datafiles. A tablespace can belong to only one database (a database can contain multiple tablespaces), including one or more data files. Tablespace can be further divided into segments, extents, and blocks. A datafile only belongs to one tablespace of a database.

 

There are two types of Tablespace: SYSTEM tablespace and Non-SYSTEM tablespace.

 

SYSTEM tablespace is created along with the creation of the database, including the data dictionary and SYSTEM undo segment; Non-SYSTEM tablespace includes the space allocated to the user for ease of database space management.

 

The other is divided into three tablespaces: permanent, undo, and temporary.

The permanent tablespace created when the object is not explicitly stored, and the permanent object is saved;

Undo tablespace is used to save undo segments for rollback operations, but cannot contain other objects;

Temporary tablespace is used for sorting. It can be shared by multiple users and cannot contain permanent objects.

 

The Temporary tablespace contains a default temporary tablespace, which specifies a global default Temporary tablespace. If this tablespace is not available, the SYSTEM tablespace is used by default to store temporary data. Obviously, this is not good. There is only one temporary tablespace by default. You can view the default temp tablespace in the database_properties table. It cannot be dropped until the new default temp tablespace is created.

 

Data dictionaries related to tablespace and datafile include dba_tablespaces, v $ tablespace, dba_data_files, v $ datafile, dba_temp_files, and v $ tempfile.

 

1. Create a tablespace

Complete command for creating a tablespace:

 

Create [undo] tablespace <ts_name>

 

Datafile <file_spec1> [, <file_spec2>]

 

Mininum extent <m> k | m

 

Blocksize <n> [k]

 

Logging clause

 

Force logging

 

Default storage_clause

 

Online | offline

 

Permanent | temporary

 

Extent_manager_clause

 

Segment_manager_clause

 

1) undo specifies that the system will create a rollback tablespace

 

2). Specify the tablespace name in tablespace.

 

3) datafile specifies the path, name, size, and auto-growth status of the data file, for example, 'e: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ TEST. DBF 'size 50 M autoextend on next 10 M maxsize 500 M, you can also specify on as off, there is no increment and maximum size behind, you can also specify the maximum unlimited size after maxsize to indicate that the tablespace is infinitely large.

 

4) mininum extent <m> k | m indicates the minimum value of extent in the tablespace. This parameter can reduce space fragments and ensure that the extent in the tablespace is an integer multiple of the value;

 

5) blocksize <n> [k]: Set the block size. To set this parameter, you must set it to an integer multiple of db_block_size;

 

6) logging cluse indicates the log attributes of all user objects in the tablespace. The default value is logging;

 

7) force logging indicates that the table space enters the forced log mode. In this case, the system records all changes to objects in the table space, except for changes to temporary segments. This parameter is higher than the nologging option in the logging parameter;

 

8) default storage_clause declares the default storage clause;

 

9) online | offline specifies the tablespace status;

 

10) permanent | temporary indicates whether the tablespace is a permanent tablespace or a temporary tablespace. Permanent tablespace stores permanent objects, while temporary tablespace stores temporary objects existing in the session lifecycle. The temporary tablespace generated by this parameter is always in dictionary management after it is created, and the extent management local option cannot be used. To create a local tablespace, you must use create temporary tablespace. If this parameter is declared, block size cannot be declared.

 

11) extent_manager_clause describes how to manage the extent table space. Once this clause is declared, these parameters can only be changed through transplantation. If you want to manage the tablespace locally, declare the local option. Local tablespace management is implemented through bitmap management. Autoallocate indicates that the table space is automatically allocated with extent. You cannot specify the extent size. Only Versions later than 9.0 have this function. Uniform indicates the fixed size of the tablespace range. The default value is 1 MB. You cannot set the SYSTEM tablespace of a locally managed database to dictionary management. We recommend that you use local tablespace management. If this clause is not set, oracle performs the default setting. If the initialization parameter compatible is smaller than 9.0.0, the system creates a dictionary management tablespace. If the value is greater than 9.0.0, set it as follows:

 

If the default storage_clause is not specified, oracle creates an automatically assigned local tablespace. Otherwise, if the mininum extent is specified, oracle determines whether the mininum extent, initial, and next are equal, and whether pctincrease is 0. If these two conditions are met, oracle is jealous and can manage the tablespace locally. extent size is initial. If not, oracle creates an automatically allocated local tablespace. If the mininum extent is not specified, oracle determines whether initial and next are equal and whether pctincrease is 0. If the two conditions are met, therefore, oracle creates a local management tablespace and creates an uniform. Otherwise, oracle creates an automatically allocated local management tablespace. (Local tablespace management can only store permanent objects. If you declare local, you cannot declare default storage_clause, mininum extent, temporary );

 

12), segment_management_clause: segment space management auto.

 

2. Table space status:

Table space has three statuses: ONLINE, OFFLINE, and read only. ONLINE is working normally. OFFLINE is not allowed to access data,

SYSTEM tablespace and DEFAULT temp tablespace cannot be OFFLINE, and tablespace with active undo segments cannot be OFFLINE.

The command to switch the ONLINE and OFFLINE statuses is alter tablespace <ts_name> offline/online. When the status changes to READ-ONLY, a checkpoint is generated. data can ONLY be READ but not written, but can be dropped, related commands are alter tablespace <ts_name> read only and alter tablespace <ts_name> read write.

 

3. Storage settings of tablespaces

Modify the storage settings of tablespace and datafiles. This operation refers to modifying the size of tablespace and the storage location of datafile.

 

Before modifying the size of a tablespace, we need to know the current storage of tablespac. You can use the following SQL statement:

 

SELECT a. tablespace_name,

 

A. bytes bytes_used,

 

B. largest,

 

Round (a. bytes-B. bytes)/a. bytes) * 100, 2) percent_used

 

FROM

 

(SELECT tablespace_name,

 

SUM (bytes) bytes

 

FROM Dba_Data_Files

 

Group by tablespace_name),

 

(SELECT tablespace_name,

 

SUM (bytes) bytes,

 

MAX (bytes) largest

 

FROM dba_free_space

 

Group by tablespace_name) B

 

WHERE a. tablespace_name = B. tablespace_name

 

Order by (a. bytes-B. bytes)/a. bytes) DESC;

 

In the preceding SQL statement, there are two subqueries. The first query gets the total number of bytes in the tablespace, and the second query gets the number of bytes in the remainder of the tablespace, the final result is the number of bytes and percentage used.

 

You can modify the size of a tablespace by using datafile. You can modify the size of a datafile in three ways:

1) make data files grow on its own;

2) Change the data file size;

3) Add a data file.

 

Auto-increment of data files: The field AUTOEXTENSIBLE In the table DBA_DATA_FILES corresponds to this method. It indicates whether the data files are auto-incrementing, that is, when the data files cannot meet the storage requirements, whether to automatically increase the size to meet your needs. Run the following command to create a tablespace with a datafile size of 5 MB-TEST: create tablespace test datafile 'e: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ TEST. DBF 'size 5 M; in this case, AUTOEXTENSIBLE is no, that is, the size of the data file is fixed and will not increase by itself (of course, you can also add the command settings to the creation command to Increase the number ). Next we will manually modify the data file to auto-increment: alter database datafile E: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ TEST. DBF 'autoextend on next 5 M maxsize 50 M; this command will TEST. the DBF data file is set to increase by 5 MB, with a maximum size of 50 MB.

 

Change the data file size: for example, if you want to set the data file size to MB, run the following command: alter database datafile 'e: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ TEST. dbf'resize 100 M;

 

Add data files: this should be the best way to facilitate management. Take the TEST tablespace as an example. The command to add a data file is as follows: alter tablespace test add datafile 'e: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ TEST01.DBF 'size 5 M autoextend on next 5 M maxsize 50 M; this command directly specifies the auto-Growth of data files.

 

In addition to modifying the tablespace size, you can also move data files in the storage settings.

There are two ways to move data files: the alter tablespace command and the alter database command.

 

Before using alter tablespace to move a data file, you need to OFFLINE the tablespace first, and then the target data file must exist (that is, copy the data file to the destination ). Take the data file TEST01.DBF as an example. Run alter tablespace test offline first, copy TEST01.DBF to the upper-level directory, and then run alter tablespace test rename datafile E: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ TEST01.DBF 'to 'e: \ oracle \ product \ 10.2.0 \ oradata \ test01.dbf'; then you can ONLINE the tablespace: alter tablespace test online ;.

 

When you use alter database to move a data file, the target data file must exist (a copy of the original file) and the database must be in the MOUNTED state. The first method is to move the data file to the parent directory, and then move it back. First shut down the database: shutdown immediate; then start the database, start the option to mount: startup mount; then run the move command: alter database rename file 'e: \ oracle \ product \ 10.2.0 \ oradata \ TEST01.DBF 'to 'e: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ TEST01.DBF'; then open the database: alter database open, and an error is returned:

 

ORA-01113: file 7 requires media recovery

 

ORA-01110: data file 7: 'e: ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ TEST01.DBF ', the details still do not understand, only know that the database that this data file was damaged, backup and log information must be used for restoration. This is a serious problem, but it is still well solved in this instance. Execute the command:

 

Recover datafile 'e: ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ test01.dbf '. Prompt for media recovery, and then open the database: alter database open; everything is normal.

 

4. delete a tablespace

Delete the tablespace and run the drop tablespace <ts_name> command. Note the following three options:

 

Including contents: indicates to delete segments in a tablespace;

 

Including contents and datafiles: indicates to delete segments AND datafiles;

 

Cascade constraints: delete all integrity CONSTRAINTS related to the space.

 

Drop tablespace test including contents and datafiles cascade constraints;

 

Note that the SYSTEM tablespace and the tablespace with active segments cannot be deleted.

From: perry's network mood