表空间管理的相关资料-凯发app官方网站

select owner, tablespace_name, round (sum (bytes) / 1024 / 1024, 2) "used(m)"
from dba_segments
group by owner, tablespace_name
order by sum (bytes) desc;

--表空间占有率
select d.tablespace_name, space "sum_space(m)", blocks sum_blocks, 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, sum(blocks) blocks
           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( )
union all --if have tempfile
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( );

--查看信息
select tablespace_name,bytes/1024/1024 mb,file_name from dba_data_files order by 1,3;

--删除用户脚本

drop user scott cascade;

--删除表空间
--首先要删除该表空间上的所有用户
alter tablespace pis offline;
drop tablespace pis including contents and datafiles;

--根据前面所列出的表空间名和对应的数据文件，到内对应路径查看，该文件是否已经被删除，如果没有被删除，通过系统删除！

-- 表空间
--创建表空间

--查看数据文件信息
select tablespace_name,bytes/1024/1024 mb,file_name from dba_data_files order by 1,3;

--创建表空间
create tablespace elarp datafile '/u02/oradata/ora10g/elarp.dbf' size 3000m autoextend off;
--调整表空间数据文件大小
alter database datafile '/u02/oradata/ora10g/elarp.dbf' resize 300m;
--取消数据文件自动扩展
alter database datafile '/u02/oradata/ora10g/users01.dbf' autoextend off;
--增加表空间数据文件
alter tablespace users add datafile '/u02/oradata/ora10g/users02.dbf' size 20480m autoextend off;
alter tablespace users add datafile '/u02/oradata/ora10g/users03.dbf' size 20480m autoextend off;

阅读(763) | 评论(1) | 转发(0) |

上一篇：经常使用的oracle监控语句

下一篇：忘记weblogic，如何修改密码

给主人留下些什么吧！~~

2012-03-19 01:46:18

表管理还没用到呢，收藏了！

| | | | |

感谢所有关心和支持过chinaunix的朋友们