Shulou(Shulou.com)05/31 Report--

This article mainly explains "the basic management method of table space of Oracle database". The content of the explanation in this article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the basic management method of table space of Oracle database".

I. Overview

Oracle database stores physical tables through tablespaces. A database instance can have N tablespaces and one tablespace can have N tables.

With the database, you can create tablespaces.

Table spaces (tablespace) are logical partitions of databases, and each database has at least one table space (called SYSTEM table space). In order to facilitate management and improve operational efficiency, you can use some additional tablespaces to divide users and applications. For example, USER tablespaces are for general users, and RBS tablespaces are for rollback segments. A tablespace can only belong to one database.

The tablespace is the largest logical unit in the database. SYSTEM and SYSAUX tablespaces are the necessary tablespaces that must be created when creating the database, as well as TEMP, USERS and so on.

Each tablespace consists of one or more data files, and a data file can only be associated with one tablespace.

The size of the tablespace is equal to the sum of all the data file sizes that make up the tablespace

-

The tablespaces that are automatically created after installing Oracle include:

SYSTEM system table space, which stores management information about table space name, control file, data file and so on, belongs to sys and system mode. Cannot be deleted or renamed.

SYSAUX assists system tablespaces to reduce the load on system tablespaces and improve efficiency

TEMP temporary tablespace for temporary tables and temporary data for sorting

USERS user tablespace, which stores permanent user objects and private information, also known as data tablespace. Note: each database should have a user tablespace so that it can be assigned to the user when the user is created, otherwise the user's data will use the SYSTEM tablespace.

UNDO redoes tablespaces to help roll back uncommitted transaction data

Second, the experimental environment host operating system IP address database version OracleCentOS 7192.168.100.135oracle 12c III, tablespace management is first switched to Oracle user [root@HOSTNAMEoracle ~] # su-oracle

Enter the database and start Oracle

[oracle@HOSTNAMEoracle ~] $sqlplus / as sysdbaSQL > startup starts three phases

Start the instance-- "database loaded--" database open

SQL > quit # exit

Enable listening [oracle@HOSTNAMEoracle ~] $lsnrctl LSNRCTL > start

LSNRCTL > quit

Enter Oracle [oracle @ HOSTNAMEoracle ~] $sqlplus / as sysdba

-create a tablespace-create a tablespace school (size 10m auto extension) SQL > create tablespace school 2 datafile'/ orc/app/oracle/oradata/school01.dbf' 3 size 10m autoextend on; tablespace has been created.

Check whether [oracle@HOSTNAMEoracle ~] $cd / orc/app/oracle/oradata [oracle@HOSTNAMEoracle oradata] $lsorcl school01.dbf has been created successfully.

-adjust the tablespace size-modify the size SQL > alter database datafile 2'/ orc/app/oracle/oradata/school01.dbf' 3 resize 80m; the database has changed.

Check it.

Add the file SQL > alter tablespace school 2 add datafile 3'/ orc/app/oracle/oradata/school02.dbf' 4 size 50m autoextend on; tablespace has changed.

Check it.

-tablespace permissions-- # change tablespace school permissions can only be read SQL > alter tablespace school read only; tablespace has been changed. # change tablespace permissions to readable and writable SQL > alter tablespace school read write; tablespace has been changed. -Delete tablespace-SQL > drop tablespace school including contents; tablespace has been deleted. (including contents optionally deletes table spaces with content)

Check SQL > select tablespace_name from dba_tablespaces

Thank you for your reading, the above is the content of "basic management methods of tablespaces in Oracle database". After the study of this article, I believe you have a deeper understanding of the basic management methods of tablespaces in Oracle databases, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.