Oracle Table Space Management

2 Table Space

The highest logical layer in Oracle Disk Management is Tablespace, and the 4 tablespaces that must be created in oracle11g are system, sysaux,temp, UNDOTBS1.

2 SYSTEM: Stored data dictionary, pl/sql code, etc.

2 Sysaux: Storing data related to database options

2 TEMP: For large sort operations

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

The next layer of a table space is a segment, and a segment can reside only in one table space, one or more extents may form a segment, each zone can reside in only one data file, and a contiguous set of data blocks can form a single area. If you want to query the table space and the corresponding data file related 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 fromdba_data_files order by Tablespace_name;

1. system table Space

The system tablespace holds internal data and data dictionaries, which primarily hold a small number of objects for the SYS user and for other users. For example, query the data objects and their types and owners that are 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,owner fromdba_segments where tablespace_name= ' USERS ';

2, Sysaux table space

The Sysaux table space acts as a secondary tablespace for the system table space, primarily for storing data objects other than the data dictionary. For example, query the number of users that the Sysaux table space holds and how many objects they have:

Select owner as user, Count (segment_name) As Object quantity fromdba_segments where Tablespace_name= ' Sysaux ' GROUP by owner;

3. Create a table space

The syntax for creating a table space 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 to create a small file table space or a large file table space

? Autoextend [On|off] Next: Indicates that the data file is automatically extended or not automatically expanded, for example, you need to set the value of next for automatic expansion.

? MaxSize: Represents the maximum length of bytes allowed for data file expansion when the data file is automatically expanded, and you do not need to specify the byte length if you specify a unlimited keyword.

? Minimum extent: Indicates the minimum value of the extent in the tablespace, which reduces space fragmentation, guaranteeing that the extent in the tablespace is an integer multiple of this value.

? Online | Offline: can be specified online or offline when creating a tablespace.

? Permanent | Temporary: Specifies whether the creation of a tablespace is a permanent tablespace or a temporary table space. The default is permanent table space.

? Logging | Nologging: Specifies whether tables in this tablespace generate logs when loading data, default to generate logs, even if set to Nologging, but Oracle still logs information to redo log when insert,update,delete operations are performed In the buffer.

? Extent Management Dictionary | Local: Specifies whether the table space is expanded using data dictionary management or localized administration. The default is localized administration.

? Autoallocate | Uniform size: If localized management is used, specify whether the extended size of each area is automatically specified by the system or at the same size when the table space expands. If you set the Uniform keyword, the default extension size is 1MB.

? Reuse: Indicates that if the file exists, it is purged and the file is rebuilt, and if the file does not exist, the file is created.

? Default storage: Sets the stored parameter values for the table, index, and cluster that you want to create later.

4, delete the table space

? Deletes empty tablespace, but does not contain physical files

Drop Tablespacetablespace_name;

? Deletes a non-empty tablespace, but does not contain physical files

Drop tablespacetablespace_name including contents;

? Delete empty table space, including physical files

Drop tablespace tablespace_nameincluding datafiles;

? Delete Non-empty tablespaces, including physical files

Drop tablespacetablespace_name including contents and datafiles;

? If a table in another table space has a field such as a FOREIGN key constraint associated with a table in this table space, add the cascadeconstraints

Drop tablespacetablespace_name including contents and datafiles CASCADE CONSTRAINTS;

5. Case

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

Createtablespace EXAMPLETB

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

' e:examp02.dbf ' size 5Mautoextend on next 128k maxsize 1000m;

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

Drop TABLESPACEEXAMPLETB;

? Create table spaces and tables, and then delete the table space.

Createtablespace exampletb datafile ' e:examp01.dbf ' size 5M Autoextendon next 128k maxsize 1000m reuse,

' e:examp02.dbf ' size 5Mautoextend on next 128k maxsize 1000m reuse;

Create Tablescott.student

(

ID number,

Name VARCHAR2 (10)

) Tablespaceexampletb;

Note: When inserting data into the 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 the EXAMP01 and examp02 data files.

Drop TABLESPACEEXAMPLETB including contents;

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

Create TABLESPACEEXAMPLETB

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

' e:examp02.dbf ' size 5Mautoextend on next 128k maxsize 1000m reuse;

Drop TABLESPACEEXAMPLETB including datafiles;

? Create tablespaces and tables, and then delete the table space and data files.

Createtablespace EXAMPLETB

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

' e:examp02.dbf ' size 5Mautoextend on next 128k maxsize 1000m;

Create Tablescott.student

(

ID number,

Name VARCHAR2 (10)

) Tablespaceexampletb;

Drop TABLESPACEEXAMPLETB including contents and datafiles;

Note: If the drop TABLESPACE statement contains datafiles, the contents keyword must precede the datafiles, or you will be prompted for an error.

? Create two tablespaces, create the main and foreign code tables, and then delete the table spaces and data files that contain the main code table.

Createtablespace EXAMPLETB1

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

Createtablespace EXAMPLETB2

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

Create TABLETEST1 (mobile number () primary key) Tablespace exampletb1;

CREATE TABLE Test2 (mobile number references test1 (mobile)) tablespace EXAMPLETB2;

Drop tablespace exampletb1 including contents and datafiles cascade;