探索ORACLE_之表空间02_管理
表空间创建参考语法图如下连接：
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7003.htm#i2231734
创建表空间的语法是：
CREATE [SMALLFILE|BIGFILE] [PERMANENT|TEMPORARY|UNDO] TABLESPACE
DATAFILE|TEMPFILE datafile_clause[,datafile_clause]
[EXTENT MANAGEMENT LOCAL]
[AUTOALLOCATE|UNIFORM SIZE integer [K|M]]
[SEGMENT SPACE MANAGEMENT AUTO|MANUAL]
[BLOCKSIZE integer[K|M]]
[MININUM EXTENT integer [K|M]]
[LOGGING|NOLOGGING]
[ONLINE|OFFLINE [NORMAL|TEMPORARY|IMMEDIATE]];
其中，datafile_clause子句(数据文件子句)的语法是：
path_filename_suffix' SIZE integer [K|M]
[REUSE]
[AUTOEXTEND OFF|ON]
[NEXT integer [K|M]]
[MAXSIZE UNLIMITED | integer [K|M]]
1. 创建管理标准（小文件）表空间
1.1 创建一个标准表空间
11:04:57 SQL> create tablespace test001 datafile'/data2/test001_01.dbf' size 10m;
Tablespace created.
11:13:29 SQL> selecttablespace_name,file_name,blocks,bytes/1024/1024 M from dba_data_files whereTABLESPACE_NAME='TEST001';
TABLESPACE_NAME FILE_NAME BLOCKS M
------------------------------------------------------------ ---------- ----------
TEST001 /data2/test001_01.dbf 1280 10
Elapsed: 00:00:00.03
11:17:01 SQL> alter tablespace test001 add datafile'/data2/test001_02.dbf' size 10m;
Tablespace altered.
1.2为表空间添加数据文件
Elapsed: 00:00:00.89
11:30:05 SQL> selecttablespace_name,file_name,blocks,bytes/1024/1024 M from dba_data_files whereTABLESPACE_NAME='TEST001';
TABLESPACE_NAME FILE_NAME BLOCKS M
------------------------------------------------------------ ---------- ----------
TEST001 /data2/test001_01.dbf 1280 10
TEST001 /data2/test001_02.dbf 1280 10
Elapsed: 00:00:00.02
11:30:12 SQL>
1.3修改表空间数据文件为自动扩展
11:42:35 SQL> alter database datafile'/data2/test001_02.dbf' autoextend on next 2m maxsize 200m;
Database altered.
Elapsed: 00:00:00.03
12:11:33 SQL> selectfile_id,file_name,tablespace_name,blocks,bytes/1024/1024 bytes_M,autoextensiblefrom dba_data_files where TABLESPACE_NAME='TEST001';
FILE_IDFILE_NAME TABLESPACE_NAME BLOCKS BYTES_M AUT
---------- --------------------------------------------- ---------- ---------- ---
6/data2/test001_01.dbf TEST001 1280 10 NO
7/data2/test001_02.dbf TEST001 1280 10 YES
Elapsed: 00:00:00.04
12:11:48 SQL>
1.4通过指定段的大小来创建表空间
12:21:53 SQL> selectTABLESPACE_NAME,INITIAL_EXTENT/1024,NEXT_EXTENT/1024,EXTENT_MANAGEMENT fromdba_tablespaces;
TABLESPACE_NAME INITIAL_EXTENT/1024 NEXT_EXTENT/1024 EXTENT_MAN
-------------------- ----------------------------------- ----------
SYSTEM 64 LOCAL
UNDOTBS1 64 LOCAL
SYSAUX 64 LOCAL
TEMP 1024 1024 LOCAL
USERS 64 LOCAL
TEST01 64 LOCAL
TEST001 64 LOCAL
7 rows selected.
Elapsed: 00:00:00.03
12:22:17 SQL> create tablespace test002 datafile'/data2/test002_01.dbf' size 10m extent management local uniform size 1m;
Tablespace created.
Elapsed: 00:00:00.69
12:23:34 SQL> select TABLESPACE_NAME,INITIAL_EXTENT/1024,NEXT_EXTENT/1024,EXTENT_MANAGEMENTfrom dba_tablespaces;
TABLESPACE_NAME INITIAL_EXTENT/1024 NEXT_EXTENT/1024 EXTENT_MAN
-------------------- ----------------------------------- ----------
SYSTEM 64 LOCAL
UNDOTBS1 64 LOCAL
SYSAUX 64 LOCAL
TEMP 1024 1024 LOCAL
USERS 64 LOCAL
TEST01 64 LOCAL
TEST001 64 LOCAL
TEST002 1024 1024 LOCAL
8 rows selected.
Elapsed: 00:00:00.01
12:23:39 SQL>
2. 创建管理大文件表空间
2.1 查询数据库创建表空间的默认信息，该视图得知为小文件表空间，当然这个是可以修改的：
12:27:23 SQL> select * from database_propertieswhere property_name = 'DEFAULT_TBS_TYPE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
--------------------------------------------------------------------------------------------------------------
DEFAULT_TBS_TYPE SMALLFILE Defaulttablespace type
2.2 创建一个大文件表空间：
create bigfile tablespace bigfile001 datafile'/data2/bigfile001.dbf' size 5m;
Tablespace created.
Elapsed: 00:00:04.37
2.3 查看表空间类型，是否为大文件表空间
12:31:24 SQL> SELECT tablespace_name, bigfile fromdba_tablespaces;
TABLESPACE_NAME BIG
-------------------- ---
SYSTEM NO
UNDOTBS1 NO
SYSAUX NO
TEMP NO
USERS NO
TEST01 NO
TEST001 NO
TEST002 NO
BIGFILE001 YES
通过另外一个视图查看
12:32:18 SQL> select * from v$tablespace;
TS#NAME INC BIGFLA ENC
---------- ------------------------------ --- --- ------
0SYSTEM YESNO YES
1UNDOTBS1 YESNO YES
2SYSAUX YES NO YES
4USERS YESNO YES
3TEMP NO NO YES
6TEST01 YESNO YES
7TEST001 YESNO YES
8TEST002 YESNO YES
9 BIGFILE001 YES YES YES
2.4 大文件表空间只允许有一个数据文件，所以相对于的文件号也是只有固定的1024
12:34:18 SQL> alter tablespace bigfile001 adddatafile '/data2/bigfile002.dbf' size 4m;
alter tablespace bigfile001 add datafile'/data2/bigfile002.dbf' size 4m
*
ERROR at line 1:
ORA-32771: cannot add file to bigfiletablespace
Elapsed: 00:00:00.05
12:34:32 SQL>
12:53:05 SQL> selectfile_id,tablespace_name,bytes/1024/1024 bytes_M,autoextensible,relative_fnofrom dba_data_files;
FILE_IDTABLESPACE_NAME BYTES_M AUTRELATIVE_FNO
---------- -------------------- ---------- ---------------
4USERS 5 YES 4
3SYSAUX 240 YES 3
2 UNDOTBS1 25 YES 2
1SYSTEM 480 YES 1
5TEST01 3584 NO 5
6TEST001 10 NO 6
7TEST001 10 YES 7
8TEST002 10 NO 8
9 BIGFILE001 5YES 1024
9 rows selected.
Elapsed: 00:00:00.04
2.5 这样的话，oracle建议用户将大数据文件表空间设置为可自动扩展，当然在创建的时候也可以指定自动扩展属性：
12:38:37 SQL> alter tablespace BIGFILE001autoextend on;
Tablespace altered.
12:42:59 SQL> selectfile_id,file_name,tablespace_name,blocks,bytes/1024/1024 bytes_M,autoextensiblefrom dba_data_files where tablespace_name='BIGFILE001';
FILE_IDFILE_NAME TABLESPACE_NAME BLOCKS BYTES_M AUT
---------- --------------------------------------------- ---------- ---------- ---
9/data2/bigfile001.dbf BIGFILE001 640 5 YES
Elapsed: 00:00:00.03
12:43:19 SQL>
2.6 那么它可以自动扩展到多大呢?
在这里引入一个参数: db_block_size
12:45:07 SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
单个文件最大限制的对应关系
文件系统(块) 文件大小限制 文件系统大小限制
ext2/3 (2K) 256G 8T
ext2/3 (4K) 2T 16T
ext2/3 (8K) 64T 32T
ReiserFS 3.6 1E 16T
检查操作系统的文件块大小，这个大小是在创建文件系统的时候可以指定：
[root@ora10g admin]# tune2fs -l /dev/sdb |grep Block
Block count: 2097152
Block size: 4096
Blocks per group: 32768
由以上得出，我们可创建的单个文件的大小为2TB，同时我们上面创建的那个大文件表空间可自动扩展的最大大小同样也为2TB。
超过操作系统规定的单个文件大小将报如下错误：
12:56:12 SQL> create bigfile tablespace bigfile002 datafile '/data2/bigfile002.dbf' size 3T reuse;
create bigfile tablespace bigfile002 datafile '/data2/bigfile002.dbf' size 3T reuse
*
ERROR at line 1:
ORA-01119: error in creating database file '/data2/bigfile002.dbf'
ORA-27044: unable to write the header block of file
Linux Error: 27: File too large
Additional information: 4
我们创建2T以下的表空间是可以创建的，没有报错：
13:03:55 SQL> create bigfile tablespace bigfile002 datafile '/data2/bigfile002.dbf' size 1900G reuse;
Tablespace created.
3. 创建管理临时表空间
3.1 查看默认的临时表空间
13:19:00 SQL> select * fromdatabase_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
--------------------------------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporarytablespace
Elapsed: 00:00:00.01
3.2 创建临时表空间，并切换到新的临时表空间
13:21:01 SQL> create temporarytablespace temp01 tempfile '/data2/temp01_01.dbf' size 10m;
Tablespace created.
13:24:13 SQL> select file_id,tablespace_name,file_name,bytes/1024/1024byte_M from dba_temp_files;
FILE_ID TABLESPACE_NAME FILE_NAME BYTE_M
---------- --------------------------------------------- ----------
1 TEMP /oradata/wwl/temp01.dbf 20
2 TEMP01 /data2/temp01_01.dbf 10 ----新创建的临时表空间
Elapsed: 00:00:00.07
注意:
新创建的临时表空间可以作为全局临时表空间，同时也可以做为单个用户的临时表空间，甚至可以作为一个临时表空间组中的一个临时表空间成员
3.3 切换全局临时表空间
13:24:32 SQL> alter database defaulttemporary tablespace temp01;
Database altered.
Elapsed: 00:00:00.18
13:33:01 SQL> select * from database_properties whereproperty_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP01 Name of default temporary tablespace
3.4 创建新用户指定非默认临时表空间
13:35:22 SQL> create user test03identified by oracle temporary tablespace TEMP;
User created.
13:37:23 SQL> selectusername,temporary_tablespace,ACCOUNT_STATUS from dba_users whereACCOUNT_STATUS='OPEN';
USERNAME TEMPORARY_TABLE ACCOUNT_STATUS
---------- -----------------------------------------------
SYSTEM TEMP01 OPEN
SYS TEMP01 OPEN
TEST03 TEMP OPEN
TEST01 TEMP01 OPEN
3.5 通过临时表空间组对临时表空间进行管理，可以实现临时表空间负载
创建临时表空间组
13:37:48 SQL> alter tablespace temptablespace group tmpgroup;
Tablespace altered.
Elapsed: 00:00:00.08
13:41:23 SQL> alter tablespace temp01tablespace group tmpgroup;
Tablespace altered.
Elapsed: 00:00:00.05
3.5.1 查询临时表空间组中的表空间
13:41:44 SQL> select * fromdba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
---------------------------------------------
TMPGROUP TEMP
TMPGROUP TEMP01
Elapsed: 00:00:00.02
3.5.2 将临时表空间组设为数据库默认临时表空间
13:41:59 SQL> alter database defaulttemporary tablespace tmpgroup;
Database altered.
3.5.3 查询当前默认临时表空间
13:44:03 SQL> select * fromdatabase_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
--------------------------------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE TMPGROUP Name of default temporary tablespace
Elapsed: 00:00:00.02
4. 创建管理Undo表空间： 由于undo表空间的管理非常复杂，在这里不做详细论述，将在后期的体系架构里面做详细说明。
在单个数据库中undo表空间可以有多个，但处于active的只能有一个，并且active的undo表空间是不能offlin和drop。如果未创建undo表空间oracle将使用system undo segment。
4.1 查看默认undo表空间 --可以看到当前undotbs1为active表空间。
13:44:05 SQL> show parameter undo
NAME TYPE VALUE
----------------------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
13:50:36 SQL>
4.2 创建新的undo表空间并做切换。
4.2.1 创建新的UNDO表空间
13:50:36 SQL> create undo tablespaceundotbs2 datafile '/data2/undotbs2_01.dbf' size 10m;
Tablespace created.
13:54:26 SQL> selectTABLESPACE_NAME,STATUS,CONTENTS from dba_tablespaces where CONTENTS='UNDO';
TABLESPACE_NAME STATUS CONTENTS
--------------- --------- ---------
UNDOTBS1 ONLINE UNDO
UNDOTBS2 ONLINE UNDO
4.2.2 切换undo表空间
13:54:49 SQL> alter system setundo_tablespace='UNDOTBS2';
System altered.
Elapsed: 00:00:00.29
13:55:48 SQL> show parameter undo;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
13:55:57 SQL>
4.2.3 删除非默认undo表空间：
18:39:25 SQL> select * from v$tablespaceorder by name;
TS# NAME INC BIG FLAENC
---------- --------------- --- --- --- ---
9 BIGFILE001 YES YES YES
2 SYSAUX YES NO YES
0 SYSTEM YES NO YES
3 TEMP NO NO YES
11 TEMP01 NO NO YES
7 TEST001 YES NO YES
8 TEST002 YES NO YES
6 TEST01 YES NO YES
1 UNDOTBS1 YES NO YES
5 UNDOTBS2 YES NO YES
4 USERS YES NO YES
18:40:03 SQL> drop tablespace UNDOTBS1including contents and datafiles;
Tablespace dropped.
Elapsed: 00:00:02.23
18:42:37 SQL> select * from v$tablespaceorder by name;
TS# NAME INC BIG FLAENC
---------- --------------- --- --- --- ---
9 BIGFILE001 YES YES YES
2 SYSAUX YES NO YES
0 SYSTEM YES NO YES
3 TEMP NO NO YES
11 TEMP01 NO NO YES
7 TEST001 YES NO YES
8 TEST002 YES NO YES
6 TEST01 YES NO YES
5 UNDOTBS2 YES NO YES
4 USERS YES NO YES
10 rows selected.
Elapsed: 00:00:00.02
18:42:43 SQL>
4.2.4 删除当前undo表空间则报错
18:42:43 SQL> drop tablespace UNDOTBS2including contents and datafiles;
drop tablespace UNDOTBS2 including contentsand datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace'UNDOTBS2' is currently in use
11 rows selected.