Oracle Database-table space basic syntax and examples

Storage structure of the database

Databases are primarily used to store and retrieve related information, and the Oracle database contains logical and physical structures.

Physical structure refers to a real storage unit, consisting of a set of files, such as data files, log files, control files.

Data file: The file used to store the data. such as tables, indexes, and data are stored in the data file.

Log file: Used to record modification information for a database.

Control file: A binary file used to store Oracle instance information, data file information, and log file information. Because information about data files and log files is stored, Oracle must have access to the control files at startup.

Logical structure refers to the organization of data conceptual. Includes table space, table, row and other concepts.

The concept of a table space (tablespace)

Tablespace is a special Oracle invention for storing disk logical spaces for objects such as database tables, indexes, and so on. The physical table space consists of one or more data files, and the database is logically composed of one or more table spaces.

The table space is divided into 3 categories in the north of oracle10g:

1, permanent table space: generally keep the base table, view, stored procedures and indexes and other data. The SYSTEM, Sysaux, USERS, and example table spaces are installed by default.

2, temporary table space: For the preservation of short-term activities in the system data, such as sorting data.

3. Undo the tablespace; to help roll back uncommitted transaction data, the submitted data is not recoverable here.

You generally do not need to create temporary tablespace and undo tablespace unless you transfer them to another disk to improve performance.

The purpose of the table space:

1. Assign different table spaces to different users, assign different table spaces to different mode objects, and facilitate the management of user operation and Mode objects.

2, you can create different data files to different disks, to facilitate the management of disk space, to improve the I/O performance, backup and recovery of data and so on.

　　

Second, the system comes with the table space:

　　system tablespace: data used to hold internal tables and data dictionaries for Oracle systems, such as table names, column names, user names, and so on. It is not recommended that objects such as user-created tables, indexes, and so on be stored in the system table space.

　　Sysaus Table Space: An object that is used to store common sample users inside an Oracle system. The Sysaux table space typically does not store user data and is automatically maintained by the Oracle system.

　　Undotbs table Space: The Undotbs table space is used to hold the table space for the revocation information. When we add, modify, and delete data from a database table, Oracle automatically uses the Undo table space to temporarily store the data before it is modified. When the action is committed, Oracle frees up some space in the undo table space as long as it needs to retain the amount of data before it is modified. Typically after you create an Oracle instance, Oracle automatically creates an undo tablespace named UNDOTBS1.

　　temp tablespace: A special table space used to hold temporary data. For example, when a user needs to sort, Oracle's sorted data is temporarily placed in the table space. When sorting is complete, the space occupied by the sort data is freed.

　　Users table Space: The Users table space is a table space that Oracle recommends that users use to create various objects, such as creating tables, indexes, and so on.

　　Example Tablespace : (instance tablespace) it is used to store the data information of the instance user mode that we created when we installed Oracle.

In addition to the table space that comes with Oracle, users can create multiple table spaces as needed to differentiate between user data and system data. Typically in an Oracle database, you create only one DB instance, and you create multiple table spaces to manage the database tables that are saved in multiple projects.

　　

Third, create TABLE space

Grammar:

CREATE tablespace Tablespacename

datafile ' filename ' [size integer]

[autoextend[off| On]]

Tablespancename: Represents the name of the created tablespace.

FileName: Represents a live multiple data file that makes up a tablespace, used when there are multiple data files, delimited.

Size: Specifies the size of the file, in either K (bytes) or M (trillion).

Autoextend: Used to enable or police data file automatic extension, only after setting the Autoextend, after the storage space is used to automatically expand, set to ON, if set to OFF, you cannot automatically expand. Results in a situation where storage space is low.

Example:

--Create a tablespace, the physical file is shop_dev_data.dbf, the initial size is 100M, for autogrow create tablespace shop_dev_datadatafile ' E:\DB\Shop\shop_dev_ data.dbf ' size 100m autoextend on

Iv. Operating Table Space

　　1. Change the size of the table space:

ALTER DATABASE datafile ' data file path ' Resize < new size >

--Set the physical file size of the tablespace to 50MALTER DATABASE datafile ' E:\DB\Shop\shop_dev_data.dbf ' resize 50m

　　2. Change the properties and state of the table space

ALTER tablespace < tablespace name > READ WRITE;

--Set Tablespace as read-only alter tablespace shop_dev_data read only;--Set table space for read-write status alter tablespace Shop_dev_data read write;-- Modify the table space state to offline; ALTER tablespace shop_dev_data OFFLINE;

　　

　　3. Delete Table space

DROP tablespace tablespacename;

--Delete Table space drop tablespace shop_dev_data

　　

　　4. Querying all the table spaces in the system

SELECT T.tablespace_name,t.status from Dba_tablespaces t

　　

　　5. Query table space and data file correspondence relationship

SELECT D.tablespace_name,d.file_name from Dba_data_files D

　　

　　6. Query the user's default table space　

--Query user default Tablespace select U.username,u.default_tablespace from Dba_users

　　

　　7. What tables are included in the query table space　

--Query the "shop_dev_data" table space for all tables that correspond to select * FROM User_tables ut WHERE ut. Tablespace_name = ' Shop_dev_data '  --name must be all uppercase

　　8. Move the table to a new table space　

--Move newtable to the users table space under ALTER TABLE newtable move tablespace usersselect * from User_tables utwhere UT. Tablespace_name = ' USERS '

　　9. Modify the user default table space　　

SELECT username,default_tablespace from dba_users--modify Shop_user user default tablespace for usersalter user Shop_user defaults tablespace  USERS

Oracle Database-table space basic syntax and examples