Choosing a password scheme for the database

In the Security Guide there is a section to assist you with the decisions about what rules you might want to have in place when users choose passwords, namely attributes like the minimum length of a password, the types of characters it must (and must not) contain, re-use of old passwords etc etc. The documentation refers to a number of pre-supplied routines that are now available in 12c to assist administrators.  This is just a quick blog post to let you know that there is no “smoke and mirrors” going on here in terms of these functions. We’re implementing them in the same way that you might choose to build them yourself. In fact, you can readily take a look at exactly what the routines do because they are just simple PL/SQL code:


SQL> select text
  2   from dba_source
  3  where name in (
  4    'ORA12C_STRONG_VERIFY_FUNCTION'
  5    'ORA12C_VERIFY_FUNCTION',
  6    'ORA_COMPLEXITY_CHECK',
  7    'ORA_STRING_DISTANCE')
  8  order by name, line;

TEXT
----------------------------------------------------------------------------------------------------------------------------------
function ora12c_strong_verify_function
(username varchar2,
 password varchar2,
 old_password varchar2)
return boolean IS
   differ integer;
begin
   if not ora_complexity_check(password, chars => 9, upper => 2, lower => 2,
                           digit => 2, special => 2) then
      return(false);
   end if;

   -- Check if the password differs from the previous password by at least
   -- 4 characters
   if old_password is not null then
      differ := ora_string_distance(old_password, password);
      if differ < 4 then
         raise_application_error(-20032, 'Password should differ from previous '
                                 || 'password by at least 4 characters');
      end if;
   end if;

   return(true);
end;

FUNCTION ora12c_verify_function
(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
   differ integer;
   db_name varchar2(40);
   i integer;
   reverse_user dbms_id;
   canon_username dbms_id := username;
BEGIN
   -- Bug 22369990: Dbms_Utility may not be available at this point, so switch
   -- to dynamic SQL to execute canonicalize procedure.
   IF (substr(username,1,1) = '"') THEN
     execute immediate 'begin dbms_utility.canonicalize(:p1,  :p2, 128); end;'
                        using IN username, OUT canon_username;
   END IF;
   IF NOT ora_complexity_check(password, chars => 8, letter => 1, digit => 1,
                               special => 1) THEN
      RETURN(FALSE);
   END IF;

   -- Check if the password contains the username
   IF regexp_instr(password, canon_username, 1, 1, 0, 'i') > 0 THEN
     raise_application_error(-20002, 'Password contains the username');
   END IF;

   -- Check if the password contains the username reversed
   FOR i in REVERSE 1..length(canon_username) LOOP
     reverse_user := reverse_user || substr(canon_username, i, 1);
   END LOOP;
   IF regexp_instr(password, reverse_user, 1, 1, 0, 'i') > 0 THEN
     raise_application_error(-20003, 'Password contains the username ' ||
                                     'reversed');
   END IF;

   -- Check if the password contains the server name
   select name into db_name from sys.v$database;
   IF regexp_instr(password, db_name, 1, 1, 0, 'i') > 0 THEN
      raise_application_error(-20004, 'Password contains the server name');
   END IF;

   -- Check if the password contains 'oracle'
   IF regexp_instr(password, 'oracle', 1, 1, 0, 'i') > 0 THEN
        raise_application_error(-20006, 'Password too simple');
   END IF;

   -- Check if the password differs from the previous password by at least
   -- 3 characters
   IF old_password IS NOT NULL THEN
     differ := ora_string_distance(old_password, password);
     IF differ < 3 THEN
        raise_application_error(-20010, 'Password should differ from the '
                                || 'old password by at least 3 characters');
     END IF;
   END IF ;

   RETURN(TRUE);
END;

function ora_complexity_check
(password varchar2,
 chars integer := null,
 letter integer := null,
 upper integer := null,
 lower integer := null,
 digit integer := null,
 special integer := null)
return boolean is
   digit_array varchar2(10) := '0123456789';
   alpha_array varchar2(26) := 'abcdefghijklmnopqrstuvwxyz';
   cnt_letter integer := 0;
   cnt_upper integer := 0;
   cnt_lower integer := 0;
   cnt_digit integer := 0;
   cnt_special integer := 0;
   delimiter boolean := false;
   len integer := nvl (length(password), 0);
   i integer ;
   ch char(1);
begin
   -- Check that the password length does not exceed 2 * (max DB pwd len)
   -- The maximum length of any DB User password is 128 bytes.
   -- This limit improves the performance of the Edit Distance calculation
   -- between old and new passwords.
   if len > 256 then
      raise_application_error(-20020, 'Password length more than 256 characters');
   end if;

   -- Classify each character in the password.
   for i in 1..len loop
      ch := substr(password, i, 1);
      if ch = '"' then
         delimiter := true;
      elsif instr(digit_array, ch) > 0 then
         cnt_digit := cnt_digit + 1;
      elsif instr(alpha_array, nls_lower(ch)) > 0 then
         cnt_letter := cnt_letter + 1;
         if ch = nls_lower(ch) then
            cnt_lower := cnt_lower + 1;
         else
            cnt_upper := cnt_upper + 1;
         end if;
      else
         cnt_special := cnt_special + 1;
      end if;
   end loop;

   if delimiter = true then
      raise_application_error(-20012, 'password must NOT contain a '
                               || 'double-quotation mark which is '
                               || 'reserved as a password delimiter');
   end if;
   if chars is not null and len < chars then
      raise_application_error(-20001, 'Password length less than ' ||
                              chars);
   end if;

   if letter is not null and cnt_letter < letter then
      raise_application_error(-20022, 'Password must contain at least ' ||
                                      letter || ' letter(s)');
   end if;
   if upper is not null and cnt_upper < upper then
      raise_application_error(-20023, 'Password must contain at least ' ||
                                      upper || ' uppercase character(s)');
   end if;
   if lower is not null and cnt_lower < lower then
      raise_application_error(-20024, 'Password must contain at least ' ||
                                      lower || ' lowercase character(s)');
   end if;
   if digit is not null and cnt_digit < digit then
      raise_application_error(-20025, 'Password must contain at least ' ||
                                      digit || ' digit(s)');
   end if;
   if special is not null and cnt_special < special then
      raise_application_error(-20026, 'Password must contain at least ' ||
                                      special || ' special character(s)');
   end if;

   return(true);
end;

function ora_string_distance
(s varchar2,
 t varchar2)
return integer is
   s_len    integer := nvl (length(s), 0);
   t_len    integer := nvl (length(t), 0);
   type arr_type is table of number index by binary_integer;
   d_col    arr_type ;
   dist     integer := 0;
begin
   if s_len = 0 then
      dist := t_len;
   elsif t_len = 0 then
      dist := s_len;
   -- Bug 18237713 : If source or target length exceeds max DB password length
   -- that is 128 bytes, then raise exception.
   elsif t_len > 128 or s_len > 128 then
     raise_application_error(-20027,'Password length more than 128 bytes');
   elsif s = t then
     return(0);
   else
      for j in 1 .. (t_len+1) * (s_len+1) - 1 loop
          d_col(j) := 0 ;
      end loop;
      for i in 0 .. s_len loop
          d_col(i) := i;
      end loop;
      for j IN 1 .. t_len loop
          d_col(j * (s_len + 1)) := j;
      end loop;

      for i in 1.. s_len loop
        for j IN 1 .. t_len loop
          if substr(s, i, 1) = substr(t, j, 1)
          then
             d_col(j * (s_len + 1) + i) := d_col((j-1) * (s_len+1) + i-1) ;
          else
             d_col(j * (s_len + 1) + i) := LEAST (
                       d_col( j * (s_len+1) + (i-1)) + 1,      -- Deletion
                       d_col((j-1) * (s_len+1) + i) + 1,       -- Insertion
                       d_col((j-1) * (s_len+1) + i-1) + 1 ) ;  -- Substitution
          end if ;
        end loop;
      end loop;
      dist :=  d_col(t_len * (s_len+1) + s_len);
   end if;

   return (dist);
end;

The good thing about this is that:

  • the routines are transparent, which is how all good security models should be presented. Because then they are open to scrutiny and not reliant on any kind of obfuscation to be secure, and 
  • if the routines do not exactly meet your requirements, then you now have a well established starting point from which to build your own custom routines.

Of course, if you are just after a string distance function, you might be better off using the pre-supplied UTL_MATCH package.

AskTOM–more experts to help you!

I’m thrilled to announce the “formal” addition of globalization and characterset guru Sergiusz Wolicki to the AskTOM team. I say “formal” addition because the team was already getting guidance from Sergiusz whenever we had tough question on charactersets, but just like his enthusiasm to help customers on the forums, Sergiusz was keen to help our AskTOM visitors as well.

Sergiusz is a 20+ year veteran of Oracle Corporation, with over half that time specializing in globalization, internationalization of Oracle products. It only takes a quick glance at the community space for Globalization to gauge the contribution he makes there !

image

 

And it didn’t take long before his knowledge came to good use on AskTOM !

 

image

Welcome Sergiusz !

Oracle Code … Not for database people ?

imageJump over to the Oracle Code home page and you will see the “mission statement” of the Oracle Code conference series:

“Learn from technical experts in sessions for developing software in Java, Node.js, and other languages and frameworks.”

You might hence be thinking that “old school” stuff like (relational) database technology has no place at such a conference, and certainly the agenda looks slanted away from database technologies.  But I think you’re wrong Smile and here is why I think that.  I did a talk in Bangalore last week at the Oracle Code event there (which by the way was a wonderful event, so thanks to all that came along) on some SQL language techniques.  After the talk, one of attendees came up to me, thanked me for the talk and said this (I’m paraphrasing):

“It was really interesting to see all the stuff that you could do in SQL.  I’m a Java person, and whenever I have complicated data requirements, I have always simply retrieved the data from the database and then done the complex part of the operations in Java.  But your session has convinced me to explore doing some of that in SQL”

It is so easy to have a bias for the technology(s) that you are most capable with.  I am just as guilty of that as anyone. I’ll generally look for a SQL or PL/SQL means to solve a business problem before considering other options that may actually be more appropriate.  But here we had an attendee who was happy to consider looking outside his sphere of expertise to focus on optimal solutions to problems rather than just solutions that sat inside his “comfort zone”.  That really struck a chord with me, and made me feel like the entire trip was worthwhile. Because when we have a bias toward a particular technology, it is easy to lulled into an argument that other technologies are inappropriate for any usage.  And then suddenly we’re into a shouting match about why technology “X” is the best and that anything that is not technology “X” is junk.  We all lose when that’s the case.

So there’s an argument to made that Oracle Code is indeed not for database developers, but in the same way, it is not for middle tier developers, and not for front end developers.  Oracle Code is about creating the balanced developer – a developer that has expertise in one (or more) areas but more importantly, can understand the whole stack and have an impartial, unclouded (no pun intended) view of the benefits of all layers in the application stack.  Because that balance ultimately leads to a better development community, and better opportunities to maximize the benefits of each of the components in the array of technologies that now permeate our development careers.

So whatever your area of expertise, Oracle Code has something for you, and and perhaps the best thing you can do at an Oracle Code event, is attend something outside your current area of expertise.

Interval partitioning just got better

Interval partitioning was a great feature when it arrived in version 11, because we no longer had to worry so much about ensuring partitions were available for new data when it arrived.  Partitions would just be created on the fly as required.  I’m not going to talk about interval partition in detail because there’s plenty of good content already out there.  But one key element for interval partitioning is that the intervals have to start from somewhere, which is why you always have to define a table with at least one partition.

 

image

 

So what if I want to drop that partition that is the conceptual “starting point”.  Well…I get problems Smile


SQL> create table sales
  2  ( tstamp       timestamp    not null,
  3    empno        number(10)   not null,
  4    ename        varchar2(10) not null,
  5    deptno       varchar2(10) not null
  6  )
  7  partition by range (tstamp)
  8  interval( numtoyminterval(1,'YEAR'))
  9  (
 10    partition p00 values less than (timestamp '2010-01-01 00:00:00')
 11  );

Table created.

SQL>
SQL> insert into sales values ( timestamp '2011-01-01 00:00:00' , 0,0,0);

1 row created.

SQL> insert into sales values ( timestamp '2012-01-01 00:00:00' , 0,0,0);

1 row created.

SQL> insert into sales values ( timestamp '2013-01-01 00:00:00' , 0,0,0);

1 row created.

SQL>
SQL> col high_value format a36
SQL> col pname format a10
SQL>
SQL> select
  2    partition_name pname,
  3    partition_position pos,
  4    high_value,
  5    interval
  6  from   user_tab_partitions
  7  where  table_name = 'SALES';

PNAME             POS HIGH_VALUE                           INT
---------- ---------- ------------------------------------ ---
P00                 1 TIMESTAMP' 2010-01-01 00:00:00'      NO
SYS_P121            2 TIMESTAMP' 2012-01-01 00:00:00'      YES
SYS_P122            3 TIMESTAMP' 2013-01-01 00:00:00'      YES
SYS_P123            4 TIMESTAMP' 2014-01-01 00:00:00'      YES

4 rows selected.

SQL>
SQL> alter table sales drop partition p00;
alter table sales drop partition p00
                                 *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped

There is a simple workaround for that issue. If you re-issue the INTERVAL definition for the table, all of the existing partitions will be “upgraded” (or should be it downgraded…I dunno) to being range partitions. Hence we will now have “moved” the starting point, and can then drop the problem partition.


SQL> alter table sales set interval( numtoyminterval(1,'YEAR'));

Table altered.

SQL> select
  2    partition_name pname,
  3    partition_position pos,
  4    high_value,
  5    interval
  6  from   user_tab_partitions
  7  where  table_name = 'SALES';

PNAME             POS HIGH_VALUE                           INT
---------- ---------- ------------------------------------ ---
P00                 1 TIMESTAMP' 2010-01-01 00:00:00'      NO
SYS_P121            2 TIMESTAMP' 2012-01-01 00:00:00'      NO
SYS_P122            3 TIMESTAMP' 2013-01-01 00:00:00'      NO
SYS_P123            4 TIMESTAMP' 2014-01-01 00:00:00'      NO

4 rows selected.

SQL> alter table sales drop partition p00;

Table altered.

So that’s all pretty easy, but of course, you must now run this “re-interval” command all the time to be sure that you will always be able to drop any partition you want.

Unless of course….. you’re on 12.2 ! Let’s repeat the demo on 12.2


SQL> create table sales
  2  ( tstamp       timestamp    not null,
  3    empno        number(10)   not null,
  4    ename        varchar2(10) not null,
  5    deptno       varchar2(10) not null
  6  )
  7  partition by range (tstamp)
  8  interval( numtoyminterval(1,'YEAR'))
  9  (
 10    partition p00 values less than (timestamp '2010-01-01 00:00:00')
 11  );

Table created.

SQL>
SQL> insert into sales values ( timestamp '2011-01-01 00:00:00' , 0,0,0);

1 row created.

SQL> insert into sales values ( timestamp '2012-01-01 00:00:00' , 0,0,0);

1 row created.

SQL> insert into sales values ( timestamp '2013-01-01 00:00:00' , 0,0,0);

1 row created.

SQL>
SQL> col high_value format a36
SQL> col pname format a10
SQL>
SQL> select
  2    partition_name pname,
  3    partition_position pos,
  4    high_value,
  5    interval
  6  from   user_tab_partitions
  7  where  table_name = 'SALES';

PNAME             POS HIGH_VALUE                           INT
---------- ---------- ------------------------------------ ---
P00                 1 TIMESTAMP' 2010-01-01 00:00:00'      NO
SYS_P3415           2 TIMESTAMP' 2012-01-01 00:00:00'      YES
SYS_P3416           3 TIMESTAMP' 2013-01-01 00:00:00'      YES
SYS_P3417           4 TIMESTAMP' 2014-01-01 00:00:00'      YES

4 rows selected.

SQL>
SQL> alter table sales drop partition p00;

Table altered.

SQL>
SQL> select
  2    partition_name pname,
  3    partition_position pos,
  4    high_value,
  5    interval
  6  from   user_tab_partitions
  7  where  table_name = 'SALES';

PNAME             POS HIGH_VALUE                           INT
---------- ---------- ------------------------------------ ---
SYS_P3415           1 TIMESTAMP' 2012-01-01 00:00:00'      NO
SYS_P3416           2 TIMESTAMP' 2013-01-01 00:00:00'      YES
SYS_P3417           3 TIMESTAMP' 2014-01-01 00:00:00'      YES

3 rows selected.

How cool is that! We now automatically modify one of the interval partitions to being a range partition, so you’ll not get the error.

The little features are often the coolest Smile

Quick tip–database link passwords

If you are relying on database links in your application, think carefully about how you want to manage the accounts that you connect with, in particular, when it comes to password expiry.

With a standard connect request to the database, if your password is going to expire soon, you will get some feedback on this:



SQL> conn demo/demo@np12
ERROR:
ORA-28002: the password will expire within 6 days


Connected.


But when using those same credentials via a database link, you will not get any warning, so when that password expires…you might be dead in the water.



SQL> create database link demolink connect to demo identified by demo using 'np12';

Database link created.

SQL> select * from tab@demolink;

TNAME
------------------------------------------------------------------------------------------------
TABTYPE  CLUSTERID
------- ----------
EMPLOYEES
TABLE

AskTOM TV episode 8

On AskTOM episode 8, I’ve taken a look at locating the SQL Plan Directives used for a particular query.  Here is the script output from the video if you want to use this for your own exploration


SQL>
SQL> create table t as
  2  select *
  3  from dba_objects
  4  where owner = 'SYS' and rownum <= 20
  5  union all
  6  select *
  7  from dba_objects
  8  where owner = 'SYSTEM'
  9  and rownum <= 200;

Table created.

SQL>
SQL> create index ix on t ( owner);

Index created.

SQL>
SQL> select COLUMN_NAME,NUM_DISTINCT,AVG_COL_LEN, num_nulls, density
  2  from   user_tab_cols
  3  where  table_name = 'T'
  4  order by COLUMN_ID;

COLUMN_NAME                    NUM_DISTINCT AVG_COL_LEN  NUM_NULLS    DENSITY
------------------------------ ------------ ----------- ---------- ----------
OWNER                                     2           7          0         .5
OBJECT_NAME                             199          18          0 .005025126
SUBOBJECT_NAME                            1           3        199          1
OBJECT_ID                               220           4          0 .004545455
DATA_OBJECT_ID                          167           4         49 .005988024
OBJECT_TYPE                               9           8          0 .111111111
CREATED                                  12           8          0 .083333333
LAST_DDL_TIME                            16           8          0      .0625
TIMESTAMP                                13          20          0 .076923077
STATUS                                    1           6          0          1
TEMPORARY                                 2           2          0         .5
GENERATED                                 2           2          0         .5
SECONDARY                                 1           2          0          1
NAMESPACE                                 4           3          0        .25
EDITION_NAME                              0           0        220          0
SHARING                                   2          10          0         .5
EDITIONABLE                               1           2        206          1
ORACLE_MAINTAINED                         1           2          0          1
APPLICATION                               1           2          0          1
DEFAULT_COLLATION                         1           7        136          1
DUPLICATED                                1           2          0          1
SHARDED                                   1           2          0          1
CREATED_APPID                             0           0        220          0
CREATED_VSNID                             0           0        220          0
MODIFIED_APPID                            0           0        220          0
MODIFIED_VSNID                            0           0        220          0

26 rows selected.

SQL>
SQL> insert into t  select * from dba_objects;

78329 rows created.

SQL> insert into t  select * from dba_objects;

78329 rows created.

SQL> insert into t  select * from dba_objects;

78329 rows created.

SQL> insert into t  select * from dba_objects;

78329 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select /*+ gather_plan_statistics */ count(created)
  2  from t
  3  where owner = 'SYS'
  4  and object_type = 'JAVA CLASS';

COUNT(CREATED)
--------------
        138424

1 row selected.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3qyuxjtjy92m5, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(created) from t where owner
= 'SYS' and object_type = 'JAVA CLASS'

Plan hash value: 2143077847

-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |      1 |00:00:00.08 |    5991 |
|   1 |  SORT AGGREGATE                      |      |      1 |      1 |      1 |00:00:00.08 |    5991 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |     12 |    138K|00:00:00.07 |    5991 |
|*  3 |    INDEX RANGE SCAN                  | IX   |      1 |    110 |    207K|00:00:00.03 |    1218 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='JAVA CLASS')
   3 - access("OWNER"='SYS')


22 rows selected.

SQL>
SQL> select sql_id, child_number,is_reoptimizable  from v$sql where sql_id = '3qyuxjtjy92m5';

SQL_ID        CHILD_NUMBER I
------------- ------------ -
3qyuxjtjy92m5            0 Y

1 row selected.

SQL>
SQL> exec dbms_spd.FLUSH_SQL_PLAN_DIRECTIVE

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ gather_plan_statistics */ count(created)
  2  from t
  3  where owner = 'SYS'
  4  and object_type = 'JAVA CLASS';

COUNT(CREATED)
--------------
        138424

1 row selected.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3qyuxjtjy92m5, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(created) from t where owner
= 'SYS' and object_type = 'JAVA CLASS'

Plan hash value: 2143077847

-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |      1 |00:00:00.06 |    5383 |
|   1 |  SORT AGGREGATE                      |      |      1 |      1 |      1 |00:00:00.06 |    5383 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |    138K|    138K|00:00:00.05 |    5383 |
|*  3 |    INDEX RANGE SCAN                  | IX   |      1 |    220 |    207K|00:00:00.02 |     610 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='JAVA CLASS')
   3 - access("OWNER"='SYS')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - statistics feedback used for this statement
   - performance feedback used for this statement
   - 1 Sql Plan Directive used for this statement


29 rows selected.

SQL>
SQL> select count(*) from dba_sql_plan_directives;

  COUNT(*)
----------
       354

1 row selected.

SQL>
SQL> set lines 60
SQL>
SQL> desc v$sql_plan
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ADDRESS                                RAW(8)
 HASH_VALUE                             NUMBER
 SQL_ID                                 VARCHAR2(13)
 PLAN_HASH_VALUE                        NUMBER
 FULL_PLAN_HASH_VALUE                   NUMBER
 CHILD_ADDRESS                          RAW(8)
 CHILD_NUMBER                           NUMBER
 TIMESTAMP                              DATE
 OPERATION                              VARCHAR2(30)
 OPTIONS                                VARCHAR2(30)
 OBJECT_NODE                            VARCHAR2(40)
 OBJECT#                                NUMBER
 OBJECT_OWNER                           VARCHAR2(128)
 OBJECT_NAME                            VARCHAR2(128)
 OBJECT_ALIAS                           VARCHAR2(261)
 OBJECT_TYPE                            VARCHAR2(20)
 OPTIMIZER                              VARCHAR2(20)
 ID                                     NUMBER
 PARENT_ID                              NUMBER
 DEPTH                                  NUMBER
 POSITION                               NUMBER
 SEARCH_COLUMNS                         NUMBER
 COST                                   NUMBER
 CARDINALITY                            NUMBER
 BYTES                                  NUMBER
 OTHER_TAG                              VARCHAR2(35)
 PARTITION_START                        VARCHAR2(64)
 PARTITION_STOP                         VARCHAR2(64)
 PARTITION_ID                           NUMBER
 OTHER                                  VARCHAR2(4000)
 DISTRIBUTION                           VARCHAR2(20)
 CPU_COST                               NUMBER
 IO_COST                                NUMBER
 TEMP_SPACE                             NUMBER
 ACCESS_PREDICATES                      VARCHAR2(4000)
 FILTER_PREDICATES                      VARCHAR2(4000)
 PROJECTION                             VARCHAR2(4000)
 TIME                                   NUMBER
 QBLOCK_NAME                            VARCHAR2(128)
 REMARKS                                VARCHAR2(4000)
 OTHER_XML                              CLOB
 CON_ID                                 NUMBER

SQL> set lines 200
SQL>
SQL>
SQL> select other from v$sql_plan
  2  where other is not null
  3  and sql_id = '3qyuxjtjy92m5'
  4  and child_number > 0;

no rows selected

SQL>
SQL> select remarks from v$sql_plan
  2  where remarks is not null
  3  and sql_id = '3qyuxjtjy92m5'
  4  and child_number > 0;

no rows selected

SQL>
SQL> select other_xml from v$sql_plan
  2  where other_xml is not null
  3  and sql_id = '3qyuxjtjy92m5'
  4  and child_number > 0;

OTHER_XML
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<other_xml><info type="performance_feedback" note="y">yes</info><info type="cardinality_feedback" ...


1 row selected.

SQL>
SQL> select xmltype(other_xml) from v$sql_plan
  2  where other_xml is not null
  3  and sql_id = '3qyuxjtjy92m5'
  4  and child_number > 0;

XMLTYPE(OTHER_XML)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<other_xml>
  <info note="y" type="performance_feedback">yes</info>
  <info note="y" type="cardinality_feedback">yes</info>
  <info type="db_version">12.2.0.1</info>
  <info type="parse_schema"></info>
  <info note="y" type="dynamic_sampling">2</info>
  <info type="plan_hash_full">1068910003</info>
  <info type="plan_hash">2143077847</info>
  <info type="plan_hash_2">1068910003</info>
  <spd>
    <cv>0</cv>
    <cu>1</cu>
  </spd>
  <outline_data>
    <hint></hint>
    <hint></hint>
    <hint></hint>
    <hint></hint>
    <hint></hint>
    <hint></hint>
    <hint></hint>
  </outline_data>
</other_xml>


1 row selected.

SQL>
SQL> explain plan for
  2  select count(created)
  3  from t
  4  where owner = 'SYS'
  5  and object_type = 'JAVA CLASS';

Explained.

SQL>
SQL> select * from table(dbms_xplan.display(format=>'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2143077847

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |     1 |    23 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |      |     1 |    23 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |   143K|  3226K|     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX   |   220 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T@SEL$1
   3 - SEL$1 / T@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='JAVA CLASS')
   3 - access("OWNER"='SYS')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT("CREATED")[22]
   2 - "CREATED"[DATE,7]
   3 - "T".ROWID[ROWID,10]

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement

35 rows selected.

SQL>
SQL> select * from table(dbms_xplan.display(format=>'all +metrics'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2143077847

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |     1 |    23 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |      |     1 |    23 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |   143K|  3226K|     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX   |   220 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T@SEL$1
   3 - SEL$1 / T@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='JAVA CLASS')
   3 - access("OWNER"='SYS')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT("CREATED")[22]
   2 - "CREATED"[DATE,7]
   3 - "T".ROWID[ROWID,10]

Sql Plan Directive information:
-------------------------------

  Used directive ids:
    14906410523430420431

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement

41 rows selected.

SQL>
SQL>

Top Ten Travel hints and Tips

Well, let me be honest right at the top here.  These are not travel hints Smile  These will not help you in any way.

This is me having a whine and a rant about a minority of people that I occasionally encounter when travelling.

Yes, this can probably be best described as me and my first world problems, but I need to expunge these so that next time I travel, I don’t lose my head, and stuff some poor unsuspecting innocent passenger“under the seat in front of me or in the overhead locker” Smile

So sit back, relax and enjoy Connor’s “Travel Tips” Smile  (possibly NSFW)

1) Boarding pass

The term “boarding pass” stems from the Latin derivation: “The pass you need to present in order to board the bloody plane!”

So you know what ? At some stage, there is going to be a person who wants to see your boarding pass.  Incredible eh ?  And do you know where this happens ?  In every freakin’ airport !  We don’t need to suffer while you present a bemused expression to the security person asking for boarding pass, whilst you say “Oh….do I need my boarding pass ?  Let me hunt for it in the bottom of my bag for 15 minutes”.  On a recent flight, I even saw someone launch into a debate with the ground staff about why they have to present their pass!  Seriously ?  Were you trying out for the school debating team ?  Just keep it in your pocket or in your hand, and you’re done.  Easy !

2) Security check

There’s also going to be some people who want to X-ray your stuff.  Do you know where this happens ?  In every freakin’ airport !  And that huge placard that just about hit you on the head as you entered the security checkpoint said something along the lines of:

  • Take out your laptop
  • Empty your pockets

or we can take that down to real simple terms…. Metal and electrical stuff – bad.  Human body – good. Pretty….simple….concept.

But that’s ok, you can just ignore all that, because nothing makes a security officer feel more complete, than watching that bag of yours go though the X-ray machine 4 times, each time with one less electronic device in it, combined with your silly grin and shrug of the shoulders…. And they’ll ever happier when you follow that up with you carrying your wallet, phone, pocket knife, kitchen utensils, meccano set, your complete IKEA Applaro outdoor furniture setting, and a Milwaukee ride-on lawn mower, all stuffed in your jeans pockets, so that body scanner sounds more like Tchaikovsky’s Dance of the Sugar Plum fairy.

3) Boarding the plane

A few budget airlines have a policy where the seats are no allocated, you simply take what is available as you enter plane.

But the huge majority of airlines offer an incredible, amazingly sophisticated service for you as a passenger.

It’s called …. AN ALLOCATED SEAT.

You’re going to get one.  Incredible isn’t it ?!?!  Whether you like it or not… there is a seat on that plane that is waiting for you… JUST FOR YOU!  It doesn’t matter if you are the first person on the plane, or the last person on the plane, you are going to get that seat.  Because we’ve all seen the unholy hell of a nightmare that results when a passenger does not arrive for their seat after they’ve checked in.  Staff run around in a total panic like headless chickens yelling out “MR SMITH !!! URGENTLY PAGING MR SMITH!!!!”.  Every airline employee involved with that airline’s imminent departure is thoroughly invested in getting you to the your seat so the plane can leave, and they can see the back of you and go grab a coffee.

Do you know what this means ?  It means that when the boarding announcement is made, you do not have to charge the gate like the Orc army in the Lord of the Rings.  Do you know why airlines board the plane in a particular order ?  So they get can the damn plane into the air and on it’s way! That is sortta a prerequisite of travelling by plane to a destination – at some stage the plane has to get into the air for this to work ! You are not trying to storm a battlefront, or escape a stampede of bison, or get a limited edition of the AskTom commemorative sticker Smile.

So surprise surprise … if you wait for your boarding zone to be called, you will actually get to your destination faster.  And as a bonus, we can all get away faster.

4) Carry on

There is probably some unique set of circumstances out there, or some incredibly rare set of events put in motion, that means on this particular day, on this particular flight:

  • you are emigrating to another country never to return, AND
  • the aircraft is doing an emergency shipment of food to a stranded herd of Nepalese mountain goats, and hence the cargo hold is full of hay.

but unless both of those conditions are true, then sorry, you do not need to bring a metric tonne of belongings spread across 34 bags into the cabin.  If you don’t carry so much junk with you, you’re also less likely to needing riot gear to rush the door (see 3 above) worrying that you won’t be able to find storage space for those 34 bags you’re lugging.  And if you had listened to me in #1, you’d have your boarding pass in one hand, and only one hand left for carry-on luggage.  That’s ample!

5) Your seat

“Wow, I walked onto the plane, and every row number was just in a random order throughout the plane”

… said no passenger on any airline ever.

It’s pretty simple.  The numbers start low and get higher.  It’s a lot like …. hmm… what’s the term I’m looking for …. oh yeah, counting! Smile  Don’t get me wrong – we’ve all done the “walk mistake” or “sit mistake” where we end up 1 row adjacent to where we should have gone.  No problems with that – it’s easy to fix.  But how on earth did you get down to row 64 when your boarding pass said “Row 17”.  What happened in that long slow walk down the plane where you missed the numbers 18 through 63 ?  I reckon I know why you missed those numbers – you were looking for storage bins to put your 34 carryon bags, plus the IKEA Applaro outdoor furniture setting that’s in your back pocket (which would be uncomfortable to sit on for the flight) Smile

Now, if you’re going to take me to task on this one and tell me that things might be more complicated on an A380 because of it’s multi-deck system, then I’m still not budging.  See #3 above.  When the boarding call announcements are made, the ground staff will tell you which door to take to the plane.  But you might have missed that during your Ussain Bolt impersonation trying to be the first person on the plane Smile

(Caveat: If the standard decimal Hindu-Arabic number system is not native to your language…you get a leave pass for this one)

6) The loo (toilet, rest room)

There’s plenty of ridiculous research studies performed each year, so perhaps somewhere, in some remote corner of our planet, there is a study being conducted to see in how many ways in a confined space a male can pee and deliberately not hit the target.

An aircraft is not one of the places…. Ugh.

7) Your phone

I think I side with the majority here, in that I’m pretty confident that using mobile phone is not going to cause any problems to the plane.

But … unless I work as an electronics/avionics engineer for that airline I’m flying on, that is not my decision to make.  It’s not yours either.

So when the announcement to turn off or flight mode your phone is made – try this technique:  Stop using your damn phone.

Because even if there is only a 0.001% chance of your phone usage causing an issue on the plane, there is no way that you “Poking” or “Liking” your cousin’s new gluten-free sugar-free chocolate chip muffin recipe on Facebook even comes close to taking even that infinitesimal risk.  When they’re crawling through the wreckage of the plane to find your remains, that’s not so great a eulogy to have read out at your funeral: “Yes, John did bring the plane down with his phone by messing up its navigation, but at least he enjoyed cousin Susie’s Paleo muffins”

8) The seat belt sign

I’ve travelled a lot.  Like most people, I’m not a fan of turbulence, but there are some times when I am literally appealing to a higher power for sudden, unexpected, near catastrophic turbulence to hit the plane – even if we’re still on the ground!  It’s when we’re about to take off, or we’re about to land, because someone has decided it’s time to get out of their seat and and embark upon a voyage of exploration throughout the entire cabin.  You can see their mindset – “I paid for my ticket. Why should I have to abide by the instructions of the flight attendant?”. If they just could take their over-inflated sense of self-importance offline for a few minutes, they’d probably realise that that trip to the galley area to demand an apple juice, (because hey, apple juice is much more critical than the tasks that flight attendants are currently doing in preparation for take off) could probably wait for a few minutes until the seat belt sign is switched off.  Which it will be for 99% of the flight!

The other day, I was in a plane that was seriously 20 seconds from touching down, and some joker gets up and makes his way to the toilet.  They had been locked by the flight attendants because …. we’re busy landing the bloody plane!  But that’s lets face it, if a seat belt sign hasn’t deterred the passenger, then nothing as simple as a locked door is going to stop him either.  So he’s standing there trying to break down the door like a SWAT team doing a drug raid, whilst the poor flight attendant has to put her safety at risk to get this numpty to the sit the hell back down.

9) The baggage carousel

As well as SpaceX and cross-continental transport tunnels, Elon Musk has already invested billions of dollars into baggage carousels at airports. Baggage carousels are incredibly hi-tech pieces of equipment.  There is a series of highly sophisticated Bluetooth devices that scan every single passenger as they approach via the entrance hall – devices that measure each passenger’s exact distance from their shoes to the carousel down to the nearest millimetre.  These measurements are sent wirelessly to the luggage delivery staff who then proceed to deliver the suitcases in exactly the order of proximity of passengers to the carousel.  The closer you stand, the faster your bag will come out.

I don’t have any concrete evidence of Elon’s involvement in baggage carousels – but surely that must be the case, because what other reason could there be for people to jam themselves around the thing like sardines in a can, making it impossible for anyone to see or even pick up their bag. Oh, and here’s a great idea – grab that airport luggage trolley and jam it right up against the carousel as well.  Heaven knows, if you packed 34 items of carry-on, you probably have an aircraft carrier plus a 4-storey building jammed into your suitcase.  No way you’ll be able to lift that more than a couple of inches.

Pro tip:  Standing just 1 meter back from the carousel lets everyone easily see and grab their bags.  And there’s no Bluetooth either, or Elon Musk for that matter.  He’s got more important stuff to do than watch you make a fool of yourself around a baggage carousel. Smile

10 ) Your airline staff

This is perhaps the motivation for this entire post.  On a few flights recently, I’ve seen the flight attendants over stretched in their duties because they are spending so much time trying to get passengers to do what passengers are meant to be doing without explicit instruction.  Or even worse, people being just plain rude to them.  Man, that gets me wound up.  So wound up it makes me want to blog about it Smile

Whatever your profession, nothing is more insulting than someone coming up to you and basically announcing “Whatever task you are doing is not as important as you catering to my most trivial of needs right now”.  It’s disrespectful and demeaning.  At the end of my work day, I feel best when I look back at the day and think “I achieved a lot today”.  More and more nowadays, when I’m a plane sitting within eyeshot of a flight attendants, when the plane lands I don’t see a look of job satisfaction; I see “Thank heavens, these rude and ignorant people will be getting off this damn plane so I won’t have to deal with them again”.  That’s a really sad reflection on us as passengers.

So there’s my “travel tips” for you.  Happy flying Smile