oracle日常操作命令手册(表空间管理)-从零到无

手工目录：

#############################################################

二、管理表空间 
  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  删除表空间

 持续更新