Oracle表空间和数据文件操作笔记

创建表空间

1.创建普通表空间
create tablespace oracle_tablespace
datafile '/home/oracle/oradata/orcl/oracle_tablespace.dbf'
size 100m
autoextend on next 10M maxsize 200M
extent management local
uniform size 1m;

2.创建undo表空间
CREATE undo tablespace undo_oracle
datafile '/home/oracle/oradata/orcl/undo_oracle.dbf'
size 50m
extent management local;

3.创建temporary表空间
CREATE temporary tablespace temporary_oracle
tempfile '/home/oracle/oradata/orcl/temporary_oracle.dbf'
size 50m
autoextend on next 10M maxsize 100M
extent management local
uniform size 1m;

 

扩展表空间

1.扩展数据表空间

alter tablespace oracle_tablespace add datafile '/home/oracle/oradata/orcl/oracle_tablespace1.dbf' size 100M;

2.扩展undo表空间

alter tablespace oracle_tablespace add datafile '/home/oracle/oradata/orcl/undo_tablespace1.dbf' size 100M;

3.扩展temporary表空间

alter tablespace oracle_tablespace add tempfile '/home/oracle/oradata/orcl/temporary_tablespace1.dbf' size 100M;

删除表空间

--删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
--删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
--删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
--删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

查看表空间使用情况

1.

with
a as (select tablespace_name,sum(bytes)/1024/1024 total_m from dba_data_files group by tablespace_name),
b as (select tablespace_name,sum(bytes)/1024/1024 free_m from dba_free_space group by tablespace_name),
c as (select tablespace_name,count(file_name) sum_files from dba_data_files group by tablespace_name)
select a.tablespace_name,total_m,free_m,total_m-free_m used_m,to_char(round((total_m-free_m)/total_m*100,2),'990.99')||'%' used_percent,c.sum_files from
a,b,c where a.tablespace_name=b.tablespace_name and b.tablespace_name = c.tablespace_name order by used_percent desc;

2.

select a.tablespace_name,total_m,free_m,total_m-free_m used_m,to_char(round(free_m/total_m*100,3),'990.999')||'%' free_percent from
(select tablespace_name,sum(bytes)/1024/1024 total_m from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes)/1024/1024 free_m from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by free_percent;

3.查看临时表空间情况

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(+);

修改默认表空间

1.修改全局默认临时表空间

alter database default temporary tablespace TEMPORARY_ORACLE;

2.修改用户默认临时表空间

alter user scott temporary tablespace temporary_oracle;

3.修改全局默认undo表空间

alter system set undo_tablespace=undo_oracle;

设置表空间为脱、联机，只读，读写状态

1.设置表空间为脱机

alter tablespace ORACLE_TABLESPACE offline;

2.设置表空间为联机

alter tablespace ORACLE_TABLESPACE online;

3.设置表空间为只读状态

alter tablespace ORACLE_TABLESPACE read only;

4.设置表空间为读写状态

alter tablespace ORACLE_TABLESPACE read write;

 

伸缩表空间

1.变大数据文件的大小

alter database datafile 4 resize 500M;

或

alter database datafile ' /home/oracle/oradata/orcl/users01.dbf' resize 500M;

2.缩小数据文件大小

1. 先估算该表空间内各个数据文件的空间使用情况：

SQL>select file#,name from v$datafile;

SQL>select max(block_id) from dba_extents where file_id=n;

MAX(BLOCK_ID)
-------------
13657

SQL>show parameter db_block_size

NAME TYPE VALUE
----------------------------- ------- ----------- db_block_size integer 8192
SQL>select 13657*8/1024 from dual;

13657*8/1024
-----------
106.695313

这说明该文件中最大使用块位于106M与107M之间，

SQL> alter database datafile/tempfile '/ora_data/cninsite/insitedev02.dbf' resize 107M;

改变数据文件位置

1.alter database datafile 4 offline;

2.alter tablespace users rename datafile '/home/oracle/oradata/orcl/users01.dbf' to '/home/oracle/oradata/orcl2/users01.dbf';
或
alter tablespace users rename datafile 4 to '/home/oracle/oradata/orcl2/users01.dbf';

3.alter database datafile 4 online;

l另外在mount状态：alter database rename file '/home/oracle/oradata/orcl1/users01.dbf' to '/home/oracle/oradata/orcl/users01.dbf';

4.启用数据文件自动扩展

alter datafile 4 autoextend on;

5.关闭数据文件自动扩展

alter datafile 4 autoextend off;