Friday, May 23, 2008

Capital of India hosts its first ever Blog Camp

India Gate, New DelhiI just learned that Delhi (Capital of India) is hosting its first ever Blog Camp. I was waiting for this event to happen but not sure if I can make it or not but I will make an attempt though.

Hats off to its organizers!!!

When
24th May 2008 9:30 am IST

Where
Microsoft Corp
5th Floor, Eros Towers
Nehru Place, New Delhi
Map

More details at http://www.barcamp.org/BlogCampDelhi

Storing milliseconds in Oracle

I got an interesting question from someone in my team today - does timestamp column store the time in milliseconds? If not, is it possible?

This is something I never bothered to think about so far...so I had to do some quick research...and eventually found that Oracle does not store millisecond directly but it does store seconds with a fraction component which can help you to get the milliseconds. Not only millisecond rather you can get to microseconds too! In fact you can specify the number of digits that oracle stores in the fractional part of seconds i.e. the precision part for TIMESTAMP datatype. The default is platform dependant, on UNIX it defaults to 6 and on windows it defaults to 3. The valid range is [0-9].

Here is how the fractional component is shown, which can be used to derive milliseconds :-



SIPRAS@orademo> select time_in_ms from test_tab;

TIME_IN_MS
------------------------------------------------
23-MAY-08 01.29.59.008864 PM
23-MAY-08 11.11.11.100000 AM


You can insert milliseconds too! here is an example :-




SIPRAS@orademo> create table test_tab (time_in_ms timestamp);

Table created.

SIPRAS@orademo> insert into test_tab values(systimestamp);

1 row created.

SIPRAS@orademo> insert into test_tab values('23-MAY-2008 11:11:11.1');

1 row created.

SIPRAS@orademo> commit;

Commit complete.

SIPRAS@orademo> select * from test_tab;

TIME_IN_MS
---------------------------------------------------------------------------
23-MAY-08 01.29.59.008864 PM
23-MAY-08 11.11.11.100000 AM


I was glad that Oracle does provide some mechanism to store milliseconds otherwise I would have been really surprised.

Wednesday, May 21, 2008

11g New Features: Data pump enhancements

I was going through the enhancements made to Oracle Data pump import/export utilities and a couple of them got my attention. To be very specific I liked the newly introduced remap_table and remap_data parameters most :-

  • Remap_Data
Remap the data of a column while importing; Basically you would use a function to do the remap. Particularly useful in numerous imports of production data. Now you can use this parameter to mask/convert data such as passwords, credit card details, email ids etc. Isn't that really cool?
  • Remap_Table
Now its possible to rename a table while importing. Useful? Yes, I think so; In situations where you keep table exports or want to get data from production and compare with stage data during bug fixing etc. With this parameter you would not need to import the table into a different schema (coz the table already exists in stage database) to compare. Indeed, another useful parameter.
Apart from the ones listed above, Oracle 11g has introduced a few other enhancement to data pump utilities which are :-

1. data compression => compress data and metadata before writing to the dump files
2. compress dump files => reduces dump file size by 10-15%
3. encryption features => you can encrypt data during export and can also specify encryption algorithm. you can encrypt dump files too!
4. data options => to ignore non-deferrable constraints
5. reuse dumpfiles => overwrite dump files; earlier data pump export would return an error if a dump file with same name exists. so now it will overwrite any existing dump files. its both good and bad since you may have to move dump files to a different location if you have a need to retain them.
6. transportable option for tables

In all, quite a few enhancements to data pump utility though I still don't understand the "transportable" option for tables and where/how do I put it to use. Thoughts?

BLOB support in SQL*Plus

Oracle 11g SQL*Plus client comes with an improvement - it now supports BLOB column; Now you can verify blob column content using sqlplus though you may not still be able to make a sense out it if the content is of type image or pdf etc.

Here is an example :-

$ sqlplus sipras

SQL*Plus: Release 11.1.0.6.0 - Production on Wed May 14 14:47:30 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SIPRAS@11glab> create table test (id number, pic blob);

Table created.

SIPRAS@11glab> insert into test values (1,'122334');

1 row created.

SIPRAS@11GLAB > select * from test;

ID
----------
PIC
----------------------------------------------------------------------------------------------------
1
122334


1 row selected.


- An example using image file being loaded into blob column

SIPRAS@11glab> create or replace directory blobdir as '/tmp';

Directory created.

SIPRAS@11glab>insert into test values(2, utl_raw.cast_to_raw('testing blob'));

SIPRAS@11GLAB > select * from test;

ID
----------
PIC
----------------------------------------------------------------------------------------------------
1
122334

2
74657374696E6720626C6F62


2 rows selected.


-- anonymous block to load the content of an image file to the blob


SIPRAS@11glab>DECLARE
vblob BLOB;
vbfile BFILE := BFILENAME ('BLOBDIR', '10gocplogo.jpg');
vamt INTEGER;
VSIZE INTEGER;
BEGIN
SELECT pic
INTO vblob
FROM TEST
WHERE ID = 1
FOR UPDATE;

DBMS_LOB.fileopen (vbfile);
VSIZE := DBMS_LOB.getlength (vbfile);
DBMS_OUTPUT.put_line ('Size of input file: ' || VSIZE);
DBMS_LOB.loadfromfile (vblob, vbfile, VSIZE);
DBMS_OUTPUT.put_line ('After loadfromfile');
VSIZE := DBMS_LOB.getlength (vblob);
DBMS_OUTPUT.put_line ('Size of blob: ' || VSIZE);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('An error occurred');
DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
END;
/

Size of input file: 15054
After loadfromfile
Size of blob: 15054

PL/SQL procedure successfully completed.

SQL> select * from test;

ID
----------
PIC
----------------------------------------------------------------------------------------------------
1
FFD8FFE000104A46494600010201012C012C0000FFED002C50686F746F73686F7020332E30003842494D03ED000000000010
012C000000010001012C000000010001FFEE000E41646F62650064C00000

2
74657374696E6720626C6F62

2 rows selected.

I know that you cannot interpret the values since it was an image file but still just wanted to show that you will not get "SP2-0678 Column or attribute type can not be displayed by SQL*Plus" error any more.

However, if you try the same thing from 10g client it won't work and you will get the SP2-0678 error :-

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 14 13:21:11 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SIPRAS@11glab> truncate table test;
Table truncated.

SIPRAS@11glab> insert into test values (1,'122334');

1 row created.

SIPRAS@11glab> select * from test;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus

Tuesday, May 13, 2008

11g New Features - Tablespace Encryption

You might have heard about Transparent Data Encryption or TDE in 10g; In Oracle 11g, it has been taken to a next level with "tablespace encryption". So TDE which started at column level is available at tablespace level.

How does it help?
Now instead of encrypting the columns one-by-one in a table or for a set of tables, you can simply put all tables which need to be encrypted in a single tablespace and encrypt it.

How to do it?
- Create and open the wallet
- Create tablespace with encryption property

CREATE TABLESPACE encrypt_tbsp
DATAFILE '/
oradata/encrypt_tbsp01.dbf'
SIZE 100M

ENCRYPTION

DEFAULT STORAGE(ENCRYPT);


- Create tables in the encrypted tablespace

How to find if tablespace is encrypted or not?
- A new column "ENCRYPTED" has been added to DBA_TABLESPACES which will indicate if the tablespace is encrypted or not.
- Query a new dynamic performance view V$ENCRYPTED_TABLESPACES for encrypted tablespaces.

Do I need to do anything special to access data from a encrypted tablespace?
Not really, you continue to access data as usual BUT the wallet must be open. No modification whatsoever is required for the code/SQL to access data from encrypted tablespace.

What is supported and not supported with encrypted tablespaces?
Supported
- Move table back and forth between encrypted tablespace and non-encrypted tablespace
- Datapump is supported to export/import encrypted content/tablespaces

- Transportable tablespace is supported using datapump

Not Supported
- Tablespace encryption cannot be used for SYSTEM, SYSAUX, UNDO and TEMP tablespaces
- Existing tablespace cannot be encrypted

- Traditional export/import utilities for encrypted content


Though I have not been able to create an encrypted tablespace but still wanted to share this information. I will try to post my learnings from the exercise later. Hope this is useful. NJoy! encrypting and do let me know if I have missed out any key messaging herein.

References:-
Oracle® Database Advanced Security Administrator's Guide 11g Release 1 (11.1) Part Number B28530-02

Thursday, May 8, 2008

11g New Feature - SYSDBA authentication gets stronger

The SYSDBA & SYSOPER authentication gets stronger from 11g; Oracle now extended support for PKI, Kerberos and Radius to SYSDBA and SYSOPER connections. Earlier, this was limited to all users except SYSDBA and SYSOPER. A new initialization parameter LDAP_DIRECTORY_SYSAUTH has been introduced which needs to be set along with LDAP_DIRECTORY_ACCESS for stronger SYSDBA authentication.

You login as SYSDBA the following way if you configure Directory authentication (OID) :-

CONNECT user@11glab AS SYSDBA
Enter password: password

If you have used Kerberos or SSL then connect as SYSDBA the following way :-

CONNECT /@11glab AS SYSDBA

References: Oracle® Database Security Guide 11g Release 1 (11.1) Part Number B28531-04