Thursday, 18 June 2015

Steps to configure Oracle Wallet for Storing schema password

   Following are steps to configure Oracle wallet so that the authorized user need not be specified the password and username while connecting to the database. It will be helpful if we have a shell scripts to be executed and which the credential in clear case. Once this is configured, credential information can be removed from the file and database can be accessed using the alias specified in the wallet configuration. 



  • Decide the location of the Oracle wallet say("/u01/app/oracle/wallet")



  • Add the following lines to the sqlnet.ora which is present at $ORACLE_HOME/network/admin/sqlnet.ora

    1. WALLET_LOCATION =  
    2.    (SOURCE =  
    3.      (METHOD = FILE)  
    4.      (METHOD_DATA =  
    5.        (DIRECTORY = /u01/app/oracle/wallet)  
    6.      )  
    7.    )  
    8.   
    9. SQLNET.WALLET_OVERRIDE = TRUE  
    10. SSL_CLIENT_AUTHENTICATION = FALSE  
    11. SSL_VERSION = 0 
    The SQLNET.WALLET_OVERRIDE entry allows this method to override any existing OS authentication configuration. 



  • Create an Oracle wallet in the previously specified location using the mkstore utility with the -create option. The wallet is password protected, but is defined with the "Auto Login" property enabled so connection attempts by the user who created the wallet do not require a password. It will ask for a wallet password.

    • view plaincopy to clipboardprint?
    1. $ mkdir /u01/app/oracle/wallet  
    2. $ mkstore -wrl "/u01/app/oracle/wallet" -create  
    3. Enter password:  
    4. Enter password again:  
    5. $  

    Add the password credential to the wallet using the following command.

    1. $ mkstore -wrl "/u01/app/oracle/wallet" -createCredential db10g_test scott tiger  
    2. Enter password:  
    3.   
    4. Create credential oracle.security.client.connect_string1  
    5.   
    6. $  

    The db_alias, in this case "db10g", is the identifier used in the "/@db_alias" syntax, and must have a matching entry in the "tnsnames.ora" file. 



  • If multiple users are present, simply add a new entry into the wallet using a different db_alias and make sure the alias is present in the "tnsnames.ora" file.
    Make a new entry for the "db10g" database in the client "tnsnames.ora" file.

    1. DB10G_TEST =  
    2.   (DESCRIPTION =  
    3.     (ADDRESS = (PROTOCOL = TCP)(HOST = <IP_ADDRESS>)(PORT = 1521))  
    4.     (CONNECT_DATA =  
    5.       (SERVER = DEDICATED)  
    6.       (SERVICE_NAME = <SID>)  
    7.     )  
    8.   )  



  • Now connect to the "test" user as shown below.

    1. $ sqlplus /@db10g_test  
    2.   
    3. SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 19 10:17:47 2007  
    4.   
    5. Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
    6.   
    7. Connected to:  
    8. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production  
    9. With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options  
    10.   
    11. SQL> show user  
    12. USER is "SCOTT"  
    13.   
    14. SQL  

    Posted by at No comments:

    Wednesday, 10 June 2015

    Pivot and UnPivot Examples in Oracle

    Hi, 

    Today I am explaining you about two simple functions;but powerful as it can be used for data warehousing application. Those are mentioned below. 



  • Pivot Functions : Pivot Functions basically converts a row information into a column. Assume we have a table "sales_sample" with the structure as mentioned below.


    • view plaincopy to clipboardprint?
    1. create table sales_sample ( sale_region varchar2(30), sale_year number,  sale_month varchar2(20), sale_product_code varchar2(30), quantity  number )   

    I am populating the table with the following values. 

    view plaincopy to clipboardprint?
    1. insert into sales_sample values('North America',2014,'Jan','Iphone',100);  
    2.   
    3. insert into sales_sample values('North America',2014,'Jan','Samsung',200);  
    4.   
    5. insert into sales_sample values('North America',2014,'Jan','Nexus',300);  
    6.   
    7. insert into sales_sample values('Asia Pacific',2014,'Jan','Iphone',510);  
    8.   
    9. insert into sales_sample values('Asia Pacific',2014,'Jan','Samsung',220);  
    10.   
    11. insert into sales_sample values('Asia Pacific',2014,'Jan','Nexus',320);  
    12.   
    13. insert into sales_sample values('North America',2014,'Feb','Iphone',11);  
    14.   
    15. insert into sales_sample values('North America',2014,'Feb','Samsung',22);  
    16.   
    17. insert into sales_sample values('North America',2014,'Feb','Nexus',33);  
    18.   
    19. insert into sales_sample values('Asia Pacific',2014,'Feb','Iphone',44);  
    20.   
    21. insert into sales_sample values('Asia Pacific',2014,'Feb','Samsung',55);  
    22.   
    23. insert into sales_sample values('Asia Pacific',2014,'Feb','Nexus',66);  
    24.   
    25. SQL>select * from sales_sample;  
    26.   
    27. SALE_REGION    SALE_YEAR  SALE_MONTH  SALE_PRODUCT_CODE   QUANTITY  
    28. --------------------------------------------------------------------------------------------  
    29. North America   2014             Jan                  Iphone                            100    
    30. North America   2014             Jan                  Samsung                           200    
    31. North America   2014             Jan                  Nexus                             300    
    32. North America   2014             Feb                  Iphone                            11    
    33. North America   2014             Feb                  Samsung                           22    
    34. North America   2014             Feb                  Nexus                             33    
    35. Pacific         2014             Jan                  Iphone                            510    
    36. Pacific         2014             Jan                  Samsung                           220    
    37. Pacific         2014             Jan                  Nexus                             320    
    38. Pacific         2014             Feb                  Iphone                            44    
    39. Pacific         2014             Feb                  Samsung                           55    
    40. Pacific         2014             Feb                  Nexus                             66    
    41. -----------------------------------------------------------------------------------------

    Now apply the pivot function on the data set; the syntax is mentioned below 

    Pivot Function which will give region,year and month level aggregates of totla quantity of products sold. 

    view plaincopy to clipboardprint?
    1. select * from   
    2. (  
    3. select sale_region, SALE_YEAR, sale_month,sale_product_code,quantity from sales_sample ) PIVOT  
    4. (  
    5. SUM(quantity)   
    6.   FOR sale_product_code IN ('Iphone' AS IPHONE_QUANTITY, 'Samsung' SAMSUNG_QUANTITY,'Nexus' NEXUS_QUANTITY)   
    7. )  
    8. ORDER BY SALE_REGION,sale_month  
    9.   
    10. Output  
    11. --------  
    12.   
    13. REGION           YEAR     MONTH  IPHONE_QUANTITY  SAMSUMG_QUANITY   NEXUS_QUANTITY   
    14. ----------------------------------------------------------------------------------------- ---------------------  
    15. Asia Pacific        2014      Feb           44                        55                            66    
    16. Asia Pacific        2014      Jan           510                       220                           320    
    17. North America       2014      Feb           11                        22                            33    
    18. North America       2014      Jan           100                       200                           300    
    19. --------------------------------------------------------------------------------------------------------------  




  • Un Pivot Functions : Converts a column into a corresponding row. It is opposite process of pivoting.


    • UnPivot Example 
    -------------------- 

    view plaincopy to clipboardprint?
    1. create table sales_region_year(  
    2. SALE_REGION           VARCHAR2(30)   
    3. SALE_YEAR             NUMBER         
    4. IPHONE_QUANTITY       NUMBER         
    5. SAMSUNG_QUANTITY      NUMBER         
    6. NEXUS_QUANTITY        NUMBER     
    7. )  
    8.   
    9. SELECT * FROM SALES_REGION_YEAR   
    10. UNPIVOT INCLUDE NULLS (quantity FOR product_code IN (IPHONE_QUANTITY AS 'Iphone', SAMSUNG_QUANTITY AS 'Samsung', NEXUS_QUANTITY AS 'Nexus'));  
    11.   
    12. SQL>SELECT * FROM SALES_REGION_YEAR ;  
    13.   
    14. SALE_REGION    SALE_YEAR    PRODUCT_CODE   QUANTITY  
    15. ------------------------------------------------------------------  
    16. North America  2014         Iphone               111  
    17. North America  2014         Samsung              222  
    18. North America  2014         Nexus                333  
    19. Pacific        2014         Iphone               554  
    20. Pacific        2014         Samsung              275  
    21. Pacific        2014         Nexus                386  
    22. -------------------------------------------------------------------- 

    Hope it is very informative.


    Posted by at No comments:
    Subscribe to: Posts (Atom)