常用Oracle SQL
1.删除所有的表
create or replace PROCEDURE SP_DROPTABLE
(v_begintime in varchar2, v_endtime in varchar2)
as
v_tablename varchar2(256);
cursor cur_tablename is select object_name from user_objects where object_type = ‘TABLE‘ and to_char(CREATED, ‘yyyy-mm-dd‘) between v_begintime and v_endtime order by CREATED desc;
begin
open cur_tablename;
loop fetch cur_tablename into v_tablename; exit when cur_tablename%notfound; execute immediate ‘drop table ‘ || v_tablename || ‘‘; end loop;
close cur_tablename;
end sp_droptable;
---------------------------------------------------------- begin
SP_DROPTABLE(‘1900-01-01‘,‘2014-12-31‘);
end;
2.Oracle查询锁表信息并结束进程.
--Oracle查询锁表信息并结束进程
SELECT A.OWNER 方案名, A.OBJECT_NAME 表名, B.XIDUSN 回滚段号, B.XIDSLOT 槽号, B.XIDSQN 序列号, B.SESSION_ID 锁表SESSION_ID, B.ORACLE_USERNAME 锁表用户名, decode(D.type,‘XR‘,‘NULL‘,‘RS‘,‘SS(Row-S)‘,‘CF‘,‘SS(Row-S)‘, ‘TM‘, ‘TABLE LOCK‘, ‘PW‘, ‘TABLE LOCK‘, ‘TO‘, ‘TABLE LOCK‘, ‘TS‘, ‘TABLE LOCK‘, ‘RT‘, ‘ROW LOCK‘, ‘TX‘, ‘ROW LOCK‘,‘MR‘,‘S(Share)‘,NULL) 锁定方式, C.MACHINE 用户组, C.TERMINAL 机器名, B.OS_USER_NAME 系统用户名, B.PROCESS 系统进程id, DECODE(C.STATUS,‘INACTIVE‘,‘不活动‘,‘ACTIVE‘,‘活动‘) 活动情况 , C.SERVER, C.SID, C.SERIAL#, C.PROGRAM 连接方式, C.LOGON_TIME FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C, v$lock d WHERE ( A.OBJECT_ID = B.OBJECT_ID ) AND (B.PROCESS = C.PROCESS ) and C.sid = d.sid and B.LOCKED_MODE = D.LMODE ORDER BY 1,2;
alter system kill session ‘50, 3436‘; 139 : sid 182 : sertal#
3. 数据库导入导出
exp gdaphp/[email protected]/fruitshop file=D:\ExportData\gdaphp20130701.dmp
imp gdaphp/[email protected]/fruitstest file=D:\ExportData\gdaphp20130620.dmp
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。