Oracle 数据文件操作笔记

12. November 2015 笔记 0

alter tablespace users add datafile ‘D:\oracle\product\10.2.0\oradata\orcl\lijian.dbf’ size 10M; //创建users用户下的数据文件

alter tablespace temp add tempfile ‘D:\oracle\product\10.2.0\oradata\orcl\li.dbf’ size 5M;//创建temp下临时数据文件

select tablespace_name,sum(bytes)/1024/1024 from dba_segments group by tablespace_name; //look up tablespaces’ size 查看表空间

alter tablespace  users add datafile ‘D:\oracle\product\10.2.0\oradata\orcl\lijians.dbf’ size 10 autoextend on next 512k maxsize 50M;//创建自动增长表空间

alter database datafile ‘D:\oracle\product\10.2.0\oradata\orcl\lijian.dbf’ autoextend on next 512k maxsize 50M;

alter databese datafile ‘D:\oracle\product\10.2.0\oradata\orcl\lijian.sbf’ autoextend off; 关闭自增

alter tatabase datafiel ‘D:\oracle\product\10.2.0\oradata\orcl\lijian.dbf’ resize 100M; 重新定义数据文件大小

conn system/oracle10 as sysdba;

archive log list

shutdown  immediate

startup mount



alter database noarchivelog 非归档模式

alter database archivelog    归档模式


alter database datafile ‘D:\oracle\product\10.2.0\oradata\orcl\lijian.dbf’ offline; 数据库文件离线

alter database datafile ‘D:\oracle\product\10.2.0\oradata\orcl\lijian.dbf’ online; 数据库文件在线


alter tablespace users datafile offline 表空间离线

recover tablespace users;  表空间恢复介质

alter tablespace users datafile online

alter tablespace users rename datafile ‘D:\oracle\product\10.2.0\oradata\orcl\ljian.dbf’ to ‘D:\oracle\product\10.2.0\oradata\orcl\lijian3.dbf’ 赋值数据文件

shutdown immediate

startup mount

select  name,file# from v$datafile;

select tablespace_name file_name from dba_temp_files;

alter tablespace users drop datafile ‘D:\oracle\product\10.2.0\oradata\orcl\lijian.dbf’;

select member from v$logfile; 查看日志文件

select name from v$datafile; 查看数据库文件

recover database;

recover database using backup  controlfile 恢复数据文件

alter database add logfile member ‘D:\oracle\product\10.2.0\oradata\orcl\lijian.log’ to group1

shutdown immediate

startup mount

alter database archivelog

alter database noarchivelog

alter database open;

alter system archive log all对所有已经写满的从做日志进行归档

alter system archive log current 对当前的联机日志文件进行归档。


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.