oracle dba 应该熟悉的命令（10）

241 配置缺省的路径
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'E:RMAN％U.BAK' MAXPIECESIZE 2G;
242 清除缺省配置
CONFIGURE CHANNEL DEVICE type DISK clear;
243 配置并行数
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
244 配置自动备份控制文件
CONFIGURE CONTROLFILE AUTOBACKUP on;
245 配置有益度
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
246 配置可以恢复到7天内的保留窗口
CONFIGURE RETENTION POLICY TO recovery window of 7;
247 备份表空间
backup tablespace users;
248 备份数据文件
backup datafile 6;
249 备份前一天的重做日志
backup archivelog until time 'sysdate-1';
250 备份SP文件
backup spfile;
251 备份当前的控制文件
backup current controlfile;
252 列出所有的备份集
list backupset ;
253 列出所有的汇总备份集
list backupset summary;
254 列出具体的备份信息
list backupset 16
255 删除备份集13
delete backupset 13;
256 不提示删除所有的备份集
delete noprompt backupset;
257 凡是USERS的备份都删除
delete backup of tablespace users;
258 列出哪些不需要的备份集
report obsolete ;
259 边备份边压缩数据库
backup as compressed backupset database;
260 增量备份时的0级备份一个表空间
backup incremental level 0 tablespace users;
261 2级备份累积备份
backup incremental level 2 cumulative tablespace users;
262 删除指定日期的归档日志
DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1/24';

263 在RMAN恢复指定表空间
RUN {
SQL 'ALTER TABLESPACE USERS OFFLINE IMMEDIATE';
RESTORE TABLESPACE USERS;
RECOVER TABLESPACE USERS;
SQL 'ALTER TABLESPACE USERS ONLINE';
}
264 在RMAN的恢复控制文件
STARTUP NOMOUNT
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;
RESTORE DATABASE VALIDATE;
RECOVER DATABASE ;
ALTER DATABASE OPEN RESETLOGS;
265 查询数据库的字符集
select * from database_properties where property_name like 'NLS_CHA％';
NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
266 导出数据到一个文件中
exp userid=system/oracle file=full.dmp full=y
exp userid=system/oracle file=full1.dmp,full2.dmp,full3.dmp filesize=2g full=y direct=y log=full.log feedback=10000
267 导出指定用户的表
exp userid=system/oracle file=gary.dmp owner=gary,wei direct=y log=gary
exp userid=system/oracle file=gary.dmp owner=gary direct=y log=gary.log
268 导出指定表
exp userid=system/oracle file=gary_a.dmp tables=gary.a direct=y log=gary
269 导出指定表
exp userid=system/oracle file=gary_a1.dmp tables=gary.a query='where owner=''GARY''' log=gary.LOG
270 查看导出的帮助信息
exp -help
271 删除gary的用户
drop user gary cascade;
272 重建用户GARY
CREATE USER "GARY" PROFILE "DEFAULT" IDENTIFIED BY "GARY" ACCOUNT UNLOCK
273 授权用户GARY
GRANT UNLIMITED TABLESPACE,CONNECT,RESOURCE TO "GARY";
274 导入用户GARY的数据
imp userid=system/oracle fromuser=gary touser=gary show=y
imp userid=system/oracle fromuser=gary touser=gary file=full.dmp log=imp.log
imp userid=system/oracle fromuser=gary touser=wei file=gary.dmp log=imp.log
275 查询状态
select object_name,object_type,owner from dba_objects where status='INVALID';
EXEC UTL_RECOMP.RECOMP_PARALLEL
276 建立对象,允许可以用这个目录来导入导出
create directory mydir as 'e:expdata';
277 授权用户的权限
grant read,write on directory mydir to gary;
278 导出数据
expdp userid=gary/gary dumpfile=gary directory=mydir PARALLEL=2 schemas=gary job_name=gary_job
expdp userid=gary/gary attach=gary_job
268 查询任务
SELECT * FROM V$DATAPUMP_JOB;
269 导入数据
impdp userid=system/oracle dumpfile=gary directory=mydir job_name=gary_job