Estimate Tablespace Growth In Oracle

I have already posted the script for tracking the growth of the Database .Sometimes it may be requires to find the growth of  each tablespace  . I  have google and find the script for tracking the growth of  each tablespace with date-wise. I thanks Hasan Shaharear  for sharing such a good scripts. This scripts donot shows the growth of system and sysaux tablespace growth rate. Below is the script .

SQL> set heading on
SQL> set linesize 5500
SQL> SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
  2  , ts.tsname
  3  , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
  4  , max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
  5  FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
  6  , DBA_HIST_TABLESPACE_STAT ts
  7  , DBA_HIST_SNAPSHOT sp
  8  , DBA_TABLESPACES dt
  9  WHERE tsu.tablespace_id= ts.ts#
 10  AND tsu.snap_id = sp.snap_id
 11  AND ts.tsname = dt.tablespace_name
 12  AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
 13  GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
 14  ORDER BY ts.tsname, days;

DAYS                 TSNAME        CUR_SIZE_MB       USEDSIZE_MB
-------------      ---------       -------------     ---------------
07-12-2011         SDE                         2448              1813.94
08-12-2011         SDE                         2448              1813.94
09-12-2011         SDE                         2448              1813.94
10-12-2011         SDE                         2448              1813.94
11-12-2011         SDE                         2448              1815.94
12-12-2011         SDE                         2448              1815.94
13-12-2011         SDE                         2448              1816
14-12-2011         SDE                         2448              1816
15-12-2011         SDE                         2448              1816
16-12-2011         SDE                         2448              1816
07-12-2011         UNDOTBS1                4950              196.56
08-12-2011         UNDOTBS1                4950              302.56
09-12-2011         UNDOTBS1                4950              427.63
10-12-2011         UNDOTBS1                4950              348.56
11-12-2011         UNDOTBS1                6210              5317
12-12-2011         UNDOTBS1                6210              532.5
13-12-2011         UNDOTBS1                6210              388.56
14-12-2011         UNDOTBS1                6210              422.5
15-12-2011         UNDOTBS1                6210              585.63
16-12-2011         UNDOTBS1                6210              479.5
07-12-2011         USERS                      20480           19941.88
08-12-2011         USERS                      20480           20001.5
09-12-2011         USERS                      20480           20341.56
10-12-2011         USERS                      20480           20467.25
11-12-2011         USERS                      20480           20336.69
12-12-2011         USERS                      20480           20317.13
13-12-2011         USERS                      20480           20267.31
14-12-2011         USERS                      20480           20346.13
15-12-2011         USERS                      20480           20340.06
16-12-2011         USERS                      20480           20330.81
30 rows selected.


Enjoy    :-)

How to Reduce DB File Sequential Read Wait

DB File Sequential Read wait event occurs when we are trying to access data using index and oracle is waiting for the read of index block from disk to buffer cache to complete.  A sequential read is a single-block read.Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache.Db file sequential read wait events may also appear when undo blocks are read from disk in order to provide a consistent get(rarely).

To determine the actual object being waited can be checked by the p1, p2, p3 info in v$session_wait .  A sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block (See P3). This wait may also be seen for reads from datafile headers (P2 indicates a file header read) ,where p1,p2 and p3 gives the the absolute file number ,the block being read ,and  the number of blocks (i.e, P3 should be 1) respectively. 

Block reads are fairly inevitable so the aim should be to minimise un-necessary IO. This is best achieved by good application design and efficient execution plans. Changes to execution plans can yield orders of magnitude changes in performance.Hence to reduce this wait event follow the below points .

1.) Tune Oracle - tuning SQL statements to reduce unnecessary I/O request is the only guaranteed way to reduce "db file sequential read" wait time.
2.) Tune Physical Devices - Distribute(stripe) the data on diferent disk to reduce the i/o . Logical distribution is useless. "Physical" I/O performance is only governed by "independency of devices".
3.) Faster Disk - Buy the faster disk to reduce the unnecessary I/O request .
4.) Increase db_block_buffers - A larger buffer cache can (not will, "might") help .


Reference :: From  Asktom site


Enjoy     :-) 

Cannot Load OCI.DLL : While Connecting

Sometimes the error "cannot load OCI.DLL" occur whenever we try to connect with the oracle database by using the third-party tools(i.e, toad,sqltools and others) or command prompt . This error may occur because of the following reason .

1.) The oci.dll error may occur because you have not set the correct ORACLE_HOME and path in environment variables . 

2.) It might be possible that the oci.dll file may be corrupt or may not exist on the correct path .

3.) May be possible that oci.dll may not be correct version. (e.g. 32bit s/w will load a 32bit DDL - we cannot for example use a 64bit DLL for a 32bit executable) . 

To solve this issue , consider the below points .

1.) Check the ORACLE_HOME and Path setting in the envirnoment variable.
2.) Check the correct location of the oci.dll path . The path of the oci.dll file is $ORACLE_HOME\bin\oci.dll
3.) Check the oci.dll correct version .

In my case , i am facing this issue because the $ORACLE_HOME is not correctly set in the environment variables . So setting the correct path in environment variables, we find not any error while connecting the  database .

Enjoy     :-)