Oracle 相关知识记录
查表空间使用率
SELECT tn "表空间",fileb "总大小(M)",freeb "可用大小(M)",used "已用(M)",used_p "使用率(%)",free_b "可用率(%)"
FROM (
select tn,fileb,freeb, fileb - freeb used
,trunc((fileb - freeb)/fileb*100,2) used_p,trunc((freeb)/(fileb)*100,2) free_b
from(
select
f.tablespace_name tn,
ceil(sum(f.bytes)/1024/1024) fileb,
ceil(sum(t.bytes )/1024/1024) freeb
from dba_data_files f,
(select file_id,sum(bytes) bytes from dba_free_space group by file_id)t
where f.file_id = t.file_id group by f.tablespace_name
) k
) T ORDER BY T.FREE_B ;
增加表空间文件:
alter tablespace xxx add datafile 'F:\xxx.ORG' size 10240M autoextend OFF ;
order by 变慢优化
http://blog.csdn.net/xhbmj/article/details/7653132
ORACLE GOLDENGATE OGG 安装配置
http://blog.chinaunix.net/uid-10218589-id-349070.html http://ylw6006.blog.51cto.com/470441/903752/
- ORACLE中查看当前系统中锁表情况 select * from v$locked_object 可以通过查询v$locked_object拿到sid和objectid，然后用sid和v$session链表查询是哪里锁的表，用v$session中的objectid字段和dba_objects的id字段关联，查询详细的锁表情况。
查询SQL如下：
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo, dba_objects ao, v$session sess, v$process p
where ao.object_id = lo.object_id
and lo.session_id = sess.sid;
查询是什么SQL引起了锁表的原因，SQL如下：
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#;
###2. ORACLE解锁的方法
alter system kill session ’146′; –146为锁住的进程号，即spid
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;
select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
alter system kill session '137,6153';
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#;
alter system kill session '199,6326';
alter system kill session 70;
alter system kill session '199, 6326';
ALTER SYSTEM DISCONNECT SESSION '72' IMMEDIATE;
查询慢的处理
select t.*,t.rowid from wjs.query_tbs_public t where t.fid = 'GZYJMFR_CODE_DRUG' ;
ALTER SYSTEM FLUSH SHARED_POOL;
https://blogs.oracle.com/mandalika/entry/oracle_rdbms_flushing_a_single
select plan_table_output from
table(dbms_xplan.display_cursor('8fn6kd4vbc5sv',null,'basic'));
select plan_table_output from
table(dbms_xplan.display_cursor('56cz4f17fv3un',null,'basic'));
select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '8fn6kd4vbc5sv';
exec DBMS_SHARED_POOL.PURGE ('AEDF1E2C, 917903131', 'C');
查询数据版本
SELECT id, VERSIONS_STARTTIME,
VERSIONS_ENDTIME,
VERSIONS_OPERATION
FROM gdyc.t_test VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
WHERE VERSIONS_STARTTIME IS NOT NULL
ORDER BY VERSIONS_STARTTIME DESC;
归档模式设置
转:http://www.cnblogs.com/spatial/archive/2009/08/01/1536429.html
同比,环比,累计值统计函数
CREATE TABLE salaryByMonth
(
employeeNo varchar2(20),
yearMonth varchar2(6),
salary number
) ;
insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY)
values (1, '200805', 500);
insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY)
values (1, '200802', 150);
insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY)
values (1, '200803', 200);
insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY)
values (1, '200804', 300);
insert into SALARYBYMONTH (EMPLOYEENO, YEARMONTH, SALARY)
values (1, '200708', 100);
commit;
SELECT EMPLOYEENO
,YEARMONTH
,SALARY
,MIN(SALARY) KEEP(DENSE_RANK FIRST ORDER BY YEARMONTH) OVER(PARTITION BY EMPLOYEENO) FIRST_SALARY -- 基比分析 salary/first_salary
,LAG(SALARY, 1, 0) OVER(PARTITION BY EMPLOYEENO ORDER BY YEARMONTH) AS PREV_SAL -- 环比分析，与上个月份进行比较
,LAG(SALARY, 12, 0) OVER(PARTITION BY EMPLOYEENO ORDER BY YEARMONTH) AS PREV_12_SAL -- 同比分析，与上个年度相同月份进行比较
,SUM(SALARY) OVER(PARTITION BY EMPLOYEENO, SUBSTR(YEARMONTH, 1, 4) ORDER BY YEARMONTH RANGE UNBOUNDED PRECEDING) LJ --累计值
FROM SALARYBYMONTH
ORDER BY EMPLOYEENO
,YEARMONTH
###Oracle存储过程编译卡死的解决方法 转:http://blog.csdn.net/zgying/article/details/6252368 转:http://blog.csdn.net/jojo52013145/article/details/7470812 转:http://blog.csdn.net/zftang/article/details/6278860 转:http://zhidao.baidu.com/link?url=v2n-8SKLsfZJr9pN6gpzscZtuHRpyq8egPujqrEwSuc3yfWr-_QXDb0eWtcaBSuhRMXuWhWJhtyUgYDi1QyhBq 转:http://blog.csdn.net/Aritem/article/details/51644849
解决方法如下: 1：查V$DB_OBJECT_CACHE SELECT * FROM V$DB_OBJECT_CACHE WHERE name='CRM_LASTCHGINFO_DAY' AND LOCKS!='0'; 注意：CRM_LASTCHGINFO_DAY为存储过程的名称。 发现locks＝2， 2：按对象查出sid的值 select /+ rule/ SID from V$ACCESS WHERE object='CRM_LASTCHGINFO_DAY'; 注意：CRM_LASTCHGINFO_DAY为存储过程的名称。 3：查sid,serial# SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='刚才查到的SID'; 4：alter system kill session 'sid值,serial#值' immediate; 一 , 找到之前调试的存储过程 名称，使用一下语句找到 sid
select va.sid,va.OBJECT from v$access va where object like 'SP_%'
二 、拿到sid之后，去 v$session视图 去查询 sid,serial# SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID= 140 三、拿到 sid,serial# 之后就简单了，直接杀死 session，存储过程就可以重新编译了 alter system kill session '140,10555'
###获取insert script
CREATE OR REPLACE FUNCTION GET_INSERT_SCRIPT(V_TABLE_NAME VARCHAR2)
RETURN VARCHAR2 AS
B_FOUND BOOLEAN := FALSE;
V_TEMPA VARCHAR2 (8000);
V_TEMPB VARCHAR2 (8000);
V_TEMPC VARCHAR2 (255);
BEGIN
FOR TAB_REC IN (SELECT TABLE_NAME
FROM ALL_TABLES
WHERE TABLE_NAME = UPPER (V_TABLE_NAME)) LOOP
B_FOUND := TRUE;
V_TEMPA := 'select ''insert into ' || TAB_REC.TABLE_NAME || ' (';
FOR COL_REC IN (SELECT *
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = TAB_REC.TABLE_NAME
ORDER BY COLUMN_ID) LOOP
IF COL_REC.COLUMN_ID = 1 THEN
V_TEMPA := V_TEMPA || '''||chr(10)||''';
ELSE
V_TEMPA := V_TEMPA || ',''||chr(10)||''';
V_TEMPB := V_TEMPB || ',''||chr(10)||''';
END IF;
V_TEMPA := V_TEMPA || COL_REC.COLUMN_NAME;
IF INSTR (COL_REC.DATA_TYPE, 'CHAR') > 0 THEN
V_TEMPC := '''''''''||' || COL_REC.COLUMN_NAME || '||''''''''';
ELSIF INSTR (COL_REC.DATA_TYPE, 'DATE') > 0 THEN
V_TEMPC :=
'''to_date(''''''||to_char('
|| COL_REC.COLUMN_NAME
|| ',''mm/dd/yyyy hh24:mi'')||'''''',''''mm/dd/yyyy hh24:mi'''')''';
ELSE
V_TEMPC := COL_REC.COLUMN_NAME;
END IF;
V_TEMPB :=
V_TEMPB
|| '''||decode('
|| COL_REC.COLUMN_NAME
|| ',Null,''Null'','
|| V_TEMPC
|| ')||''';
END LOOP;
V_TEMPA :=
V_TEMPA
|| ') values ('
|| V_TEMPB
|| ');'' from '
|| TAB_REC.TABLE_NAME
|| ';';
END LOOP;
IF NOT B_FOUND THEN
V_TEMPA := '– Table ' || V_TABLE_NAME || ' not found';
ELSE
V_TEMPA := V_TEMPA || CHR (10) || 'select ''– commit;'' from dual;';
END IF;
RETURN V_TEMPA;
END;
包编辑死机，ddl解锁
Select b.SID,b.SERIAL#,A.* From dba_ddl_locks a, v$session b Where a.session_id = b.SID And a.name = 'xxx AND OWNER='xxx';
alter system kill session 'sid,SERIAL#' ;
oracle 快速彻底kill session
http://www.cnblogs.com/kerrycode/p/4034231.html (转)
根据sql_id查执行计划###
http://blog.itpub.net/28389881/viewspace-1310444/
sql语句调优###
源自: <<Oracle索引技术>>
DECLARE
tune_task VARCHAR2(30);
tune_sql CLOB;
BEGIN
tune_sql := ' select count(*) from T_test ';
tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => tune_sql
,user_name => 'cott'
,scope => 'COMPREHENSIVE'
,time_limit => 60
,task_name => 'tune_test'
,description => 'Provide SQL text'
);
DBMS_OUTPUT.put_line( tune_task );
END;
/
CALL dbms_sqltune.execute_tuning_task(task_name => 'tune_test');
select dbms_sqltune.report_tuning_task('tune_test') from dual;
select *
from dba_advisor_tasks
where created > sysdate - 1
order by created desc;
CALL dbms_sqltune.drop_tuning_task(task_name => 'tune_test');
DECLARE
task_name VARCHAR2(30);
BEGIN
dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor, 'QUICK_SQL_TUNE', 'select count(*) from emp ');
END;
/
begin
dbms_advisor.delete_task('QUICK_SQL_TUNE');
end;
select dbms_advisor.get_task_script('QUICK_SQL_TUNE') from dual;
oracle 查未提交的SQL语句
SELECT
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN
(SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.sid = '67')
ORDER BY piece ASC;
SELECT * FROM V$OPEN_CURSOR;
反转键索引模拟实验（reverse key index） 查热块争用
http://blog.csdn.net/wenzhongyan/article/details/7722159
查看哪些索引没有被使用
-- 查看哪些索引没有被使用
begin
for r in (select index_name from user_indexes) loop
execute immediate 'alter index ' || r.index_name || ' monitoring usage';
end loop;
end;
alter index P_PURCHASE_QUANTITY_ID nomonitoring usage;
select index_name,table_name,used,start_monitoring
from v$object_usage
where monitoring = 'YES' ;
-- 这种方法有些不准，可能有些sql已经不在缓存了
with in_plan_objects as (
select distinct object_name from v$sql_plan where object_owner = 'GDYC' )
SELECT TABLE_NAME ,INDEX_NAME, CASE WHEN OBJECT_NAME IS NULL THEN 'NO' ELSE 'YES' END AS IN_CACHED_PLAN
FROM USER_INDEXES LEFT OUTER JOIN IN_PLAN_OBJECTS ON (INDEX_NAME = OBJECT_NAME) ;
select * from v$object_usage;
select p.object_name c1, p.operation c2, p.options c3,count(1) c4
from dba_hist_sql_plan p, dba_hist_sqlstat s
where p.object_owner <> 'SYS'
and p.operation like '%INDEX%'
and p.sql_id = s.sql_id
group by p.object_name,p.operation, p.options order by 1,2,3;
with in_plan_objects as
(select distinct object_name from v$sql_plan where object_owner = USER)
select table_name, index_name,
case when object_name is null then 'NO' else 'YES'
end as in_cached_plan
from user_indexes left outer join in_plan_objects on (index_name = object_name);
alter index index_name monitoring usage;
select * from v$object_usage;
alter index index_name nomonitoring usage;
--分享一个使用index monitor usage功能监控索引使用情况的脚本：）
/**监控索引是否被使用*/
--create table
CREATE TABLE dbamonitor.WZ_INDEX_USAGE
( "INDEX_NAME" VARCHAR2(30) NOT NULL,
"TABLE_NAME" VARCHAR2(30) NOT NULL,
"MONITORING" VARCHAR2(3),
"USED" VARCHAR2(3),
"START_MONITORING" VARCHAR2(19),
"END_MONITORING" VARCHAR2(19),
"CAPATURE_TIME" date
);
--record index usage status(capture v$object_usage regularly，and reset V$OBJECT_USAGE used flag with NO）
$ cat monitor_index_usage.sh
#!/bin/sh
#Description:monitor index usage
#check interval unit(seconds)
_INTERVAL=300
while true
do
sqlplus -s / as sysdba << EOF
whenever sqlerror exit rollback
INSERT INTO dbamonitor.WZ_INDEX_USAGE
select io.name index_name,
t.name table_name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,
decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,
ou.start_monitoring start_monitoring,
ou.end_monitoring end_monitoring,
sysdate
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = 161 --modify this value to yours
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
AND bitand(i.flags, 65536)<>0
AND bitand(ou.flags, 1)<>0;
UPDATE sys.Object_Usage SET flags=0;
commit;
DECLARE
CURSOR cIndexes IS
SELECT index_name FROM user_indexes
WHERE index_type != 'LOB';
BEGIN
FOR rIndex IN cIndexes LOOP
EXECUTE IMMEDIATE 'ALTER INDEX
'||rIndex.index_name ||' MONITORING
USAGE';
END LOOP;
END;
查绑定变量可以获益的sql语句
with force_matched as (
select force_matching_signature,count(*) matches,
max(sql_id || child_number) max_sql_child,
dense_rank() over(order by count(*) desc ) ranking
from v$sql
where force_matching_signature <> 0
and parsing_schema_name <> 'SYS'
group by force_matching_signature
having count(*) > 5
)
select sql_id,matches,parsing_schema_name schema,sql_text
from v$sql join force_matched on ( sql_id || child_number = max_sql_child )
where ranking <= 10
order by matches desc
收缩一张表的方法
- 移动表
alter table xxx move
- 收缩表
alter table xxx enable row movement;
alter table xxx shrink space;
Oracle分析函数
http://love-flying-snow.iteye.com/blog/573083
查询排序最多的sql语句
rem ***********************************************************
rem
rem File: sql_workarea.sql
rem Description: Show statistics onsort and hash workareas (from V$SQL_WORKAREA)
rem
rem From 'Oracle Performance Survival Guide' by Guy Harrison
rem Chapter 11 Page 332
rem ISBN: 978-0137011957
rem See www.guyharrison.net for further information
rem
rem This work is in the public domain NSA
rem
rem
rem *********************************************************
/* Formatted on 2008/11/24 16:43 (Formatter Plus v4.8.7) */
set pages 1000
set lines 66
SET RECSEP WRAPPED
SET RECSEPCHAR "-"
column seconds format 9999.99
column "O/1/M" format a8
column "SQL ID - CHILD" format a16
column sql_text format a66
set echo on
WITH sql_workarea AS
(
SELECT sql_id || '-' || child_number SQL_ID_Child,
operation_type operation ,
last_execution last_exec,
ROUND (active_time / 1000000,
2) seconds,
optimal_executions || '/'
|| onepass_executions || '/'
|| multipasses_executions o1m,
' ' || SUBSTR (sql_text, 1, 155) sql_text,
RANK () OVER (ORDER BY active_time DESC) ranking
FROM v$sql_workarea JOIN v$sql
USING (sql_id, child_number) )
SELECT sql_id_child "SQL ID - CHILD",seconds,operation,
last_exec, o1m "O/1/M",sql_text
FROM sql_workarea
WHERE ranking <= 30
ORDER BY ranking;
plsql在数据库中执行时间的占比
rem ***********************************************************
rem
rem File: plsqltime_sys.sql
rem Description: Query to reveal the overhead of PLSQL within the database
rem
rem From 'Oracle Performance Survival Guide' by Guy Harrison
rem Chapter 12 Page 355
rem ISBN: 978-0137011957
rem See www.guyharrison.net for further information
rem
rem This work is in the public domain NSA
rem
rem
rem *********************************************************
col db_time_secs format 999,999,999.99
col plsql_time_secs format 999,999,999.99
col pct_plsql_time format 99.99
col execs heading "Execs"
col text heading "Line text"
set lines 100
set pages 10000
set echo on
WITH plsql_times
AS (SELECT SUM (CASE stat_name
WHEN 'DB time'
THEN value/1000000 END) AS db_time,
SUM(CASE stat_name
WHEN 'PL/SQL execution elapsed time'
THEN value / 1000000 END) AS plsql_time
FROM v$sys_time_model
WHERE stat_name IN ('DB time',
'PL/SQL execution elapsed time'))
SELECT ROUND (db_time, 2) db_time_secs,
ROUND (plsql_time, 2) plsql_time_secs,
ROUND (plsql_time * 100 / db_time, 2) pct_plsql_time
FROM plsql_times
/
/* Formatted on 3-Dec-2008 7:39:07 (QP5 v5.120.811.25008) */
SELECT sql_id,
SUBSTR (sql_text, 1, 150) AS sql_text,
ROUND (elapsed_time / 1000) AS elapsed_ms,
ROUND (plsql_exec_time / 1000) plsql_ms,
ROUND (plsql_exec_time * 100 / elapsed_time, 2) pct_plsql,
ROUND (plsql_exec_time * 100 / SUM (plsql_exec_time) OVER (), 2)
pct_total_plsql
FROM v$sql
WHERE plsql_exec_time > 0 AND elapsed_time > 0
ORDER BY plsql_exec_time DESC
通过dbms_profiler识别plsql中最耗资源的代码
declare
returncode binary_integer;
begin
returncode := dbms_profiler.start_profiler('profiler demo 2');
nocopy_test.test_copy(400,1);
returncode := dbms_profiler.stop_profiler;
dbms_output.put_line('profiler return code=' || returncode );
commit;
end;
WITH plsql_qry AS (
SELECT u.unit_name, line#,
ROUND (d.total_time / 1e9) time_ms,
round(d.total_time * 100 / sum(d.total_time) over(),2) pct_time,
d.total_occur as execs,
substr(ltrim(s.text),1,40) as text,
dense_rank() over(order by d.total_time desc) ranking
FROM plsql_profiler_runs r JOIN plsql_profiler_units u USING (runid)
JOIN plsql_profiler_data d USING (runid, unit_number)
LEFT OUTER JOIN all_source s
ON ( s.owner = u.unit_owner
AND s.TYPE = u.unit_type
AND s.NAME = u.unit_name
AND s.line = d.line# )
WHERE r.run_comment = 'Profiler Demo 2'
)
select unit_name,line#,time_ms,pct_time,execs,text
from plsql_qry
where ranking <=5
ORDER BY ranking;
用dbms_hprof 分层剖析plsql执行情况
create or replace directory hprof_dir as 'c:\\tracefiles' ;
declare
runid number;
begin
hprof_demo_pkg.init(1000);
dbms_hprof.start_profiling('HPROF_DIR','hprof_trace.trc',max_depth=>10);
hprof_demo_pkg.nightly_batch();
dbms_hprof.stop_profiling();
runid := dbms_hprof.analyze(location => 'HPROF_DIR',
filename => 'hprof_trace.trc',
run_comment => 'Hprof demo 1'
);
end;
通过 plshprof将 trc文件转成html报告
plshprof -output hprof_report hprof_trace.trc
或者通过sql查出层级调用树
with dbmshp as
(
select module || '.' || function as function ,
nvl(pci.calls,f.calls) calls,
nvl(pci.function_elapsed_time,f.function_elapsed_time) as function_elapsed_time,
nvl(pci.subtree_elapsed_time,f.subtree_elapsed_time) as subtree_elapsed_time,
f.symbolid,pci.parentsymid
from dbmshp_runs r
join dbmshp_function_info f on (r.runid = f.runid )
full outer join dbmshp_parent_child_info pci
on ( pci.runid = r.runid and pci.childsymid = f.symbolid)
where r.run_comment = 'Hprof demo 2')
select rpad(' ',level) || function as function,calls,
function_elapsed_time,
subtree_elapsed_time,
subtree_elapsed_time-function_elapsed_time as subtree_only_time
from dbmshp
connect by prior symbolid = parentsymid
start with parentsymid is null
;
前10条等待时间最长的sql
WITH sql_app_waits AS
(SELECT sql_id, SUBSTR(sql_text, 1, 80) sql_text,
application_wait_time/1000 app_time_ms,
elapsed_time,
ROUND(application_wait_time * 100 /
elapsed_time, 2) app_time_pct,
ROUND(application_wait_time * 100 /
SUM(application_wait_time) OVER (), 2)
pct_of_app_time,
RANK() OVER (ORDER BY application_wait_Time DESC)
ranking
FROM v$sql
WHERE elapsed_time > 0 AND application_wait_time>0)
SELECT sql_text, app_time_ms, app_time_pct,
pct_of_app_time
FROM sql_app_waits
WHERE ranking <= 10
ORDER BY ranking ;
查询遭遇行级锁最多的对象
select object_name,value row_lock_waits,
round(value * 100 / sum(value) over(),2) pct
from v$segment_statistics
where statistic_name = 'row lock waits' and value > 0
order by value desc ;
oracle 排列组合
with t as (
select 't1' nm,'a' lb,1 val from dual union all
select 't2' nm,'b' lb,2 val from dual union all
select 't3' nm,'c' lb,3 val from dual union all
select 't4' nm,'d' lb,4 val from dual
)
select level,t.*/* sys_connect_by_path(val,'#')*/ /*replace(sys_connect_by_path(val,'#'),'#') combo*/
from t /*where level = 3 */
connect by prior val < val and level <=3;
select replace(sys_connect_by_path(val,'#'),'#') combo
from t where level = 2
connect by nocycle prior val != val and level <=2;
oracle 行转列 列转行
with t as (
select 'a' t,1 id, '苹果' name, 1000 nums from dual union all
select 'b',2, '苹果', 2000 from dual union all
select 'c',3, '苹果', 4000 from dual union all
select 'a',4, '橘子', 5000 from dual union all
select 'b',5, '橘子', 3000 from dual union all
select 'a' ,6, '葡萄', 3500 from dual union all
select 'a',7, '芒果', 4200 from dual union all
select 'a',8, '芒果', 5500 from dual
)
select * from (select t,name, nums from t) pivot (sum(nums) for name in ('苹果' 苹果, '橘子', '葡萄', '芒果') from dual union all
;
with t as (
select 1 id,'苹果' name,1000 v1,2000 v2,3300 v3,5000 v4 from dual union all
select 2,'橘子',3000,3000,3200,1500 from dual union all
select 3,'香蕉',2500,3500,2200,2500 from dual union all
select 4,'葡萄',1500,2500,1200,3500 from dual
)
select id , name, t, v from t unpivot (v for t in (v1, v2, v3, v4) )
;
汉字编码互换
DECLARE
--u4e00-u9fa5 (中文);
--to_char(100,'XX')
--to_number('7D','XX')
--asciistr('中')
V_SQL VARCHAR2(100);
TEXT_BUFFER VARCHAR2(100);
V_CODE VARCHAR2(20);
BEGIN
FOR I IN 19968..40869 LOOP
V_CODE := '\' || TRIM(to_char(I,'xxxx'));
INSERT INTO TB_HZ(FCODE,FHZ)
VALUES(V_CODE,unistr( V_CODE) );
END LOOP;
END;
扫一扫
1977
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
