Oracle Parameterized View

Oracle has this feature because sometimes parameterized view can increase the performance because here predicate are compiled within the view; thus for this predicate the query will be always soft parsed.

Here is a simple example -

create view myview as

select * from t where x = userenv('client_info');


Here in this view definition, user's default namespace "userenv " and
its attribute "client_info" is used. To retrieve data from this view
you have to set this attribute by execute -

exec dbms_application_info.set_client_info
('client_info_value') ;

Some remarks on Oracle context -

1. "Userenv" is the default namespace or context.
   You can create your own context also if you have the privilege.
   
   
2. Every context(namespace) has a package within which
   you can write the code to set any attribute of your context.
   You cannot set the attribute outside the package.
   If you want to do it outside you will get error -
   ORA-01031: insufficient privileges.
   
   
3. To retrieve data from this package, we should use sys_context function.

So, you can use sys_context function also for parameterized view by calling
this function in view definition and before query the data set
the attribute used in view definition. Thus you can easily write view like user_tables -

select * from dba_tables
where owner = sys_context('USERENV', 'SESSION_USER');

Related links -
http://forums.oracle.com
http://asktom.oracle.com

Maintain shell script log in CSV

Suppose you want to log the start and end of your shell script execution. Also you want to know the total execution time. Then you should do the followings -

1. Put the start time in a variable at beginning of script;
   command - export start_date=`date +%m/%d/%Y,%H:%M:%S`.
   Be careful and use `` this operator to put the date function in a variable. Use the proper date format string as you need.
   
   
2. Put the end in a variable at the end of script;
   command - export end_date=`date +%m/%d/%Y,%H:%M:%S`.
   
   
3. Write the log and separate the field using ','.
   echo 'start,' $start_date ',end,' $end_date >> scriptexlog.csv
   
   
   

This is so simple!!

Some insight on Oracle Exception Handling

Like some high level programming language oracle provide some pretty feature on exception handling. Like other high level language here you can catch specific exception and handle that accordingly. Here you can also create your own exception also.

Firstly, how to catch specific exception :-

Suppose for network problem one of your dblink face problem and generate Oracle Exception ORA-3135. To catch this exception, you should define an exception with this exception number and then use the given name to catch this exception. That is create an exception using following code -

connection_lost Exception;
PRAGMA EXCEPTION_INIT(connection_lost, -3135);

Then in Exception block write the following -

when connection_lost then
/*some statements*/

Secondly, create and raise custom error:

create custom exception by following command -
cust_exception Exception;
PRAGMA EXCEPTION_INIT(cust_exception, -20001);

then command to raise this exception -
RAISE cust_exception;

Then catching this exception is same as other exception. There is an overload of RAISE_application_error which takes a third bool parameter. This parameter determines whether they data will be put on empty stack or top of stack.

Thirdly, Log Exception from the stack:

To get maximum log of exception, call DBMS_UTILITY.FORMAT_ERROR_STACK function and log the output. It will give not only top of stack but also all content of the procedure error stack. Along with this use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to log line from which error is generated. It is available from Oracle 10g.

For more information visit following links -

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm

http://www.oracle-developer.net/display.php?id=318

Console/admin log in Windows server

Sometimes when you want to log in a windows server OS you may get an error like terminal exceeded maximum number of user. But you have to do some admin work on the windows server; suppose disconnecting one of this user, then what you have to do?

just open command prompt, execute remote desktop connection with the following command-

mstsc -v:IP_OF_SERVER /F -admin

Then give your user & password. This is so simple!!

One thing keep in mind every time, that is when exiting the server always use log off. If you do not log off then if disconnect create some problem (which I face several time) then you cannot log in again as admin/console.

How to optimize your PL/SQL code

To increase performance of your pl/sql, you should alter system plsql parameters. It is recommended to use separate parameter value for production and development. For development, the parameter are set to default. So for development plsql_code_type will be 'INTERPRETED' and plsql_optimize_level will be 2. For production environment, you should change the parameter in following way,

alter session set plsql_code_type = 'NATIVE';

alter session set plsql_optimize_level = 3;

One very important effect of setting these two parameters is that - without setting these parameters in such values, you cannot use your function in parallel query.

If your functions are deterministic, you can create function index to increase performance. Thus you can enhance plsql performance.

Interval partitioning : A great feature of oracle 11g

Table partitioning is very much useful for data warehousing. But one problem we face before 11g is have to create the partition manually and it is cumbersome work. If we forget to create partition, the ETL job will be failed.

From 11g, oracle enhance the range partitioning with the feature of auto partitioning. Now along with range partition you only have to mention the interval of partition and create a base partition. The syntax is as follows -

CREATE TABLE SALES_PART
(TIME_ID NUMBER,
REGION_ID NUMBER,
ORDER_ID NUMBER,
ORDER_DATE DATE,
SALES_QTY NUMBER(10,2),
SALES_AMOUNT NUMBER(12,2)
)
PARTITION BY RANGE (ORDER_DATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(PARTITION p_first VALUES LESS THAN ('01-JAN-2006'));

The violet lines are for interval and base partition. In this example, the partitions are created on daily basis. If want to create them on week basis, write INTERVAL (NUMTODSINTERVAL(7,'day')) and INTERVAL (NUMTOYMINTERVAL(1,'month')) for monthly basis.

The automatic created partitions have system generated name and hence not follow naming convention. So to alter partition name issue following sql command -

ALTER TABLE SALES_PART RENAME PARTITION sys_p22 TO p_third

Some fact about oracle listener, tns, service & SID

1. Service_Name and SID are two different things. Service name directly maps to service of database. SID is system identification. The difference will be apparent in RAC environment where SID will be instance name and service name will be database service. This difference will also come into picture if your service name contains domain name. For example, your service name is dw.tmib.net.bd but your SID is dw.
   
   
2. For OS authentication, ORACLE_SID should be set to instance name i.e. SID.
   
   
3. For Listener, SID and Service Name are interoperable.
   
   
4. tnsping command confirms that there is a listener which listens to host:port referred by the tns entry.
   
   
5. In sqlplus connect command, @tnsname refers to tns entry in tnsnames.ora file. tns entry always goes to listener.
   
   
6. If the listener is not running, from sqlplus of server the database can be still connected. In this case, it is recommended to set the ORACLE_SID variable. It is required not to use @tnsname.
   
   
7. In a tns entry, under the description several addresses can be added. This helps the fail over. If any host:port service fails then go to the next host:port.
   DW=
   (DESCRIPTION=
   (ADDRESS=
   (PROTOCOL=TCP)
   (HOST=192.168.8.21)
   (PORT=1521)
   )
   (ADDRESS=
   (PROTOCOL=TCP)
   (HOST=192.168.8.22)
   (PORT=1521)
   )
   (CONNECT_DATA=
   (SERVER=dedicated)
   (SID=dw2)
   )
   )
   So if 192.168.8.22:1521 fails to serve, the request will be forwarded to 192.168.8.1:1521