----------------------------------
c:\windows\system32;D:\Program Files\MySQL\MySQL Server 5.5\bin;%JAVA_HOME%\bin;D:\Program Files\TortoiseSVN\bin;%M2_HOME%\bin;
alter table T_JDZH_WTBD add WORKFLOW_INSTANCE_ID number(22);
comment on column T_JDZH_WTBD.WORKFLOW_INSTANCE_ID is '流程实例ID';
alter table T_XMGL_AQJDJHCYDWQK rename to T_GCGL_AQJDJHCYDWQK;
comment on table T_GCGL_AQJDJHCYDWQK is '安全监督计划参与单位情况';
alter table T_XTZD_ZDWXYTZZX rename column TXX to TZZX;
comment on column T_XTZD_ZDWXYTZZX.TZZX is '特征值项';
alter table T_QYRYGL_RYJBXX modify ZP BLOB;
alter table T_GCGL_ZLJDZJDGZJH modify JDPC NVARCHAR2(100);
alter table t_platform_fields add constraint pk_t_platform_fields primary key(id);
alter table T_XMGL_JSDWBG modify XMID not null;
alter table PSN_SI_ADDINSURANCE drop constraint PSN_SI_ADDINSURANCE_PK;
alter table T_YYZC_HMXM drop column SFBZDXWSSB;
alter table T_XMGL_DWGCXX modify (SFWCZLBJ CHAR(1) default '0');
rename SEQ_T_XMGL_AQJDJHCYDWQK to SEQ_T_GCGL_AQJDJHCYDWQK;
alter index IDX_WATER_FEE_DOOR_KZRQ unusable;
create index IDX_T_ZHYWBL_RKHXX_XZQHDM on T_ZHYWBL_RKHXX (XZQHDM)
tablespace HMZDGC_WHS_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 256K
minextents 1
maxextents unlimited
);
create bitmap index IDX_T_ZHYWBL_RKHXX_SFYX on T_ZHYWBL_RKHXX (SFYX)
tablespace HMZDGC_WHS_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 256K
minextents 1
maxextents unlimited
);
create index IDX_BZDXSB_SUBSTR_XZQHDM6 on T_ZHYWBL_BZDXSB (SUBSTR(XSXZQHDM,1,6))
tablespace HMZDGC_WHS_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 24M
minextents 1
maxextents unlimited
);
drop table 表名 cascade constraints;
select t.COLUMN_NAME||',' from user_tab_columns t where t.TABLE_NAME = 'T_PSN_BASE_INFO'
select * from psn_fund_payer_info;
alter table psn_fund_payer_info rename to psn_fund_payer_info_2;
select object_id,session_id,locked_mode from v$locked_object;
select distinct t2.username,t2.sid,t2.serial#,t3.SQL_TEXT,t2.logon_time,'alter system kill session '''||t2.sid||','||t2.serial#||''';'
from v$locked_object t1,v$session t2,v$sql t3
where t1.session_id=t2.sid and t3.SQL_ID = t2.SQL_ID order by t2.logon_time;
alter system kill session '262,331';
alter system kill session '439,100';
CREATE table t_user_info_1216 LIKE t_user_info;
INSERT INTO t_user_info_1216 SELECT * FROM t_user_info;
create database link DBLINK_Analytic_TEST
connect to analytic identified by analyticDB12345
using '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.103)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SID = testorcl)))';
机构代码:xxxxx 机构名称:xxxxxxxx
修改内容:
法人:田小虎
有效期:2012/4/2
xxxx:xxxxx
xxxx:xxxxx
xxxx:xxxxx
select t.SQL_ID from v$session t where t.SID = 246;
select * from v$sql t where t.SQL_ID = 'ccr4th0rbq6hm'
select * from sended_message t WHERE t.telephone='13349211007' order by t.send_time desc
select * from user_tab_comments t
select b.owner TABLEOWNER, b.object_name TABLENAME, c.OSUSER LOCKBY, c.USERNAME LOGINID, c.sid SID, c.SERIAL# SERIAL,'alter system kill session '''||c.sid||','||c.serial#||''';'
from v$locked_object a,dba_objects b, v$session c
where b.object_id = a.object_id AND a.SESSION_ID =c.sid;
select spid, osuser, s.program from v$session s,v$process p where
s.paddr=p.addr and s.sid=376;
orakill orcl 2964;
orakill cos 2248;
kill -9 18992;
select username,sid,serial#,vs.machine,'alter system kill session '''||vs.SID||','||vs.SERIAL#||''';' from v$session vs where vs.username = 'HMZDGC_WHS'
select utl_inaddr.get_host_address(s.TERMINAL) from v$session s
select * from V$DB_OBJECT_CACHE where owner='HMZDGC_WHS' AND LOCKS!=0 and type like '%PACKAGE%';
select * from v$access where owner='HMZDGC_WHS' and object='PACKAGE_HMXMYW'
select vs.SID,vs.SERIAL#,vs.PADDR,voc.NAME,voc.TYPE,'alter system kill session '''||vs.SID||','||vs.SERIAL#||''';'
from V$DB_OBJECT_CACHE voc
join v$access va on voc.NAME = va.OBJECT and voc.OWNER = va.OWNER
join v$session vs on vs.SID = va.SID
where voc.OWNER = 'ETLRES' and voc.LOCKS != 0 and voc.TYPE like '%PACKAGE%';
SELECT bs.username "Blocking User",
bs.username "DB User",
ws.username "Waiting User",
bs.SID "SID",
ws.SID "WSID",
bs.serial# "Serial#",
bs.sql_address "address",
bs.sql_hash_value "Sql hash",
bs.program "Blocking App",
ws.program "Waiting App",
bs.machine "Blocking Machine",
ws.machine "Waiting Machine",
bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User",
bs.serial# "Serial#",
ws.serial# "WSerial#",
DECODE(wk.TYPE,
'MR',
'Media Recovery',
'RT',
'Redo Thread',
'UN',
'USER Name',
'TX',
'Transaction',
'TM',
'DML',
'UL',
'PL/SQL USER LOCK',
'DX',
'Distributed Xaction',
'CF',
'Control FILE',
'IS',
'Instance State',
'FS',
'FILE SET',
'IR',
'Instance Recovery',
'ST',
'Disk SPACE Transaction',
'TS',
'Temp Segment',
'IV',
'Library Cache Invalidation',
'LS',
'LOG START OR Switch',
'RW',
'ROW Wait',
'SQ',
'Sequence Number',
'TE',
'Extend TABLE',
'TT',
'Temp TABLE',
wk.TYPE) lock_type,
DECODE(hk.lmode,
0,
'None',
1,
'NULL',
2,
'ROW-S (SS)',
3,
'ROW-X (SX)',
4,
'SHARE',
5,
'S/ROW-X (SSX)',
6,
'EXCLUSIVE',
TO_CHAR(hk.lmode)) mode_held,
DECODE(wk.request,
0,
'None',
1,
'NULL',
2,
'ROW-S (SS)',
3,
'ROW-X (SX)',
4,
'SHARE',
5,
'S/ROW-X (SSX)',
6,
'EXCLUSIVE',
TO_CHAR(wk.request)) mode_requested,
TO_CHAR(hk.id1) lock_id1,
TO_CHAR(hk.id2) lock_id2,
DECODE(hk.BLOCK,
0,
'NOT Blocking',
1,
'Blocking',
2,
'Global',
TO_CHAR(hk.BLOCK)) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.SID = bs.SID(+)
AND wk.SID = ws.SID(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> 'SYSTEM')
AND (bs.username <> 'SYS')
ORDER BY 1;
create or replace directory MY_DIR
as 'D:\EXPORT_EXCEL';
select Sum(bytes)/1024/1024/1024 from user_segments t where t.segment_type = 'TABLE' and (t.segment_name like 'PSN_SI%' or t.segment_name like 'PSN_SOCIAL%') and t.segment_name != 'PSN_SI_DETIAIL_BACK20140402'
select Sum(bytes)/1024/1024/1024 from user_extents t where t.segment_type = 'TABLE' and (t.segment_name like 'PSN_SI%' or t.segment_name like 'PSN_SOCIAL%') and t.segment_name != 'PSN_SI_DETIAIL_BACK20140402' ;
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
select T.column_name||','||'
begin
dbms_scheduler.CREATE_JOB(
job_name => 'dump_psn_si',
job_type => 'PLSQL_BLOCK',
JOB_ACTION => 'BEGIN pkg_a_数据同步.PRC_A_数据同步; END;',
start_date => to_date('2014-06-12','yyyy-mm-dd'),
repeat_interval => 'freq=MONTHlY; BYMONTHDAY=12,27',
end_date => NULL,
enabled => TRUE,
comments => 'Calls PLSQL monthly;when 01-12,01-27...'
);
end;
begin
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => 'AHSIMISCX.DUMP_PSN_SI',
attribute => 'restartable',
value=> TRUE
);
end;
SIMPLIFIED CHINESE_CHINA.ZHS16GBK 包含 AMERICAN_AMERICA.AL32UTF8.
SQL> conn /as sysdba
SQL> shutdown immediate;
SQL> startup mount
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> alter database open;
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
ORA-12712: new character set must be a superset of old character set
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
SQL> select * from v$nls_parameters;
SQL> shutdown immediate;
SQL> startup
SQL> select * from v$nls_parameters;
begin
sys.dbms_job.submit(job => :job,
what => 'pkg_a_数据同步.PRC_A_数据同步;',
next_date => to_date('01-01-4000', 'dd-mm-yyyy'),
interval => 'trunc(sysdate) + 1');
commit;
end;
explain plan for
delete from PSN_SI_ADDINSURANCE nologging;
select * from table(dbms_xplan.display);
alter table biz_bus_test enable row movement;
ALTER TABLE biz_bus_test SHRINK SPACE CASCADE;
drop materialized view log on table_a;
begin
for t2 in (
select p2.f_phone f_phone,p2.m_phone m_phone,p2.idcard idcard from psn_education p1 join psn_student_temp p2 on p1.idcard=p2.idcard
) loop
update psn_education p3 set
p3.father_tel=t2.f_phone,
p3.mother_tel=t2.m_phone
where p3.idcard=t2.idcard;
end loop;
end;