About Oracle (4)

Post:
Oracle tablespace and Maintenance
Basic Concepts
The Oracle database is divided into logical regions called tablespaces-forming the logical structure of the Oracle database. I
Oracle databases can have one or more tablespaces, while one tablespace corresponds to one or more physical databases.
File. Tablespace is the minimum unit for Oracle database recovery. It contains many database entities, such as tables, views, and indexes.
Cluster, rollback segment, and temporary segment.
Each Oracle database has a system tablespace, which is automatically created when the database is created. System tablespace
Always keep online, because it contains the basic information required for database operation (the data word about the entire database
Download, online help mechanism, all rollback segments, temporary and automatic segments, all user database entities, and other Oracle
Software Product requirements ).
Oracle databases for a small application usually only contain system tablespaces.
E database uses multiple tablespaces to facilitate database use.
Function
Table space helps DBA users to do the following:
1. Determines the space allocation of database entities;
2. Set the Space share of database users;
3. Control the availability of some data in the database;
4. Distribution of data between different devices to improve performance;
5. Back up and restore data.
When a user creates a database entity, the user must have the corresponding power in the given tablespace.
To manipulate the data in an Oracle database, you should:
1. The resource privilege in one or more tablespaces is granted;
2. The default tablespace is specified;
3. The storage space used by the specified tablespace is allocated;
4. The tablespace of the default temporary segment is specified.
Maintenance
Table space maintenance is implemented by the Oracle Database System Administrator DBA using the SQL * Plus statement.
The file name in the creation and modification cannot contain a path. Therefore, DBA must operate in the Oracle/DBS directory.
1. Create a New tablespace
Syntax format: Create tablespace name
Datafile File Identifier [, file identifier]...
[Default storage (storage configuration parameters)]
[Online/offline];
Where: File Identifier = 'filename '[size integer [k/m] [reuse]
2. Modify the tablespace Configuration
Syntax format: Alter tablespce tablespace name
(Add datafile file File Identifier [, file identifier]...
/Rename datafile 'file name' [, 'file name']...
To 'filename '[, 'filename']...
/Default storage (storage configuration parameters)
/Online/offline [Normal/immediate]
/(Begin/end) backup );
3. Cancel tablespace
Syntax format: Drop tablespace name [including contents];
4. Check table space usage
(1) Check the current user space allocation
Select tablespace_name, sum (extents), sum (blocks), sum (bytes)
From user_segments
Group by tablespace_name
(2) A. Check the space allocation of each user
Select owner, tablespace _;
(2) B. Check the space allocation of each user
Select owner, tablespace_name, sum (extents), sum (blocks), sum (bytes)
From dba_segments
Group by owner, tablespace_name;
(3) Check the physical space usage of the current user database
Select tablespace_name, segment_name, segment_type,
Count (extent_id), sum (blocks), sum (bytes)
From user_extents
Group by tablespace_name, segment_name, segment_type;
(4) Check the space usage of each user
Select owner, tablespace_name, count (extent_id), sum (blocks ),
Sum (bytes) from user_extents
Group by owner, tablespace_name;
(5) Check database space usage
Select tablespace_name, count (extent_id), sum (blocks), sum (bytes)
From user_extents
Group by tablespace_name;
(6) Check the free space of the current user
Select tablespace_name, count (block_id), sum (blocks), sum (bytes)
From user_free_space
Group by tablespace_name;
(7) Check the free space of the database
Select tablespace_name, count (block_id), sum (blocks), sum (bytes)
From dba_free_space
Group by tablespace_name;
Iv. Conclusion
Table space is the main object for Oracle Database System Maintenance. Through this article, we can learn more about its basic concepts and functions.
, And master their daily maintenance knowledge