Oracle entry-1 tablespace

 

1 From: http://soft.chinabyte.com/database/430/11385430.shtml

2 from: http://qxh.javaeye.com/blog/609437

 

1. in Oracle, table space is the basic method for data management. All user objects must be stored in the table space, that is, users have the right to use the space before they can create users.

Object. Otherwise, it is not enough to create objects, because you want to create objects, such as tables and indexes, and there is no place to store them. Oracle will prompt that there is no storage quota.

Therefore, before creating an object, you must first allocate a bucket.

To allocate storage space, create a tablespace:

Example of creating a tablespace:

Create tablespace "sample"

Logging

Datafile 'd: \ oracle \ oradata \ ora92 \ luntan. ora size 5 m

Extent management local segment space management auto

The preceding statement consists of the following parts:

1. Create tablespace "sample" to create a tablespace named "sample.

To name a table space, follow the naming rules of oracle.

There are three types of tablespaces that can be created in Oracle:

(1) Temporary: Temporary tablespace for storing temporary data;

The syntax for creating a temporary tablespace is as follows:

Create temporary tablespace "sample "......

(2) undo: Restore the tablespace. used to store the redo log file.

The syntax for creating a restored tablespace is as follows:

Create undo tablespace "sample "......

(3) User tablespace: it is the most important and used to store user data table space.

You can directly write it as: Create tablespace "sample"

Temporary and undo tablespaces are special tablespaces managed by Oracle. They are only used to store system-related data.

Second: Logging

There are two options: nologging and logging,

Nologging: when creating a tablespace, no redo logs are created.

Logging is the opposite of nologging, that is, the redo log is generated when the tablespace is created.

When nologging is used, the advantage is that logs are not generated during creation, so that the tablespace is created quickly, but logs are not available. After the data is lost, it cannot be recovered.

When creating a tablespace, there is no data. Generally, after creating a tablespace and importing data, you need to back up the data. Therefore, you do not need to create a tablespace.

Create logs. Therefore, when creating a tablespace, select nologging to accelerate the creation of the tablespace.

Third, datafile is used to specify the location and size of the data file.

For example, datafile 'd: \ oracle \ oradata \ ora92 \ luntan. ora size 5 m

The storage location of the file is D: \ oracle \ oradata \ ora92 \ luntan. ora, and the file size is 5 MB.

If multiple files exist, separate them with commas:

Datafile 'd: \ oracle \ oradata \ ora92 \ luntan. ora size 5 m,

'D: \ oracle \ oradata \ ora92 \ dd. ora 'size 5 m

However, the size of each file must be specified. The unit is subject to the specified unit, for example, 5 m or 500 K.

Specific files can be stored on different media, such as disk arrays, based on different needs to reduce Io competition.

When the file name is specified, it must be an absolute address and cannot be a relative address.

Fourth: extent management Local Storage Area Management Method

Before Oracle 8i, there are two options: one is to manage the dictionary and the other is local. From 9i, it can only be local.

Because local management has many advantages.

Dictionary management: each storage unit in the data file is used as a record. Therefore, a large number of management operations are generated during DM operations.

The Delete and update operations on tables. When a large amount of data is managed, many DM operations will be generated, which seriously affects the performance. At the same time, operations on table data for a long time will produce

A lot of disk fragments, which is why disk sorting is required.

Local: the disk is managed in binary mode with high efficiency. The disk can be used to the maximum extent. At the same time, the system can automatically track records near idle space.

To avoid merging idle zones.

Fifth: Segment space management

Disk extension management method:

Segment space management: the time zone size of this option is automatically determined by the system. Since oracle can determine the optimal size of each region, the zone size is

Changed.

Uniform segment space management: Specifies the partition size. You can also use the default value (1 MB ).

Sixth: section space management:

Auto: it can only be used in locally managed tablespaces. When local is used to manage tablespaces, after the free space in the data block is increased or decreased, its new State will be in the bitmap

. Bitmap makes Oracle's management of free space more automated, and provides better performance for management of free space, but does not

Can be automatically managed.

Manual: It is currently not used, mainly for backward compatibility.

7. Specify the block size. You can specify the size of the tablespace data block.

Example:

1 create tablespace "sample"

2 Logging

3 datafile 'd: \ oracle \ oradata \ ora92 \ sample. ora size 5 m,

4 'd: \ oracle \ oradata \ ora92 \ dd. ora size 5 m

5 extent management local

6 uniform segment space management

7 * auto

SQL>/

The tablespace has been created.

To delete the tablespace, you can

SQL> drop tablespace sample;

The tablespace is discarded.

 

2. Manage tablespaces and data files
Tablespace is a logical component of the database. Physically, the database data is stored in the data file. Logically, the database is stored in the tablespace, which is composed of one or more data files.
The Logical Structure in Oracle includes tablespaces, segments, partitions, and blocks.
It is explained that the database is composed of tablespaces, while the tablespace is composed of segments, and the segments are composed of partitions, And the partitions are composed of Oracle blocks, which improves the efficiency of the database.
Tablespaces are used to logically organize data in databases. The database is logically composed of one or more tablespaces. Table space can serve the following purposes:
1) control the disk space occupied by the database
2) DBA can deploy different data types to different locations, which improves I/O performance and facilitates management operations such as backup and recovery.
Create a tablespace
The create tablespace command is used to create a tablespace. In general, the creation of a tablespace is executed by a privileged user or DBA. If other users are used to create a tablespace, the user must have the system permission for create tablespace.
Create a data table space
After creating a database, you are advised to create your own tablespace to facilitate table management.
Create tablespace morf01 datafile 'd: \ test \ morf01.dbf 'size 20 m uniform size 128 K
After executing the preceding command, a tablespace named morf01 is created and a data file named morf01.dbf is created for the tablespace. The partition size is 128 kb.
Use tablespace
Create Table mytable (a number (3) tablespace morf01;
Change the tablespace status
When a tablespace is created, the tablespace is in the online status. At this time, the tablespace is accessible and can be read and written, that is, the tablespace data can be queried, you can also execute various statements in the tablespace, but you may need to change the tablespace status during system maintenance or data maintenance. Generally, it is operated by a privileged user or DBA.
1) take the tablespace offline
Alter tablespace morf01 offline;
2) Bring the tablespace online
Alter tablespace morf01 online;
3) read-only tablespace
When a tablespace is created, the tablespace can be read and written. If you do not want to perform the insert/update/delete operation on the tablespace, you can change the tablespace to read-only.
Alter tablespace morf01 read only;
Open read/write permissions:
Alter tablespace morf01 read write;
4) Know the tablespace name and display all tables included in the tablespace.
Select * From all_tables where tablespace_name = 'tablespace name ';
5) Know the table name and check which tablespace the table belongs.
Select tablespace_name, table_name from user_tables where table_name = 'emp ';
We can understand scott. EMP is on the system tablespace. Now we can change system to read-only, but we won't succeed, because system is the system tablespace. If it is a common tablespace, then we can set it as read-only.
6) delete a tablespace
Generally, privileged users or DBAs operate on the table. If the operation is performed by another user, the user is required to have the system permission to drop tablespace.
Drop tablespace name including contents and datafiles
Note: Including contents indicates that all database objects in the tablespace are deleted when the tablespace is deleted, while datafiles indicates that the database files are also deleted.
7) extended tablespace
A tablespace is composed of data files. The tablespace size is actually the size after the data files are added. As we can imagine, if there are too many data inserts, there must be an error message indicating insufficient table space. In this case, we should try to expand the tablespace in three ways:
○ 1 add data files
Alter tablespace morf01 add datafile 'd: \ test \ morf001.dbf' size 20 m
○ 2 increase the size of data files
Alter tablespace name 'd: \ test \ morf01.dbf' resize 200 m;
Note that the data file size cannot exceed 500 mb.
○ 3 set Automatic File Growth
Alter tablespace name 'd: \ test \ morf01.dbf 'Auto extend on next 10 m maxsize 500 m;
Move data files
Sometimes, if the disk where your data file is located is damaged, the data file will no longer be used. To make it available again, you need to move copies of these files to other disks and restore them.
The following describes how to move the data file morf01.dbf.
1) determine the tablespace where the data file is located
Select tablespace_name from dba_data_files where file_name = 'd: \ test \ morf01.dbf'
2) The tablespace is offline.
Ensure data file consistency and change the tablespace to Offline state.
Alter tablespace morf01 offline;
3) use commands to move data files to the specified target location
Host move D: \ test \ morf01.dbf c: \ test \ morf01.dbf;
4) execute the alter tablespace command
After physically moving the data, you must execute the alter tablespace command to modify the logic of the database file.
Alter tablespace morf01 rename datafile 'd: \ test \ morf01.dbf 'to 'C: \ test \ morf01.dbf ';
5) Bring the tablespace online
After a data file is moved, it must be changed to the online status to allow users to access the tablespace.
Alter tablespace morf01 online;
Display tablespace Information
Query the data dictionary view dba_tablespaces and display the tablespace information:
Select tablespace_name from dba_tablespaces;
Display the data files contained in the tablespace
Query the data dictionary view dba_data_files to display the data files contained in the tablespace, as follows:
Select file_name, bytes from dba_data_files where tablespace_name = 'tablespace name ';
Other tablespaces
1) index tablespace
2) undo tablespace
3) Temporary tablespace
4) non-standard block tablespaces