Shulou(Shulou.com)06/01 Report--

Only when you learn and think twice can you understand, and only then can you remember effectively for a long time.

At the beginning of learning Oracle, already understand the advantages of Oracle, such as distributed management, easy coordination; superior performance; support big data format; there is another point is that Oracle original tablespace is unique, and has a strong flexibility, multiplicity, logical thinking is more thorough, since so powerful, must learn!

Next, come with me to learn about the table room of Oracle.

What is tablespace: to put it simply, tablespaces are logical tables in a database, such as views, tables, indexes and so on in our database. We can't manipulate objects directly on physical files. We use SQL statements to manipulate these logical tables to achieve our needs.

So how does it exist in the system? what kind of logical relationship is it? 1. Database-> 2, tablespace-> 3, data table, this is the included relationship.

Advantages:

1. Avoid sudden disk exhaustion

To be familiar and understandable, because Oracle itself is a process of storing data, it is essentially a trend. Then if we plan the database irrationally, then the disk space may be occupied because of the database itself, and the table space exists, so we can flexibly control the size of the table space to prevent the disk from being exhausted by data during long-term use.

2. More flexible planning

In the actual business process work, it is possible to plan different types of data to be stored in distributed storage according to the needs of the business, so that we can divide multiple table spaces and store the data in different table spaces according to the business needs. it is convenient for us to manage and query efficiently.

4. Flexibility and efficiency of data and database performance

Scenario one, when we query the data frequently, we can put this kind of data into a separate table space and store the table space on a disk with good performance. Similarly, we sort the unpopular data into another table space and store it to a disk with lower performance. In another application scenario, when our data is frequently modified due to the needs of the development work, the data operation is frequently modified. Then a large number of operations will not only affect the performance of the system, but also have greater pressure on the disk, to achieve the rational use of services, the maximum performance to improve database performance.

Some data iterations are particularly frequent, so we put them into tablespaces separately and specify different backup cycles for different tablespaces to minimize the resource consumption caused by data share.

5. Data is more secure

In addition to being * by the system, then we have different data tables and put them in different table spaces so that when one disk is damaged, it will not cause all the data tables to be damaged.

On the other hand, we can store data files and log files in different tablespaces and different disks, and use log files to recover when our data files are damaged, so as to effectively reduce data loss.

The concept of tablespace design:

We start to reason from the advantages, in order to avoid disk exhaustion, we can think that reasonable planning of the database, including scalability, management levels, logical structure are very clear.

The code re-creates a tablespace to start

Format:

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

explain

Create a table space with a size of 20m, which can be expanded. The capacity of each expansion is 5m and the maximum table space is 200m unlimited.

Format:

Select file_name,tablespace_name from dba_data_files order by file_name

explain

Query results tablespace results

Format:

Select tablespace_name,status,allocation_type from dba_tablespaces

Explanation:

Query tablespace information

Format:

Select user_id,username,default_tablespace from dba_user

Parsing:

Query each user's tablespace

Format:

Select tablespace table_name rename to table_name2

Analysis

Rename tablespace

Format

Drop tablespace user_table including contents and datafiles

Analysis

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

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.