10.
10.1. 10.2. 10.3. 10.4. 触发器管理
找出数据库中所有触发器 找出特定用户的触发器 找出当前用户定义的触发器 查看某个用户自定义的触发器内容 SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='TRIGGER' SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE='TRIGGER' AND OWNER='SCOTT'; SELECT * FROM USER_SOURCE WHERE TYPE='TRIGGER'; select * from dba_triggers where OWNER='SCOTT'; select text from dba_source where owner= 'SCOTT' and name='ON_LOGON_TRIGGER' order by line; 10.5. 查看某个表关联的触发器 select * from all_triggers where table_name='EMP'; select * from dba_triggers where table_name='EMP'; 10.6. 查看当前用户所有触发器及存储过程 select * from user_source 11.
Redo Log 管理
11.1. 离线迁移日志文件 SQL> select g.member, v.status from v$log v, v$logfile g where v.GROUP#=g.GROUP#; SQL>alter system switch logfile; SQL> shutdown immediate; mv /u01/app/oracle/oradata/racdb1/redo01.log /u01/app/oracle/oradata/racdb1/RedoLog/redo01.log mv /u01/app/oracle/oradata/racdb1/redo02.log /u01/app/oracle/oradata/racdb1/RedoLog/redo02.log mv /u01/app/oracle/oradata/racdb1/redo03.log /u01/app/oracle/oradata/racdb1/RedoLog/redo03.log SQL>startup mount; alter database rename file '/u01/app/oracle/oradata/racdb1/redo01.log' to '/u01/app/oracle/oradata/racdb1/RedoLog/redo01.log'; alter database rename file '/u01/app/oracle/oradata/racdb1/redo02.log' to '/u01/app/oracle/oradata/racdb1/RedoLog/redo02.log'; alter database rename file '/u01/app/oracle/oradata/racdb1/redo03.log' to '/u01/app/oracle/oradata/racdb1/RedoLog/redo03.log'; SQL> alter database open; SQL>alter system switch logfile; SQL> select g.member, v.status from v$log v, v$logfile g where v.GROUP#=g.GROUP#; 说明 1. 查看日志文件信息 2. 关闭数据库
3. 4. 5. 6.
移动Redo Log 数据文件到新位置 启动数据库到mount状态 更新控制文件
打开数据库并查看改变结果
11.2. 在线更改Redo Log文件容量 SQL>select * from v$logfile; SQL> select * from v$log; SQL>select g.member, v.status from v$log v, v$logfile g where v.GROUP#=g.GROUP#; SQL>alter database add logfile group 4 ('/u01/app/oracle/oradata/racdb1/RedoLog/redo401.log','/u01/app/oracle/oradata/racdb1/RedoLog/redo402.log ') size 100M; SQL>alter database add logfile group 5 ('/u01/app/oracle/oradata/racdb1/RedoLog/redo501.log','/u01/app/oracle/oradata/racdb1/RedoLog/redo502.log ') size 100M; SQL>alter database add logfile group 6 ('/u01/app/oracle/oradata/racdb1/RedoLog/redo601.log','/u01/app/oracle/oradata/racdb1/RedoLog/redo602.log ') size 100M; alter system switch logfile; alter system switch logfile; alter system switch logfile; SQL> select * from v$log; SQL> alter database drop logfile group 1; SQL>alter database drop logfile group 2; SQL> alter database drop logfile group 3; [root@racdb1 ~]# rm –rf /u01/app/oracle/oradata/racdb1/RedoLog/redo01.log [root@racdb1 ~]# rm –rf /u01/app/oracle/oradata/racdb1/RedoLog/redo02.log [root@racdb1 ~]# rm –rf /u01/app/oracle/oradata/racdb1/RedoLog/redo02.log SQL> select * from v$logfile; 说明1. 2. 3.
4. 5. 6.
查看日志文件，组，当前日志组信息 新增日志组
旧日志组切换到新日志组
使用日志查询命令查出的结果中，必定有一个日志组为CURRENT状态 重复执行日志切换命令，可以使新增加的日志组状态由unused变为active
如果删除日志时报错,报错的日志组会转入active状态，该状态不能删除，至少等待5分钟，当该日志组自动从active状态转为inactive后，该日志文件才能被删除。
本步骤的删除、切换、查看日志命令可以反复执行，直到将group1,gourp2,gourp3日志都删除后，方可进入下一步。 删除旧日志组 手动删除日志文件 检查切换结果