Oracle tablespace Management

2 tablespace

The maximum logical layer in Oracle disk management is the tablespace. The four tablespaces that must be created in Oracle11g are SYSTEM, SYSAUX, TEMP, and UNDOTBS1.

2 SYSTEM: stores data dictionaries, pl/SQL code, etc.

2 SYSAUX: stores data related to database options

2 TEMP: used for large sorting operations

2 UNDUTBS1: stores transaction information for read consistency and restoration purposes.

The next layer of a tablespace is a segment. A segment can only reside in one tablespace. One or more partitions can form one segment, and each partition can only reside in one data file; A group of continuous data blocks can form a zone. To query information about the table space and corresponding data files, you can query the table space and its data files from the dba_data_files data dictionary, for example:

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 tablespace

The SYSTEM tablespace stores internal data and data dictionaries. It mainly stores various objects of SYS users and a small number of objects of other users. For example, query the data objects in the USERS tablespace, their types, and owners.

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 tablespace

The SYSAUX tablespace serves as the auxiliary tablespace of the system tablespace and is mainly used to store data objects other than data dictionaries. For example, query the users in the SYSAUX tablespace and the number of objects they own:

Select owner as user, count (segment_name) as object quantity fromdba_segments where tablespace_name = 'sysaux 'group by owner;

3. Create a tablespace

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]

[Online | offline]

[Logging | nologging]

[Permanent | temporary]

[Extent management dictionary | local [autoallocate | uniform size num [k | m];

Note:

? Smallfile | bigfile: whether to create a small file tablespace or a large file tablespace.

? Autoextend [on | off] next: indicates that the data file is automatically extended or not automatically extended. If it is automatically extended, you need to set the value of next.

? Maxsize: Maximum number of bytes allowed for Data File expansion when the data file is automatically expanded. If the unlimited keyword is specified, the length of bytes is not required.

? Minimum extent: specifies 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 this value.

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

? Permanent | temporary: specify whether the created tablespace is a permanent or temporary tablespace. The default value is permanent tablespace.

? Logging | nologging: Specifies whether the table in the tablespace generates logs when loading data. The default value is log generation. Even if it is set to nologging, the insert, update, and delete operations are performed, oracle will still record the information to the redo log buffer.

? Extent management dictionary | local: whether to use data dictionary management or local management to expand the tablespace. The default value is local management.

? Autoallocate | uniform size: if local management is adopted, during tablespace expansion, specify whether the expansion size of each partition is automatically specified by the system or equal. If the uniform keyword is set, the default expansion size is 1 MB.

? Reuse: indicates that if the file exists, the file is cleared and then recreated. If the file does not exist, the file is created.

? Default storage: Set the storage parameter values of tables, indexes, and clusters to be created later.

4. delete a tablespace

? Deletes empty tablespaces, but does not contain physical files.

Drop tablespacetablespace_name;

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

Drop tablespacetablespace_name including contents;

? Delete empty tablespace, including physical files

Drop tablespace tablespace_nameincluding datafiles;

? Delete non-empty tablespace, including physical files

Drop tablespacetablespace_name including contents and datafiles;

? If the tables in other tablespaces have foreign keys and other constraints associated with the table fields in the current tablespace, CASCADECONSTRAINTS must be added.

Drop tablespacetablespace_name including contents and datafiles cascade constraints;

5. Cases

? Create and delete a tablespace.

Createtablespace exampletb

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

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

Note: The tablespace created in the preceding example consists of examp01.dbf and examp02.dbf. After the file is created, you can find that two files are added to the corresponding path.

Drop tablespaceexampletb;

? Create and delete a tablespace.

Createtablespace exampletb Datafile 'e: \ examp01.dbf' size 5 M autoextendon next 128 k maxsize 1000 m reuse,

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

Create tablescott. student

(

Id number,

Name VARCHAR2 (10)

) Tablespaceexampletb;

Note: when data is inserted 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 finally stored in examp01 and examp02 data files.

Drop tablespaceexampletb including contents;

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

Create tablespaceexampletb

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

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

Drop tablespaceexampletb including datafiles;

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

Createtablespace exampletb

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

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

Create tablescott. student

(

Id number,

Name VARCHAR2 (10)

) Tablespaceexampletb;

Drop tablespaceexampletb including contents and datafiles;

Note: If the drop tablespace statement contains datafiles, you must have the contents keyword before datafiles. Otherwise, an error is prompted.

? Create two tablespaces: The primary table and the External table, and delete the tablespaces and data files that contain the primary table.

Createtablespace exampletb1

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

Createtablespace exampletb2

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

Create tabletest1 (mobile number (13) primary key) tablespace exampletb1;

Create table test2 (mobile number (13) references test1 (mobile) tablespace exampletb2;

Drop tablespace exampletb1 including contents and datafiles cascade constraints;