oracle常用SQL语句

–系统变量path
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’);

–修改sequence名
rename SEQ_T_XMGL_AQJDJHCYDWQK to SEQ_T_GCGL_AQJDJHCYDWQK;

–把索引改成无效
alter index IDX_WATER_FEE_DOOR_KZRQ unusable;

–增加B-Tree 索引
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
);

–增加Bitmap 索引
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;
–alter table psn_fund_payer_info_2 rename to psn_fund_payer_info;

–杀死死锁的session
select object_id,session_id,locked_mode from vlocked_object;   
select distinct t2.username,t2.sid,t2.serial#,t3.SQL_TEXT,t2.logon_time,’alter system kill session ”’||t2.sid||’,’||t2.serial#||”’;’  
from vlocked_object t1,vsessiont2,vsql 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’;

–查看导致锁死的语句
select t.SQL_ID from v$session t where t.SID = 246;

select * from v$sql t where t.SQL_ID = ‘ccr4th0rbq6hm’

–查找被锁死的表
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 vlockedobjecta,dbaobjectsb,vsession c
where b.object_id = a.object_id AND a.SESSION_ID =c.sid;

–OS 级别强制KILL
select spid, osuser, s.program from vsessions,vprocess p where
s.paddr=p.addr and s.sid=376;
orakill orcl 2964;
orakill cos 2248;

kill -9 18992;

–强制杀死登录的session
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 VDBOBJECTCACHEwhereowner=′HMZDGCWHS′ANDLOCKS!=0andtypelike‘select∗fromvaccess 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 VDBOBJECTCACHEvocjoinvaccess 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’, /*/ / Not blocking any other processes */
1,
‘Blocking’, /*/ / This lock blocks other processes */
2,
‘Global’, /*/ / This lock is global, so we can’t tell */
TO_CHAR(hk.BLOCK)) blocking_others
FROM vlockhk,vsession bs, vlockwk,vsession 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;

–创建DIR目录
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||’,’||’ –’||t.comments from user_col_comments t where t.table_name = ‘PSN_PERSON’;

–创建JOB
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;

–设置job运行失败后继续运行
begin
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => ‘AHSIMISCX.DUMP_PSN_SI’,
attribute => ‘restartable’,
value=> TRUE
);
end;

–修改服务器端字符集AL32UTF8到ZHS16GBK
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;
–我们看到这个过程和之前ALTER DATABASE CHARACTER SET操作的内部过程是完全相同的，也就是说INTERNAL_USE提供的帮助就是使Oracle数据库绕过了子集与超集的校验.
SQL> select * from vnlsparameters;SQL>shutdownimmediate;SQL>startupSQL>select∗fromvnls_parameters;
–以后安装oracle11g的时候记得选择自定义安装，把这个字符集的事情事先弄好。

–创建DBMS_JOB
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 /+ append parallel(PSN_SI_ADDINSURANCE,16) / PSN_SI_ADDINSURANCE nologging;

select * from table(dbms_xplan.display);

–调整表HVM水位线
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;