Oracle Table Space Management

The highest logical layer in Oracle Disk Management is Tablespace, and the 4 tablespace that must be created in oracle11g is System,sysaux, TEMP, UNDOTBS1.

2 SYSTEM: Store data dictionary, PL/SQL code, etc.

2 Sysaux: Store data related to database options

2 TEMP: For a large sort operation

2 UNDUTBS1: Store transaction information for read consistency and recovery purposes.

The next layer of the tablespace is a segment, and a segment can only reside in one table space, and one or more extents may consist of one segment, each of which can reside in only one data file; a contiguous set of data blocks can form a single area. If you want to query the table space and the corresponding data file information, you can query the table space and its contained data files from the Dba_data_files data dictionary, for example, as follows:

Sql> Col tablespace_name for A10;

Sql> col file_name for A50;

Sql> col bytes for 999,999,999;

Sql>select tablespace_name,file_name,bytes from Dba_data_files order by Tablespace_name;

1. system table Space

The system table space holds internal data and data dictionaries, which primarily contain the individual objects of the SYS user and a small number of objects for other users. For example, query the data objects and their types and owners stored in the users table space.

Sql>col owner for A10;

Sql>col segment_name for A30;

Sql>col Segment_type for A20;

Sql>select segment_type,segment_name,ownerfrom dba_segments where tablespace_name= ' USERS ';

2. Sysaux table Space

The Sysaux table space acts as a secondary tablespace for the system table space and is primarily used to store data objects other than the data dictionary. For example, query the user that the Sysaux table space resides in and the number of objects it owns:

Select owner as user, Count (segment_name) As Object count from Dba_segments wheretablespace_name= ' Sysaux ' GROUP by owner;

3. CREATE TABLE Space

The syntax for creating a tablespace is as follows:

Create [Smallfile | bigfile] tablespace tablespace_name

DataFile '/path/filename' size num[k|m] Reuse

['/path/filename' size num[K|m]reuse]

[, ...]

[Autoextend [On|off] next] Num [K|m]

[MaxSize [Unlimited | Num [K|m]]

[mininum extent num[k|m]]

[Default storage storage]

[Online | offline]

[Logging | nologging]

[Permanent | temporary]

[Extent Management Dictionary | local[autoallocate | uniform size num[k|m]];

Description

? Smallfile | Bigfile: Indicates whether you are creating a small file table space or a large file table space

? Autoextend [On|off] Next: Indicates that the data file is auto-extended or not auto-extended, and you need to set next value for auto-scaling.

? MaxSize: Indicates the maximum length of bytes allowed for the data file extension when the data file is automatically expanded, and the byte length is not required if the UNLIMITED keyword is specified.

? minimum extent : indicates the minimum value of the extent in the tablespace, this parameter can reduce space fragmentation and ensure that the extent of the table space is an integer multiple of this number.

? Online | Offline: You can specify either online or offline when creating a tablespace.

? Permanent | Temporary: Specifies that the Create Tablespace is a permanent tablespace or a temporary table space. The default is a permanent table space.

? Logging | nologging: Specifies whether the table in this tablespace generates a log when the data is loaded, by default to generate the log, even if it is set to nologging, but when the insert,update,delete operation is performed, Oracle will still log information to Redolog buffer.

? Extent Management Dictionary | Local: Specifies whether table spaces are extended by using data dictionary management or localization management. The default is localization management.

? Autoallocate | Uniform size: If localized management is used, specifies whether the extended size of each zone is automatically specified or equal to the size of the table space when it is expanded. If you set the Uniform keyword, the default extension size is 1MB.

? Reuse: Indicates if the file exists, clears the file and rebuilds the file, or creates the file if it does not exist.

? Default storage: Sets the value of the stored parameter for the table, index, and cluster to be created later.

4. Delete Table space

? Deletes the tablespace, but does not delete its contents and physical files.

Droptablespace Tablespace_name;

? Delete the table space and its contents.

Droptablespace tablespace_name including contents;

? Remove the tablespace and delete its physical files.

Droptablespace tablespace_name including contents and datafiles;

? If a table in another table space has a field that is associated with a constraint such as a foreign key in the table space, add cascadeconstraints

Droptablespace tablespace_name including contents and datafiles cascadeconstraints;

5. Create a temporary table space

? Example:

Create Temporary tablespace Temp1

Tempfile ' e:\ temp01.dbf ' size 5M autoextend on next 128k maxsize 1000m,

' E:\ temp 02.dbf ' size 5M autoextend on next 128k maxsize 1000m;

6, Oracle 11g new features: Deferred_segment_creation

Deferred_segment_creation, from the literal meaning of paragraph delay creation, the value range of the parameter is true and False, which is true by default. Specifically, if Deferred_segment_creation is true, when a new table is created and no data is inserted into it, the table does not immediately allocate extent, that is, it does not occupy the data space and allocates space only after the insert data, which saves a small amount of space. If Deferred_segment_creation is false, it is created with the same extent as the previous version of the table.

The statement that looks at the value of the deferred_segment_creation is as follows:

Sql> Show Parameterdeferred_segment_creation

The statement that modifies the Deferred_segment_creation value is as follows:

Sql> alter system setdeferred_segment_creation=false;

Or

Alter session Setdeferred_segment_creation=false;

Note: This new feature on not applicable to SYS and the SYSTEM users as the Segmentto the table are created along with the tab Le creation.

7. Case (Deferred_segment_creation is true)

? Create a tablespace, and then delete the table space.

Create tablespace EXAMPLETB

DataFile ' e:\ examp01.dbf ' size 5Mautoextend on next 128k maxsize 1000m,

' E:\ examp02.dbf ' size 5M autoextend on next 128k maxsize 1000m;

Description: The table space created by the above example consists of two files, examp01.dbf and examp02.dbf. Once created, you can see that 2 files have been added under the corresponding path.

Note: The statement in Linux

Create tablespace exampletb datafile ' u01\ examp01.dbf ' size 5M;

Drop Tablespace Exampletb;

? Create the tablespace and table, and then delete the table space.

Create tablespace EXAMPLETB

DataFile ' e:\ examp01.dbf ' size 5M reuse autoextend on next 128k maxsize 1000m,

' E:\examp02.dbf ' size 5M reuseautoextend on next 128k maxsize 1000m;

CREATE TABLE Scott.student

(

ID number,

Name VARCHAR2 (10)

) Tablespace Exampletb;

Select Segment_name, BYTES, blocks,extents from user_segments wheresegment_name= ' STUDENT '; --Scott users can execute the query, which SYS users cannot find.

Select Segment_name, Segment_type fromdba_segments where segment_name = ' STUDENT ' and owner= ' SCOTT ';

--Used to confirm that segment was not created

Drop Tablespace Exampletb;

? Create a table space and table, insert a record into the table, and then delete the table space.

Create tablespace EXAMPLETB

DataFile ' e:\ examp01.dbf ' size 5M reuse autoextend on next 128k maxsize 1000m,

' E:\ examp02.dbf ' size 5M reuse autoextend on next 128k maxsize1000m;

CREATE TABLE Scott.student

(

ID number,

Name VARCHAR2 (10)

) Tablespace Exampletb;

Insert into Scott.studentvalues (1, ' Lucy ');

Select Segment_name, BYTES, blocks,extents from user_segments wheresegment_name= ' STUDENT '; --Scott users can execute the query, which SYS users cannot find.

Select Segment_name, Segment_type fromdba_segments where segment_name = ' STUDENT ' and owner= ' SCOTT ';

--Used to confirm the creation of segment

Description: When inserting data into a student table, the data is stored in the tablespace Exampletb, and the EXAMPLETB table space has one or more data files, so student data is eventually stored in EXAMP01 and EXAMP02 data files.

Drop tablespace exampletb including contents;

? Create a tablespace, and then delete the tablespace and the data file.

Create tablespace EXAMPLETB

DataFile ' e:\ examp01.dbf ' size 5Mreuse autoextend on next 128k maxsize 1000m,

' E:\examp02.dbf ' size 5M reuse autoextend on next 128k maxsize 1000m;

Drop tablespace Exampletb including contents and datafiles;

Note: If the drop TABLESPACE statement contains datafiles, the datafiles must have a contents keyword before the error is prompted.

? Create two table spaces, where you create the main and outer code tables, and then delete the table spaces and data files that contain the Master Code table.

Create tablespace exampletb1

DataFile ' e:\ examp01.dbf ' size 5M autoextend on next 128k maxsize 1000m;

Create tablespace EXAMPLETB2

DataFile ' e:\ examp02.dbf ' size 5M autoextend on next 128k maxsize 1000m;

CREATE table Test1 (mobile number primary key) Tablespace exampletb1;

CREATE TABLE Test2 (mobilenumber) references test1 (mobile) tablespaceexampletb2;

Drop tablespace exampletb1 including contents and datafiles cascade constraints;

8, Case (Deferred_segment_creation is false)

? Create a tablespace, and then delete the table space.

Create tablespace EXAMPLETB

DataFile ' e:\ examp01.dbf ' size 5Mautoextend on next 128k maxsize 1000m,

' E:\ examp02.dbf ' size 5M autoextend on next 128k maxsize 1000m;

Description: The table space created by the above example consists of two files, examp01.dbf and examp02.dbf. Once created, you can see that 2 files have been added under the corresponding path.

Drop Tablespace Exampletb;

? Create the tablespace and table, and then delete the table space.

Create tablespace EXAMPLETB

DataFile ' e:\ examp01.dbf ' size 5M reuse autoextend on next 128k maxsize 1000m,

' E:\ examp02.dbf ' size 5M reuse autoextend on next 128k maxsize1000m;

CREATE TABLE Scott.student

(

ID number,

Name VARCHAR2 (10)

) Tablespace Exampletb;

Select Segment_name, BYTES, blocks,extents from user_segments wheresegment_name= ' STUDENT '; --Scott users can execute the query, which SYS users cannot find.

Select Segment_name, Segment_type fromdba_segments where segment_name = ' STUDENT ' and owner= ' SCOTT ';

--Used to confirm the creation of segment

Drop tablespace exampletb including contents;