Oracle Database tablespace Parsing

1. Basic Concept: the logical structure of oracle can be divided into tablespaces, segments, partitions, and data blocks ). Oracle consists of tablespaces. tablespaces consist of segments, which are composed of partitions and data blocks. 1) The tablespace is the largest logical unit in the database, and several tablespaces constitute the database. 2) a tablespace consists of one or more data files physically. A data file can only belong to one tablespace. Here, the distribution of tablespaces on multiple disks is formed through different data file locations. 3) Table space is divided into data segment tablespaces, index segment tablespaces, temporary segment tablespaces, and rollback segment tablespaces based on the composition of the segment type. 4) You can modify the data file size or add or delete a data file to manage the tablespace size. 5) The tablespace can be ONLINE or OFFLINE, or some tables in the www.2cto.com space can be ONLINE or OFFLINE. It also has read-only or read-write attributes. -- System and other special tablespaces cannot be set to offline or read-only 6) You can use the QUOTA parameter to limit the size of tablespaces. 7) special temporary tablespaces are mainly stored in the temporary space required for sorting or summarizing using the order by statement. After 9i, the TEMP tablespace is generally used as the temporary tablespace. We do not recommend that you use system and other special spaces as the temporary tablespace. The base table cannot be created on the temporary tablespace. After the system specifies the default temporary tablespace, other tablespaces cannot be used as the temporary tablespace. 2. SQL syntax for related tablespaces 1) Use the sqlplus tool to create database tablespaces using the following statement: create tablespace tbsname datafile 'file path and file name' size 500 m; 2) query the data files in a table space: select file_id, bytes, maxbytes, tablespace_name, file_name from dba_data_files; 3) query the free space information of the table space: select tablespace_name, sum (bytes), sum (blocks) from dba_free_space group by tablespace_name; 4) query the Temporary file Information select file #, status, name from v $ tempfile; 5) query the temporary tablespace information used by the user select username, temporary_tabl Espace from dba_users; 3. For details about the tablespace application, see 1) control the tablespace quota occupied by users. 2) control the disk space occupied by the database. 3) flexibly place tablespaces to improve the input and output performance of the database. 4) sorting of large tables. 5) open log files and data files to improve database security. 6) Step 1: Create a tablespace. Step 2: create a user and create a default tablespace for the user. For details, refer to the "tablespace" entry in Baidu encyclopedia.

From column apn172