44.oracle Use of table space

To give subordinates full play space, to allow subordinates to make mistakes, subordinates make mistakes can top down on the top, do not stare to let subordinates feel "this is not my thing, I just a small job," the group to put in the first place. Do not "pack B" in front of subordinates, others are not silly.

Oracle database pioneered the design concept of tablespaces, which made an indelible contribution to the high performance of Oracle databases. As you can say, many of the optimizations in Oracle are based on the design philosophy of the tablespace.

The Oracle database is partitioned into a logical region called the tablespace [1], which forms the logical structure of the Oracle database. An Oracle database can have one or more table spaces, while one table space corresponds to one or more physical database files. The tablespace is the smallest unit of Oracle database recovery, accommodating many database entities such as tables, views, indexes, clusters, fallback segments, and temporary segments.

Each Oracle database has a system table space, which is created automatically when the database is created. The system tablespace must always be online because it contains the basic information required to run the database (data dictionary for the entire database, online help mechanism, all fallback segments, temporary segments and bootstrap segments, all user database entities, tables for other Oracle Software PRODUCT requirements). An Oracle database for a small application typically includes only the system table space, whereas a slightly larger application with an Oracle database with multiple table spaces can be more convenient for database use.

For example: Easy to understand, the Oracle database as a real room, the table space can be regarded as the space of the room, is free to allocate, in this space can be stacked multiple boxes (chests can be regarded as database files), the box is loaded with objects (objects as a table). User-specified tablespace that is, you want to put the table that belongs to this user in that room (table space). (above from Baidu Encyclopedia)

Create a tablespace The first step: Create a temporary table space

Create temporary tablespace sgm_temp
Tempfile ' D:\1DATA\ORACLE\DATAFILE\SGM_TEMP.DBF '
Size 1024m
Autoextend on
Next 50m maxsize 2048m
Extent management Local;

The results are as follows:

Delete temporary tablespace statement: Drop tablespace sgm_temp including CONTENTS and datafiles;

Step Two: Create a data table space

Create tablespace SGM
datafile ' D:\1DATA\ORACLE\DATAFILE\SGM.DBF '
Size 1024m
Autoextend on
Next 50m maxsize 2048m
Extent management Local;

The data table space is not enough to add:

Alter tablespace SGM add datafile ' D:\1data\oracle\datafile\sgm1.dbf ' size 1024m autoextend on;

Step three: Create a user and specify a tablespace

Create user SGM identified by ORCL
Default Tablespace SGM
Temporary tablespace sgm_temp;

Fourth step: give the user authorization

Grant SYSDBA to SGM;

Once these steps are complete, you can log in with SGM/ORCL.

View Table Spaces

View Oracle Tablespace and remaining capacity:

Select T.tablespace_name,round (SUM (bytes/(1024*1024)), 0) ts_size
From Dba_tablespaces T,dba_data_files D
where t.tablespace_name = D.tablespace_name
Group BY T.tablespace_name;

To view the DB instance name:

Select instance_name from V$instance;

44.oracle Use of table space