Oracle中的一些常见操作

Landmark 的 OpenWorks 在企业级部署上，基本上都是基于 Oracle 数据库的，也有小企业模式，使用 Embedded DB，使用场景相对较少，这里就不介绍了。

作为管理员，我们时常需要直接使用Oracle去处理一些问题，所以我们将常用的命令及处理方法总结下来，方便大家查阅。

以下内容，尖括号内的内容，需要操作者根据实际情况进行替换，不再一一赘述。另外，以下SQL命令的执行，都需要在登录之后，

1. 登录SQLPLUS

-- 普通用户登录sqlplus <user_name>/<password> <SID>@<Oracle_Host>;
-- 管理员登录sqlplus <user_name>/<password> <SID>@<Oracle_Host> as sysdba;
# 如果设置了TNS_ADMIN变量，往往可以简写sqlplus <user_name>/<password> <SID> as sysdba;

2. 修改用户密码

-- 改密码，USER_NAME替换为实际的名字，USER_PASSWD替换为实际的密码；alter user <USER_NAME> identified by <USER_PASSWD>;-- 提交确认commit;

3. 修改归档日志

-- 关闭数据库shutdown immediate;-- 挂载数据库startup mount;-- 关闭归档alter database noarchivelog;-- 开启归档alter database archivelog;-- 修改归档路径alter system set log_archive_dest_1=‘LOCATION=/data/tb_oracle/arch’;alter system set log_archive_dest_1='location=更改以后的归档路径';-- 查看是否归档模式archive log list;-- 启动数据库alter database open;

4. 日期转换

-- 字符串转时间戳TO_TIMESTAMP_TZ('2009-3-9 17:51:23.23 -05:00', 'YYYY-MM-D HH24:MI:SS.FF TZH:TZM')-- 字符串转日期DateTO_DATE(yourdate, 'YYYY-MM-D HH24:MI:SS');-- 日期Date转字符串T0_CHAR(yourdate, 'YYYY-MM-D HH24:MI:SS');

5. 更改 Oracle 中的默认日期格式

-- 更改时间格式ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';-- 查询时间SELECT systimestamp Date_Time FROM dual;

6. 添加用户

-- OpenWorks不能使用这种方法创建用户create user USER_NAME identified by USER_PASSWORD default tablespace user temporary tablespace temp;

7. 锁定和解锁用户

-- 锁定<user_name>用户ALTER USER <user_name> ACCOUNT LOCK;-- 解锁<user_name>账户ALTER USER <user_name> ACCOUNT UNLOCK;

8. 开启 Oracle 的 EM Express

Oracle 的 EM Express 是Oracle提供的高级功能，能过通过图标等形式，观察Oracle的运行状态，资源占用等等，默认不开启，需要手动开启。

-- 转换成基于Java JET技术的EM Express，执行以下的脚本@?/rdbms/admin/execemx omx-- 转换成基于Flash技术的EM Express，执行@?/rdbms/admin/execemx emx-- 查询https和http的端口select dbms_xdb_config.gethttpsport() from dual;select dbms_xdb_config.gethttpport() from dual;-- 如果查询结果是0，那么设置端口，设置完成后，则可在浏览器中开启EM，https://oracle_host:5501/em，或者http://oracle_host:5500/emexec dbms_xdb_config.sethttpsport(5501);exec dbms_xdb_config.sethttpport(5501);exec dbms_xdb_config.setglobalportenabled(TRUE);

9. 删除废弃工区

我们一般推荐在OpenWorks中使用Project Admin去删除工区，在某些情况下可能存在，工区删除不干净、或者恢复的时候出现异常，并未成功恢复，但是系统中残留了未能正常恢复的工区文件及信息，这种情况下Project Admin无法看到并操作的时候，推荐使用以下操作，操作账户是sys。

-- 以sysdba登录sqlplus sys/<password> <SID>@<Oracle_Host> as sysdba;-- 依次执行以下语句，注意单引号和替换工区名字delete from owsys.ow_sys_project where project_name=upper('<PROJECT_NAME>'); delete from owsys.ow_sys_prj_delete where project_name=upper('<PROJECT_NAME>'); delete from owsys.ow_sys_prj_user where project_name=upper('<PROJECT_NAME>'); delete from owsys.ow_sys_prj_archive where project_name=upper('<PROJECT_NAME>'); delete from owsys.ow_sys_prj_bkup_log where project_name=upper('<PROJECT_NAME>'); delete from owsys.ow_sys_prj_bkup_freq where project_name=upper('<PROJECT_NAME>');select * from dba_data_files where tablespace_name=upper('<PROJECT_NAME>');drop tablespace &1 including contents cascade constraints; drop user <PROJECT_NAME> cascade;drop role manage_<PROJECT_NAME>;drop role interp_<PROJECT_NAME>;drop role l_interp_<PROJECT_NAME>;drop role browse_<PROJECT_NAME>;exit;

也可以把以上内容写成sql脚本文件然后执行这个脚本，脚本如下，名字取为del_prj.sql：

set line 400set pagesize 400delete from owsys.ow_sys_project where project_name=upper('&1'); delete from owsys.ow_sys_prj_delete where project_name=upper('&1'); delete from owsys.ow_sys_prj_user where project_name=upper('&1'); delete from owsys.ow_sys_prj_archive where project_name=upper('&1'); delete from owsys.ow_sys_prj_bkup_log where project_name=upper('&1'); delete from owsys.ow_sys_prj_bkup_freq where project_name=upper('&1');select * from dba_data_files where tablespace_name=upper('&1');drop tablespace &1 including contents cascade constraints; drop user &1 cascade;drop role manage_&1;drop role interp_&1;drop role l_interp_&1;drop role browse_&1;disconn;exit;

执行脚本：

-- 以sysdba登录，并执行脚本sqlplus sys/<password> <SID>@<Oracle_Host> as sysdba @<full_path_of_script> <PROJECT_NAME>;

10. 清理AQ

正常情况下，OpenWorks有一个定期清理AQ（Advanced Queuing）消息的机制，默认是每小时清理一次，但是某些情况（用户修改不提交，系统bug等等）会造成AQ卡死，使AQ达到上百万甚至千万条。这种情况下，需要管理员手动进行清理AQ。

-- 以owsys用户登录！sqlplus owsys/owsys@<OWSID>;-- 先查看client_qtab中还有多少消息；select count(*) from client_qtab;-- 执行手工清理DECLARE po_t dbms_aqadm.aq$_purge_options_t;BEGIN dbms_aqadm.purge_queue_table('CLIENT_QTAB', NULL, po_t);END;/
-- 再次查询结果，看一下清理是否有效select count(*) from client_qtab;