Oracle tablespace and data files

Learning Objectives

1. Describe the logical structure of the Oracle database
2. Create a tablespace
3. Change the tablespace size
4. Allocate space for temporary segments
5. Change the tablespace status
6. Change the storage settings of the tablespace.

First, let's take a look at the oracle storage structure:

1. What are the logical structures of oracle databases?

1) tablespace
A. An Oracle database is logically divided into smaller logical regions called tablespaces.
B. A tablespace belongs to only one database.
C. Each tablespace is composed of one or more system files, called datafile.
D. a tablespace can contain one or more segments.
E. When the database is running, the tablespace can be in the online status.
F. In addition to SYSTEM tablespaces or tablespaces with active rollback segments, other tablespaces can be set to the offline status.
G. The tablespace can be switched between read-write and read-only statuses.

2) Section
A. a segment is the space allocated for the specified logical storage structure in a tablespace. For example, all the storage allocated to a table is a segment.
B. A segment can belong to only one tablespace and can span multiple data files.
C. A segment consists of one or more zones.

3) Zone
A. As the segment grows, additional segments will be allocated
B. DBA can manually add a partition for the segment
C. A zone is a continuous set of oracle blocks.
D. A zone can only exist in one data file

4) Blocks
A. The block is the smallest storage unit of the oracle database, and all the data is stored in the data block.
B. An oracle data block is equivalent to one or more operating system blocks allocated from existing data files.
C. The size of a standard data block is specified by the initialization parameter DB_BLOCK_SIZE when the database is created.
D. To avoid unnecessary I/O, the data block size should be several times the size of the operating system block
E. The maximum data block size depends on the operating system.

2. What is the difference between system tablespace and non-system tablespace?

1) system tablespace system tablespaces:
A. The database is created with the creation of the database.
B. All databases must exist.
C. The system tablespace contains data dictionaries and storage program units.
E. Include system rollback segments
F. It is best not to include user data

2) non-system tablespaces:
A. Separate rollback segments, temporary segments, application data segments, and application index segments.
B. Separate data based on backup requirements
C. Separate dynamic and static data
E. Control the number of user object space allocations
F. Convenient Database Management

3. How to Create a tablespace?
Use the create tablespace statement. The following is an official example:

Create tablespace userdata
DATAFILE '/u01/oradata/userdata01.dbf' SIZE 100 M
Autoextend on next 5 m maxsize 200 M;

Create tablespace tablespace
[DATAFILE clause]
[Minimum extent integer [K | M]
[BLOCKSIZE integer [K]
[LOGGING | NOLOGGING]
[DEFAULT storage_clause]
[ONLINE | OFFLINE]
[PERMANENT | TEMPORARY]

Minimum extent specifies the maximum cell size, using K (Gigabit) or M (MB)
The default value of LOGGING. It specifies that all tables, indexes, and partitions in the tablespace are written to redo.
NOLOGGING is opposite to LOGGING
Whether the ONLINE and OFFLINE tablespaces are available immediately after they are created
PERMANENT specifies the tablespace to retain PERMANENT objects.
TEMPORARY the specified tablespace can only be used to retain TEMPORARY objects
AUTOEXTEND enable automatic expansion of data files

4. How do I manage tablespaces?
1) Local tablespace Management
A. Use bitmap (maintained in each data file) to record the idle Zone
B. Each bit corresponds to a data block.
C. bit value indicates idle and used
Benefits: reduces the contention for data dictionary tables, does not involve undo for space allocation and recovery, and does not require merging (to reduce fragments );

Example:
Create tablespace userdata
DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500 M
Extent management local uniform size 256 K;

[EXTENT MANAGEMENT
[DICTIONARY | LOCAL
[AUTOALLOCATE | UNIFORM [SIZE integer [K | M]

LOCAL and DICTIONARY are not explained.
AUTOALLOCATE: the specified tablespace is managed by the system. You cannot specify the partition size.
UNIFORM specifies that the tablespace is managed by the same partition size. Use K or M to specify the size.

2) Manage tablespaces using data dictionaries
A. Default Management Method
B. the idle zone is recorded in the data dictionary table.

Create tablespace userdata
DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500 M
EXTENT MANAGEMENT DICTIONARY
Default storage (initial 1 m next 1 M );

5. undo tablespace
1) The undo tablespace can only be used to store rollback segments.
2) Only DATAFILE and extent management statements can be used to create undo tablespaces.
Create undo tablespace undo1
DATAFILE '/u01/oradata/undo101.dbf' SIZE 40 M;

6. temporary tablespace
1) used for sorting SORT_AREA_SIZE (used to set the size of the sorting area)
2) cannot contain any permanent object
3) Local Management is recommended.
Create temporary tablespace temp
TEMPFILE '/u01/oradata/temp01.dbf' SIZE 500 M
Extent management local uniform size 10 M;

4) Temporary tablespace temporary data files in local management tempfiles:
A. Temporary files are always in NOLOGGING mode.
B. You cannot set the temporary file to read-only.
C. You cannot rename a temporary file.
D. You cannot use the alter database command to add temporary files.
E. Temporary files are required for read-only databases
F. Medium recovery does not recover temporary files
G. do not generate any temporary file information when backing up the control file
H. When creating a control file, you cannot specify any information about the temporary file.

7. default temporary tablespace
1) Avoid using SYSTEM tablespace to store temporary data
2) You can use the create database and alter database statements to CREATE
3) When you CREATE a table using the create databse command, the temporary tablespace is managed locally by default.

Restrictions on the default temporary tablespace:
A. The existing default temporary tablespace cannot be deleted before a new default temporary tablespace is specified.
B. You cannot change the default temporary tablespace to the permanent type.
C. You cannot change the default temporary tablespace to the offline status.
Note: The SYSTEM tablespace, active rollback segments, and default temporary tablespace cannot be set to offline.

1 2 Next Page