CREATE [UNDO] tablespace tablespace_name
[DataFile DATEFILE_SPEC1 [, DATEFILE_SPEC2] ...
[{mininum EXTENT integer [k|m]
| BLOCKSIZE integer [K]
|logging clause | Force LOGGING
| DEFAULT {data_segment_compression} storage_clause
| [Online|offline]
| [permanent| Temporary]
|extent_manager_clause
|segment_manager_clause}]
1. Undo
Description The system creates a rollback table space.
In 9i, the database administrator can not manage the rollback segment, only the undo table space is established, the system will automatically manage the rollback segment allocation, recycling work.
Of course, you can also create a general tablespace and create a rollback segment on it. However, for users, system management is much better than their own management.
If you need to manage it yourself, see the instructions for rollback segment management.
When no rollback table space is specified for the system, the system uses a system rollback segment for transaction management.
2, Tablespace_name
Indicates the name of the tablespace.
3, DataFile DATEFILE_SPEC1
Indicates what spatial file the table space contains.
Datefile_spec1 is shaped like: [' filename '] [SIZE integer [K | M]] [reuse] [Autoextend_clause]
[Autoextend_clause] is shaped like: autoextend {OFF | On [NEXT integer [K | M]] [Maxsize_clause]}
where filename is the pathname of the data file, either a relative path or an absolute path.
The size of the file,
Reuse indicates whether the file is reused.
Autoextend Indicates whether the extension is automatic.
OFF | On indicates whether auto-expansion is turned off.
Next indicates the size of the extension after the data file is full.
Maxsize_clause represents the maximum size of the data file. Shape as maxsize {UNLIMITED | integer [K | M]}.
UNLIMITED represents an infinite table space.
Integer is the maximum size of the data file.
DataFile ' D: "Oracle" Oradata "imagedata01.dbf ' SIZE 2000M,
' D: ' Oracle ' oradata ' imagedata02.dbf ' SIZE 2000M
4, Mininum EXTENT integer [k|m]
Indicates the minimum value of the range in the table space. This parameter reduces space fragmentation, guaranteeing that the range of the table space is an integral multiple of the value.
5, BLOCKSIZE integer [K]
This parameter can be used to set the size of a non-standard block. If you want to set this parameter, you must set the Db_block_size,
At least one db_nk_block_size, and the value of the declared integer must be equal to db_nk_block_size.
Note: This parameter cannot be set in a temporary tablespace.
6, logging clause
This clause declares the log properties of all user objects on this tablespace (by default, logging).
Includes tables, indexes, partitions, materialized views, indexes on materialized views, partitions.
7. Force LOGGING
Use this clause to indicate that the tablespace enters forced log mode. At this point, all changes to the object on the table space are recorded, except for the temporary segment changes.
This parameter is higher than the object's nologging option.
Note: Setting this parameter database does not work open and is out of read and write mode. Also, this option is not available in the temporary tablespace and the rollback table space.
8. DEFAULT Storage_clause
Declares the default storage clause.
9, Online|offline
Changes the state of the tablespace. Online makes the tablespace effective immediately after it is created. This is the default value.
Offline invalidates the table space after it is created. This value can be obtained from the dba_tablespace.
10, permanent| Temporary
Indicates whether a tablespace is a permanent tablespace or a temporary table space.
Permanent table spaces hold permanent objects, and temporary table spaces hold temporary objects that exist during the session life.
The temporary table space generated by this parameter has been created since the dictionary was managed and cannot be used with the extent management local option.
If you want to create a local management table space, you must use the Create temporary tablespace
Note that the block size cannot be declared after this parameter is declared
11, Extent_management_clause
This is the most important clause that explains how the table space manages the scope. Once you have declared this clause, you can only change these parameters by porting.
Declare the local option if you want the table space to be managed locally. The Local management table space is managed through bitmaps.
Autoallocate indicates that the table space is automatically assigned a range, and the user cannot specify the size of the range. Only more than 9.0 of the versions have this feature.
The uniform indicates the fixed size of the table space's range, which is 1m by default.
You cannot set the system table space of a locally administered database to dictionary management.
Oracle recommends using local management table spaces.
If not set extent_management_clause,oracle will set a default value for him.
If the initialization parameter compatible is less than 9.0.0, then the system creates a dictionary management table space.
If it is greater than 9.0.0, then follow these settings:
If you do not specify a default storage_clause,oracle, create an auto-assigned local management table space.
Otherwise
If mininum extent is specified, then Oracle determines whether mininum extent, initial, next are equal, and pctincrease =0.
If the above conditions are met, Oracle creates a local management table space, extent size is initial.
If the above conditions are not met, Oracle will create an automatically assigned local management table space.
If mininum extent is not specified. Initial, then Oracle determines if Next is equal, and whether Pctincrease = 0.
If you meet Oracle, create a local management tablespace and specify uniform. Otherwise, Oracle will create an automatically assigned local management table space.
Note: The Local management table space can only store persistent objects.
If you declare local, you will not be able to declare default storage_clause,mininum extent, temporary.
EXTENT MANAGEMENT LOCAL
12, Segment_management_clause
Section space management in a way that is automated or manual:
SEGMENT SPACE MANAGEMENT {auto| MANUAL}
--
Example 1: Creating a permanent table space
You can create a single table space at a time, or you can create multiple table spaces at once
1.1 Creating a single table space
Create Tablespace ts_something
Logging
DataFile '/dbf1/ts_sth.dbf '
Size 32m
Autoextend on
Next 32m MaxSize 2048m
Extent management Local;
1.2 Creating multiple table spaces
CREATE tablespace Ts_imagedata
Nologging
DataFile ' D: "Oracle" Oradata "DATA01.DBF ' SIZE 2000M,
' D: ' Oracle ' oradata ' data02.dbf ' SIZE 2000M,
' D: ' Oracle ' oradata ' data03.dbf ' SIZE 2000M,
' D: ' Oracle ' oradata ' data04.dbf ' SIZE 2000M,
' D: ' Oracle ' oradata ' data05.dbf ' SIZE 2000M EXTENT
MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT AUTO;
CREATE tablespace Ts_imagedata
LOGGING
DataFile ' E: "ORACLE" Oradata "data_01.dbf ' SIZE 2000M reuse
Autoextend on
NEXT 51200K MAXSIZE 3900M,
' E: ' ORACLE ' oradata ' XL ' data_02.dbf ' SIZE 2000M reuse
Autoextend on
NEXT 51200K MAXSIZE 3900M,
' E: ' ORACLE ' oradata ' XL ' data_03.dbf ' SIZE 2000M reuse
Autoextend on
NEXT 51200K MAXSIZE 3900M,
' E: ' ORACLE ' oradata ' XL ' data_04.dbf ' SIZE 2000M reuse
Autoextend on
NEXT 51200K MAXSIZE 3900M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
Example 2: Create an undo table space
Create undo Tablespace Ts_undo
DataFile '/dbf/undo.dbf ' size 100M;
CREATE UNDO tablespace ts_undo01
DataFile '/data/ts_undo01.dbf '
SIZE 50000M Reuse
Autoextend on;
Querying table Space Information:
SELECT * from Dba_tablespaces;
SELECT * from Dba_data_files;
Delete Table space
Drop Tablespace Tablespace_name
Delete a table space while deleting a data file
Drop tablespace tablespace_name including contents and datafiles
