常用语句
常用语句 导出数据库语句 创建用户语句 用户授权语句执行存储过程人员单位地点同步创建DB_LINK删除用户及其名下的表查看表空间和利用率查看某一表空间下的用户创建表空间Linux查看tomcat日志Linux打压缩包命令触发器PermGen space问题解决
导出数据库语句
exp ck_dzyh/ck_dzyh@172.16.100.135:1521/shitan file = d:/dzyh20221205.dmp
创建用户语句
create user 用户名 default tablespace googosoft identified by 密码;
用户授权语句
grant dba,create session,create table to 用户名;
grant select any table to 用户名;
grant update any table to 用户名;
grant delete any table to 用户名;
grant insert any table to 用户名;
执行存储过程
EXECUTE 存储过程名;
人员单位地点同步
create or replace procedure proc_sys_data_sync as
ingter int;
v_cnt number;
v_dwjc number;
begin
for item in (select rybh, rygh, xm, dwbh, xb, ryzt, mm
from zcgl.gx_sys_ryb) loop
select count(1) into ingter from gx_sys_ryb where rybh = item.rybh;
if ingter = 0 then
insert into gx_sys_ryb
(rybh, rygh, xm, dwbh, xb, ryzt, mm)
values
(item.rybh,
item.rygh,
item.xm,
item.dwbh,
item.xb,
item.ryzt,
item.mm);
end if;
if ingter <> 0 then
update gx_sys_ryb
set (rybh, rygh, xm, dwbh, xb, ryzt, mm) =
(select item.rybh,
item.rygh,
item.xm,
item.dwbh,
item.xb,
item.ryzt,
item.mm
from dual)
where rybh = item.rybh;
end if;
end loop;
for item in (select ddbh,sjdd,dwbh,ddzt,ddjc,pxxh,ddh,mc
from zcgl.zc_sys_ddb) loop
select count(1) into ingter from dzny_sys_ddb where ddbh = item.ddbh;
if ingter = 0 then
insert into dzny_sys_ddb
(ddbh,sjdd,dwbh,ddzt,ddjc,pxxh,ddh,mc)
values
(item.ddbh,
item.sjdd,
item.dwbh,
item.ddzt,
item.ddjc,
item.pxxh,
item.ddh,
item.mc);
end if;
if ingter <> 0 then
update dzny_sys_ddb
set (ddbh,sjdd,dwbh,ddzt,ddjc,pxxh,ddh,mc) =
(select item.ddbh,
item.sjdd,
item.dwbh,
item.ddzt,
item.ddjc,
item.pxxh,
item.ddh,
item.mc
from dual)
where ddbh = item.ddbh;
end if;
end loop;
for item in (select dwbh, sjdw, nvl(bmh,dwbh) bmh, mc, dwxz, dwzt from zcgl.gx_sys_dwb) loop
select count(1) into ingter from gx_sys_dwb where dwbh = item.dwbh;
if ingter = 0 then
insert into gx_sys_dwb
(dwbh, sjdw, bmh, mc, dwxz, dwzt, dwjc,sysbz)
values
(item.dwbh,
item.sjdw,
item.bmh,
item.mc,
item.dwxz,
item.dwzt,
'1',
'1');
end if;
if ingter <> 0 then
update gx_sys_dwb
set (dwbh, sjdw, bmh, mc, dwxz, dwzt) =
(select item.dwbh,
item.sjdw,
item.bmh,
item.mc,
item.dwxz,
item.dwzt
from dual)
where dwbh = item.dwbh;
end if;
end loop;
commit;
update gx_sys_dwb set dwjc = 1 where sjdw = '000000';
update gx_sys_dwb set dwjc = 0 where dwjc <> 1;
select count(*)
into v_cnt
from gx_sys_dwb
where sjdw in (select dwbh from gx_sys_dwb where dwjc = 1);
v_dwjc := 1;
while v_cnt > 0 loop
update gx_sys_dwb d
set dwjc =
(v_dwjc + 1)
where sjdw in (select dwbh from gx_sys_dwb where dwjc = v_dwjc);
v_dwjc := v_dwjc + 1;
select count(*)
into v_cnt
from gx_sys_dwb
where sjdw in (select dwbh from gx_sys_dwb where dwjc = v_dwjc);
end loop;
commit;
end;
创建DB_LINK
create public database link RENSHI_LINK
connect to renshi identified by "renshi"
using '172.16.97.26/szhpt';
删除用户及其名下的表
DROP USER 用户名 CASCADE;
查看表空间和利用率
SELECT a.tablespace_name "表空间名",
total / 1024 / 1024 "表空间大小单位M",
free / 1024 / 1024 "表空间剩余大小单位M",
(total - free) / 1024 / 1024 "表空间使用大小单位M",
Round((total - free) / total, 4) * 100 "使用率 [[%]]"FROM
(SELECT tablespace_name, Sum(bytes) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
Sum(bytes) total
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name;
查看某一表空间下的用户
select distinct s.owner from dba_segments s where s.tablespace_name ='USERS';
创建表空间
CREATE TABLESPACE GOOGOSOFT DATAFILE 'GOOGOSOFT.DBF' SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE 20480M EXTENT MANAGEMENT LOCAL;
Linux查看tomcat日志
tail -f catalina.out
Linux打压缩包命令
zip -r dzyh.zip dzyh
触发器
CREATE OR REPLACE TRIGGER TYSYS_DZYH_DWB_TRIGGER
after insert or update or delete on GX_SYS_DWB
for each row
declare
cnt number;
pragma autonomous_transaction;
-- local variables here
begin
if inserting then
select count(1) into cnt from DZYH.GX_SYS_DWB where DWBH = :new.DWBH;
if(cnt=0) then
insert into DZYH.GX_SYS_DWB(
DWBH,MC,JC,DZ,DWXZ,JLRQ,DWLD,FGLD,SJDW,DWZT,PXXH,BZ,BMH,BMSX,DWJC
)
values (:new.DWBH ,:new.MC ,:new.JC,:new.DZ,:new.DWXZ,:new.JLRQ,:new.DWLD,:new.FGLD,:new.SJDW,:new.DWZT,:new.PXXH,:new.BZ,:new.BMH,:new.BMSX,:new.DWJC );
else
update DZYH.GX_SYS_DWB set DWBH =:new.DWBH,MC =:new.MC,JC =:new.JC,DZ =:new.DZ,DWXZ =:new.DWXZ,JLRQ =:new.JLRQ,DWLD =:new.DWLD,FGLD =:new.FGLD,SJDW =:new.SJDW,DWZT =:new.DWZT,PXXH =:new.PXXH,BZ =:new.BZ,BMH =:new.BMH,BMSX =:new.BMSX,DWJC =:new.DWJC;
end if;
end if;
commit;
end;
PermGen space问题解决
双击tomcat，点击图中位置
在图中位置添加-Xms256m -Xmx1024m -XX:MaxNewSize=512m -XX:MaxPermSize=512m
Eclipse设置热部署
热部署 : 就是容器状态在运行的情况下重新部署整个项目.在这种情况下一般整个内存会清空,重新加载.简单来说就是Tomcat或者其他的web服务器会帮我们重新加载项目.这种方式可能会造成sessin丢失等情况.
热加载 : 就是容器状态在运行的情况下重新加载改变编译后的类.在这种情况下内存不会清空,sessin不会丢失,但容易造成内存溢出,或者找不到方法。因为内存无法转变成对像. 一般改变类的结构和模型就会有异常，在已经有的变量和方法中改变是不会出问题的
1.双击Tomcat，打开Tomcat的配置界面
配置界面如图所示
2.点击图中位置
如果Modules auto reload by default已经选中，需要点击取消
Oracle锁表
查看是否被锁表
select p.spid,c.object_name,b.session_id,b.oracle_username,b.os_user_name from v$process p,v$session a, v$locked_object b,all_objects c where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id ;
查询锁表原因
select l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
s.user#,
l.os_user_name,
s.machine,
s.terminal,
a.sql_text,
a.action
from v$sqlarea a, v$session s, v$locked_object l
where l.session_id = s.sid and s.prev_sql_addr = a.address order by sid, s.serial#;
查看被锁的表
select a.object_name,b.session_id,c.serial#,c.program,c.username,c.command,c.machine,c.lockwait from all_objects a,v$locked_object b,v$session c where a.object_id=b.object_id and c.sid=b.session_id;
解锁方法
alter system kill session '上面查询出来的spid';
