Oracle maintains tablespaces and data files

Oracle maintenance tablespace and data file 1: oracle tablespace concept table space is a bridge between the physical disk (data file) of the database and the logical component. The tablespace is used to store oralce database objects such: tables, indexes, and rollback segments. We can imagine the relationship between the oracle physical disk and the Logical Disk. The concept of tablespace is the same as this. PS: the data file is equivalent to a province (which actually exists), while the tablespace is equivalent to a provincial management organization and administrative organization. These are equivalent to a logical structure, the tablespace information is stored in the data dictionary. A tablespace is composed of at least one database datafiles. The total size of the tablespace is variable, and the physical disk space of all data files allocated to the tablespace is variable. When a tablespace is defined, datafiles is automatically created. In most cases, all datafiles are allocated in advance. When a datafile is created, the space is set. You, you can define the initial size of datafile. Tablespace is a logical concept that combines a set of data files into a tablespace. Concepts about oracle: 1. oracle block concept (block concept) an oracle block is the smallest storage unit of an oracle data block, the size of the oracle database block is specified when data is created. It cannot be modified unless the oracle Data is reconstructed. The size of the database block is usually 2 K, 4 K, 8 K, 16KOR 32 K. Once the block size is defined, a new tablespace can be created. The tablespace contains several blocks. Note: The oracle database instance also contains a RAM buffer cache. The blocks that constitute the RAM buffer cache are suitable for matching the physical data file data blocks. A database contains multiple tablespaces. One tablespace contains one or more datafiles. A table or index temporarily occupies one segmentextent and is composed of consecutive oracle data blocks: logically consecutive. A segment can be stored in different datafiles. extent cannot span datafiles. Oralce data block contains one or more OS blocks 2: tablespace type: 1: a database contains at least one tablespace: SYSTEM; system tablespace; when creating a database, create include data dictionary include SYSTEM undo segment Non-SYSTEM tablespace2: more scientific divide tablespace type: 1: permanent tablespace (SYSTEM and No-SYSTEM tablespace are of this type) 2: undo tablespace 3: temporary tablespace 2 and 3 are tablespaces used to manage databases. All data is not permanently stored. PS: when a data is created, it contains the following tablespaces: SYSTEM (the data dictionary) SYSAUX (optional database components) TEMP (temporary ta Blespace, see tablespace types below) UNDOTBS1 (undo tablespace, see tablespace types below) USERS (default users tablespace created) 3: create Table space oracle doc 11: simple syntax for creating a table space: [SQL] SQL> CREATE TABLESPACE peng DATAFILE 'd: \ APP \ TOPWQP \ ORADATA \ ORCL \ pen1_1.dbf's IZE 5 M; TABLESPACE created. Query tablespace information [SQL] SQL> select * from v $ tablespace; TS # name inc big fla enc ---------- ------------------------------ --- 0 system yes no yes 1 sysaux yes no yes 2 UNDOTBS1 yes no yes 4 users yes no yes 3 temp no yes 6 EXAMPLE yes no yes 7 USER_TEMP no yes 8 MY_SPACE yes no yes 9 TS_MYDB yes no yes 10 peng yes no yes 10 rows have been selected. How do I know the data files in a tablespace: [SQL] SQL> col file_name format a40 SQL> select file_name, tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME alias ---------------- D: \ APP \ TOPWQP \ ORADATA \ ORCL \ USERS01.DBF users d: \ APP \ TOPWQP \ ORADATA \ ORCL \ export UNDOTBS1 D: \ APP \ TOPWQP \ ORADATA \ ORCL \ SYSAUX01.DBF sysaux d: \ APP \ TOPWQP \ ORADATA \ ORCL \ SYSTEM01.DBF system d: \ APP \ T OPWQP \ ORADATA \ ORCL \ EXAMPLE01.DBF example d: \ APP \ TOPWQP \ ORADATA \ ORCL \ MY_SPACE.DBF MY_SPACE D: \ APP \ TOPWQP \ ORADATA \ ORCL \ TS_MYDB.DBF TS_MYDB D: \ APP \ TOPWQP \ ORADATA \ ORCL \ pen1_1.dbf PENG has selected 8 rows. 4: two data dictionary management methods: 1: locally managed tablespace autonomous management: 1: self-managed tablespace 2: there is a Bitmap in the data dictionary used to manage tablespace (a bitmap corresponds to an extent, meaning 1 occupies 0 and is not occupied) in each data file header, there will be a Bitmap bitmap, to indicate the space usage. A bit represents an extent, which occupies 1 and does not occupy 0. The locally-managed is also serial and cannot be parallel, but each table can be empty and managed by itself. All values are equivalent to parallelism. If the SYSTEM tablespace uses local-managed, other tablespaces can only be local-managed. In the future, we will use the local-managed method to manage tablespaces. If you want to change SYSTEM tablespace managed by dictionary to locally managed due to historical reasons, run the following command: [SQL] DBMS_SPACE_ADMIN.TABLESPACE_MIGRANTE_TO_LOCAL ('system'). Note: Make preparations before this command: 1: Full backup database 2: Make sure that the temporary tablespace is not the tablespace managed by SYSTEM. There are two tables to manage the data :? Do each tablespace port have its own DEBIT and CREDIT tables? DEBIT table: records all allocated extentCREDIT tables: records all idle extent tables. When applying for space, oracle queries these tables and allocates space, when inserting data into a table, you are applying for a space. 2: Manage data-dictionary. When inserting data in your own table, oracle needs to query the data dictionary (execute many implicit SQL statements) and allocate space. Because there is only one data dictionary: the access to the data dictionary must be serialized and not concurrent. If you use the data dictionary to manage the tablespace, the database overhead may be high. Therefore, this method is outdated, the central cluster is managed by data-dictionary, and local management is a new way to manage tablespaces. PS: central management of major events, and independent management of minor matters. Centralization and autonomy 3: undo tablespace: Mainly used to store undo segment. When a table or tablespace is modified, undo is to back up data first. Undo must be loacally mananged and will be explained in a specific chapter later; 5: temporary tablespaces: it is used for temporary oracle storage. For example, it is definitely not possible to do some large sorting in the memory, the memory is insufficient. In this case, you need to sort the data in the temporary tablespace. Temporary transfer station. There is a global temporary tablespace that can be shared by everyone. It is only used to store temporary information and can be used in data-dictionary mode. However, the locally-mananged management mode is recommended. we strongly recommend that you specify a temporary tablespace when creating a database. If this parameter is not specified, it is dangerous to do this by default. So you must specify. You can specify default temporary tablespace: 1 in either of the following ways. Default temporary tablespace temp TEMPFILE 'path/file name 'size 400M2: You can use alter database. Alter database default temporyary tablespace default_temp2; (create a TABLESPACE first) 6: query the DEFAULT temporary TABLESPACE [SQL] desc database_properties; col property_name format a20; col property_value format a20; select property_name, property_value from database_properties; 7: how to view the type of a tablespace: [SQL] SQL> select tablespace_name, contents, extent_management from dba_tablespaces; TABLESPACE_NAME CONTENTS EXTENT_MAN --------------------------- ---------- System permanent local sysaux permanent local UNDOTBS1 undo local temp temporary local users permanent local example permanent local USER_TEMP temporary local MY_SPACE permanent local limit permanent local peng permanent local has selected 10 rows. 8. Add a new tablespace under the default tablespace: [SQL] SQL> create temporary tablespace mytemp tempfile 'd: \ APP \ TOPWQP \ ORADATA \ ORCL \ my temp. dbf'size 100 M extent management local; the tablespace has been created. SQL> ALTER DATABASE DEFAULT TEMPORYARY TABLESPACE mytemp; ALTER DATABASE DEFAULT TEMPORYARY TABLESPACE mytemp * 1st row error: ORA-02231: the alter database option is missing or invalid. SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE mytemp; the DATABASE has been changed. SQL> the default temporary tablespace is modified. You can run the following command to query: [SQL] SQL> select property_name, property_value from database_properties; PROPERTY_NAME PROPERTY_VALUE ------------------ DICT. BASE 2 DEFAULT_TEMP_TABLESP mytemp ace DEFAULT_PERMANENT_TA users blespace a database can have multiple temporary tablespace, but only one default tablespace. 9: tablespace read-ONLY setting: alter tablespace mytemp read only; execute this statement: oracle will perform the following operations: 1: Cause checkpoint; 2: Data to READ-ONLY operation 3: you can still delete an object (such as table index) from the tablespace. This is not much used in practice. I will briefly discuss the method and will not conduct experiments. [Plain] experiment: create a tablespace: create tablespace wang datafile '/file name/'size 100 M extent management local uniform size 128 k; create user wang identified by wang default tablespace wang; then, log on to the user/user to create a table and the table is in the tablespace specified above. Create table wang (id integer, name char (10); insert into t values (0, 'wang'); commit; insert into t values (1, 'qiupeng '); the second line does not commit and then switches to the sys user. Then, execute alter tablespace wang read only. If tranmit does not have a commit statement, the statement is executed only after commit. Drop table wang; this command can be executed. The region of a province is a real file, and the provincial administrative organization is equivalent to tablespace, which is a logical structure. The logical structure of this tablespace is stored in the data field, you can perform the drop table operation to access the data dictionary. 10: make the following tablespaces of a tablespace offline: 1: SYSTEM tablespace 2: tablespace in the active undo segment 3: default temporary tablespace cannot be offline, the temporary tablespace can be offline if it is not default. [SQL] ALTER TABLESPACE mytemp OFFLINE; ALTER TABLESPACE mytemp ONLINE; experiment: [plain] create table tt (id integer, name char (10); insert into tt values (0, 'wang '); commit; select * from tt; insert into tt values (1, 'bbbbbb'); select * from tt; at this time, the second record does not have commit; this means that the undo segment has an active one. If the tablespace is offline, you cannot perform operations on the table in the tablespace. 11: increase or decrease the tablespace; (this is very practical) There are two ways: 1: The tablespace can be automatically increased when creating the tablespace, you can set autoextend on implementation. 2: You can add new data files to the tablespace to increase the tablespace. 1: Query table space usage: DBA_DATA_FILES this table records all data files. DBA_FREE_SPACE can know the number of remaining tablespaces. (If it is offline, it cannot be found .) Google Search: oracle tablespace usage SQL, which can query the usage of all tablespaces in the database. Note that this is an important SQL statement: [SQL] SELECT/* + RULE */df. tablespace_name "Tablespace", df. bytes/(1024*1024) "Size (MB)", SUM (fs. bytes)/(1024*1024) "Free (MB)", Nvl (Round (SUM (fs. bytes) * 100/df. bytes), 1) "% Free", Round (df. bytes-SUM (fs. bytes) * 100/df. bytes) "% Used" FROM dba_free_space fs, (SELECT tablespace_name, SUM (bytes) bytes F ROM dba_data_files group by tablespace_name) df WHERE fs. tablespace_name (+) = df. tablespace_name group by df. tablespace_name, df. bytes union all select/* + RULE */df. tablespace_name tspace, fs. bytes/(1024*1024), SUM (df. bytes_free)/(1024*1024), Nvl (Round (SUM (fs. bytes)-df. bytes_used) * 100/fs. bytes), 1), Round (SUM (fs. bytes)-df. bytes_free) * 100/fs. bytes) FROM dba_temp_files fs ,( SELECT tablespace_name, bytes_free, bytes_used FROM v $ temp_space_header group by tablespace_name, bytes_free, bytes_used) df WHERE fs. tablespace_name (+) = df. tablespace_name group by df. tablespace_name, fs. bytes, df. bytes_free, df. bytes_used order by 4 DESC; execution result on my database: [SQL] Tablespace Size (MB) Free (MB) % Free % Used ---------------------------- ---------- ------------ TS_MYDB 50 0 499.9375 100 0 MY_SPACE 500 499.9375 100 0 PENG 5 4.9375 99 1 MYTEMP 100 99 99 1 UNDOTBS1 365 335.6875 92 8 USER_TEMP 500 338 68 32 EXAMPLE 100 22.3125 22 78 USERS 9707.0625 462.8125 5 95 SYSAUX 821.25 41.9375 5 95 SYSTEM 710 1.125 0 100 TEMP 27 0 0 100 12: three methods to expand a table space: One: automatic expansion of the table space three methods: 1: create database when specifying datafile options autoextend on next 10 m maxsize 500 M can be added so that the database files can automatically grow. 2: create tablespace autoextend on next 10 m maxsize 500 M can also be used in create tablespace. 3: alter tablespace add datafile: (the modified tablespace must be implemented. my _) [SQL] SQL> alter TABLESPACE MY_SPACE add datafile 'd: \ APP \ TOPWQP \ ORADATA \ ORCL \ mytemp 001. DBF 'size 20 M autoextend on next 10 M maxsize 100 M; the tablespace has been changed. After adding the file, you can go to dba_data_files. Check whether the file is automatically added in the Table: [SQL] desc dba_data_files; col file_name format a20; select file_name, tablespace_name, autoextensible from dba_data_files; [SQL] SQL> select file_name, tablespace_name, autoextensible from dba_data_files; FILE_NAME TABLESPACE_NAME AUT values --- D: \ APP \ TOPWQP \ ORADAT USERS YES A \ ORCL \ USERS01.DBF D: \ APP \ TOPWQP \ ORADAT UNDOTBS1 yes a \ OR CL \ UNDOTBS01.DBF D: \ APP \ TOPWQP \ oradat sysaux yes a \ ORCL \ SYSAUX01.DBF D: \ APP \ TOPWQP \ oradat system yes a \ ORCL \ SYSTEM01.DBF D: \ APP \ TOPWQP \ oradat example yes a \ ORCL \ EXAMPLE01.DBF D: \ APP \ TOPWQP \ ORADAT MY_SPACE yes a \ ORCL \ MY_SPACE.DBF D: \ APP \ TOPWQP \ ORADAT TS_MYDB yes a \ ORCL \ TS_MYDB.DBF D: \ APP \ TOPWQP \ oradat peng no a \ ORCL \ pen1_1.dbf D: \ APP \ TOPWQP \ ORADAT MY_SPACE yes a \ ORCL \ MYTEMP001.DBF nine rows have been selected. You can see whether the newly added data file is automatically increasing autoextensible. If you do not want to add a new data file to expand the tablespace, you can use: the following command sets automatic expansion for the original data file to expand the tablespace (the same principle) [SQL] alter database datafile 'data file location + data file name' autoextend on next 10 M maxsize 100 M Method 2: redefines the size of the data file in the tablespace to manually increase the size of the data file. For example: alter database datafile ''RESIZE 200 M [SQL] This requires considering the size of the data file to be changed. Col file_name format a40; col tablespace_name format a15; select file_name, tablespace_name, byte from dba_data_files; note that the temporary tablespace is found in the table DBA_TEMP_FILES. First query the data file, then execute, modify the data file size, and then query again. If the modified size is smaller than the actual data file size, oracle will report an error. Method 3: add data files to the tablespace. In this way, the tablespace is added. Alter tablespace my_temp add datafile ''SIZE 200 M how to move DATAFILE: Method 1: Use the alter tablespace command: do two work before: 1: Make tablespace offline2: the target data file must exist. Command: first, let the file go offline: alter tablespace tablespace_name offline; then move the file and execute the following command: alter tablespace tablespace_name rename datafile ''to''; the pointer is directed again; then, launch the tablespace: alter tablespace tablespace_name online; Delete the tablespace: 1: SYSTEM tablespace cannot be deleted; 2: there is an active segments cannot be deleted. Command: drop tablesapce tablespace_name including contents and datafiles; three options are available. The preceding command can be executed, and the datafile in the corresponding tablespace is also killed.