Saturday, August 5, 2017

Display output vertically in SQLcl

image

image

image

var sql="";
for(var i=1;i<args.length;i++){
  sql = sql + " " + args[i];
}
ctx.write("\n\n SQL Statement: \n"+ sql + "\n\n");


var ret = util.executeReturnListofList(sql,null);

for (var i = 0; i < ret.size(); i++) {
    if (i ==0) { ctx.write('> SKIP HEADER ROW\n');continue; }
    else { ctx.write('>ROW \n') };
    for( var ii=0;ii<ret[i].size();ii++) {
        ctx.write("\t" + ret[0][ii] + " : " + ret[i][ii] + "\n");
    }
}

ctx.write('\n\n');

Oracle 12c Partial Indexes for Partitioned Tables

SQL> CREATE TABLE orders (
  2      order_id       NUMBER(12),
  3      order_date     DATE,
  4      order_mode     VARCHAR2(8),
  5      customer_id    NUMBER(6),
  6      order_status   NUMBER(2),
  7      order_total    NUMBER(8,2),
  8      sales_rep_id   NUMBER(6),
  9      promotion_id   NUMBER(6),
  10       CONSTRAINT order_pk PRIMARY KEY ( order_id )
  11   )
  12       INDEXING OFF
  13           PARTITION BY RANGE ( order_date ) (
  14           PARTITION P2004 VALUES LESS THAN (TO_DATE('01-JAN-2005','DD-MON-YYYY')) INDEXING OFF,
  15           PARTITION P2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY')) INDEXING OFF,
  16           PARTITION P2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')) INDEXING OFF,
  17           PARTITION P2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')) INDEXING OFF,
  18           PARTITION P2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')) INDEXING ON,
  19           PARTITION P2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')) INDEXING ON
  20           )
  21  /

Table ORDERS created.

SQL> select order_date from oe.orders sample(10);
ORDER_DATE
-------------------------------
14-SEP-06 06.03.04.763452000 AM
17-NOV-06 01.22.11.262552000 AM
12-MAR-07 08.53.54.562432000 PM
29-MAR-07 03.41.20.945676000 PM
07-JUN-07 05.18.08.883310000 AM
16-AUG-07 02.34.12.234359000 PM
10-NOV-07 04.49.25.526321000 AM
27-FEB-08 03.41.45.109654000 AM
26-JUN-08 09.19.43.190089000 PM

9 rows selected.

SQL> insert into orders select * from oe.orders;
105 rows inserted.

SQL> commit;

Commit complete.

SQL> create index order_gi1 on orders (sales_rep_id) global indexing partial;
 

Index ORDER_GI1 created.

SQL> create index order_li1 on orders (customer_id) local indexing partial;

Index ORDER_LI1 created.

SQL> set sqlformat ansiconsole

SQL> select partition_name,indexing from user_tab_partitions where table_name='ORDERS';
PARTITION_NAME  INDEXING
P2004           OFF
P2005           OFF
P2006           OFF
P2007           OFF

P2008           ON
P2009           ON
6 rows selected.

SQL>  select index_name,partition_name,status from user_ind_partitions;
INDEX_NAME  PARTITION_NAME  STATUS
ORDER_LI1   P2004           UNUSABLE
ORDER_LI1   P2005           UNUSABLE
ORDER_LI1   P2006           UNUSABLE
ORDER_LI1   P2007           UNUSABLE

ORDER_LI1   P2008           USABLE
ORDER_LI1   P2009           USABLE


6 rows selected.


SQL> select index_name,indexing from user_indexes;
INDEX_NAME  INDEXING
ORDER_LI1   PARTIAL
ORDER_PK    FULL
ORDER_GI1   PARTIAL


SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT                                                                                                        
Plan hash value: 670661013                                                                                               
                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |           |     1 |    93 |   821   (1)| 00:00:01 |       |       |
|   1 |  VIEW                                        | VW_TE_2   |     2 |   186 |   821   (1)| 00:00:01 |       |       |
|   2 |  UNION-ALL                                  |           |       |       |            |          |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS    |     1 |    93 |     1   (0)| 00:00:01 | ROWID | ROWID |
|*  4 |     INDEX RANGE SCAN                         | ORDER_GI1 |     1 |       |     1   (0)| 00:00:01 |       |       |
|   5 |    PARTITION RANGE ITERATOR                  |           |     1 |    93 |   820   (1)| 00:00:01 |     1 |     4 |
|*  6 |     TABLE ACCESS FULL                        | ORDERS    |     1 |    93 |   820   (1)| 00:00:01 |     1 |     4 |
--------------------------------------------------------------------------------------------------------------------------
                
Predicate Information (identified by operation id):                                                                      
---------------------------------------------------                                  
   3 - filter("ORDERS"."ORDER_DATE">=TO_DATE(' 2008-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND        
              "ORDERS"."ORDER_DATE"<TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))  
   4 - access("SALES_REP_ID"=154)                                                                    
   6 - filter("SALES_REP_ID"=154)  
       
Note                                                                                        
-----                                                                      
   - dynamic statistics used: dynamic sampling (level=2)                        

25 rows selected.



SQL> explain plan for select * from orders where customer_id=104;
Explained.


SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT                                                                                                        
Plan hash value: 4090115495                                                                                              
                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |           |     1 |    93 |   963   (1)| 00:00:01 |       |       |
|   1 |  VIEW                                        | VW_TE_2   |    17 |  1581 |   963   (1)| 00:00:01 |       |       |
|   2 |   UNION-ALL                                  |           |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR                  |           |    16 |  1488 |   143   (0)| 00:00:01 |     5 |     6 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ORDERS    |    16 |  1488 |   143   (0)| 00:00:01 |     5 |     6 |
|*  5 |      INDEX RANGE SCAN                        | ORDER_LI1 |    16 |       |     1   (0)| 00:00:01 |     5 |     6 |
|   6 |    PARTITION RANGE ITERATOR                  |           |     1 |    93 |   820   (1)| 00:00:01 |     1 |     4 |
|*  7 |     TABLE ACCESS FULL                        | ORDERS    |     1 |    93 |   820   (1)| 00:00:01 |     1 |     4 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):                                                                      
---------------------------------------------------                  
   5 - access("CUSTOMER_ID"=104)                  
   7 - filter("CUSTOMER_ID"=104)                 
                                                                                                                         
Note                                                                                                                     
-----              
   - dynamic statistics used: dynamic sampling (level=2) 


24 rows selected.

Thursday, August 3, 2017

Verification of SQL Server JDBC integrated authentication with sample script

SET JAVA_HOME="C:\Program Files\Java\jdk1.8.0_131"

SET CLASSPATH=.;C:\temp\sqljdbc_6.2\enu\mssql-jdbc-6.2.1.jre8.jar


C:\temp\sqljdbc_6.2\enu\samples\connections>%JAVA_HOME%\bin\javac connectURL.java

C:\temp\sqljdbc_6.2\enu\samples\connections>%JAVA_HOME%\bin\java connectURL
Aug 04, 2017 9:48:25 AM com.microsoft.sqlserver.jdbc.AuthenticationJNI
WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path
com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:9f25a766-3663-4bc5-b68c-19a551cbcd20
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2435)
        at com.microsoft.sqlserver.jdbc.AuthenticationJNI.(AuthenticationJNI.java:75)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3129)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:82)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3121)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2026)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1687)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1528)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:866)
        at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:569)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:270)
        at connectURL.main(connectURL.java:43)
Caused by: java.lang.UnsatisfiedLinkError: no sqljdbc_auth in java.library.path
        at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1867)
        at java.lang.Runtime.loadLibrary0(Runtime.java:870)
        at java.lang.System.loadLibrary(System.java:1122)
        at com.microsoft.sqlserver.jdbc.AuthenticationJNI.(AuthenticationJNI.java:50)
        ... 13 more


C:\temp\sqljdbc_6.2\enu\samples\connections>dir "C:\temp\sqljdbc_6.2\enu\auth\x6
4"
 Volume in drive C has no label.
 Volume Serial Number is D0AD-1A2D

 Directory of C:\temp\sqljdbc_6.2\enu\auth\x64

08/04/2017  09:38 AM             .

08/04/2017  09:38 AM             ..

07/14/2017  11:41 AM           310,480 sqljdbc_auth.dll
               1 File(s)        310,480 bytes
               2 Dir(s)  58,810,060,800 bytes free

C:\temp\sqljdbc_6.2\enu\samples\connections>SET PATH=%PATH%;C:\temp\sqljdbc_6\enu\auth\x64

C:\temp\sqljdbc_6.2\enu\samples\connections>%JAVA_HOME%\bin\java connectURL
1 Accounting Manager
2 Assistant Sales Agent
3 Assistant Sales Representative
4 Coordinator Foreign Markets
5 Export Administrator
6 International Marketing Manager
7 Marketing Assistant
8 Marketing Manager
9 Marketing Representative
10 Order Administrator




=========================== connectURL.java ===========================
import java.sql.*;

public class connectURL {

public static void main(String[] args) {

// Create a variable for the connection string.
String connectionUrl = "jdbc:sqlserver://WIN-L2D9O5BHNHA:2433;" +
"databaseName=AdventureWorks2012;integratedSecurity=true;";

/* 
// Below connection string using instance name for named instance, required Browser
// Service up and running
String connectionUrl = "jdbc:sqlserver://WIN-L2D9O5BHNHA;" +
"instanceName=PROD;databaseName=AdventureWorks2012;integratedSecurity=true;";
*/

// Declare the JDBC objects.
Connection con = null;
Statement stmt = null;
ResultSet rs = null;

        try {
        // Establish the connection.
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(connectionUrl);
            
            // Create and execute an SQL statement that returns some data.
            String SQL = "SELECT TOP 10 * FROM Person.ContactType";
            stmt = con.createStatement();
            rs = stmt.executeQuery(SQL);
            
            // Iterate through the data in the result set and display it.
            while (rs.next()) {
            System.out.println(rs.getString(1) + " " + rs.getString(2));
            }
        }
        
// Handle any errors that may have occurred.
catch (Exception e) {
e.printStackTrace();
}

finally {
if (rs != null) try { rs.close(); } catch(Exception e) {}
    if (stmt != null) try { stmt.close(); } catch(Exception e) {}
    if (con != null) try { con.close(); } catch(Exception e) {}
}
}
}















Tuesday, August 1, 2017

Add firewall rules in Redhat EL7 for Oracle database

[root@vmxdb01 ~]# firewall-cmd --permanent --zone=public --add-port=1521/tcp
success

[root@vmxdb01 ~]# firewall-cmd --reload
success

[root@vmxdb01 ~]# firewall-cmd --permanent --zone=public --list-ports
1521/tcp

[root@vmxdb01 ~]# firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: enp0s3
  sources:
  services: dhcpv6-client ssh
  ports: 1521/tcp
  protocols:
  masquerade: no
  forward-ports:
  sourceports:
  icmp-blocks:
  rich rules: