作者：夏石兵 | 来源：互联网 | 2014-07-11 17:33
表空间相关命令及查询SQL1、删除表空间及数据文件droptablespaceTS_GLOBAL_01includingcontentsanddatafiles;2、离线删除数据文件www.2cto.comalterdatabasedatafile'/oradata/DTCDB/...
表空间相关命令及查询SQL
1、删除表空间及数据文件
drop tablespace TS_GLOBAL_01 including contents and datafiles;
2、离线删除数据文件 www.2cto.com
alter database datafile &＃39;/oradata/DTCDB/TS_INDEX_02&＃39; offline drop;
3、自动回滚表空间文件删除
alter tablespace undotbs add datafile &＃39;/oradata/DTCDB/undotbs_01.dbf&＃39;;
4、创建自动回滚表空间文件
create undo tablespace undotbs02 datafile &＃39;/oradata/DTCDB/undotbs_02.dbf&＃39; size 10M;
5、查询段、数据文件信息
select distinct t1.owner, /*t1.segment_name,*/ t1.segment_type, t1.segment_subtype, t2.*
from dba_segments t1, dba_data_files t2
where t1.tablespace_name = t2.tablespace_name
--and t2.file_name = &＃39;/opt/oracle/11g/dbs/TS_INDEX_01&＃39;
and t2.tablespace_name = &＃39;TS_INDEX_01&＃39;
order by t1.owner, t2.file_name
www.2cto.com
select t.file# ,t.status,t1.file_name from v$datafile t ,dba_data_files t1 where t.file# = t1.file_id and t.STATUS=&＃39;RECOVER&＃39;;
6、清空全局回收管理器
purge dba_recyclebin;
7、查询所有段信息
select t.owner, sum(t.bytes) / 1024 / 1024 / 1024 as size_g
from dba_segments t
--where t.segment_type = &＃39;TABLE&＃39;
group by t.owner
order by t.owner;
8、查询表空间文件所占物理空间
select t.tablespace_name,
trunc(t.bytes / 1024 / 1024 / 1024, 3) as size_g,
t.file_name,
t.online_status
from dba_data_files t
where t.tablespace_name like &＃39;TS_%&＃39;
and t.bytes / 1024 / 1024 / 1024 > 1
order by t.tablespace_name,size_g desc, t.file_name;
9、查询数据文件的状态信息
select file#,name,status,enabled,bytes/1024/1024 M from v$datafile t
where t.NAME=&＃39;/oradata/DTCDB/TS_INDEX_02&＃39;
10、查询
数据库系统文件信息 www.2cto.com
select * from sys.file$
11、数据库文件恢复操作
shutdown abort;
a、恢复成功
startup mount;
recover datafile &＃39;/oradata/DTCDB/TS_PARTITION_A_000&＃39;;
alter database open;
b、恢复失败
startup mount;
alter database datafile &＃39;/oradata/DTCDB/TS_PARTITION_A_000&＃39; offline drop;
alter database open;
作者 AttaGain
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有 