oracle alter database,DBA常用命令之alter database

1.修改数据文件将数据文件脱机alter database datafile 4 offline;

alter database datafile '/u01/app/oracle/oradata/xiaoyao/users01.dbf' offline;将数据文件联机alter database datafile '/u01/app/oracle/oradata/xiaoyao/users01.dbf' online;改变数据文件大小(注意一般只有增加)

alter database datafile '/u01/app/oracle/oradata/xiaoyao/users01.dbf' resize

100M;将数据文件设置为自动扩展，每次增长100M，文件最大为1000M

alter database datafile '/u01/app/oracle/oradata/xiaoyao/users01.dbf' autoextend

on next 100M maxsize 1000M;

2.修改临时文件(tempfile)改变临时文件的大小alter database tempfile 4 resize 100M;将临时文件设置成为自动扩展，每次增长100M，文件最大为1000M

alter database tempfile 4 autoextend on next 100M maxsize 1000M;删除临时文件包括所属的数据文件alter database tempfile 4 drop including datafiles;把临时文件脱机alter database tempfile 4 offline;

3.归档日志(archivelog)模式命令设置为归档模式(在mount状态下)alter database archivelog;设置为非归档模式(在mount状态下)alter database noarchivelog;

alter database force logging;

alter database clear logfile '/u01/app/oracle/oradata/xiaoyao/redo01.log';

alter database clear unarchived logfile

'/u01/app/oracle/oradata/xiaoyao/redo01.log';

3.控制文件操作将控制文件备份到trace

alter database backup controlfile to trace;

alter database backup controlfile to trace as

'/u01/app/oracle/logfile_backup/backup_logfile.trc' reuse resetlogs;

alter database backup controlfile to

'/u01/app/oracle/logfile_backup/backup_logfile.ctl';

4.创建数据文件alter database create datafile '/u01/app/oracle/oradata/xiaoyao/users01.dbf' as

'/u01/app/oracle/oradata/xiaoyao/users01.dbf';

alter database create datafile 4 as

'/u01/app/oracle/oradata/xiaoyao/users01.dbf';

alter database create datafile '/u01/app/oracle/oradata/xiaoyao/users01.dbf' as

new;

5.日志文件命令alter database add logfile group 2('/u01/app/oracle/oradata/xiaoyao/redo11.log','/u01/app/oracle/oradata/xiaoyao/redo12.log')

size 300M reuse;

alter database add logfile member '/u01/app/oracle/oradata/xiaoyao/redo13.log'

to group 2;

alter database add logfile thread 3 group

2('/u01/app/oracle/oradata/xiaoyao/redo11.log','/u01/app/oracle/oradata/xiaoyao/redo12.log')

size 300M reuse;

alter database drop logfile group 3;

alter database drop logfile member

'/u01/app/oracle/oradata/xiaoyao/redo12.log';

6.打开数据库alter database mount;

alter database open;以只读方式打开数据库alter database open read only;

alter database open resetlogs;

7.移动或重命名数据库文件或联机重做日志alter database rename file '/u01/app/oracle/oradata/xiaoyao/users01.dbf' to

'/u01/app/oracle/oradata/xiaoyao/users1.dbf';