Oracle Storage Fabric-Table space-notes

 
Oracle database, all the 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.
An Oracle database can have one or more table spaces, while one table space corresponds to one or more physical database files.

table Space Definition: a container for storing data. A tablespace consists of one data file or multiple data files, and one data file belongs to only one table
space.
-----(This container increases as the data file grows)


the role of tablespaces can help DBA users do the following:
1. Determining the spatial distribution of database entities;
2. Set the space share of the database users;
3. Control the availability of some data in the database;
4. Distribute data between different devices to improve performance;
5. Back up and restore data.



based on Oracle, how many basic table spaces are in the Oracle system?
select * from Dba_tablespaces;

several table spaces created by default in the system:

system table Space

Sysaux System Auxiliary Table space

Users User table space

UNDOTBS1 Roll Back table space

EXAMPLE

temp temporary table space

What are the required table spaces in the system?

answer: SYSTEM, Sysaux, TEMP, UNDO, table spaces like users, example, etc. are optional.



Table Space Classification: (by Time of storage)

1. Permanent table space holds permanent data, such as tables, indexes, etc.
2. Temporary table space to hold temporary data, save the PGA memory overflow data sorting, group generated temporary data
3.UNDO table space holds the image before the data is modified. (Oracle copies a copy of the data to the undo table space before modifying it)


How table spaces are managed:
A. Local management table space (LMT)
B. Data dictionary management table Space (DMT)

A. Local Management table space: A more advanced way to manage extensions (extent), using 6 blocks when using local management table spaces
(from third to eighth) to identify each extension in the entire tablespace (extent);

B. Dictionary management tablespace: This is provided for compatibility with previous versions. In 11g, you cannot create a dictionary management table space.
, is forced to recommend, only to pour into the old version of the Dictionary management table space.

--------------------------------------------------------------------

table space Additions and deletions related operations:

--Create TABLE space:
1. Create a data file with an initial size of 10M, each time self-growth of 1M, the maximum self-growth of 20M table space.
sql> Create tablespace TBS
2 datafile '/oracle/tbs01.dbf ' size 10M autoextend on next 1M maxsize 20M;

2. Create a table space containing two data files, the first data file size is 20m, each self-growth is 1M, the maximum self-growth is 30M;
The second data file has an initial size of 10M, each self-growth of 5 m, unlimited size;
sql> Create tablespace ts
2 datafile '/oracle/ts01.dbf ' size 20M autoextend on next 1M maxsize 30M,
3 '/oracle/ts02.dbf ' size 10M autoextend on next 5M;


--extended table space;
When a table exceeds the size of a table space, the table space is expanded. (There are three ways to increase the size of the table space)
(1) Add data file.
(2) Change the size of the data file.
(3) Allow automatic extension of data files.

A. Add a data file:
statement: Alter tablespace tablespace_name add Datafiel ' path_dbf ' size;
--Add a data file with an initial size of 3M that does not grow from the TS table space;
sql> alter tablespace TS
2 Add datafile '/oracle/ts03.dbf ' size 3M;
Thinking-
sql> alter tablespace TS
2 Add datafile '/oracle/ts04.dbf ' size 2M autoextend on next 2M maxsize 10M
3/


B. Changing the size of a data file
ALTER DATABASE datafile ' PATH_.DBF ' resze size;
-Add 8m to the TS tablespace data file;
sql> ALTER DATABASE datafile '/ORACLE/TS04.DBF ' resize 8M;


c. Allow automatic extension of data files
ALTER DATABASE datafile ' PATH_.DBF ' autoextend on next size_ size maxsize size_ size;
-Change TBS tablespace No. 01 data file to self-growth, each time self-growth 1M, the maximum limit is 10M;
sql> ALTER DATABASE datafile '/ORACLE/TBS01.DBF ' autoextend on next 1M maxsize 10M;


-------How to specify a tablespace for a new table;
Two examples:
sql> CREATE TABLE C1 (a int,b char)
2 tablespace TBS
3/
    
                  
sql> CREATE TABLE C2
2 tablespace TS
3 AS SELECT * from dept;


----------Migrate the table to another table space;
statement: ALTER TABLE table_name move tablespace new_tablespace_name;
                    
sql> ALTER TABLE C2 move tablespace TBS;

----another------
statement: ALTER TABLE table_name move;
Organize table space fragmentation;

-------------Delete Table spaces:
Delete TS table space;
sql>drop tablespace TQ;
    
Delete The table space and delete the data files;
statement: Drop tabelpaces tablespace_name including contents and datafiles;
sql> drop tablespace TQ including contents and datafiles;


--------Modify table space names
sql> alter tablespace TS rename to ts1;



--------------query table space Related:
  
a little recap: How many Oracle views have you learned so far?
1.user_constraint constraint related views
2.recyclebin Recycle Bin View
3.dba_tables Table Properties View
4.dba_tablespaces table Space View
5.dba_data_files data File View


take several common queries as an example:
----query which table space the table belongs to;
Sql>select table_name,tablespace_name from dba_tables where table_name= ';

--Contains descriptive information for all table spaces in the database
sql> SELECT * from Dba_tablespaces;

--Contains the information describing the current user's table space
sql> SELECT * from User_tablespaces;

--Contains the tablespace name and number information obtained from the control file
sql> SELECT * from V$tablespace;

 

View data Files

--Contains descriptive information about the data file and the table space to which it belongs
sql> SELECT * from Dba_data_files;

--Contains descriptive information about the temporary data file and the table space to which it belongs
sql> 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
sql> SELECT * from Dba_temp_files;

--Contains basic information for all temporary data files
sql> SELECT * from V$datafile;

Oracle Storage Fabric-Table space-notes