oracle日常操作命令手册(表空间管理)-从零到无
--时间：2020年8月24日
--作者：飞翔的小胖猪
手工目录：
#############################################################
二、管理表空间
2.1 创建数据表空间
2.1.1 不指定路径创建表空间
2.1.2 指定路径创建表空间
2.1.3 创建自增长表空间
2.1.4 查看获取表空间使用情况
2.1.5 扩容表空间
2.2 管理临时表空间
2.2.1 新建临时表空间
2.2.2 扩容临时表空间
2.2.3 查看临时表空间状态
2.3 删除表空间
2.3.1 查看表空间是否绑定用户
2.3.2 查看表空间中是否有表
2.3.3 删除表空间
#############################################################
2.1 创建数据表空间
新建表空间时需要指定表空间名、表空间文件路径、表空间大小、是否自动增长等参数。
2.1.1 不指定路径创建表空间
创建表空间的时候可以不指定文件路径，系统将会默认的格式创建一个新文件。
SQL> show parameter db_create_file_dest; #查看表空间自动填充目录
SQL> alter system set db_create_file_dest='/app/oracle/oradata/orcl/' scope＝both; #设置表空间自动填充目录
SQL> show parameter db_create_file_dest; #查看表空间自动填充目录
SQL> create tablespace test_lvan datafile size 2G; #新建一个名为test_lvan的表空间
SQL> set linesize 200; #设置显示行长度200字符
SQL> col file_name for a60; #设置file_name字段显示长度a60
SQL> col TABLESPACE_NAME for a15; #设置tablespace_name字段显示长度a15
SQL> select file_name,file_id,tablespace_name,round(bytes/1024/1024) total_M from dba_data_files; #查看表空间名及表空间大小
2.1.2 指定路径创建表空间
使用指定路径的方式创建表空间时，首先要确认oracle用户对路径文件夹有读写执行权限。否则将无法正常使用。
SQL> set linesize 200; #设置显示行长度200字符
SQL> col file_name for a60; #设置file_name字段显示长度a60
SQL> col TABLESPACE_NAME for a15; #设置tablespace_name字段显示长度啊5
SQL> select file_name,file_id,tablespace_name,round(bytes/1024/1024) total_M from dba_data_files; #查看表空间名及表空间大小
从命令得知所有表空间的文件均在/app/oracle/oradata/WANWAN/datafile/目录下。
手动指定一个路径创建一个表空间
SQL> create tablespace test_wan datafile '/app/oracle/oradata/WANWAN/datafile/test_wan.dbf'size 2G; #新建一个名为test_wan的表空间,大小为2G。
SQL> select file_name,file_id,tablespace_name,round(bytes/1024/1024) total_M from dba_data_files; #查看表空间名及表空间大小
2.1.3 创建自增长表空间
SQL> set linesize 220; #设置显示行长度220字符
SQL> col tablespace_name for a15; #设置tablespace_name字段显示长度为a15
SQL> col file_name for a75; #设置file_name字段显示长度a75
SQL> select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_files; #查看表空间是否为自增长
SQL> create tablespace test_auto_add datafile size 2G autoextend on next 100M maxsize 16G; #新建一个初始大小为2G的表空间，开启自动增长，每次增加100M，最大为16G。
SQL> set linesize 220; #设置显示行长度220字符
SQL> col tablespace_name for a15; #设置tablespace_name字段显示长度为a15
SQL> col file_name for a75; #设置file_name字段显示长度a75
SQL> select tablespace_name,file_name,AUTOEXTENSIBLE,round(bytes/1024/1024) total_MB from dba_data_files; #查看表空间是否为自增长
2.1.4 查看获取表空间使用情况
SQL> SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);
2.1.5 扩容表空间
使用重置文件大小方式扩容表空间
格式：
alter database datafile '表空间位置'resize 新的尺寸
SQL> alter database datafile '/u01/oracle/oradata/wyzc11g/sysaux01.dbf' resize 780M;
使用添加文件方式扩容表空间
格式：
alter tablespace 表空间名称add datafile '新的数据文件地址' size 数据文件大小
SQL> alter tablespace sysaux add datafile '/u01/oracle/oradata/wyzc11g/sysaux04.dbf' size 100M;
为表空间设置自动扩展
格式：
alter database datafile '数据文件位置' autoextend on next 自动扩展大小maxsize 最大扩展大小
SQL> alter database datafile '/u01/oracle/oradata/wyzc11g/sysaux04.dbf' autoextend on next 10M maxsize 1000M;
2.2.1 新建临时表空间
所有用户默认共享使用同一个临时表空间，对于业务而言一般需要创建独立的临时表空间。
SQL> create temporary tablespace tmpadd tempfile size 2G;
SQL> SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);
2.2.2 扩容临时表空间
通过添加文件的方式扩容temp表空间
扩展temp表空间并开启自动增长，使用alter tablespace temp add tempfile ‘文件路径’ size 大小 autoextend(自动扩展开启) on next 每次增长大小 maxsize 最大大小；
SQL>alter tablespace temp add tempfile '/u01/oracle/oradata/wyzc11g/temp02.dbf' size 1G autoextend on next 128M maxsize 3G;
添加文件方式扩容temp不开启自动增长
扩展temp表空间不开启自动增长，使用alter tablespace temp add tempfile ‘文件路径’ size 大小 autoextend off (自动扩展关闭)；
SQL>alter tablespace temp add tempfile '/u01/oracle/oradata/wyzc11g/temp02.dbf' size 1G autoextend off;
重置文件大小的方式扩容temp表空间
使用重置文件大小方式扩容表空空间，不建议缩小通过这种方式，大表空间不能用重置文件方式扩容表空间。命令：alter database tempfile ‘表空间文件路径’ resize 调整大小；
SQL> alter database tempfile '/u01/oracle/oradata/wyzc11g/temp03.dbf' resize 3G ;
设置现有temp文件为自动扩展
设置当前使用的temp表空间文件设置自动扩展
SQL>alter database tempfile '/u01/oracle/oradata/wyzc11g/temp03.dbf' autoextend on next 100M maxsize 6G;
2.2.3 查看临时表空间状态
查看系统temp表空间的文件路径名及表空间名
SQL>set linesize 200;
SQL> col file for a50;
SQL> select f.file#,t.ts#,f.name "File",t.name "Tablespace" from v$tempfile f,V$tablespace t where f.ts# = t.ts#;
查看每个用户TEMP状态
查看有哪些用户使用了temp表空间
SQL>SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;
查看临时表空间使用情况
SQL> SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);
2.3 删除表空间
在删除表空间的时候需要确认表空间是否真的不需要了、是否为用户的默认存储表空间、是否存在不可删除的数据表。如果确定真的不需要该表空间时则进行删除操作。
2.3.1 查看表空间是否绑定用户
持续更新
2.3.2 查看表空间中是否有表
持续更新
2.3.3 删除表空间
持续更新
