I 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
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; |
SIPRAS@orademo> create table test_tab (time_in_ms timestamp); |
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 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?
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 :-
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.
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 '/
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:-
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
