Product point of view

Ok, I could not resist...
Probably you have already seen, but I think it is really funny...

Kill session in Sap Hana

In Oracle an easy trick to kill a session is to run the following query:

 SELECT 'exec sys.kill_session(' || a.sid || ',' || a.serial# || ');'  
    ,osuser  
    ,terminal  
    ,machine  
    ,program  
    ,a.*  
  FROM v$session a  
 WHERE status = 'ACTIVE' AND username = USER;  

Of course you have to be careful on the way you use it... You should be sure about what is running in the correspondent session.
You can use for example the tool offered by SQL Developer (you need to have the rights on the table v$session).

What about SAP HANA? The basic command you need is:

   ALTER SYSTEM CANCEL SESSION 'conn_id';  

Where conn_id is in the table M_CONNECTIONS, column CONNECTION_ID.
You can retrieve the complete command with the following query:

 SELECT   
    'alter system cancel session ''' || C.CONNECTION_ID || ''';' command,   
    C.CONNECTION_ID,   
    PS.STATEMENT_STRING  
 FROM M_CONNECTIONS C JOIN M_PREPARED_STATEMENTS PS  
     ON C.CONNECTION_ID = PS.CONNECTION_ID   
     AND C.CURRENT_STATEMENT_ID = PS.STATEMENT_ID  
 WHERE C.CONNECTION_STATUS = 'RUNNING'   
  AND C.CONNECTION_TYPE = 'Remote';  

    

Execution plan in SAP HANA

Do you have a slow query in Hana?
Do you want to improve the performances of a query?

First step is to retrieve the execution plan:

 DELETE FROM explain_plan_table WHERE statement_name = 'marco test nr 1';  
 DELETE FROM explain_plan_table WHERE statement_name = 'marco test nr 1';  
 EXPLAIN PLAN SET STATEMENT_NAME = 'marco test nr 1' FOR  
 select * from dummy; --put here your query!!!  
   
   
   
 SELECT 
   table_size,   
   to_number(output_size, 10, 2) as output_size,   
   to_number(subtree_cost, 10, 2) cost,   
   operator_name,   
   operator_details,   
   table_name  
 FROM explain_plan_table  
 WHERE statement_name = 'marco test nr 1'  
 order by operator_id;    

The output of the last select will look like this in your SAP HANA STUDIO:

In order to read the execution plan, I believe that the most important column is the "Operator Name". 
This is the column telling you how the DBMS is approaching the problem. 
Here you will see if it is doing something stupid. 
The question is: what is the meaning of the content of this column? Here is the set of possible values: 

Operator Name	Description
ROW SEARCH	Starting position of row engine operators. OPERATOR_DETAILS lists projected columns.
LIMIT	Operator for limiting number of output rows
ORDER BY	Operator for sorting output rows
HAVING	Operator for filtering with predicates on top of grouping and aggregation
GROUP BY	Operator for grouping and aggregation
MERGE AGGREGATION	Operator for merging the results of multiple parallel grouping and aggregations
DISTINCT	Operator for duplicate elimination
FILTER	Operator for filtering with predicates
UNION ALL	Operator for producing union-all of input relations
MATERIALIZED UNION ALL	Operator for producing union-all of input relations with intermediate result materialization
BTREE INDEX JOIN	Operator for joining input relations through B-tree index searches. Join type suffix can be added. For example, B-tree index join for left outer join is shown as BTREE INDEX JOIN (LEFT OUTER). Join without join type suffix means inner join.
CPBTREE INDEX JOIN	Operator for joining input relations through CPB-tree index searches. Join type suffix can be added.
HASH JOIN	Operator for joining input relations through probing hash table built on the fly. Join type suffix can be added.
NESTED LOOP JOIN	Operator for joining input relations through nested looping. Join type suffix can be added.
MIXED INVERTED INDEX JOIN	Operator for joining an input relation of row store format with a column table without format conversion using an inverted index of the column table. Join type suffix can be added.
BTREE INDEX SEARCH	Table access through B-tree index search
CPBTREE INDEX SEARCH	Table access through CPB-tree index search
TABLE SCAN	Table access through scanning
AGGR TABLE	Operator for aggregating base table directly
MONITOR SEARCH	Monitoring view access through search
MONITOR SCAN	Monitoring view access through scanning

Source of information: 
https://help.sap.com/hana/html/sql_explain_plan.html

Oracle: easy access the file system from PLSQL

How to easily access the file system from PLSQL ?
The goal is to perform an simple command like:
- DOS: dir
- LINUX: ls

and get the result as select.

Very good tool is offered by:
http://plsqlexecoscomm.sourceforge.net/


You need to download the PLSQL package from the web site above, install the PLSQL script and then perform the simple select:

 select *   
 from table(file_pkg.get_file_list(file_pkg.get_file('/')))  

Oracle: Measure disk space used

You need to know how much space your schema is using:

 -- estimated bytes per table used:  
 select   
   table_name,   
   avg_row_len*num_rows/1024/1024 size_mbytes   
 from user_tables   
 order by 2 desc; 

 --top table-segments for bytes used:  
 select   
      owner,   
      segment_name,   
      partition_name,   
      segment_type,   
      round(bytes/1024/1024) size_MB   
 from dba_segments   
 where owner like '%MY_FAVOURITE_NAME%'  
 order by bytes desc;  

 --top table-segments for bytes used: (per Segment_name)  
 select   
      owner,   
      segment_name,   
      sum(size_MB) size_MB   
 from (  
  select   
       owner,   
       segment_name,   
       partition_name,   
       segment_type,   
       round(bytes/1024/1024) size_MB   
  from   
       dba_segments   
 )  
 where owner like '%MY_FAVOURITE_NAME%'  
 group by owner, segment_name  
 order by size_MB desc;

 -- top owner for bytes used:  
 select   
      owner,   
      round(sum(bytes/1024/1024)) size_MB   
 from dba_segments   
 where owner like '%MY_FAVOURITE_NAME%'  
 group by owner   
 order by 2 desc;   

-- disk free  
 select   
      tablespace_name,   
      round(sum(bytes)/(1024*1024*1024),0) GB_free   
 from dba_free_space   
 group by tablespace_name   
 order by 2 desc;  

Responsive HTML5 menu

I need a menu that is adaptative, so much that if I open it from a laptop that it is already exploded but if I open it from a mobile device, then it needs to be clicked and exploded.

Such tool I used on my web site. If I see my resume section:
http://marcoandolfi.eu5.org/cv.html

you have this view from your laptop:

Instead if you open it from your iPhone you should have a view like this:

First view:

When Opened:

Ok, nice! How? 

First of all you need a JS which is the main engine 

(on my website is a separate file "dropdown-menu.js"): 

   
 if(window.addEventListener){   
      window.addEventListener("load", doLoad, false);  
 }else if(window.attachEvent){   
      window.attachEvent("onload", doLoad);  
 }else if(window.onLoad){  
   window.onload = doLoad;  
 }  
   
function doLoad(){  
        var nav = document.getElementsByTagName("NAV")[0];  
        var childs = nav.childNodes;  
        for(var i = 0; i < childs.length; i++){  
        var child = childs[i];  
        if(child.tagName === "H2"){  
          child.onclick = function(){toogleMenu(nav)};  
          child.onfocus = function(){toogleMenu(nav)};  
          break;  
        }  
}  
   
      var elClass = nav.className;  
      var arrClasses = elClass.split(" ");  
      for(var i = 0; i < arrClasses.length; i++){  
         if(arrClasses[i] === "expanded") arrClasses.splice(i, 1);  
      }  
      nav.className = arrClasses.join(" ");  
      nav.className += " non-expanded";  
      return false;  
 };  
   
 function toogleMenu(el){  
      var elClass = el.className;  
      var arrClasses = elClass.split(" ");  
      for(var i = 0; i < arrClasses.length; i++){  
        if(arrClasses[i] === "expanded"){  
          arrClasses.splice(i, 1);  
          arrClasses[length + 1] = "non-expanded";  
        }  else if(arrClasses[i] === "non-expanded"){  
          arrClasses.splice(i, 1);  
          arrClasses[length + 1] = "expanded";  
        }  
      }  
      el.className = arrClasses.join(" ");  
      return false;  
 }  

Then a bit of CSS 

   
 nav h2, aside h2{  
      position: absolute;  
      left: -999em;  
 }  
   
 nav {  
      clear: left;  
      margin: 0 0 2em;  
 }  
   
 nav ol{  
      border: 1px solid #e6e6e6;  
      padding-left: 0;  
 }  
   
 nav li {  
      border-bottom: 1px solid #eee;  
      line-height: 2em;  
      list-style: none;  
      margin-left: 0;  
 }  
   
 nav li:last-child {  
   border-bottom: 0;  
 }  
   
 nav a {  
      color: #999;  
      display: block;  
      padding: .5em .8em;  
      text-decoration: none;  
 }  
   
 nav .current {  
      font-weight: bold;  
 }  
   
 .dropdown-header {  
      font-size: 14px;  
      font-weight: bold;  
      color: #000000;  
 }  
   
 @media screen and (max-width: 37.499em){ /* base: 16px -> [< 600px] */  
   
      nav {  
           margin: 0 0 1.5em;  
           overflow: hidden;  
           position: relative;  
      }  
   
      nav h2 {  
           cursor: pointer;  
           font-size: 0.5em;  
           height: 4.2em;   
           left: auto;  
           margin: 0;  
           right: 0;  
           position: absolute;  
           text-indent: -999em;  
           top: 0;  
           width: 4em;  
      }  
   
      nav.non-expanded h2 {  
           background: #ccc url(../img/check_icon.png) no-repeat -35px 45%;  
      }  
   
      nav.expanded h2 {  
           background: #ccc url(../img/check_icon.png) no-repeat 0px 45%;  
      }  
   
      nav h2{  
           background: none;  
      }  
   
      nav a {  
           padding-right: 3em;  
      }  
   
      nav li {  
           clear: left;  
           line-height: 1em;  
      }  
   
      nav.non-expanded li{  
           display: none;  
      }  
   
      nav.expanded li{  
           display: block;  
      }  
   
      nav li.current {  
           border-bottom-width: 0;  
           display: block;  
      }  
   
      nav.expanded li.current {  
           border-bottom-width: 1px;  
      }  
   
 }  

(on my website is a separate file "dropdown-menu.css"): 

and now the HTML:

 <!DOCTYPE html>  
 <html lang="en">  
  <head>  
   [...]  
   <!-- Bootstrap core CSS -->  
   <link href="css/bootstrap.min.css" rel="stylesheet">  
   <!-- CSS for drop down menú adaptive -->  
   <link href="css/dropdown-menu.css" rel="stylesheet">  
   [...]  
  </head>  
  <body>  
    [...]          
       <nav>  
        <h2>Documents</h2>  
         <ol>  
           <li class="dropdown-header"> Professional </li>  
           <li class="selectable" id="CV"><a href="javascript:loadFrame('CV');">Curriculum Vitae</a></li>  
           <li class="selectable" id="ProjectList"><a href="javascript:loadFrame('ProjectList');">Project List</a></li>  
           <li class="selectable" id="Recommendation"><a href="javascript:loadFrame('Recommendation');">Recommendation</a></li>  
           <li class="dropdown-header"> Education </li>  
           <li class="selectable" id="HighSchoolDiploma"><a href="javascript:loadFrame('HighSchoolDiploma');">High School Diploma</a></li>  
           <li class="selectable" id="Bachelor"><a href="javascript:loadFrame('Bachelor');">Bachelor Degree</a></li>  
           <li class="selectable" id="Master"><a href="javascript:loadFrame('Master');">Master Degree</a></li>  
           <li class="selectable" id="Paper"><a href="javascript:loadFrame('Paper');">Scientific publication</a></li>  
           <li class="dropdown-header"> Certification </li>  
           <li class="selectable" id="TUV"><a href="javascript:loadFrame('TUV');">PM Certification</a></li>  
           <li class="selectable" id="Scrum"><a href="javascript:loadFrame('Scrum');">Scrum Master Certification</a></li>  
           <li class="selectable" id="HTML5"><a href="javascript:loadFrame('HTML5');">HTML5 Certification</a></li>  
           <li class="selectable" id="SAPHANA"><a href="javascript:loadFrame('SAPHANA');">SAP HANA Certification</a></li>  
         </ol>  
       </nav>  
   [...]  
   <!-- Bootstrap core JavaScript  
   ================================================== -->  
   <!-- Placed at the end of the document so the pages load faster -->  
   <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>  
   <script src="js/dropdown-menu.js"></script>  
   <script src="js/bootstrap.min.js"></script>  
   [...]  
  </body>  
 </html>  

eh voilá!


Constraint: 
The JS is based on the search of the tag <nav>.
This means: if you have more than one <nav> tag potentially it will not work. You need to have only one <nav>. It is easy to extend to code and change the search of the tag not on the tag type, but on the tag id.

Positive Attitude

There is no word to mention...