Oracle table Space to

A study think three knocks to understand, can long-term effective memory

When I first learned Oracle, we learned about Oracle's advantages, such as distributed management, easy coordination, superior performance, support for big data formats, and the fact that Oracle has unique table spaces and is highly flexible, multi-sexual, logic-minded, and so powerful that it has to learn!

Now, come with me. Learn about Oracle Tablespace

What is a tablespace: simply speaking, a table space is a logical table in a database, like a view in our database, a table, an index, and so on, we cannot manipulate objects directly on physical files, and we manipulate these logical tables with SQL statements to achieve our needs.

So how does it exist in the system? 1, database----> 2, table Space-----> 3, data table This is the relationship that is contained

Advantages:

1. To avoid sudden disk exhaustion

Easy to understand, because Oracle itself as a process of storing data, so the nature of the trend. So if we do not plan the database properly then it is possible that because the database itself and the disk space is full, table space exists, we can flexibly control the size of the table space, to prevent the long-term use of the disk is data exhaustion.

2. More flexible planning

In the actual business process, it is possible to plan different types of data according to the needs of the business in the distributed storage, so that we can divide the table space, according to the business requirements of the data stored in different table space, so that we efficiently manage and query.

4. Flexible and efficient data and database performance

Scene one, when we often query the data frequently, this kind of data we can put in a table space, the table space stored in the performance of the disk, the same we put the popular data sorting into another table space, storage to the performance of the lower disk, another scenario, when our data due to development work needs, Frequent changes to the data operation, then a large number of operations will not only affect the performance of the system, the disk also has a greater pressure to achieve reasonable use of services, the maximum performance of improving database performance.

There are some data iterations that are particularly frequent, so we put the table space alone, and for different table spaces, specify a different backup cycle for the table space, minimizing the resource footprint of the data.

5. More secure data

In addition to being hacked by the system, we have different data tables, placed in different table spaces so that we when a disk is damaged, will not cause all data table corruption

Another way we can do it. data files and log files are stored in different table spaces, stored on different disks, and when our data files are corrupted, the log files are used for recovery, which effectively reduces the loss of data.

The concept of table space design:

We take advantage of the idea of reasoning, in order to avoid disk depletion, so that we can think of reasonable planning database to start, including scalability, management level, the logical structure is very clear.

Code hit a table space started

Format:

Create tablespace user1 datafile D:\path\user1_data.dbf size 20M autoextend on next 5M maxsize (unlimited);

Explain

Create a table space, size 20M, can be extended, each expansion capacity 5M maximum table space 200M unlimited no upper limit.

Format:

Select File_name,tablespace_name from Dba_data_files order by file_name;

Explain

Query result table Space results

Format:

Select Tablespace_name,status,allocation_type from Dba_tablespaces;

Explain:

Querying table space Information

Format:

Select User_id,username,default_tablespace from Dba_user;

Analytical:

Querying each user's tablespace

Format:

Select tablespace table_name Rename to table_name2;

Analytical

Renaming a table space

Format

Drop tablespace user_table including contents and datafiles;

Analytical

When you delete a tablespace, delete the data file (including contents and datafiles).

Oracle table Space to