17认证网-- 普通用户登录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;
-- 改密码，USER_NAME替换为实际的名字，USER_PASSWD替换为实际的密码；alter user <USER_NAME> identified by <USER_PASSWD>;-- 提交确认commit;
-- 关闭数据库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;
-- 字符串转时间戳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');
-- 更改时间格式ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';-- 查询时间SELECT systimestamp Date_Time FROM dual;
-- OpenWorks不能使用这种方法创建用户create user USER_NAME identified by USER_PASSWORD defaulttablespace user temporary tablespace temp;
-- 锁定<user_name>用户ALTER USER <user_name> ACCOUNT LOCK;-- 解锁<user_name>账户ALTER USER <user_name> ACCOUNT UNLOCK;
-- 转换成基于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);
-- 以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;
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>;
-- 以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;
想了解更多干货，可通过下方扫码关注
可扫码添加上智启元官方客服微信👇
