Orale Tablespace&datafile--Oracle Tablespace ____oracle

When learning Oracle database infrastructure earlier, Oracle's storage structure was understood, logically, Oracle data was stored in tablespaces and physically stored in datafiles. A tablespace can only belong to one database (one database can include multiple tablespace), including 1 or more data files. Tablespace can be further divided into segments, extents and blocks. A datafile belongs to only one tablespace of a database.

There are 2 kinds of tablespace classification, one is divided into SYSTEM tablespace and Non-system tablespace.

System Tablespace is created with the creation of the database, contains the data dictionary and system undo Segment;non-system Tablespace includes the space allocated to the user to facilitate the spatial management of the database.

Another kind divides into permanent, undo, temporary three kinds of tablespace. The permanent tablespace that you create when you typically do not add a special description, save a persistent object, and undo Tablespace to save the undo segments for rollback operations and not to include other objects; temporary Tablespace is used for sorting operations, can be shared by multiple users, and cannot contain persistent objects.

There is a default temporary tablespace in the temporary tablespace that specifies a global, temporary tablespace, which, by default, uses system tablespace to store temporary data. Obviously, this is bad, the default temp table space is only one, you can view default temp tablespace from table Database_properties, before the new default temp tablespace is created, Can't be dropped.

The data dictionaries associated with tablespace and datafile are: Dba_tablespaces, V$tablespace, Dba_data_files, V$datafile, Dba_temp_files, V$tempfile.

1. Create a table space
To create a complete command for a table space:

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 table space

2), tablespace Specify the table space name

3), datafile Specify the path, name, size, and self-growth status of the data file: concrete form such as ' e:/oracle/product/10.2.0/oradata/orcl/test. DBF ' size 50M autoextend on next 10M maxsize 500M, you can also specify on to OFF, there is no subsequent increment and maximum size, or you can specify the maximum size after MaxSize Unlimited indicates that the table space is infinitely large.

4, mininum extent <m> k|m points out that the minimum value of extent in the table space, this parameter can reduce space debris, and ensure that the extent in the table space is the integer multiple of this value;

5, BlockSize <n> [K] to set the size of the block, if you want to set this parameter, must be set to Db_block_size integer times;

6), logging Cluse indicates the log properties of all user objects on this tablespace, by default, logging;

7), force logging indicates that the table space enters the forced log mode. The system will then record all changes to the objects on the table space, except for the temporary paragraph changes. This parameter is higher than the nologging option in the logging parameter;

8), default Storage_clause declare the defaults of the storage clause;

9), Online|offline specified table space state;

10), Permanent | Temporary indicates the properties of the tablespace, whether it is a permanent table space or a temporary tablespace. The permanent table space holds the permanent object, and the temporary table space holds the temporary object that exists in the session lifetime. This parameter generates a temporary table space that is always managed by the dictionary and cannot be used with the extent management local option. If you want to create a local management table space, you must use the Create temporary tablespace. You cannot declare the block size if you declare this argument.

11), Extent_manager_clause explain how the table space to manage extent. Once this clause is declared, the parameters can only be changed by transplanting. If you want the table space to be managed locally, declare the local option. The Local management table space is managed through bitmaps. Autoallocate The table space is automatically allocated extent, the user cannot specify the size of the extent. Only 9.0 or more versions have this feature. The uniform describes the fixed size of the scope of the tablespace, and the default is 1M. You cannot set the system table space for a locally managed database as a dictionary management. Orace recommends using a local management table space. If this clause is not set, Oracle makes the default settings. If the initialization parameter compatible is less than 9.0.0, then the system creates a dictionary to manage the table space, and if it is greater than 9.0.0, set the following:

If default storage_clause,oracle is not specified to create an automatically allocated local management table space, if mininum extent is specified, Oracle Judges mininum extent, initial, Next is equal, and if the pctincrease is 0, if the 2 conditions are met, Oracle is jealous of a local management table space, extent size is initial, if not satisfied, then Oracle will create an automatically allocated local management table space If extent is not specified, Oracle determines if initial and next are equal and if Pctincrease is 0, and if these 2 conditions are met, Oracle creates a local management table space and mininum the uniform, Otherwise, Oracle will create an automatically allocated local management table space. (The local admin table space can store only persistent objects.) If you declare a local, you cannot declare default Storage_clause,mininum extent,temporary);

12), Segment_management_clause:segment space management Auto.

2, the State of the table space:
The state of tablespace has 3 kinds: online, OFFLINE, READ only. Online is a working state, offline state is not allowed to access data, SYSTEM tablespace and default temp tablespace can not be offline, and with active undo Segments's tablespace can not be offline. The command to toggle online and offline status is Alter Tablespace <ts_name> Offline/online. When the state becomes read-only, a checkpoint is generated, at which point the data can only be read and not written, but can drop objects, and the related commands are alter tablespace <ts_name> READ only and alter Tablespace <ts_name> Read write.

3, the table space storage settings
Modify storage settings for Tablespace and datafiles. This work refers to the modification of the tablespace size and datafile storage location.

Before modifying the size of tablespace, we need to know the current storage situation of TABLESPAC. You can implement this using 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) A,

(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 this SQL statement, there are 2 subqueries, the first gets the total number of bytes in the table space, and the second gets the number of bytes left in the tablespace, resulting in the number of bytes and percentages that have been used.

Modify the size of the tablespace, mainly through the size of the datafile, to modify the size of the datafile there are 3 ways: 1, so that the data file from the growth of 2, change the size of the data file, 3, add data files.

To make a data file grow from: There is a field in table Dba_data_files that corresponds to this method, indicating whether the data file is growing, that is, whether the data file is automatically increased in size to meet the requirements when it does not meet the storage requirements. Run the following command to create a table space with a datafile size of 5 m-test:create tablespace TEST datafile ' e:/oracle/product/10.2.0/oradata/orcl/test. DBF ' size 5M; At this time, autoextensible is no, that is, the size of the data file is fixed and will not grow (of course, we can also add instructions to the creation command set from growth). Below we manually modify the data file for self growth: ALTER DATABASE datafile ' E:/oracle/product/10.2.0/oradata/orcl/test. DBF ' autoextend on next 5M maxsize 50M; This command sets the TEST.DBF data file to grow from 5M size, maximum 50M.

To change the data file size: Take the previous data file as an example, I want to set the data file to 100M size to execute the command: ALTER DATABASE datafile ' E:/oracle/product/10.2.0/oradata/orcl/test. DBF ' Resize 100M;

To add a data file: This should be the best way to facilitate management. For example, in the test table space, 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 5M autoextend on next 5M maxsize 50M; This command directly specifies the data file to grow from.

In addition to modifying the size of the table space, one of the tasks that can be done in storage settings is to move data files. There are 2 ways to move data files, one of which is to use the Alter TABLESPACE command, and one is to use the ALTER DATABASE command.

Before you can use alter tablespace to move a data file, you need to offline the table space, and then the destination data file must exist (that is, copy the data file you want to move to the destination). For example, move the data file test01.dbf to the previous level directory. Execute the command first: Alter TABLESPACE test offline and then copy test01.dbf to the previous directory and execute the command: Alter tablespace test rename DataFile product/10.2.0/oradata/orcl/test01. DBF ' to ' e:/oracle/product/10.2.0/oradata/test01. DBF '; then the table space online can be: Alter tablespace test online;.

When you move a data file using ALTER DATABASE, the target data file must also exist (a copy of the original file) and the database needs to be in a mounted state. The first method, has moved the data file to the parent directory, and then move it back. Close the database first: Shutdown immediate, then start the database, launch the option Mount:startup mount, and then execute 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 DB open, error:

ORA-01113: File 7 requires media recovery

ORA-01110: Data file 7: ' e:oracle/product/10.2.0/oradata/orcl/test01. DBF ', the details do not understand, only know that the data file is considered to be corrupted by the database, need to use Backup, log information to recover. This would have been a rather serious problem, but in this case, it's a good solution, execute the command:

Recover DataFile ' e:oracle/product/10.2.0/oradata/orcl/test01. DBF '. Prompts to complete the media restore, and then open the database: ALTER DATABASE open; everything is fine.

4, delete the table space
Deletes the tablespace, using the command drop tablespace <ts_name>. But there are 3 options to note:

Including CONTENTS: Indicates the deletion of segments in the table space;

including CONTENTS and datafiles: instructions for deleting segments and datafiles;

CASCADE CONSTRAINTS: Deletes all the integrity constraints associated with the space.

Drop tablespace test including CONTENTS and Datafiles CASCADE CONSTRAINTS;

It is important to note that the system table space and tablespace with active segments cannot be deleted.

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/gan690416372/archive/2010/01/30/5272192.aspx