--查询表锁
select S.MACHINE,S.PROGRAM,O.OBJECT_NAME from v$locked_object L,dba_objects O,v$session S where L.OBJECT_ID=O.Object_Id and O.Object_Type='TABLE' and L.SESSION_ID=S.SID;
--创建序列
DROP SEQUENCE PMS_CODE;
CREATE SEQUENCE PMS_CODE
INCREMENT BY 1
START WITH 40000000
MAXVALUE 999999999999
NOCYCLE
NOCACHE;

select PMS_CODE.NEXTVAL from dual;
select LPAD(PMS_CODE.CURRVAL,12,'0') from dual;
select LPAD(PMS_CODE.NEXTVAL,12,'0') from dual;

--查询归档日志
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss:ff';
alter session set nls_date_format='yyyy-mm-dd';

begin
sys.dbms_logmnr_d.build('dic.dat','/oraarchlog/logmnr');
end;
/

begin
sys.dbms_logmnr.add_logfile('/oraarchlog/1_78.dbf',sys.dbms_logmnr.new);
end;
/

begin
sys.dbms_logmnr.add_logfile('/oraarchlog/1_79.dbf',sys.dbms_logmnr.addfile);
end;
/

begin
sys.dbms_logmnr.add_logfile('/oraarchlog/1_79.dbf',sys.dbms_logmnr.removefile);
end;
/

begin
sys.dbms_logmnr.start_logmnr('/oraarchlog/logmnr/dic.dat');
end;
/

begin
sys.dbms_logmnr.start_logmnr(0,0,to_date('2008-11-25 1:1;1','yyyy-mm-dd hh24:mi:ss'),to_date('2008-11-25 23:59;59','yyyy-mm-dd hh24:mi:ss'),'/oraarchlog/logmnr/dic.dat');
end;
/

create table temp as select * from V$LOGMNR_CONTENTS;

begin
sys.dbms_logmnr.end_logmnr;
end;
/
--PMS-GIS数据同步操作
create or replace function GIS_XML(sTab in VARCHAR2, sUri in VARCHAR2) return varchar2 is
xmlc varchar2(4000);
sqlbuf varchar2(512);
buf varchar2(4000);
field varchar2(32);
ftype varchar2(32);
cursor c_tab is select cname,coltype from col where tname=sTab order by cname;
begin
open c_tab;
xmlc:='';
loop
fetch c_tab into field,ftype;
exit when c_tab%NOTFOUND;
xmlc:=xmlc||'<'||field||'>';
if ftype='DATE' then
sqlbuf:='select to_char('||field||',''yyyy-mm-dd'') from '||sTab||' where uri='''||sUri||'''';
else
sqlbuf:='select '||field||' from '||sTab||' where uri='''||sUri||'''';
end if;
execute immediate sqlbuf into buf;
xmlc:=xmlc||buf;
xmlc:=xmlc||'';
end loop;
close c_tab;
return(xmlc);
exception
when others then
return null;
end GIS_XML;
/
create or replace procedure GIS_TO_PMS(sDw in VARCHAR2,nOpt in NUMBER,sTab in VARCHAR2,sUri in VARCHAR2) is
err_msg VARCHAR2(256);
e_opt_invalid EXCEPTION;
e_dw_invalid EXCEPTION;
buf VARCHAR2(4000);
sqlbuf VARCHAR2(2048);
guid VARCHAR2(38);
begin
--验证数据修改类型
if NOT(nOpt = 1 or nOpt = 2 or nOpt = 3) then
raise e_opt_invalid;
end if;
--验证单位
select value into buf from adam_options where rownum=1;
if buf<>sDw then
raise e_dw_invalid;
end if;
sqlbuf :='select dwid from '|| sTab || ' where uri='''|| sUri || '''';
EXECUTE IMMEDIATE sqlbuf into buf;
if buf<>sDw then
raise e_dw_invalid;
end if;
--验证表
select tname into buf from tab where tname=sTab;
--取出修改数据的DATA_GUID
sqlbuf :='select data_guid from '|| sTab || ' where uri='''|| sUri || '''';
EXECUTE IMMEDIATE sqlbuf into buf;
--生成修改记录的DATA_GUID
select sys_guid() into guid from dual;
--写入数据
savepoint s1;
insert into gis_pms_update_data(nr_data_guid,data_guid,xgsj,flag,xgbz,dwid,xgtablename,seg_flag,xgrenpos,uri)values(buf,guid,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),0,nOpt,sDw,sTab,'GIS-TO-PMS','后台处理',sUri);
update gis_pms_update_data set xgren=SYS_CONTEXT('USERENV','TERMINAL'),xgrenjq=SYS_CONTEXT('USERENV','IP_ADDRESS'),xgnr=GIS_XML(sTab,sUri) where data_guid=guid;
commit;
exception
WHEN e_opt_invalid then
dbms_output.put_line('数据操作类型不合法');
WHEN e_dw_invalid then
dbms_output.put_line('数据单位不合法');

WHEN OTHERS then
err_msg := SUBSTR(SQLERRM, 1, 255);
dbms_output.put_line(err_msg);
rollback;
end GIS_TO_PMS;
/

--查询低效率SQL语句
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>0 AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 ORDER BY 4 DESC;

--计算日期
select sysdate,floor(mod(to_number(to_char(sysdate,'sssss')),3600)/60) a,mod(floor(mod(to_number(to_char(sysdate,'sssss')),3600)/60),5) b,
floor(mod(to_number(to_char(sysdate,'sssss')),3600)/60)-mod(floor(mod(to_number(to_char(sysdate,'sssss')),3600)/60),5) c from dual;

--查询重复记录
select * from (select dwid,pdl_user,key_yhbh,pdl_id,row_number() over(partition by pdl_user,key_yhbh,pdl_id order by pdl_user,key_yhbh,pdl_id)
as row_num from gkgyhkg) where row_num > 1;

--安全的字符转数字
create or replace function TO_NUM_FROM_STR(strVal in VARCHAR2) return number is
Result number;
begin
Result:=TO_NUMBER(strVal);
return(Result);
exception
when others then
return null;
end TO_NUM_FROM_STR;

--查询不合法的日期
declare
sqlstr varchar2(2048);
ctable integer;
cdate integer;
row_pro integer;
tname varchar2(40);
cname varchar2(40);
--dw varchar2(20);
data varchar(20);
begin
--sqlstr:='select tname from tab where tabtype='||''''||'TABLE'||''''||' and tname not like ';
--sqlstr:= sqlstr ||''''||'ADAM_%'||''''||' and tname not like '||''''||'PARA%'||'''';
--select tname,cname from col where tname in (select tname from tab where tabtype='TABLE'
--and tname not like 'PARA%' and tname not like 'ADAM_%') and coltype='DATE'

sqlstr:='select tname,cname from col where tname in (select tname from tab where tabtype='||''''||'TABLE'||'''';
sqlstr:= sqlstr || 'and tname not like '||''''||'PARA%'||''''||' and tname not like ';
sqlstr:= sqlstr || ''''||'ADAM_%'||''''||') and coltype='||''''||'DATE'||'''';
--dbms_output.put_line(sqlstr);

ctable:=dbms_sql.open_cursor();
cdate:= dbms_sql.open_cursor();
dbms_sql.parse(ctable,sqlstr,dbms_sql.native);
dbms_sql.define_column(ctable,1,tname,40);
dbms_sql.define_column(ctable,2,cname,40);

row_pro:=dbms_sql.execute(ctable);

loop
row_pro:=dbms_sql.fetch_rows(ctable);
if row_pro <= 0 then
exit;
end if;
dbms_sql.column_value(ctable,1,tname);
dbms_sql.column_value(ctable,2,cname);
--
--sqlstr:='update '||tname||' set '||cname||'=null where '||cname ||'< to_date(';
--sqlstr:= sqlstr ||''''||'1000-1-1'||''''||','||''''||'yyyy-mm-dd'||''''||')';
--dbms_output.put_line(sqlstr);
--
sqlstr:='select to_char('||cname||','||''''||'yyyy-mm-dd'||''''||') from '||tname||' where '||cname|| ' < to_date('||''''||'1950-01-01'||''''||',';
sqlstr:= sqlstr||''''||'yyyy-mm-dd'||''''||')';
dbms_sql.parse(cdate,sqlstr,dbms_sql.native);
--dbms_sql.define_column(cdate,1,dw,20);
dbms_sql.define_column(cdate,1,data,20);
row_pro:=dbms_sql.execute(cdate);
loop <>
row_pro:=dbms_sql.fetch_rows(cdate);
if row_pro <= 0 then
exit;
end if;
--dbms_sql.column_value(cdate,1,dw);
dbms_sql.column_value(cdate,1,data);
sqlstr:=tname||' '||cname|| ' '||data;
dbms_output.put_line(sqlstr);
end loop a;
end loop;
dbms_sql.close_cursor(ctable);
end dealdate;
/

--查询主关键字
select * from
(select a.table_name,a.column_name from user_cons_columns@bjpb a,
(select constraint_name from user_constraints@bjpb where owner='BJ_EFGIS_DBA' and constraint_type='P' and table_name in ('GKGYHBH','GKGYHBLQ','GKGYHBYQ','GKGYHDLHGQ','GKGYHDRQ',
'GKGYHHW','GKGYHKG','GKGYHKGG','GKGYHPT','GKGYHUPS','GKGYHZFD','GKZYDZ')) b
where a.constraint_name=b.constraint_name)
minus
(select a.table_name,a.column_name from user_cons_columns a,
(select constraint_name from user_constraints where owner='BJ_EFGIS_DBA' and constraint_type='P' and table_name in ('GKGYHBH','GKGYHBLQ','GKGYHBYQ','GKGYHDLHGQ','GKGYHDRQ',
'GKGYHHW','GKGYHKG','GKGYHKGG','GKGYHPT','GKGYHUPS','GKGYHZFD','GKZYDZ')) b
where a.constraint_name=b.constraint_name)