Tuesday, December 20, 2011

What is CLOUD computing ??


I have read a  article on Cloud Computing and decided to post it. This article is written by one of the famous s/w engineer Gagan Kundra .

Cloud computing is a marketing term for technologies that provide computation, software, data access, and storage services that do not require end-user knowledge of the physical location and configuration of the system that delivers the services. A parallel to this concept can be drawn with the electricity grid, wherein end-users consume power without needing to understand the component devices or infrastructure required to provide the service.

Apple CEO Steve Jobs whenever someone comes on stage, the world is alarmed. Who let this time show little device that will change people's lives. Why is Apple the company, who gave us a iPod, Macintosh and iPhone are such tools. Jobs done something like this again. The only difference was that he did not have any tools. Just talked about a similar service to the people on the entire data and information from different devices can. Aiclaud Apple is the name of the service. But it's not actually Apple, but shows the power of cloud computing. Service's name may be, whether the service to be Apple, or Google, or Microsoft, but cloud computing in the coming days will sputter. Tech - pundits say that it will change our lives. Companies like Infosys have been predicted that almost all the work in the coming days the world will be on clouds. Just remember that no water in these clouds, but digital data - like - is filled with such information and other associated material. And the clouds in the sky, but on a giant computer - the server is called - are found.

Cloud computing is getting to be too much nowadays, but it is not a new thing. If you are on the Internet, so that - unconsciously you will also have to use it. I can not believe the e-mail as simple as taking the example of an Internet service. Most people e-mail on your computer, do not download, but leave it on the internet to see. Who your computer's hard drive to the e-mail wants to fill? But you never thought that this e-mail that you have left over the Internet or the Web, Where saving is kept, so you whenever you want your e-mail account, going to see it. The answer is simple - to cloud. You probably do not know, but nearly every time you leave something on the web, the data contained on these clouds. Whether it be a picture on Facebook, you - a video on the tube, or a new article on your blog. If he left you on the web, you are using cloud computing. You will have noticed that you leave the stuff on the internet, on any computer or cell phone or a tablet-like device can see. After all, your e-mail or Facebook Most office and home computer or your phone may get. This is the greatest power of cloud computing and specialty. It kept you on the Internet, its content or data from anywhere, anytime viewing and gives the ability to change.

 A few years ago was meant to use any software to download it to your computer or cellphone. Then the software could only be played on computers or cell phones. Today, via cloud computing could use the same software, with nothing to download, virtually any device - phone, tablet, computer or even from television. Consider that the cloud has taken the place of your computer. Now, Google's Google Docs, which runs entirely on the internet and gives you almost the same features you get with Microsoft Office, there is no need to download. It will remain on the files and documents, you can look at any computer or cellphone. and Google Docs is just an example. Dozens of world power and benefits of cloud computing companies trying to use it are given. Amazon's cloud service to their customers, keep your favorite songs have the chance. 

Microsoft has put many products on the cloud and its famous office software is also put on some cloud. And now Apple has also stepped into cloud computing. Aiclaud service people like him - look at the software and information and to use the facility will offer many tools. Interestingly, many are free of cloud computing service. To use Google Docs and Aiclaud you do not have light pockets at all. like - like an increasing number of software will run on the web, anyway - so we will run the digital cloud. Our work on certain devices, but will depend on our Internet connection. The Internet will be able to do or where we will be able to satisfy yourself. Whether we are or tablet computer, or cell phone, or TV, or game console, or to connect to the Internet even if the clock. Your work and be entertained, where would the internet. He will not be tied to a device. 


Enjoy    J  J J


Friday, December 16, 2011

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    :-)




Tuesday, December 13, 2011

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     :-) 


Friday, December 9, 2011

Dbms_Metadata Package : To Extract all DDL


There  are various method available to extract the DDL from the oracle database . We can extract the DDL either by  export/import utility , dbms_metadata package ,or by using others third party tools . The best ways to extract the DDL is by using the DBMS_METADATA pacakage if don't have any tools . Here we will discuss the various DDL extracting method to extract the DDL from the Oracle .

The DBMS_METADATA package provides a way to retrieve metadata from the database dictionary as XML or  DDL . We can retrive either a particular object type (such as a table, index, or procedure) or a heterogeneous collection of object types that form a logical unit (such as a database export or schema export) 

One of the very useful function i.e, GET_xxxx functions are used to extract the DDL .  The following GET_xxxx functions fetches the metadata for objects with a single call . They encapsulate calls to OPEN, SET_FILTER, and so on. The function we use depends on the characteristics of the object type and on whether we want XML or DDL.

1.) GET_DDL( )   :  This function is used to fetch named objects, especially schema objects (tables, views). They can also be used with nameless objects, such as resource_cost .

Snytax   :
DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

Example  : Here we will extract the DDL for the "employees"  table .

SQL> select dbms_metadata.get_ddl('TABLE', 'EMP')   from dual ;

DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------
   CREATE TABLE "ALEX"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPTNO")
          REFERENCES "ALEX"."DEPT" ("DEPTNO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

Similary , we also extract the DDL of others objects like tablespace,views,sequence and others .

If we want only the DDL  not the storage clause and constraints ,then we need to modify some transformation parameters as

SQL> begin
  2     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);
  3     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
  4     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);
  5     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
  6     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false);
  7  end;
  8  /
 PL/SQL procedure successfully completed.

Now on running the same above statements give the following results .

SQL> select dbms_metadata.get_ddl ('TABLE', 'EMP')  from dual ;
 DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------
   CREATE TABLE "ALEX"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   )

One of the nice things is that we don't need to modify all the transformation parameters again to go back to the default. They made it really easy to return to the default settings:

SQL> begin
  2     dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'DEFAULT');
  3  end;
  4  /
 PL/SQL procedure successfully completed.

2.) GET_DEPENDENT_DDL( ) : This function is used to fetch dependent objects (audits, object grants).
 Syntax :
DBMS_METADATA.GET_DEPENDENT_DDL (
object_type         IN VARCHAR2,
base_object_name    IN VARCHAR2,
base_object_schema  IN VARCHAR2 DEFAULT NULL,
version             IN VARCHAR2 DEFAULT 'COMPATIBLE',
model               IN VARCHAR2 DEFAULT 'ORACLE',
transform           IN VARCHAR2 DEFAULT 'DDL',
object_count        IN NUMBER   DEFAULT 10000)
RETURN CLOB ;

Example :   In this example, we will extract the reference constraints which is dependent on another tables .

SQL > select dbms_metadata.get_dependent_ddl( 'REF_CONSTRAINT', table_name) DDL
              FROM USER_TABLES WHERE table_name = 'EMPLOYEES' ;
ALTER  TABLE  "HR"."EMPLOYEES" ADD CONSTRAINT  "EMP_DEPT_FK"  FOREIGN KEY ("DEPARTMENT_ID")  REFERENCES  "HR"."DEPARTMENTS"  ("DEPARTMENT_ID") ENABLE

ALTER  TABLE "HR"."EMPLOYEES"  ADD  CONSTRAINT "EMP_JOB_FK"  FOREIGN KEY ("JOB_ID") REFERENCES  "HR"."JOBS"  ("JOB_ID") ENABLE

ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")  REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE

3.) GET_GRANTED_DDL( ) : This function  is used to fetch granted objects (system grants, role grants ) to the users of the database .

Syntax : 


DBMS_METADATA.GET_GRANTED_DDL (
object_type     IN VARCHAR2,
grantee         IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL',
object_count    IN NUMBER   DEFAULT 10000)
RETURN CLOB;

Example :  Here we will extract the "system grant" assigned to the user "SYSTEM "

SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','SYSTEM') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SYSTEM')
--------------------------------------------------------------------------------
  GRANT GLOBAL QUERY REWRITE TO "SYSTEM"
  GRANT CREATE MATERIALIZED VIEW TO "SYSTEM"
  GRANT SELECT ANY TABLE TO "SYSTEM"
  GRANT CREATE TABLE TO "SYSTEM"
  GRANT UNLIMITED TABLESPACE TO "SYSTEM" WITH ADMIN OPTION

To extract all the grants to all the user we can use the below statements  .

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’, USERNAME) || ‘/’ DDL FROM DBA_USERS where exists (select ‘x’ from dba_role_privs drp where drp.grantee = dba_users.username)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’, USERNAME) || ‘/’ DDL FROM DBA_USERS where exists (select ‘x’ from dba_role_privs drp where drp.grantee = dba_users.username)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’, USERNAME) || ‘/’ DDL FROM  DBA_USERS where exists (select ‘x’ from dba_tab_privs dtp where  dtp.grantee = dba_users.username);


Enjoy   :-)