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:

  

Tuesday, July 18, 2017

Install and Configure PGAUDIT in PostgreSQL 9.6 step by step

Install prerequisites

# yum install readline readline-devel zlib zlib-devel bison bison-devel flex flex-devel

Clone the PostgreSQL repository:

git clone https://github.com/postgres/postgres.git

Checkout REL9_6_STABLE branch:

cd postgres

git checkout REL9_6_STABLE

Make PostgreSQL:

./configure --enable-debug --prefix=/var/lib/pgsql/pgsql_latest/ --with-pgport=5555
make install -s

Change to the contrib directory:

cd contrib

Clone the pgAudit extension:

git clone https://github.com/pgaudit/pgaudit.git

Change to pgAudit directory:

cd pgaudit

Build pgAudit and run regression tests:

make -s check

============== creating temporary instance            ==============
============== initializing database system           ==============
============== starting postmaster                    ==============
running on port 57835 with PID 17530
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test pgaudit                  ... ok
============== shutting down postmaster               ==============
============== removing temporary instance            ==============

=====================
  All 1 tests passed.
=====================

Install pgAudit:

make install

/bin/mkdir -p '/var/lib/pgsql/pgsql_latest/lib'
/bin/mkdir -p '/var/lib/pgsql/pgsql_latest/share/extension'
/bin/mkdir -p '/var/lib/pgsql/pgsql_latest/share/extension'
/bin/install -c -m 755  pgaudit.so '/var/lib/pgsql/pgsql_latest/lib/pgaudit.so'
/bin/install -c -m 644 ./pgaudit.control '/var/lib/pgsql/pgsql_latest/share/extension/'
/bin/install -c -m 644 ./pgaudit--1.1.1.sql ./pgaudit--1.0--1.1.1.sql  '/var/lib/pgsql/pgsql_latest/share/extension/'

Configure Parameter:

$ grep -i audit postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'all, -misc'
log_connections = on  # audit setting
log_disconnections = on # audit setting
log_line_prefix = '<%m:%r:%u@%d:[%p]:> '        # audit setting
log_statement = 'none'                  # audit setting

Startup Log:

$ /var/lib/pgsql/pgsql_latest/bin/pg_ctl start -D /var/lib/pgsql/9.6/data
server starting
<2017-07-18 22:10:11.455 +08::@:[17758]:> LOG:  pgaudit extension initialized
< 2017-07-18 22:10:11.470 +08::@:[17758]:> LOG:  redirecting log output to logging collector process
< 2017-07-18 22:10:11.470 +08::@:[17758]:> HINT:  Future log output will appear in directory "pg_log".

Sample Output:

<2017-07-18 22:12:05.776 +08:127.0.0.1(54486):[unknown]@[unknown]:[17804]:> LOG:  connection received: host=127.0.0.1 port=54486
< 2017-07-18 22:12:12.429 +08:127.0.0.1(54488):[unknown]@[unknown]:[17807]:> LOG:  connection received: host=127.0.0.1 port=54488
< 2017-07-18 22:12:12.430 +08:127.0.0.1(54488):admin1@testdb:[17807]:> LOG:  connection authorized: user=admin1 database=testdb
< 2017-07-18 22:12:37.644 +08:127.0.0.1(54488):admin1@testdb:[17807]:> LOG:  AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,create table t1(i integer);,<not logged>
< 2017-07-18 22:13:09.207 +08:127.0.0.1(54488):admin1@testdb:[17807]:> LOG: AUDIT: SESSION,2,1,WRITE,INSERT,,,insert into t1 values (1);,<not logged>
< 2017-07-18 22:13:13.911 +08:127.0.0.1(54488):admin1@testdb:[17807]:> LOG:  AUDIT: SESSION,3,1,READ,SELECT,,,select * from t1;,<not logged>
< 2017-07-18 22:13:15.232 +08:127.0.0.1(54488):admin1@testdb:[17807]:> LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,select * from t1;,<not logged>
< 2017-07-18 22:13:52.766 +08:127.0.0.1(54488):admin1@testdb:[17807]:> ERROR:  column "id2" does not exist at character 8
< 2017-07-18 22:13:52.766 +08:127.0.0.1(54488):admin1@testdb:[17807]:> STATEMENT:  select id2 from t1;
< 2017-07-18 22:14:13.596 +08:127.0.0.1(54488):admin1@testdb:[17807]:> LOG:  AUDIT: SESSION,5,1,WRITE,DELETE,,,delete from t1;,<not logged>
< 2017-07-18 22:14:23.391 +08:127.0.0.1(54488):admin1@testdb:[17807]:> LOG:  AUDIT: SESSION,6,1,WRITE,TRUNCATE TABLE,,,truncate table t1;,<not logged>
< 2017-07-18 22:14:26.746 +08:127.0.0.1(54488):admin1@testdb:[17807]:> LOG:  AUDIT: SESSION,7,1,DDL,DROP TABLE,,,drop table t1;,<not logged>
< 2017-07-18 22:14:29.103 +08:127.0.0.1(54488):admin1@testdb:[17807]:> LOG:  disconnection: session time: 0:02:16.674 user=admin1 database=testdb host=127.0.0.1 port=54488

image

Use standard logging for audit purpose in PostgreSQL 9.6

Configuration:

$ grep -i audit postgresql.conf
log_connections = on  # audit setting
log_disconnections = on # audit setting
log_line_prefix = '<%m:%r:%u@%d:[%p]:> '        # audit setting
log_statement = 'all'                   # audit setting

Sample pg_log output:

<2017-07-18 20:25:12.374 +08:127.0.0.1(57640):[unknown]@[unknown]:[3541]:> LOG:  connection received: host=127.0.0.1 port=57640
< 2017-07-18 20:25:12.375 +08:127.0.0.1(57640):admin1@testdb:[3541]:> LOG:  connection authorized: user=admin1 database=testdb
< 2017-07-18 20:25:13.037 +08:127.0.0.1(57640):admin1@testdb:[3541]:> LOG:  disconnection: session time: 0:00:00.662 user=admin1 database=testdb host=127.0.0.1 port=57640
< 2017-07-18 20:25:17.622 +08:127.0.0.1(57642):[unknown]@[unknown]:[3543]:> LOG:  connection received: host=127.0.0.1 port=57642
< 2017-07-18 20:25:17.623 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  connection authorized: user=admin1 database=testdb
< 2017-07-18 20:25:32.728 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  statement: create table t1(id integer);
< 2017-07-18 20:25:41.154 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  statement: insert into t1 values (1);
< 2017-07-18 20:25:47.598 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  statement: select * from t1;
< 2017-07-18 20:25:50.049 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  statement: drop table t1;
< 2017-07-18 20:25:54.762 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  disconnection: session time: 0:00:37.139 user=admin1 database=testdb host=127.0.0.1 port=57642

image

Sunday, July 16, 2017

SQL Server 2016 AG Setup Part 6–AG3 between SQL01\AGINST2 and SQL02\AGINST2

This is the demonstrate 2nd AG added to same set of instances already have AG in place. (refer to http://www.dbaglobe.com/2017/07/sql-server-2016-ag-setup-part-5ag2.html for AG2 setup)
VirtualBox_PEGAAD_15_07_2017_15_27_06
VirtualBox_PEGAAD_15_07_2017_15_27_56
VirtualBox_PEGAAD_15_07_2017_15_28_05
VirtualBox_PEGAAD_15_07_2017_15_29_14
VirtualBox_PEGAAD_15_07_2017_15_29_21
VirtualBox_PEGAAD_15_07_2017_15_29_39
VirtualBox_PEGAAD_15_07_2017_15_29_57
VirtualBox_PEGAAD_15_07_2017_16_37_08