11g New Features - Virtual column

The other day while going through Oracle forums, I found an interesting question on partitioning. The gentlemen had a problem which most of us face; He wanted to partition a table based on year and month but both columns were being stored in CHAR datatype; here is the table structure

CREATE TABLE SALES
(
YEARS CHAR(4 BYTE),
MONTH CHAR(2 BYTE),
)

The solution, I gave, was to add a new column just for the partition purpose. In fact thats how it used to happen till 10g. But 11g comes with an interesting feature; you can now create a virtual column. I have used the same table structure to explain how virtual column works in 11g. Basically virtual column is nothing but a derived column; you do not insert data into it rather it derives its value based on the values inputed in some other columns or combination of columns. It even gives you the capability to write a small piece of code to generate the value.

Create the table with a virtual column to partition the data into 4 partitions Q1, Q2, Q3 and Q4.

CREATE TABLE SALES
(
YEARS VARCHAR2(4),
MONTH VARCHAR2(2),
PART_COL
VARCHAR2(6)
generated always as
(
case
when MONTH in ('01','02','03')
then 'Q1'
when MONTH in ('04','05','06')
then 'Q2'
when MONTH in ('07','08','09')
then 'Q3'
when MONTH in ('10','11','12')
then 'Q4'
end
) virtual
)
partition by list (PART_COL)
(
partition p_q1 values ('Q1'),
partition p_q2 values ('Q2'),
partition p_q3 values ('Q3'),
partition p_q4 values ('Q4')
);

Insert data into the table

insert into sales (years,month) values ('2007','01');
insert into sales (years,month) values ('2007','01');
insert into sales (years,month) values ('2007','02');
insert into sales (years,month) values ('2007','02');
insert into sales (years,month) values ('2007','12');
insert into sales (years,month) values ('2007','09');
insert into sales (years,month) values ('2007','05');
insert into sales (years,month) values ('2007','07');
insert into sales (years,month) values ('2007','11');

SIPRAS@11glab> commit;

Now select from the table and see how the data has gone into different partitions

SIPRAS@11glab> select * from sales;

YEAR MO PA
---- -- --
2007 01 Q1
2007 02 Q1
2007 02 Q1
2007 01 Q1
2007 05 Q2
2007 09 Q3
2007 07 Q3
2007 12 Q4
2007 11 Q4

9 rows selected.

SIPRAS@11glab> select * from sales partition (p_q1);

YEAR MO PA
---- -- --
2007 01 Q1
2007 02 Q1
2007 02 Q1
2007 01 Q1

SIPRAS@11glab> select * from sales partition (p_q2);

YEAR MO PA
---- -- --
2007 05 Q2

SIPRAS@11glab> select * from sales partition (p_q3);

YEAR MO PA
---- -- --
2007 09 Q3
2007 07 Q3

SIPRAS@11glab> select * from sales partition (p_q4);

YEAR MO PA
---- -- --
2007 12 Q4
2007 11 Q4

According to me, Virtual column is a real powerful addition. In situation like this I need not force the development team to insert data into the new column that will be used for partition or write a trigger for it. Simply creating virtual column and partitioning it would do wonders. Moreover it's use not limited only for partitions; it can be used in general e.g. wherever we use a derived columns which gets it value through a trigger or a stored procedures or a default value (only to be updated by a piece of code later).

Reference :- Oracle Database VLDB and Partitioning Guide 11g Release 1 (11.1)

11g New Features - Case-sensitive passwords

Probably a long overdue feature...though one could have implemented the same using password verify function in earlier releases but it was necessitated to be in compliance with industry wide Data security standards. Starting 11g case sensitive passwords automatically enforced.

Here is how to implement case-sensitive passwords feature :-

SIPRAS@11glab> create user TESTUSR identified by TESTUSR;

User created.

SIPRAS@11glab> grant create session to TESTUSR;

Grant succeeded.

SIPRAS@11glab> conn testusr/testusr@11glab
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
@> conn testusr/TESTUSR@11glab
Connected.
TESTUSR@11glab>

See the difference - since the user was created with an upper case password, it did not allow lower case password while connecting to "TESTUSR". Had it been 10g, you would easily got connected. So now, "TESTUSR", "testusr" and "TestUsr" are different passwords.

However, Oracle has also provided an initialization parameter to disable case-sensitive passwords i.e. going back to old way of 10g and prior versions.

SIPRAS@11glab> show parameter SEC_CASE_SENSITIVE_LOGON

NAME TYPE VALUE
------------------------------------ ----------- ---------
sec_case_sensitive_logon boolean TRUE

SIPRAS@11glab> ALTER SYSTEM set SEC_CASE_SENSITIVE_LOGON=FALSE scope=both;

System altered.

SIPRAS@11glab> show parameter SEC_CASE_SENSITIVE_LOGON

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE

And now see the difference...

SIPRAS@11glab> conn testusr/testusr@11glab
Connected.

TESTUSR@11glab> conn testusr/TESTUSR@11glab
Connected.
TESTUSR@11glab>

So it would connect irrespective of case. A new column "PASSWORD_VERSIONS" has been added to "DBA_USERS" view to indicate database version in which the password was created or changed.

SIPRAS@11glab> select username,PASSWORD_VERSIONS from dba_users;

USERNAME PASSWORD
------------------------------ --------
.....
SCOTT 10G 11G
TESTUSR 10G 11G

However, I am not able to find answer as to why a user created in 11g has both "10G" and "11G" in PASSWORD_VERSIONS column. According to the documentation if a database was migrated from 10g then it would have both "10G", "11G" in it which is not true in my case..

One can also enforce case-sensitive passwords for SYSDBA users. Use "ignorecase" argument while creating password files using "ORAPWD" utility. Default values for "ignorecase" is "n", and you can set it to "y" to enable case-sensitive passwords.

e.g. $orapwd file=orapw entries=5 ignorecase=y

So if you plan to upgrade to 11g then make sure you change passwords to adhere to case-sensitivity and ensure that you change your scripts which have inconsistent password cases too.

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

11g New Features - "Read only Tables"

Did you read that right? It's read only TABLES...11g has introduced read only tables. It's now possible to make a table read only to it's owner also. Earlier we used to grant "SELECT" privilege on a table to other users or create a view to make it read only for others BUT that was only for other users and not the owner. See the example below to switch the table from read-write to read-only and vice-versa...

SCOTT@11glab> create table deptnew as select * from dept;

Table created.

SCOTT@11glab> select * from deptnew;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SCOTT@11glab> insert into deptnew values (50,'New Dept','New Location');

1 row created.

SCOTT@11glab> commit;

Commit complete.

Make it READ ONLY

SCOTT@11glab> alter table deptnew read only;

Table altered.

SCOTT@11glab> insert into deptnew values (60,'New Dep1','New Location1');
insert into deptnew values (60,'New Dep1','New Location1')
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."DEPTNEW"

How do I find out if a table is read-only or read-write. Check the new column in "USER_TABLES"

SCOTT@11glab> select table_name,read_only from user_tables where table_name='DEPTNEW';

TABLE_NAME REA
------------------------------ ---
DEPTNEW YES

Make it READ WRITE again to insert data...

SCOTT@11glab> alter table deptnew read write;

Table altered.

SCOTT@11glab> insert into deptnew values (60,'New Dep1','New Location1');

1 row created.

SCOTT@11glab> commit;

Commit complete.

SCOTT@11glab> select * from deptnew;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 New Dept New Location
60 New Dep1 New Location1

6 rows selected.

Very useful indeed...

11g - My first look

It's been a while since I have installed 11g but hardly got a chance to explore or look at it's new features. It was only last week that I started exploring it. There are quite a bit of really interesting and useful new features; I will start posting on some of the new features that I come across.

Here is list of some of the interesting new features that I have bumped into so far;

1. ADR - a big change in the way the diagnostic logs (alert.log, trace files) are now stored.
2. Database Replay
3. Case-sensitive passwords - Long overdue
4. Data-masking
5. ACL for calling packages
6. Generated columns or Virtual columns
7. Read only tables - Did you hear that "Read only tables" not "Tablespace"
8. Virtual column partitioning - really interesting
9. Get advice on how good is your table design
10. SYSASM role
11. Online patching and patching based on features you use

This is not the final list...there are many more which I am yet to explore. Keep a tab on my blog as I will try to bring in the interesting ones...

Cheers!

dbfsize - did you know about it?

...I didn't till the time I bumped into it today while trying to solve a ORA-27047 issue. I got this error when I was trying to restore a database received from a client for a project; What I had was cold backup + controlfile trace.

CREATE CONTROLFILE SET DATABASE "ND01" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'/oracle/oradata/ND01/ND01_tools_01.dbf'
ORA-27047: unable to read the header block of file
Additional information: 2

Bumped into dbfsize command while doing some research. Once can use dbv utility (dbverify) but it does not report datafile OS block header corruption thats when you can use dbfsize. It's available in UNIX only. Here is an example of how to use and what it reports :-

$dbfsize ND01_tools_01.dbf
ND01_tools_01.dbf: Header block magic number is bad

Whereas, the dbv would show the following output :-

DBVERIFY - Verification complete

Total Pages Examined : 25600
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 0
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 25600
Total Pages Influx : 0
Highest block SCN : 0 (0.0)

Learning never stops...

Orastack

ORASTACK is another interesting thing that I came across recently. Till now, it was unknown to me hence I thought it will be a useful post.

ORASTACK is an Oracle supplied utility specifically for Windows Operating system. It allows us to modify the default stack size of a thread/session when created in the Oracle executable. It should be run against a specific Oracle binary such as "sqlplus.exe", "tnslsnr.exe" etc. It alters the part of the binary header that defines the default stack size used by "the create thread API".

By reducing the stack of every session created in the Oracle executable, it is possible to serve more users. In a system with a 1000 users reducing the
stack from 1Mb to 500K would release 500Mb of the address space for other
allocations or more users. However if its set too low then session might fail with ORA-3113 error.

It's useful when you get frequent ORA-04030 or TNS-12518/TNS-12500 with ORA-04030 on windows environment.

The usage is -
orastack executable_name new_stack_size_in_bytes

e.g.
'orastack oracle.exe 500000'
'orastack tnslsnr.exe 500000

Remember to shutdown database if changing for "oracle.exe" and for others, ensure that no instance of the process, for which you would run ORASTACK, is running.

Finally a word of caution: - Always consult Oracle support before doing such things for your production environment.

Using "LOCAL" environment variable

You can bypass using TNSNAMES by setting this environment variable on Windows. You can set this variable to the remote database connect string and SQL*Net would simply use this as connect string whenever user does not supply it. This environment variable is for WINDOWS only and it's equivalent in UNIX is TWO_TASK variable.

C:\>set LOCAL=ORA10G

C:\>sqlplus scott/tiger

SCOTT@ORA10G> select global_name from global_name;

GLOBAL_NAME
------------------------------------------------------------

ORA10G.REGRESS.RDBMS.DEV.US.ORACLE.COM

See the impact if its set to a wrong value

C:\>set LOCAL=ORA10

C:\>sqlplus scott/tiger

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

This variable has been there since long but has not been mentioned at many places so I thought it might be an useful post in my blog. Feel free to disagree ;)

TRACEFILE_IDENTIFIER - Another useful parameter

Many a times you need to enable tracing at a session level. And when you do that, you have to make a little bit of effort in digging out your session's trace file. Why? because the trace file is created in the user_dump_dest directory and there would be so many other trace files, and all of them would have similar naming convention "SID_ora_nnnn.trc". However with the help of the parameter "TRACEFILE_IDENTIFIER", you can easily distinguish your trace file. Very useful specifically when you want to use trace analyzer etc.

Here is how?

1. Set the tracefile identifier as you want, this will be appended to trace file name.
alter session set tracefile_identifier='sipras_sql_trace';

2. Enable your session level tracing
alter session set sql_trace=true;
alter session set events '10046 trace name context forever, level 12' ;

3. Execute your queries/statements

4. Turn-off tracing
alter session set events '10046 trace name context off';
alter session set sql_trace=false;

5. Check the user_dump_dest folder and you will find a file name "SID_ora_nnnn_sipras_sql_trace.trc

See now it's so easy to identify. Having said that you can still find out the trace file name without using TRACEFILE_IDENTIFIER parameter using the following SQL but when Oracle has provided an easier method, why not use it?

-- sql to find out trace file name for your session.

select c.value || '/' || d.instance_name ||'_ora_' || a.spid || '.trc' trace
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
/