Oracle 11g Learning 3--table Space Operations

I. Overview of table Spaces

The tablespace is the largest logical storage structure in Oracle and corresponds to the data files in the operating system;

Basic tablespace: A permanent tablespace used by the user to store the user's persistent data
Temporary tablespace: Primarily used to store temporary data generated during sorting or summarization;
Large file tablespace: for storing large data (such as lobs)
Non-standard block table space: Used to create a table space with different sizes of data blocks in a DB instance;
Undo Tablespace: The revocation data used to store transactions, used when recovering data.

Second, create TABLE space

Grammar:
CREATE [Temporary | UNDO] Tablespace tablespace_name
[DataFile | Tempfile ' file_name ' size size k| M [Reuse]]
[Autoextend off| On
[NEXT number k| M MAXSIZE Nulimited|number k| M
][,...]
[Mininum EXTENT number k| M
[BLOCKSIZE number K]
[online| OFFLINE]
[Logging| Nologging]
[Force LOGGING]
[DEFAULT STORAGE STORAGE]
[compress| Nocompress]
[permanent| Temporary]
[EXTENT MANAGEMENT DICTIONARY | LOCAL
[autoallocate| UNIFORM SIZE Number k| M]]
[SEGMENT SPACE MANAGEMENT auto| MANUAL];

Description
Temporary: Specifies that the tablespace is a temporary table space
Undo: Specify table space as undo Tablespace
If you do not specify temporary or undo: Indicates that the specified base table space
Tablespace_name: Name of table space
DataFile: Specifies a data file for a tablespace when it is specified as a basic table space
Tempfile: Specifies a temporary file for a tablespace when it is specified as a temporary table space
Reuse: Identity file already exists

Settings for the file
Autoextend: Identifies whether the file is automatically extended
Next: Identify the size of the next extension of the file
MAXSIZE: Identifies the maximum capacity of a file, UNLIMITED: Unlimited identity size
MINIMUM EXTENT: The minimum capacity that identifies the extents that can be allocated
BLOCKSIZE: Standard block size (only for standard table spaces)
ONLINE: Identifies the created tablespace immediately available OFFLINE: cannot be used immediately
LOGGING: Generate Logging entry nologging: Do not generate logging entries
Force LOGGING: Mandatory Logging entry
Default STORAGE: Storage object for database object defaults
COMPRESS: Compressed data nocompress: No compression
PERMANENT: Persist data Object Temporary: temporarily save data object
EXTENT MANAGEMENT DICTIONARY: Data dictionary management method for data dictionary management
Local: Data dictionary is managed in a localized way
When Autoallocate:local is managed, the size of the panel is automatically assigned
UNIFORM size:local Management mode, the size of the panel is evenly distributed, you can specify the size
SEGMENT space MANAGEMENT: How to manage the middle of a table space
Auto: Automatic Management
MANUAL: Manual Management

For example:
CREATE tablespace MySpace
DataFile ' E:\app\myspace\myspace.dbf '

SIZE 10M autoextend on NEXT 5M MAXSIZE 100M;

Third, table space State settings
The state properties of the tablespace are: online, offline (OFFLINE), read only, and read Write 4.
You can manage the use of tablespaces by setting the state properties of the tablespace.


//1, online
Allow access to data in a tablespace when the status of the table space is online

For example:
Modify the table space status to online
ALTER tablespace tablespace_name ONLINE;

//2, Offline
The offline state, which does not allow access to data in the tablespace. You can perform an offline backup of the tablespace, upgrade and maintain the application, and so on.

For example:
To modify the Tablespace state to an offline state
ALTER tablespace tablespace_name OFFLINE parameter;

Description
Parameter represents the parameters that can be used to switch the tablespace to the offline state:
Normal: Switch Normally, default mode
Temporary: Temporary, Oracle does not check if data files are available while checking
IMMEDIATE: Immediate mode, Oracle does not perform checkpoints
For RECOVER: Recovery method, often used for time-based recovery of a database

//3, read-only
Only data can be read and no updates or deletions are made to ensure data security in the Tablespace

For example:
Set the tablespace to read only
ALTER tablespace tablespace_name READ only;

Description
To set the tablespace to read only considerations before:
--table space must be in online state
--The tablespace cannot contain a fallback segment for any transaction
--tablespace cannot be in the online database backup period

//4, read/write
You can have normal access to a tablespace

For example:
Modify table space to read write state
ALTER tablespace tablespace_name READ WRITE;

Note: The status of the modified tablespace is read WRITE, and you need to ensure that the tablespace is in the online state.


To view the status of a table space
Select Tablespace_name, status from Dba_tablespaces;

Iv. Modifying table spaces

(1), modify the size of the table space

Increase the size of the table space, when implemented, you can increase the size of the data file, you can also add a new data file to increase the table space.

1**, modifying the size of a table space
The size of the base table space equals the sum of the size of all the data files associated with it. When the tablespace size is insufficient, you need to increase the size of the table space, specifically, you can increase the size of the data file, you can also add a new data ask price to increase the table space.


To modify the size of a data file in a table space
Grammar:
ALTER DATABASE datafile file_name RESIZE newsize k| M

Description
file_name: The name and path of the data file
RESIZE newsize: The size of the modified data file is newsize.

Example:
ALTER DATABASE datafile ' E:\APP\MYSPACMYSPACE. DBF ' RESIZE 30M;

2**, adding table space data files
Grammar:
ALTER tablespace Tablespace_name
ADD datafile file_name SIZE number k| M
[Autoextend off| On
[NEXT number k| M MAXSIZE Unlimited|number k| M
][,...];

Example:
ALTER tablespace myspace ADD datafile ' E:\app\myspace\myspace02.dbf ' SIZE 100M;

(2), renaming table spaces

Modifying the name of a tablespace does not affect the data in the tablespace, but it cannot modify the name of the system table space Systems and Sysaux.
Grammar:
ALTER tablespace tablespace_name RENAME to New_tablespace_name;
Description
Note: If the tablespace has a status of offline, you cannot rename the table space.

(3), delete table space

Grammar:
DROP tablespace Tablespace_name
[including CONTENTS [and Datafiles]]

Description
Including CONTENTS:
Indicates that all database objects in the tablespace are deleted while the table space is being dropped. You must use this option if there are database objects in the tablespace.

and datafiles
Represents the deletion of a tablespace while deleting the data file for the table space. If you do not use this option, deleting a tablespace actually removes information about the tablespace from the data dictionary and the control file, not the data file that corresponds to the tablespace in the operating system.

For example:
DROP tablespace Userspace
including CONTENTS and datafiles;

V. Modifying data files in Tablespace

(1), modifying the automatic extensibility of tablespace data Files
Grammar:
ALTER DATABASE datafile file_name autoextend off| On
[NEXT number k| M MAXSIZE Unlimited|number k| M

For example:
ALTER DATABASE datafile ' E:\APP\MYSPACE\MYSPACE02. DBF '
Autoextend on
NEXT 5M MAXSIZE 50M;

(2), modify the status of the data file in the Tablespace
There are 3 main states of data files: ONLINE, offline, and offline DROP.
Online: Indicates the presence of a data file that can be used
OFFLINE: Indicates an offline state in which the data file is not available for use in the case where the database is running in archive mode
OFFLINE drop: The data file is deleted, as is the case with OFFLINE for setting the data file to be unavailable, but for the database to run in non-archive mode.

Note: When the data file is set to the offline state, the state of the tablespace is not affected, but when the tablespace is set to the offline state, all data files that are part of the tablespace are set to the offline state.

Grammar:
ALTER DATABASE
datafile file_name online| offline| OFFLINE DROP

Example:
ALTER DATABASE datafile ' E:\APP\MYSPACE\MYSPACE02. DBF ' OFFLINE;

(3), moving data files in table space
The size of the data file is limited by the size of the disk space on which the data file is located, and you need to move the data file to a new disk when the disk space is insufficient.

Method:
The first step: set the corresponding tablespace to the offline state
The second step: Enter the disk, move the data file to the new location, you can also modify the file name
Step Three: Rename the data file
For example: ALTER tablespace MySpace
RENAME datafile ' E:\APP\MYSPACE\MYSPACE02. DBF '
To ' D:\ORACLEFILE\MYSPACE03. DBF '
Fourth step: Set the corresponding table space to online status

vi. temporary table space

Create and modify a temporary table space
A temporary tablespace is a disk space that is used primarily to store temporary data that is generated when a user performs a sort or rollup of statements such as an order by. By default, all users use temp as the default temporary table space. However, it is also allowed to use other temporary tablespace as the default temporary table space, which needs to be specified when the user is created.
The temporary keyword is required to create a temporary tablespace, and the temporary table space corresponds to a temp file, specified by the Tempfile keyword, which means that the data file is no longer used in the temporary table space and the temporary file is used.

Example:
CREATE Temporary tablespace mytemp
Tempfile ' E:\app\myspace\mytemp.dbf '
SIZE 5M autoextend on NEXT 2M MAXSIZE 20M;

Modifying a temporary table space
Because the temporary file does not store permanent data, only the temporary data generated during operations such as sorting are stored, and the data stored in the temporary file is deleted by the system after the user's operation, so there is no need to adjust the temporary table space, but when the concurrent users are very much and the operation is more complex, There may be a shortage of temporary table space. At this point, the database administrator can add temporary files to increase the temporary table space.
If you need to add temporary files, you can use the Add tempfile clause.
If you need to modify the size of the temporary file, you can use the Resize keyword.
You can also modify the temporary file's status to offline or online.

Temporary table space groups
Refers to a collection of multiple temporal table spaces, for collection operations
In Oracle 11g, users can create temporary tablespace groups that can contain one or more temporary table spaces in a temporary table space group.
The primary features of the temporary Tablespace group are as follows:
* * A temporary tablespace group must consist of at least one temporary tablespace, and there is no explicit limit to the maximum number.
* * If you delete all members of a temporary tablespace group, the group is also automatically deleted.
* * The name of a temporary tablespace cannot be the same as the name of a temporary table space group.
* * You can use the name of a temporary tablespace group instead of the actual temporary tablespace name when assigning a temporary tablespace to a user, or you can use the name of a temporary tablespace group when assigning a default temporary tablespace to a database.

Advantages of using temporary table space groups:
* * Because SQL queries can be sorted concurrently using several temporary table spaces, SQL queries rarely result in a sorting space that is out of order, avoiding the problem of disk sorting caused by insufficient temporary tablespace.
* * Multiple default temporary table spaces can be specified at the database level.
* * Parallel servers with parallel operations will effectively utilize multiple temporal table spaces
* * A user can use more than one temporary table space at a time in different sessions.

To manipulate a temporary table space group
1. Create a temporary table space group
You only need to use the Tablespace group statement to make a group for a temporary tablespace when you create it.

Example:
CREATE Temporary tablespace tempgroup
Tempfile ' E:\app\myspace\tempgroup01.dbf ' SIZE 5M
Tablespace GROUP Group01;

2. Viewing temporary tablespace Group information
Data dictionary Dba_tablespace_groups

Example:
SELECT * from Dba_tablespace_groups;

3. Move temporary table space
Using the ALTER TABLESPACE statement

Example:
ALTER tablespace tempgroup tablespace GROUP group02;

4. Delete temporary table space groups

vii. Large File table space

Large File Table space
The large file tablespace is a new type of tablespace introduced by Oracle 10g, primarily to address the problem of insufficient storage file size. Unlike normal table spaces, a large file table space can only correspond to a single data file or temporary file, while the normal table space may correspond to up to 1022 data files or temporary files.
Although a large file table space can only correspond to one data file or temporary file, its corresponding file can be up to 4G blocks in size. The file size corresponding to the normal table space can be up to 4M blocks.

Create a large file table space
Use the Bigfile keyword, and you can only create one data file or temporary file for it
Ordinary table spaces are typically represented by the Smallfile keyword, which is omitted by default.
The Data dictionary database_properties lets you know the default tablespace type for the current database.

For example:
CREATE bigfile tablespace Mybigspace
DataFile ' E:\app\myspace\bigspace.dbf '
SIZE 10M;

To see if a tablespace is a large file table space
Select Tablespace_name, bigfile from Dba_tablespaces;

View the default tablespace type for the current database
Select Property_name, Property_value, description
From Database_properties
where property_name = ' default_tbs_type ';

viii. non-standard data block table space
Non-standard chunk table space
Non-standard (block) table space, which is a tablespace whose data block size is not based on the size of a standard chunk.
When you create a tablespace, you can use the BLOCKSIZE clause, which is used to set the chunk size in the tablespace, and if you do not specify it, the default chunk size is determined by the system initialization parameter db_block_size. The data block size specified by the Db_block_size parameter is the standard chunk size, and the value of the parameter cannot be modified after the database is created.

Creating non-standard block table spaces
Oracle 11g allows users to create non-standard block table spaces, using the BLOCKSIZE clause to specify the size of the data block in the tablespace, but must have the value of the data buffer parameter Db_nk_cache_size match the value of the BlockSize parameter, as follows:
BLOCKSIZE db_nk_cache_size
2KB db_2k_cache_size
4KB db_4k_cache_size
8KB db_8k_cache_size
16KB db_16k_cache_size
32KB db_32k_cache_size

To view the size of a table space's data block
Select Tablespace_name, block_size from Dba_tablespaces;

Create an undo table space
Example:
First step, modify the Db_nk_cache_size parameter
ALTER SYSTEM SET db_16k_cache_size = 16M;

Second step, create non-standard table spaces
CREATE tablespace Blockspace
DataFile ' E:\app\myspace\blockspace.dbf ' SIZE 10M
Autoextend on NEXT 5M
BLOCKSIZE 16K;

Note: The value of the blocksize corresponds to the value of the Db_nk_cache_size parameter.

ix. cancellation of table space
Undo Table Space
To enable operations such as data fallback, recovery, transaction rollback, and revocation, the Oracle database provides a portion of the storage space, specifically saving the undo record, and saving the modified data to that space, so this part of the space is called the Undo table space. Multiple undo table spaces can exist in a database, but only one undo tablespace is available at any given time.

Create an undo table space
For example:
Create undo Tablespace Undotbs
DataFile ' e:\app\myspace\undo01.dbf ' size 20m
Autoextend on;

To modify the data file for the undo table space
Add a new data file
Alter Tablespace UNDOTBS
Add datafile ' e:\app\muspace\undo02.dbf ' size 10m

Modify the data file size of the undo table space
ALTER DATABASE datafile ' E:\APP\MYSPACE\UNDO02.DBF ' resize 15m;

Set the status of the data file for the undo tablespace to online or offline
Alter tablespace UNDOTBS offline;

Action Undo Table Space
There can be more than one undo table space in a database, but only one undo table space is used at a time in a database. By default, the database uses the UNDOTBS1 undo table space that is automatically created by the system. If you switch the Undo table space used by the database to a different tablespace, use the ALTER SYSTEM statement to modify the value of the parameter undo_tablespace. After you toggle the Undo table space, the revocation data for new transactions in the database is saved in the new undo table space.

Toggle Table Space
alter system set Undo_tablespace = UNDOETBS02;

In automatic undo records management, you can specify how long revocation information needs to be retained after submission to prevent snapshot too old errors during long queries.
In the automatic revocation management mode, the DBA uses the undo_restention parameter to specify the table Liu time to revoke the record. Since the Undo_retention parameter is a dynamic parameter, in the run of an Oracle instance, the ALTER SYSTEM SET undo_retention statement can be used to modify the time the revocation record is retained.
The Undo record retention time is in seconds, and the default value is 900, which is 15 minutes.
For example, change the retention time of the revocation record to 10 minutes, as follows:
alter system set undo_retention = 600;
Show parameter undo;

Delete an undo table space
Before removing the undo tablespace, you need to ensure that the undo tablespace is not the tablespace that the system is using.

For example:
Drop tablespace undotbs02 including contents and datafiles;

To set the default table space
In Oracle, the default persistent tablespace for users is system, and the default temp table space is temp. If all users use the default tablespace, there is no doubt that the system and temp table space will be more competitive.
Oracle allows a custom tablespace to be used as the default persistent tablespace, using a custom temporary tablespace as the default temporary tablespace.

Grammar:
ALTER DATABASE DEFAULT [temporary] tablespace tablespace_name;

Description
Using the TEMPORARY keyword means setting the default temporary tablespace, or setting the default persistent tablespace if the keyword is not used.

Querying the default table space
Select Default_tablespace from User_users;

Select Property_name, Property_value
From Database_properties
where Property_name in (' Default_permanent_tablespace ', ' default_temp_tablespace ');

Create a log file
Create a log file group
Grammar:
ALTER datafile database_name
ADD LOGFILE [GROUP Group_number]
(file_name [, file_name[,...]])
[Size size] [Reuse];

Description
*group group_number: Specify the group number for the log file group
*file_name: Create log file members for this group
*size Number: Specifies the size of the log file members
*reuse: If you create a log file member that already exists, you can overwrite the existing file with the Reuse keyword. However, the file cannot already belong to another log file group. Otherwise, it cannot be replaced.

Create a log file
It is generally a point to add log members to the log file group and you need to use the Alter DATABASE ... ADD LOGFILE Member Statement

For example:
ALTER DATABASE add LogFile Member
' F:\oraclefile\logfile\redo03.log '
to group 4;

Viewing log file information
Select group#, member from V$logfile;

Create a log file
ALTER DATABASE add logfile Group 4
(
' E:\app\myspace\redo01.log ',
' E:\app\myspace\redo02.log '
) SIZE 10M;

Oracle 11g Learning 3--table Space Operations