Oracle maintains tablespaces and data files

Document directory

• Method 1: automatically expand the tablespace
• Method 2: redefine the data file size in the tablespace
• Method 3: add data files to the tablespace. In this way, the tablespace is added.

1: important reference Wiki

2: Oracle Doc tablespace reference

3: Reference from DBA-Oracle

26, 27, 28, 29

I. Concept of Oracle tablespace

A tablespace is a bridge between the physical disk (data file) of the database and the logical component. A tablespace is used to store oralce database objects such as 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.

Some concepts about ORACLE:

The following concepts are introduced:

1: Oracle block concept (Block concept)

An Oracle block is the smallest storage unit of an Oracle data block. The size of an Oracle database block is specified when data is created. It cannot be modified unless the Oracle Data is reconstructed, the database block size is usually 2 K, 4 K, 8 K, 16 Kor 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.

The database contains multiple tablespaces. One tablespace contains one or more datafiles. A table or index is currently in the same segment.

Extent is composed of consecutive ORACLE data blocks: logically continuous.

A segment can be stored in different datafiles. extent cannot span datafiles. Oralce data block contains one or more OS Blocks

2. Table space type: 1. A database must contain at least one tablespace, Which is system;

System tablespace; when creating a database, create a data dictionary containing System undo segment

Non-system tablespace

2: more scientifically divide the tablespace type:

1: Permanent 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 table spaces:

• System (the data
  Dictionary)
• Sysaux (optional database components)
• Temp (temporary tablespace, see tablespace types below)
• Undotbs1 (UNDO tablespace, see tablespace types below)
• Users (default users tablespace created)

3: Create tablespace Oracle Doc 11:

Simple syntax for creating a tablespace:

SQL> Create tablespace Peng datafile 'd: \ app \ topwqp \ oradata \ orcl \ pen#1.dbf' size 5 m; tablespace created.

Query tablespace Information

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 to know the data files in a tablespace:

SQL> Col file_name format a40sql> SQL> select file_name, tablespace_name from dba_data_files; file_name tablespace_name ------------------ D: \ app \ topwqp \ oradata \ orcl \ users01.dbf usersd: \ app \ topwqp \ oradata \ orcl \ export undotbs1d: \ app \ topwqp \ oradata \ orcl \ sysaux01.dbf sysauxd: \ app \ topwqp \ oradata \ orcl \ system01.dbf release EMD: \ app \ topwqp \ oradata \ orcl \ example01.dbf Exampled: \ app \ topwqp \ oradata \ orcl \ my_space.dbf my_spaced: \ app \ topwqp \ oradata \ orcl \ ts_mydb.dbf ts_mydbd: \ 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: Manage tablespace by yourself
2: There is a bitmap in the data dictionary for tablespace management (a bitmap corresponds to an extent, meaning 1 occupies 0 and is not occupied)

Each data file header Has A Bitmap bitmap to indicate 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 the system tablespace managed by dictionary wants to become locally managed due to historical reasons, run the following command:

DBMS_SPACE_ADMIN.TABLESPACE_MIGRANTE_TO_LOCAL('SYSTEM');

Note: preparations before this command: 1: Full backup database 2: Ensure that the temporary tablespace is not a system

The self-managed tablespace has two tables to manage the data:
? Do each tablespace port have its own debit and credit tables?
Debit table: records all allocated extent
Credit table: records all idle extent
When applying for space, Oracle queries these tables and allocates space.
When Inserting Data, 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:

It is mainly used to store undo segment, that is, when a table or tablespace is modified, undo is used to back up data first. Undo must be described in a specific chapter after loacally mananged;

5: Temporary tablespaces:

It is used for temporary Oracle storage. For example, if you do some large sorting, it will definitely not work in the memory and 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 in two ways:
1: when creating a database using create database.

Default temporary tablespace temp tempfile 'path/file name 'size 400 m
2: You can use alter database.

Alter database default temporyary tablespace default_temp2; (create a tablespace first)

6: query the default temporary tablespace

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> select tablespace_name, contents, extent_management from dba_tablespaces; tablespace_name contents temporary ------------ system permanent temporary Permanent temporary undo localtemp temporary localusers permanent localexample permanent localuser_temp temporary Permanent localpeng permanent local has selected 10 rows.

8: Modify the default tablespace

Add a new tablespace as follows:

SQL> create temporary tablespace mytemp tempfile 'd: \ app \ topwqp \ oradata \ orcl \ mytemp. 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 it:

SQL> select  property_name,property_value  from  database_properties;PROPERTY_NAME        PROPERTY_VALUE-------------------- --------------------DICT.BASE            2DEFAULT_TEMP_TABLESP MYTEMPACEDEFAULT_PERMANENT_TA USERSBLESPACE

A database can have multiple temporary tablespaces, but only one default tablespace.

9: tablespace read-only settings:

Alter tablespace mytemp read only;

Execute this statement: Oracle will perform the following operations:
1: Cause checkpoint;
2: convert data into read-only operations
3: You can still delete an object (such as table index) from the tablespace)

This is not much used in practice. I will not do the experiment if I simply talk about the method.

Experiment: Create a tablespace: Create tablespace Wang datafile '/file name/'size 100 mextent 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 a tablespace offline

The following table spaces cannot be offline:
1: System tablespace
2: The tablespace in the active undo segment
3: Default temporary tablespaces cannot be offline. Temporary tablespaces can be offline if they are not default.

ALTER TABLESPACE mytemp  OFFLINE;ALTER TABLESPACE mytemp  ONLINE;

Lab:

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 a 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 of practical significance)

There are two methods:
1: You can set autoextend on when creating a tablespace.

2: You can add new data files to the tablespace to increase the tablespace.

1: Query table space usage:
The dba_data_files 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
This SQL statement can be used to query the usage of all tablespaces in the database;
Note that this is an important SQL statement:

 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          FROM dba_data_files         GROUP BY tablespace_name) df WHERE fs.tablespace_name (+)  = df.tablespace_name GROUP BY df.tablespace_name,df.bytesUNION ALLSELECT /* + 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 results on my database:

Tablespace                      Size (MB)  Free (MB)     % Free     % Used------------------------------ ---------- ---------- ---------- ----------TS_MYDB                               500   499.9375        100          0MY_SPACE                              500   499.9375        100          0PENG                                    5     4.9375         99          1MYTEMP                                100         99         99          1UNDOTBS1                              365   335.6875         92          8USER_TEMP                             500        338         68         32EXAMPLE                               100    22.3125         22         78USERS                           9707.0625   462.8125          5         95SYSAUX                             821.25    41.9375          5         95SYSTEM                                710      1.125          0        100TEMP                                   27          0          0        100

12: three methods to expand a table space method 1: automatically expand a table space

Three methods:
1: You can add the autoextend on next 10 m maxsize 500 m option when you create database to specify datafile so that the database file 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 following uses the third method for the experiment: (the modified tablespace must be implemented. This is my _)

SQL> alter tablespace my_space add datafile 'd: \ app \ topwqp \ oradata \ orcl \ mytemp001.dbf 'size 20 m autoextend on next 10 m maxsize 100 m; tablespace changed.

After adding the file, you can go to dba_data_files. Check whether the file is automatically added in the table:

 desc dba_data_files;    col file_name format a20;    select file_name , tablespace_name,autoextensible from  dba_data_files;

SQL> select file_name, tablespace_name, autoextensible from dba_data_files; file_name tablespace_name aut values ---------------- --- D: \ app \ topwqp \ oradat users yesa \ orcl \ users01.dbfd: \ app \ topwqp \ oradat undotbs1 yesa \ orcl \ drivers: \ app \ topwqp \ oradat sysaux yesa \ orcl \ drivers: \ app \ topwqp \ oradat system yesa \ orcl \ system01.dbfd: \ app \ topwqp \ oradat example yesa \ orcl \ example01.dbfd : \ App \ topwqp \ oradat my_space yesa \ orcl \ drivers: \ app \ topwqp \ oradat ts_mydb yesa \ orcl \ drivers: \ app \ topwqp \ oradat Peng Noa \ orcl \ pen1_1.dbfd: \ app \ topwqp \ oradat my_space yesa \ 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 new data files to expand the tablespace, you can use the following command to set automatic expansion for the original data file to expand the tablespace (in the same way)

Alter database datafile 'data file location + file name 'autoextend on next 10 m maxsize 100 m

Method 2: redefine the data file size in the tablespace

Manually make the data file bigger and smaller: make the existing data file bigger and smaller,

Example:
Alter database datafile ''resize 200 m

Consider 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 dba_temp_files table.

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:
Run the alter tablespace command:
Before doing this, you must do two jobs:
1: Make tablespace offline
2: The target data file must exist.
Command:
First, make the file offline:
Alter tablespace tablespace_name offline;
Then move the file
Run 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 tablespace:
1: The system tablespace cannot be deleted.
2: 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.