java - Generate Unique hash from Long id - Stack Overflow
to customize your list.
This site uses cookies to deliver our services and to show you relevant ads and job listings.
By using our site, you acknowledge that you have read and understand our , , and our .
Your use of Stack Overflow’s Products and Services, including the Stack Overflow Network, is subject to these policies and terms.
Join Stack Overflow to learn, share knowledge, and build your career.
or sign in with
I need to generate a unique hash from a ID value of type Long. My concern is that it should not globally generate the same hash from two different Long/long values.
MD5 hashing looks a nice solution but the hash String is very long. I only need characters
a-z and A-Z
And just 6-characters like: j4qwO7
What could be the simpliest solution?
11.1k44180352
You don't have to use the hex representation. Build your own hash representation by using the actual hash bytes from the function. You could truncate the hash output to simplify the hash representation, but that would make collisions more probable.
The other answers stating that what you ask isn't possible, based on the number of possible long values, is teoretically true, if you actually need the whole range.
If your IDs are auto incremented from zero and up, just 62^6 =
values might be more than enough for you, depending on your needs.
5,09011833
Your requirements cannot be met. You've got an alphabet of 62 possible characters, and 6 characters available - which means there are 626 possible IDs of that form.
However, there are 2568 possible long values. By the , it's impossible to give each of those long values a different ID of the given form.
1034k63676468245
Switch to using ints instead of longs, or allow for a longer "hash".
See every other answer for discussion of why 6 characters is unsufficient for dealing with longs.
Encrypt your number using an algorithm which does not using padding.
Personally, I suggest skip32 encoding.
I make no promises that this is strong enough for security, but if your goal is "make random-looking IDs," it works well.
Encode your number as a base_62 number (as opposed to base_10, not as opposed to base64 encoding).
18.1k1364145
Your question doesn't make sense.
'Unique hash' is a contradiction in terms.
A 'unique hash' value of a Java long must be 64 bits in length, like the long itself, and of course the simplest hash function for that is f(x) = x, i.e. the long value itself.
6 characters that can be 0-9, A-Z, and a-z can only yield 62^6 =
distinct values, which isn't enough.
251k22197335
You can use long value irself as same as hash (for indexing/search purposes).
if you need to obfuscate/hide your long value, you can use any symmetric
encryption algorithm with 64-bit block, for example - DES or AES in ECB-mode.
No need to use Hashids. Base 36 is pretty enough.
long id = 12345;
String hash = Integer.toString(Math.abs((int)id), 36);
Original answer, with Hashids:
You might want to use
long id = 12345;
Hashids hashids = new Hashids("this is my salt");
String hash = hashids.encrypt(id); // "ryBo"
"ryBo" is going to be unique, as it can be converted back to your long. Hashids just converts, doesn't hash further.
long[] numbers = hashids.decrypt("ryBo");
// numbers[0] == 12345
If you really have a 64-bit value, the hash string is going to be quite long (around 16 characters, depending on the alphabet), but if you don't plan to have more than 2^16 thingies, you can get away with truncating the 64-bit hash to 32-bit (an int).
long id = 12345;
String hash = hashids.encrypt(Math.abs((int)id));
3,68541720
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Post as a guest
Post as a guest
Post Your Answer
By clicking &Post Your Answer&, you acknowledge that you have read our updated ,
and , and that your continued use of the website is subject to these policies.
Not the answer you're looking for?
Browse other questions tagged
Stack Overflow works best with JavaScript enabled【ASH】如何导出视图DBA_HIST_ACTIVE_SESS_HISTORY的查询结果数据
1.1 &BLOG文档结构图
1.2 &前言部分
1.2.1 &导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(&_&)O~：
① 如何导出ASH数据--利用exp导出基表的数据（重点）
② 12c的expdp参数VIEWS_AS_TABLES选项
③ expdp工具不能导出哪些对象？
① 本文在itpub（）、博客园()和微信公众号（xiaomaimiaolhr）上有同步更新。
② 文章中用到的所有代码、相关软件、相关资料及本文的pdf版本都请前往小麦苗的云盘下载，小麦苗的云盘地址见：。
③ 若网页文章代码格式有错乱，请下载pdf格式的文档来阅读。
④ 在本篇BLOG中，代码输出部分一般放在一行一列的表格中。
本文若有错误或不完善的地方请大家多多指正，您的批评指正是我写作的最大动力。
1.2.2 &相关文章链接
12c的dmp文件导入11g中参考：【故障处理】IMP-00010错误 12C的dmp文件导入11G，地址为：http://blog.itpub.net//viewspace-2128197/
1.2.3 &本文简介
众所周知，视图只是一个查询数据的窗口，其不存储数据，所以在使用exp等工具导出的时候只能导出其定义，而不能导出视图的查询结果数据。在Oracle 12c中，可以采用expdp中的一个新增参数VIEWS_AS_TABLES来将视图作为表来导出，非常实用，不过对于一些特殊的表仍然不能采用expdp导出，例如SYS和SYSTEM下的一些表，AUD$表不能使用expdp来导出。
另外，对于一些安全类很高的系统是不允许随意创建表，也不允许使用PLSQL Developer等客户端的工具，那么若是查询DBA_HIST_ACTIVE_SESS_HISTORY等视图的时候就非常不方便了，这个时候我们可以将该视图的内容导出来，然后导入到我们自己的测试库中就可以随意的进行分析了。那么，如何来导出这些数据的内容呢？本文将详细介绍这些内容。
1.3 &如何导出ash数据？
根据前边的分析，我们知道视图的查询结果数据不能直接导出，那么我们可以导出这个视图的基表数据：
SELECT D.NAME, D.TYPE, D.REFERENCED_NAME, D.REFERENCED_TYPE
& FROM DBA_DEPENDENCIES D
&WHERE D.NAME IN&('DBA_HIST_ACTIVE_SESS_HISTORY',
&&&&&&&&&&&&&&&&& 'DBA_HIST_PLAN_OPERATION_NAME',
&&&&&&&&&&&&&&&&& 'DBA_HIST_PLAN_OPTION_NAME',
&&&&&&&&&&&&&&&&& 'DBA_HIST_SQLCOMMAND_NAME',
&&&&&&&&&&&&&&&&& 'DBA_HIST_TOPLEVELCALL_NAME')
&& AND D.TYPE =&'VIEW'
&ORDER&BY D.NAME, D.REFERENCED_NAME;
主要涉及的表是图中方框里的去掉X$表后的7个表，其中最主要的还是WRH$_ACTIVE_SESSION_HISTORY表，该表是一个分区表，导出的时候可以按照时间进行导出。其它表都是很小的表，可以全量导出。
下面尝试使用exp和expdp来导出。
1.3.1 &expdp导出sys用户下的表报错ORA-39165 和ORA-39166
DataPump Export (EXPDP) Fails With Error ORA-39165: Schema SYS Was Not Found (文档 ID )
该文章给出了如下答案：
1.&There&is&a&restriction&on&dataPump&export.&It&cannot&export&schemas&like&SYS,&ORDSYS,&EXFSYS,&MDSYS,&DMSYS,&CTXSYS,&ORDPLUGINS,&LBACSYS,&XDB,&SI_INFORMTN_SCHEMA,&DIP,&DBSNMP&and&WMSYS&in&any&mode.&&
2.&The&Utilities&Guide&indicates&the&restriction&only&on&full&export&mode,&but&the&restriction&actually&applies&to&all&modes.&
而：MOS：Why Can an Object Not Be Exported? Expdp of SYSTEM User's Table Returns ORA-39166 or ORA-31655 (文档 ID )列出来了哪些对象不能导出：
Objects (tables, views, schemas, etc) which fall under either of below conditions are not exported with expdp because they are regarded as system maintained objects.
Object is listed in ku_noexp_view.
This view is a union of ku_noexp_tab and noexp$ tables.
Objects that are listed in this view are not exported.
Object is ORACLE_MAINTAINED='Y' in ALL_OBJECTS (and DBA_OBJECTS).----针对12c
在视图sys.Ku_Noexp_View中或DBA_OBJECTS的ORACLE_MAINTAINED列为Y的对象不能导出。
SELECT&*&FROM sys.Ku_Noexp_View d WHERE d.name LIKE&'%WRH%'&;
SELECT&*&FROM DBA_OBJECTS d WHERE d.ORACLE_MAINTAINED='Y'&AND D.object_name LIKE&'WR%';
解决该报错的方法是：
1. 使用exp 导出
2.ctas的方法在不受限制的schema下创建表，然后导出该新建的表
3. use the DBMS_AUDIT_MGMT package of Audit Vault to manage and purge audit data (see Note ). This allows for the facility to move the AUD$ table out of the SYSTEM tablespace, which can negate the need to export the table.
注意：This issue also applies to other SYS owned auditing tables such as FGA_LOG$
1.3.2 &采用exp导出ASH数据
1.3.2.1 &方法1：ctas建表导出，有的客户不让建表
实验过程：
1.3.2.2 &方法2：导出基表的数据
导出基表数据：
---more /tmp/exp_ash_lhr_01.par
query="WHERE SAMPLE_TIME BETWEEN TO_DATE(' 08:30:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(' 23:38:00', 'YYYY-MM-DD HH24:MI:SS')"
exp \'/ AS SYSDBA\'& tables='WRH$_ACTIVE_SESSION_HISTORY' file=/tmp/exp_ash_lhr_01.dmp parfile=/tmp/exp_ash_lhr_01.par log=/tmp/exp_ash_lhr_01.log GRANTS=N CONSTRAINTS=N& STATISTICS=NONE
exp \'/ AS SYSDBA\'& tables='WRM$_SNAPSHOT','WRH$_EVENT_NAME','WRH$_SQLCOMMAND_NAME','WRH$_PLAN_OPERATION_NAME','WRH$_PLAN_OPTION_NAME','WRH$_TOPLEVELCALL_NAME' file=/tmp/exp_ash_lhr_02.dmp log=/tmp/exp_ash_lhr_02.log& GRANTS=N& CONSTRAINTS=N& STATISTICS=NONE
导入到测试用户：
imp lhr/lhr file=/tmp/exp_ash_lhr_01.dmp tables='WRH$_ACTIVE_SESSION_HISTORY' log=/tmp/imp_ash_lhr_01.log& FROMUSER=SYS TOUSER=LHR
imp lhr/lhr file=/tmp/exp_ash_lhr_02.dmp tables='WRM$_SNAPSHOT','WRH$_EVENT_NAME','WRH$_SQLCOMMAND_NAME','WRH$_PLAN_OPERATION_NAME','WRH$_PLAN_OPTION_NAME','WRH$_TOPLEVELCALL_NAME' log=/tmp/imp_ash_lhr_02.log&& FROMUSER=SYS TOUSER=LHR
DROP TABLE LHR.WRH$_ACTIVE_SESSION_HISTORY PURGE;
DROP TABLE LHR.WRM$_SNAPSHOT PURGE;
DROP TABLE LHR.WRH$_EVENT_NAME PURGE;
DROP TABLE LHR.WRH$_SQLCOMMAND_NAME PURGE;
DROP TABLE LHR.WRH$_PLAN_OPERATION_NAME PURGE;
DROP TABLE LHR.WRH$_PLAN_OPTION_NAME PURGE;
DROP TABLE LHR.WRH$_TOPLEVELCALL_NAME PURGE;
接下来就是根据这些基表来创建自己的视图了，不再详述。
1.3.3 &12c& expdp VIEWS_AS_TABLES选项
expdp VIEWS_AS_TABLES选项可以将视图看做表并将其数据导出。
expdp system/lhr DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=expdp_vw.log VIEWS_AS_TABLES=lhr.my_view
表数据准备：
开始导出：
12月 16 16:31:49 2016
: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
"SYSTEM"."SYS_EXPORT_TABLE_01":& system/******** DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=expdp_vw.log VIEWS_AS_TABLES=lhr.my_view
BLOCKS 方法进行估计...
TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
BLOCKS 方法的总估计: 16 KB
TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
"LHR"."MY_VIEW"&&&&&&&&&&&&&&&&&&&&&&&&&&&& 5.929 KB&&&&&& 2 行
/卸载了主表 "SYSTEM"."SYS_EXPORT_TABLE_01"
"SYSTEM"."SYS_EXPORT_TABLE_01" 已于 星期五 12月 16 16:32:36 2016 elapsed 0 00:00:31 成功完成
查看其DDL语句：
12月 16 16:35:14 2016
: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
/卸载了主表 "SYSTEM"."SYS_SQL_FILE_FULL_01"
"SYSTEM"."SYS_SQL_FILE_FULL_01":& system/******** DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=impdp_vw.log sqlfile=a.txt
TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
"SYSTEM"."SYS_SQL_FILE_FULL_01" 已于 星期五 12月 16 16:35:26 2016 elapsed 0 00:00:10 成功完成
DDL语句内容：
-- CONNECT SYSTEM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
CREATE TABLE "LHR"."MY_VIEW"
&& ("NR" NUMBER,
"TXT" VARCHAR2(10 BYTE),
"COL3" VARCHAR2(10 BYTE)
&& ) SEGMENT CREATION DEFERRED
& PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
& TABLESPACE "USERS" ;
进行导入：
12月 16 16:37:03 2016
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
12月 16 16:39:49 2016
: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
/卸载了主表 "SYSTEM"."SYS_IMPORT_FULL_02"
"SYSTEM"."SYS_IMPORT_FULL_02":& system/******** DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=impdp_vw.log remap_schema=lhr:lhr01
TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
"LHR01"."MY_VIEW"&&&&&&&&&&&&&&&&&&&&&&&&&& 5.929 KB&&&&&& 2 行
"SYSTEM"."SYS_IMPORT_FULL_02" 已于 星期五 12月 16 16:39:57 2016 elapsed 0 00:00:06 成功完成
--- 方法1：ctas建表导出 有的客户不让建表
--- 方法2：导出基表的数据
采用exp导出ASH数据的命令：
---more /tmp/exp_ash_lhr_01.par
query="WHERE SAMPLE_TIME BETWEEN TO_DATE(' 08:30:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(' 23:38:00', 'YYYY-MM-DD HH24:MI:SS')"
exp \'/ AS SYSDBA\'& tables='WRH$_ACTIVE_SESSION_HISTORY' file=/tmp/exp_ash_lhr_01.dmp parfile=/tmp/exp_ash_lhr_01.par log=/tmp/exp_ash_lhr_01.log GRANTS=N CONSTRAINTS=N& STATISTICS=NONE
exp \'/ AS SYSDBA\'& tables='WRM$_SNAPSHOT','WRH$_EVENT_NAME','WRH$_SQLCOMMAND_NAME','WRH$_PLAN_OPERATION_NAME','WRH$_PLAN_OPTION_NAME','WRH$_TOPLEVELCALL_NAME' file=/tmp/exp_ash_lhr_02.dmp log=/tmp/exp_ash_lhr_02.log& GRANTS=N& CONSTRAINTS=N& STATISTICS=NONE
imp lhr/lhr file=/tmp/exp_ash_lhr_01.dmp tables='WRH$_ACTIVE_SESSION_HISTORY' log=/tmp/imp_ash_lhr_01.log& FROMUSER=SYS TOUSER=LHR
imp lhr/lhr file=/tmp/exp_ash_lhr_02.dmp tables='WRM$_SNAPSHOT','WRH$_EVENT_NAME','WRH$_SQLCOMMAND_NAME','WRH$_PLAN_OPERATION_NAME','WRH$_PLAN_OPTION_NAME','WRH$_TOPLEVELCALL_NAME' log=/tmp/imp_ash_lhr_02.log&& FROMUSER=SYS TOUSER=LHR
DROP TABLE LHR.WRH$_ACTIVE_SESSION_HISTORY PURGE;
DROP TABLE LHR.WRM$_SNAPSHOT PURGE;
DROP TABLE LHR.WRH$_EVENT_NAME PURGE;
DROP TABLE LHR.WRH$_SQLCOMMAND_NAME PURGE;
DROP TABLE LHR.WRH$_PLAN_OPERATION_NAME PURGE;
DROP TABLE LHR.WRH$_PLAN_OPTION_NAME PURGE;
DROP TABLE LHR.WRH$_TOPLEVELCALL_NAME PURGE;
创建自己的ASH视图：
---- 11.2.0.3
create or replace view dh_ash_11g_lhr
(snap_id, dbid, instance_number, sample_id, sample_time, session_id, session_serial#, session_type, flags, user_id, sql_id, is_sqlid_current, sql_child_number, sql_opcode, sql_opname, force_matching_signature, top_level_sql_id, top_level_sql_opcode, sql_plan_hash_value, sql_plan_line_id, sql_plan_operation, sql_plan_options, sql_exec_id, sql_exec_start, plsql_entry_object_id, plsql_entry_subprogram_id, plsql_object_id, plsql_subprogram_id, qc_instance_id, qc_session_id, qc_session_serial#, px_flags, event, event_id, seq#, p1text, p1, p2text, p2, p3text, p3, wait_class, wait_class_id, wait_time, session_state, time_waited, blocking_session_status, blocking_session, blocking_session_serial#, blocking_inst_id, blocking_hangchain_info, current_obj#, current_file#, current_block#, current_row#, top_level_call#, top_level_call_name, consumer_group_id, xid, remote_instance#, time_model, in_connection_mgmt, in_parse, in_hard_parse, in_sql_execution, in_plsql_execution, in_plsql_rpc, in_plsql_compilation, in_java_execution, in_bind, in_cursor_close, in_sequence_load, capture_overhead, replay_overhead, is_captured, is_replayed, service_hash, program, module, action, client_id, machine, port, ecid, dbreplay_file_id, dbreplay_call_counter, tm_delta_time, tm_delta_cpu_time, tm_delta_db_time, delta_time, delta_read_io_requests, delta_write_io_requests, delta_read_io_bytes, delta_write_io_bytes, delta_interconnect_io_bytes, pga_allocated, temp_space_allocated)
select /* ASH/AWR meta attributes */
&&&&&& ash.snap_id, ash.dbid, ash.instance_number,
&&&&&& ash.sample_id, ash.sample_time,
&&&&&& /* Session/User attributes */
&&&&&& ash.session_id, ash.session_serial#,
&&&&&& decode(ash.session_type, 1,'FOREGROUND', 'BACKGROUND'),
&&&&&& ash.flags,
&&&&&& ash.user_id,
&&&&&& /* SQL attributes */
&&&&&& ash.sql_id,
&&&&&& decode(bitand(ash.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'),
&&&&&& ash.sql_child_number, ash.sql_opcode,
&&&&&& (select command_name from WRH$_SQLCOMMAND_NAME
&&&&&&& where command_type = ash.sql_opcode
&&&&&&& and dbid = ash.dbid) as sql_opname,
&&&&&& ash.force_matching_signature,
&&&&&& decode(ash.top_level_sql_id, NULL, ash.sql_id, ash.top_level_sql_id),
&&&&&& decode(ash.top_level_sql_id, NULL, ash.sql_opcode,
&&&&&&&&&&&&& ash.top_level_sql_opcode),
&&&&&& /* SQL Plan/Execution attributes */
&&&&&& ash.sql_plan_hash_value,
&&&&&& decode(ash.sql_plan_line_id, 0, to_number(NULL), ash.sql_plan_line_id),
&&&&&& (select operation_name from WRH$_PLAN_OPERATION_NAME
&&&&&&& where& operation_id = ash.sql_plan_operation#
&&&&&&&&& and& dbid = ash.dbid) as sql_plan_operation,
&&&&&& (select option_name from WRH$_PLAN_OPTION_NAME
&&&&&&& where& option_id = ash.sql_plan_options#
&&&&&&&&& and& dbid = ash.dbid) as sql_plan_options,
&&&&&& decode(ash.sql_exec_id, 0, to_number(NULL), ash.sql_exec_id),
&&&&&& ash.sql_exec_start,
&&&&&& /* PL/SQL attributes */
&&&&&& decode(ash.plsql_entry_object_id,0,to_number(NULL),
&&&&&&&&&&&&& ash.plsql_entry_object_id),
&&&&&& decode(ash.plsql_entry_object_id,0,to_number(NULL),
&&&&&&&&&&&&& ash.plsql_entry_subprogram_id),
&&&&&& decode(ash.plsql_object_id,0,to_number(NULL),
&&&&&&&&&&&&& ash.plsql_object_id),
&&&&&& decode(ash.plsql_object_id,0,to_number(NULL),
&&&&&&&&&&&&& ash.plsql_subprogram_id),
&&&&&& /* PQ attributes */
&&&&&& decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_instance_id),
&&&&&& decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_id),
&&&&&& decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_serial#),
&&&&&& decode(ash.px_flags,&&&&& 0, to_number(NULL), ash.px_flags),
&&&&&& /* Wait event attributes */
&&&&&& decode(ash.wait_time, 0, evt.event_name, NULL),
&&&&&& decode(ash.wait_time, 0, evt.event_id,&& NULL),
&&&&&& ash.seq#,
&&&&&& evt.parameter1, ash.p1,
&&&&&& evt.parameter2, ash.p2,
&&&&&& evt.parameter3, ash.p3,
&&&&&& decode(ash.wait_time, 0, evt.wait_class,&&& NULL),
&&&&&& decode(ash.wait_time, 0, evt.wait_class_id, NULL),
&&&&&& ash.wait_time,
&&&&&& decode(ash.wait_time, 0, 'WAITING', 'ON CPU'),
&&&&&& ash.time_waited,
&&&&&& (case when ash.blocking_session =
&&&&&&&&&&&&&& then 'UNKNOWN'
&&&&&&&&&&&& when ash.blocking_session =
&&&&&&&&&&&&&& then 'GLOBAL'
&&&&&&&&&&&& when ash.blocking_session =
&&&&&&&&&&&&&& then 'UNKNOWN'
&&&&&&&&&&&& when ash.blocking_session =
&&&&&&&&&&&&&& then 'NO HOLDER'
&&&&&&&&&&&& when ash.blocking_session =
&&&&&&&&&&&&&& then 'NOT IN WAIT'
&&&&&&&&&&&& else 'VALID'
&&&&&&& end),
&&&&&& (case when ash.blocking_session between
&&&&&&&&&&&&&& then to_number(NULL)
&&&&&&&&&&&& else ash.blocking_session
&&&&&&& end),
&&&&&& (case when ash.blocking_session between
&&&&&&&&&&&&&& then to_number(NULL)
&&&&&&&&&&&& else ash.blocking_session_serial#
&&&&&&& end),
&&&&&& (case when ash.blocking_session between
&&&&&&&&&&&&&& then to_number(NULL)
&&&&&&&&&&&& else ash.blocking_inst_id
&&&&&&&&& end),
&&&&&& (case when ash.blocking_session between
&&&&&&&&&&&&&& then NULL
&&&&&&&&&&&& else decode(bitand(ash.flags, power(2, 3)), NULL, 'N',
&&&&&&&&&&&&&&&&&&&&&&&& 0, 'N', 'Y')
&&&&&&&&& end),
&&&&&& /* Session's working context */
&&&&&& ash.current_obj#, ash.current_file#, ash.current_block#,
&&&&&& ash.current_row#, ash.top_level_call#,
&&&&&& (select top_level_call_name from WRH$_TOPLEVELCALL_NAME
&&&&&&& where top_level_call# = ash.top_level_call#
&&&&&&& and dbid = ash.dbid) as top_level_call_name,
&&&&&& decode(ash.consumer_group_id, 0, to_number(NULL),
&&&&&&&&&&&&& ash.consumer_group_id),
&&&&&& ash.xid,
&&&&&& decode(ash.remote_instance#, 0, to_number(NULL), ash.remote_instance#),
&&&&&& ash.time_model,
&&&&&& decode(bitand(ash.time_model,power(2, 3)),0,'N','Y')
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& as in_connection_mgmt,
&&&&&& decode(bitand(ash.time_model,power(2, 4)),0,'N','Y')as in_parse,
&&&&&& decode(bitand(ash.time_model,power(2, 7)),0,'N','Y')as in_hard_parse,
&&&&&& decode(bitand(ash.time_model,power(2,10)),0,'N','Y')as in_sql_execution,
&&&&&& decode(bitand(ash.time_model,power(2,11)),0,'N','Y')
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& as in_plsql_execution,
&&&&&& decode(bitand(ash.time_model,power(2,12)),0,'N','Y')as in_plsql_rpc,
&&&&&& decode(bitand(ash.time_model,power(2,13)),0,'N','Y')
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& as in_plsql_compilation,
&&&&&& decode(bitand(ash.time_model,power(2,14)),0,'N','Y')
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& as in_java_execution,
&&&&&& decode(bitand(ash.time_model,power(2,15)),0,'N','Y')as in_bind,
&&&&&& decode(bitand(ash.time_model,power(2,16)),0,'N','Y')as in_cursor_close,
&&&&&& decode(bitand(ash.time_model,power(2,17)),0,'N','Y')as in_sequence_load,
&&&&&& decode(bitand(ash.flags,power(2,5)),NULL,'N',0,'N','Y')
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& as capture_overhead,
&&&&&& decode(bitand(ash.flags,power(2,6)), NULL,'N',0,'N','Y' )
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& as replay_overhead,
&&&&&& decode(bitand(ash.flags,power(2,0)),NULL,'N',0,'N','Y') as is_captured,
&&&&&& decode(bitand(ash.flags,power(2,2)), NULL,'N',0,'N','Y' )as is_replayed,
&&&&&& /* Application attributes */
&&&&&& ash.service_hash, ash.program,
&&&&&& ash.module module,
&&&&&& ash.action action,
&&&&&& ash.client_id,
&&&&&& ash.machine, ash.port, ash.ecid,
&&&&&& /* DB Replay info */
&&&&&& ash.dbreplay_file_id, ash.dbreplay_call_counter,
&&&&&& /* stash columns */
&&&&&& ash.tm_delta_time,
&&&&&& ash.tm_delta_cpu_time,
&&&&&& ash.tm_delta_db_time,
&&&&&& ash.delta_time,
&&&&&& ash.delta_read_io_requests,
&&&&&& ash.delta_write_io_requests,
&&&&&& ash.delta_read_io_bytes,
&&&&&& ash.delta_write_io_bytes,
&&&&&& ash.delta_interconnect_io_bytes,
&&&&&& ash.pga_allocated,
&&&&&& ash.temp_space_allocated
from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY ash, WRH$_EVENT_NAME evt
where&&&&& ash.snap_id&&&&&&&&& = sn.snap_id(+)
&&&&& and& ash.dbid&&&&&&&&&&&& = sn.dbid(+)
&&&&& and& ash.instance_number& = sn.instance_number(+)
&&&&& and& ash.dbid&&&&&&&&&&&& = evt.dbid
&&&&& and& ash.event_id&&&&&&&& = evt.event_
---------- 12c
create or replace view dh_ash_12c_lhr
(snap_id, dbid, instance_number, sample_id, sample_time, session_id, session_serial#, session_type, flags, user_id, sql_id, is_sqlid_current, sql_child_number, sql_opcode, sql_opname, force_matching_signature, top_level_sql_id, top_level_sql_opcode, sql_plan_hash_value, sql_full_plan_hash_value, sql_adaptive_plan_resolved, sql_plan_line_id, sql_plan_operation, sql_plan_options, sql_exec_id, sql_exec_start, plsql_entry_object_id, plsql_entry_subprogram_id, plsql_object_id, plsql_subprogram_id, qc_instance_id, qc_session_id, qc_session_serial#, px_flags, event, event_id, seq#, p1text, p1, p2text, p2, p3text, p3, wait_class, wait_class_id, wait_time, session_state, time_waited, blocking_session_status, blocking_session, blocking_session_serial#, blocking_inst_id, blocking_hangchain_info, current_obj#, current_file#, current_block#, current_row#, top_level_call#, top_level_call_name, consumer_group_id, xid, remote_instance#, time_model, in_connection_mgmt, in_parse, in_hard_parse, in_sql_execution, in_plsql_execution, in_plsql_rpc, in_plsql_compilation, in_java_execution, in_bind, in_cursor_close, in_sequence_load, in_inmemory_query, in_inmemory_populate, in_inmemory_prepopulate, in_inmemory_repopulate, in_inmemory_trepopulate, capture_overhead, replay_overhead, is_captured, is_replayed, service_hash, program, module, action, client_id, machine, port, ecid, dbreplay_file_id, dbreplay_call_counter, tm_delta_time, tm_delta_cpu_time, tm_delta_db_time, delta_time, delta_read_io_requests, delta_write_io_requests, delta_read_io_bytes, delta_write_io_bytes, delta_interconnect_io_bytes, pga_allocated, temp_space_allocated, dbop_name, dbop_exec_id, con_dbid/*, con_id*/)
select /* ASH/AWR meta attributes */
&&&&&& ash.snap_id, ash.dbid, ash.instance_number,
&&&&&& ash.sample_id, ash.sample_time,
&&&&&& /* Session/User attributes */
&&&&&& ash.session_id, ash.session_serial#,
&&&&&& decode(ash.session_type, 1,'FOREGROUND', 'BACKGROUND'),
&&&&&& ash.flags,
&&&&&& ash.user_id,
&&&&&& /* SQL attributes */
&&&&&& ash.sql_id,
&&&&&& decode(bitand(ash.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'),
&&&&&& ash.sql_child_number, ash.sql_opcode,
&&&&&& (select command_name
&&&&&&&&& from WRH$_SQLCOMMAND_NAME s
&&&&&&&& where s.command_type = ash.sql_opcode
&&&&&&&&&& and s.dbid = ash.dbid
&&&&&&&&&& and s.con_dbid = ash.dbid) as sql_opname,
&&&&&& ash.force_matching_signature,
&&&&&& decode(ash.top_level_sql_id, NULL, ash.sql_id, ash.top_level_sql_id),
&&&&&& decode(ash.top_level_sql_id, NULL, ash.sql_opcode,
&&&&&&&&&&&&& ash.top_level_sql_opcode),
&&&&&& /* SQL Plan/Execution attributes */
&&&&&& ash.sql_plan_hash_value,
&&&&&& ash.sql_full_plan_hash_value,
&&&&&& ash.sql_adaptive_plan_resolved,
&&&&&& decode(ash.sql_plan_line_id, 0, to_number(NULL), ash.sql_plan_line_id),
&&&&&& (select operation_name
&&&&&&&&& from WRH$_PLAN_OPERATION_NAME pn
&&&&&&&& where& pn.operation_id = ash.sql_plan_operation#
&&&&&&&&&& and& pn.dbid = ash.dbid
&&&&&&&&&& and& pn.con_dbid = ash.dbid) as sql_plan_operation,
&&&&&& (select option_name
&&&&&&&&& from WRH$_PLAN_OPTION_NAME po
&&&&&&&& where& po.option_id = ash.sql_plan_options#
&&&&&&&&&& and& po.dbid = ash.dbid
&&&&&&&&&& and& po.con_dbid = ash.dbid) as sql_plan_options,
&&&&&& decode(ash.sql_exec_id, 0, to_number(NULL), ash.sql_exec_id),
&&&&&& ash.sql_exec_start,
&&&&&& /* PL/SQL attributes */
&&&&&& decode(ash.plsql_entry_object_id,0,to_number(NULL),
&&&&&&&&&&&&& ash.plsql_entry_object_id),
&&&&&& decode(ash.plsql_entry_object_id,0,to_number(NULL),
&&&&&&&&&&&&& ash.plsql_entry_subprogram_id),
&&&&&& decode(ash.plsql_object_id,0,to_number(NULL),
&&&&&&&&&&&&& ash.plsql_object_id),
&&&&&& decode(ash.plsql_object_id,0,to_number(NULL),
&&&&&&&&&&&&& ash.plsql_subprogram_id),
&&&&&& /* PQ attributes */
&&&&&& decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_instance_id),
&&&&&& decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_id),
&&&&&& decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_serial#),
&&&&&& decode(ash.px_flags,&&&&& 0, to_number(NULL), ash.px_flags),
&&&&&& /* Wait event attributes */
&&&&&& decode(ash.wait_time, 0, evt.event_name, NULL),
&&&&&& decode(ash.wait_time, 0, evt.event_id,&& NULL),
&&&&&& ash.seq#,
&&&&&& evt.parameter1, ash.p1,
&&&&&& evt.parameter2, ash.p2,
&&&&&& evt.parameter3, ash.p3,
&&&&&& decode(ash.wait_time, 0, evt.wait_class,&&& NULL),
&&&&&& decode(ash.wait_time, 0, evt.wait_class_id, NULL),
&&&&&& ash.wait_time,
&&&&&& decode(ash.wait_time, 0, 'WAITING', 'ON CPU'),
&&&&&& ash.time_waited,
&&&&&& (case when ash.blocking_session =
&&&&&&&&&&&&&& then 'UNKNOWN'
&&&&&&&&&&&& when ash.blocking_session =
&&&&&&&&&&&&&& then 'GLOBAL'
&&&&&&&&&&&& when ash.blocking_session =
&&&&&&&&&&&&&& then 'UNKNOWN'
&&&&&&&&&&&& when ash.blocking_session =
&&&&&&&&&&&&&& then 'NO HOLDER'
&&&&&&&&&&&& when ash.blocking_session =
&&&&&&&&&&&&&& then 'NOT IN WAIT'
&&&&&&&&&&&& else 'VALID'
&&&&&&& end),
&&&&&& (case when ash.blocking_session between
&&&&&&&&&&&&&& then to_number(NULL)
&&&&&&&&&&&& else ash.blocking_session
&&&&&&& end),
&&&&&& (case when ash.blocking_session between
&&&&&&&&&&&&&& then to_number(NULL)
&&&&&&&&&&&& else ash.blocking_session_serial#
&&&&&&& end),
&&&&&& (case when ash.blocking_session between
&&&&&&&&&&&&&& then to_number(NULL)
&&&&&&&&&&&& else ash.blocking_inst_id
&&&&&&&&& end),
&&&&&& (case when ash.blocking_session between
&&&&&&&&&&&&&& then NULL
&&&&&&&&&&&& else decode(bitand(ash.flags, power(2, 3)), NULL, 'N',
&&&&&&&&&&&&&&&&&&&&&&&& 0, 'N', 'Y')
&&&&&&&&& end),
&&&&&& /* Session's working context */
&&&&&& ash.current_obj#, ash.current_file#, ash.current_block#,
&&&&&& ash.current_row#, ash.top_level_call#,
&&&&&& (select top_level_call_name
&&&&&&&&& from WRH$_TOPLEVELCALL_NAME t
&&&&&&&& where top_level_call# = ash.top_level_call#
&&&&&&&&&& and t.dbid = ash.dbid
&&&&&&&&&& and t.con_dbid = ash.dbid) as top_level_call_name,
&&&&&& decode(ash.consumer_group_id, 0, to_number(NULL),
&&&&&&&&&&&&& ash.consumer_group_id),
&&&&&& ash.xid,
&&&&&& decode(ash.remote_instance#, 0, to_number(NULL), ash.remote_instance#),
&&&&&& ash.time_model,
&&&&&& decode(bitand(ash.time_model,power(2, 3)),0,'N','Y')
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& as in_connection_mgmt,
&&&&&& decode(bitand(ash.time_model,power(2, 4)),0,'N','Y')as in_parse,
&&&&&& decode(bitand(ash.time_model,power(2, 7)),0,'N','Y')as in_hard_parse,
&&&&&& decode(bitand(ash.time_model,power(2,10)),0,'N','Y')as in_sql_execution,
&&&&&& decode(bitand(ash.time_model,power(2,11)),0,'N','Y')
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& as in_plsql_execution,
&&&&&& decode(bitand(ash.time_model,power(2,12)),0,'N','Y')as in_plsql_rpc,
&&&&&& decode(bitand(ash.time_model,power(2,13)),0,'N','Y')
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& as in_plsql_compilation,
&&&&&& decode(bitand(ash.time_model,power(2,14)),0,'N','Y')
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& as in_java_execution,
&&&&&& decode(bitand(ash.time_model,power(2,15)),0,'N','Y')as in_bind,
&&&&&& decode(bitand(ash.time_model,power(2,16)),0,'N','Y')as in_cursor_close,
&&&&&& decode(bitand(ash.time_model,power(2,17)),0,'N','Y')as in_sequence_load,
&&&&&& decode(bitand(ash.time_model,power(2,18)),0,'N','Y')as in_inmemory_query,
&&&&&& decode(bitand(ash.time_model,power(2,19)),0,'N','Y')
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& as in_inmemory_populate,
&&&&&& decode(bitand(ash.time_model,power(2,20)),0,'N','Y')
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& as in_inmemory_prepopulate,
&&&&&& decode(bitand(ash.time_model,power(2,21)),0,'N','Y')
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& as in_inmemory_repopulate,
&&&&&& decode(bitand(ash.time_model,power(2,22)),0,'N','Y')
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& as in_inmemory_trepopulate,
&&&&&& decode(bitand(ash.flags,power(2,5)),NULL,'N',0,'N','Y')
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& as capture_overhead,
&&&&&& decode(bitand(ash.flags,power(2,6)), NULL,'N',0,'N','Y' )
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& as replay_overhead,
&&&&&& decode(bitand(ash.flags,power(2,0)),NULL,'N',0,'N','Y') as is_captured,
&&&&&& decode(bitand(ash.flags,power(2,2)), NULL,'N',0,'N','Y' )as is_replayed,
&&&&&& /* Application attributes */
&&&&&& ash.service_hash, ash.program,
&&&&&& ash.module module,
&&&&&& ash.action action,
&&&&&& ash.client_id,
&&&&&& ash.machine, ash.port, ash.ecid,
&&&&&& /* DB Replay info */
&&&&&& ash.dbreplay_file_id, ash.dbreplay_call_counter,
&&&&&& /* stash columns */
&&&&&& ash.tm_delta_time,
&&&&&& ash.tm_delta_cpu_time,
&&&&&& ash.tm_delta_db_time,
&&&&&& ash.delta_time,
&&&&&& ash.delta_read_io_requests,
&&&&&& ash.delta_write_io_requests,
&&&&&& ash.delta_read_io_bytes,
&&&&&& ash.delta_write_io_bytes,
&&&&&& ash.delta_interconnect_io_bytes,
&&&&&& ash.pga_allocated,
&&&&&& ash.temp_space_allocated,
&&&&&& ash.dbop_name,
&&&&&& ash.dbop_exec_id,
&&&&&& decode(ash.con_dbid, 0, ash.dbid, ash.con_dbid)/*,
&&&&&& con_dbid_to_id(decode(ash.con_dbid, 0, ash.dbid, ash.con_dbid)) con_id*/
from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY ash, WRH$_EVENT_NAME evt
where&&&&& ash.snap_id&&&&&&&&& = sn.snap_id(+)
&&&&& and& ash.dbid&&&&&&&&&&&& = sn.dbid(+)
&&&&& and& ash.instance_number& = sn.instance_number(+)
&&&&& and& ash.dbid&&&&&&&&&&&& = evt.dbid
&&&&& and& ash.event_id&&&&&&&& = evt.event_
...............................................................................................................................
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 本文在itpub（）、博客园（）和个人微信公众号（）上有同步更新
● 本文itpub地址：
● 本文博客园地址：
● 本文pdf版及小麦苗云盘地址：
● QQ群：<span style="line-height: 1.5; mso-spacerun: 'yes'; mso-fareast-font-family: mso-font-kerning: 1.599&&&& 微信群：私聊
● 联系我请加QQ好友()，注明添加缘由
● 于 <span style="line-height: 1.5; mso-spacerun: 'yes'; mso-fareast-font-family: mso-font-kerning: 1.-11-28 10:00 ~
22:00 在农行完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
...............................................................................................................................
手机长按下图识别二维码或微信客户端扫描下边的二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，免费学习最实用的数据库技术。
阅读(...) 评论()