Dba_tablespace concept and control of the table space (concept)

2014-07-24 Baoxinjian

I. Summary

We know that the Oarcle database is the data files that really hold the data;

Oarcle table Space (tablespaces) is actually a logical concept, he does not exist physically, then a set of data files are twisted together to become a table space;

In the Oracle database, all data from the logical structure is stored in the table space, of course, the table space is also a section, area, block and other logical structure;

From the physical structure is placed in the data file. A table space can consist of multiple data files;

1. Table Space Properties

• A database can contain more than one table space, and a table space can belong to a single database
• A table space contains multiple data files, and one data file can belong to only one table space
• Table This space can be divided into finer logical storage units

2. Basic tablespace (several table spaces created by default in the system:)

• SYSTEM
• Sysaux
• USERS
• UNDOTBS1
• EXAMPLE
• TEMP

3. Classification of Table spaces

• Permanent table space holds persistent data, such as tables, indexes, and so on.
• Temporary table spaces cannot hold persistent objects, which are used to hold database sorting and to create temporary data when grouped.
• Undo table Space holds the mirror image before the data is modified.

4. Table Space Management

• Dictionary management: All spatial allocations in the library are placed in the data dictionary. It is easy to cause dictionary contention and cause performance problems.
• Local administration: Spatial allocations are not placed in the data dictionary, while the bitmap blocks in the 3rd to 8th blocks of the header of each data file are allocated to manage spatial allocations.

5. Basic operation

• 1: Create TABLE space
• 2: Table Space Management
• 3: Maintain table space
• 4: Table Space quotas

Second, structural analysis

1. Table space and data file relationships

2. Database storage structure

Iii. Case 1-Create a table space

1. Create a tablespace and specify two database files

1 CREATE tablespace bxj_data2DataFile'/oradata/bxj_data_001.dbf'   3 SIZE 100M4 EXTENT MANAGEMENT LOCAL5 SEGMENT SPACE MANAGEMENT AUTO ONLINE;6 7 ALTER tablespace bxj_data8ADD datafile'/oradata/bxj_data_001.dbf'  9 SIZE 100MTenAutoextend OFF;

Iv. Case 2-table space management

1. Common table Queries

• --Contains descriptive information for all table spaces in the database
  • SELECT * from Dba_tablespaces
• --Contains the information describing the current user's tablespace
  • SELECT * from User_tablespaces
• --Contains the tablespace name and number information obtained from the control file
  • SELECT * from V$tablespace;
• --Contains descriptive information about the data file and the table space to which it belongs
  • SELECT * from Dba_data_files
• --Contains descriptive information about the temporary data file and the table space to which it belongs
  • SELECT * from Dba_temp_files
• --Contains basic information about the data file obtained from the control file, including the table space name, number, etc. to which it belongs
  • SELECT * from V$datafile
• --Contains basic information for all temporary data files
  • SELECT * from V$tempfile

2. View table Space usage

3. Calculating tablespace usage (taking into account the automatic growth of data files)

V. Case 3-Maintain table space

1. There are several states of the tablespace status: Online, offline, read only, read write

SELECT file#, STATUS, ENABLED from V$datafile

2. Set to Offline status

Sql>alter tablespace tbs_dm_dat OFFLINE IMMEDIATE;

3. Set to Online status

sql> ALTER tablespace tbs_dm_dat ONLINE;

4. Set to read-only status

Sql>alter tablespace Tbs_dm_dat ready only;

5. Set to read/write status

Sql>alter tablespace tbs_dm_dat READ WRITE;

6. Modify Table Space names

Before Oracle 10g, the name of a tablespace cannot be modified. In Oracle 11G, the database administrator can modify the name of a tablespace by using the rename clause in the ALTER TABLESPACE statement

7. Modify table space automatically increase open

ALTER DATABASE datafile '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf ' autoextend on;

8. Modify table space automatically increase shutdown

ALTER DATABASE datafile '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf ' autoextend OFF;

VI. Case 4-table space quota level

Insufficient table space and insufficient user quotas are two different concepts. The size of the tablespace refers to the size of the actual user table space, and the quota size refers to the size that the user specifies to use the table space. The two solutions are also different

1. View the user and table arrow quotas

• View quota for all user tablespace: SELECT * from Dba_ts_quotas
• View quotas for the current user table space: SELECT * from User_ts_quotas

Abalone New ********************

Reference: http://www.cnblogs.com/kerrycode/p/3418694.html