当前位置:Oracle研究中心 > 运维DBA >
时间:2016-06-23 20:01 来源:Oracle研究中心 作者:惜分飞 点击: 次
create tablespace oracleplus datafile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\
oracleplus.DBF’ size 10m reuse autoextend on next 10m maxsize UNLIMITED ;
create temporary tablespace xff_temp tempfile
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\xff_temp.dbf’ size 10m;
create undo tablespace xff_undo datafile
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\xff_undo.dbf’ size 10m;
alter database default temporary tablespace xff_temp;
select tablespace_name,contents from dba_tablespaces;
select * from database_properties where property_name like ‘%TEMP_TABLE%’;
alter tablespace oracleplus read only;
note:该表空间中的objects可以被drop,因为表的记录是放在数据字典(system)中
alter tablespace oracleplus read write;
alter tablespace oracleplus offline;
note:如果有数据没有commit,会自动被commit掉
alter tablespace oracleplus online;
select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used", round((f.free/a.total)*100) "% Free" from (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a, (select tablespace_name, round(sum(bytes/(1024*1024))) used from dba_extents group by tablespace_name) u, (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f WHERE a.tablespace_name = f.tablespace_name and a.tablespace_name = u.tablespace_name order by "% Free";
alter database datafile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\oracleplus.DBF‘autoextend on next 2m maxsize 100m;
alter tablespace oracleplus add datafile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\oracleplus1.DBF’ size 20m;
select file_name,autoextensible from dba_data_files;
alter database datafile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\oracleplus1.DBF’resize 15m;
select tablespace_name,file_name from dba_temp_files;
1)alter tablespace oracleplus rename datafile
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ oracleplus1.DBF’to‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\abc\ oracleplus1.DBF’
note:target文件必须存在,表空间必须离线
2) alter database rename file ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\oracleplus1.DBF’to C:\ORACLE\PRODUCT\10.2.0\ORADATA\orcl\oracleplus1.DBF’
note:target文件必须存在,数据库必须mount状态
alter tablespace oracleplus drop datafile‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\oracleplus1.DBF’;
drop tablespace oracleplus including contents and datafiles;
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之学习笔记:Oracle表空间管理 汇总DBA常用创建、修改表空间及临时表空间案例
本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/487.html
Powered by Oracle研究中心 OraclePlus Inc.
BY DedeCMS 京ICP备14022373号-5
声明:Oracle研究中心部分文章源自互联网,转载均按原文作者要求转载,如有疑问联系QQ:1026002015,协商未果24小时删除。