Using SQL Developer to connect database via SSH Tunnelling

1. Below is the database server: SID: orcl1, I have normal unix account “donghua” to access the server via ssh.

2. Create the connection string profile. The hostname is “localhost” because the connection is tunnelling via SSH to the server, rather than remotely.

3. Click “Advanced” in above screenshot, enter SSH details. It’s possible to use SSH private key to automate the login. (Not used in my testing)

4. When connect to the database, it will promote for SSH password. (Since I already saved database password, it will not ask DB password here).

5. Connected to database. You can work with the GUI interface rather than SQLPlus now.

How to fix “The database principal owns a schema in the database, and cannot be dropped.”

PS C:\Users\Administrator> sqlcmd -S  .
1> use DB1
2> go
Changed database context to 'DB1'.

1> drop user U1
2> go
Msg 15138, Level 16, State 1, Server WIN-922S55M9QDP, Line 1
The database principal owns a schema in the database, and cannot be dropped.

1> select  name from sys.schemas where principal_id=DATABASE_PRINCIPAL_ID('U1')
2> go
name
----------------------------------------------------
db_ddladmin
db_datareader
db_datawriter

(3 rows affected)

1> alter authorization on schema::db_ddladmin to dbo
2> go

1> alter authorization on schema::db_datareader to dbo
2> go

1> alter authorization on schema::db_datawriter to dbo
2> go

1> select  name from sys.schemas where principal_id=DATABASE_PRINCIPAL_ID('U1')
2> go
name
----------------------------------------------------

(0 rows affected)
1> drop user u1
2> go

Approximate Count Distinct–Oracle Database 12c

 

Not every query requires a completely accurate result, for example “How many distinct individuals visited our website last week?”.

New SQL function for approximate results for COUNT DISTINCT aggregates –APPROX_COUNT_DISTINCT()

• Approximate results can be significantly faster and use less resources than exact calculations
• –5x to 50x ++ times faster (depending upon number of distinct values and complexity of SQL)
  –Accuracy > 97% (with 95% confidence)

Example:

SQL> select count(distinct prod_id) from sh.sales;

COUNT(DISTINCTPROD_ID)
----------------------
                    72

SQL> select approx_count_distinct(prod_id) from sh.sales;

APPROX_COUNT_DISTINCT(PROD_ID)
------------------------------
                            72

SQL>  select count(distinct cust_id) from sh.sales;

COUNT(DISTINCTCUST_ID)
----------------------
                  7059

SQL> select approx_count_distinct(cust_id) from sh.sales;

APPROX_COUNT_DISTINCT(CUST_ID)
------------------------------
                          7014

Cannot drop suspect mirror database? Here is explanation and workaround

·         Mirror database is suspect (hardware failure causing corruption), SQL Server can’t apply the redo logs on the mirror

·         With the intent to recover DB and reestablish mirroring, ALTER DATABASE … SET PARTNER OFF is issued on the principal

o   DB Mirroring breaks DBM session from the principal point of view, and then sends the message to the mirror

o   Mirror tries to break the DBM session; however, SQL Server attempts to apply all the remaining log in the redo queue before it breaks the DBM session. This is by design. The goal is to apply all the logs it has received because the user may be attempting to break the mirror so as to read the data on the mirror database. It makes sense to apply as much redo log records as possible

·         DBM session is not broken from the mirror point of view

·         All subsequent actions (drop database, restore database) are blocked, because SQL Server still views this as a mirror database

Workaround:

Shutdown the instance and remove the mdf, ndf and ldf for the suspect database, and start the instance. You should be able to restore the instance, and recreate the mirror.

SQL Text Expansion in Oracle 12c

This procedure will be handy if you are asked to tune a query, which looks like short, but actual complexity hidden by the views. You can use this procedure to expand the view definition into the query.

https://docs.oracle.com/database/121/ARPLS/d_util.htm#ARPLS73973

SQL> variable x clob
SQL> begin
  2    dbms_utility.expand_sql_text(
  3      input_sql_text => 'select * from hr.emp_details_view where employee_id=107',
  4      output_sql_text => :x
  5    );
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> print x

X
--------------------------------------------------------------------------------
SELECT "A1"."EMPLOYEE_ID" "EMPLOYEE_ID","A1"."JOB_ID" "JOB_ID","A1"."MANAGER_ID"

SQL> set long 2000
SQL> set longc 2000
SQL> set pages 999
SQL> print x

X
--------------------------------------------------------------------------------
SELECT "A1"."EMPLOYEE_ID" "EMPLOYEE_ID","A1"."JOB_ID" "JOB_ID","A1"."MANAGER_ID"
"MANAGER_ID","A1"."DEPARTMENT_ID" "DEPARTMENT_ID","A1"."LOCATION_ID" "LOCATION_
ID","A1"."COUNTRY_ID" "COUNTRY_ID","A1"."FIRST_NAME" "FIRST_NAME","A1"."LAST_NAM
E" "LAST_NAME","A1"."SALARY" "SALARY","A1"."COMMISSION_PCT" "COMMISSION_PCT","A1
"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A1"."JOB_TITLE" "JOB_TITLE","A1"."CITY" "
CITY","A1"."STATE_PROVINCE" "STATE_PROVINCE","A1"."COUNTRY_NAME" "COUNTRY_NAME",
"A1"."REGION_NAME" "REGION_NAME" FROM  (SELECT "A7"."EMPLOYEE_ID" "EMPLOYEE_ID",
"A7"."JOB_ID" "JOB_ID","A7"."MANAGER_ID" "MANAGER_ID","A7"."DEPARTMENT_ID" "DEPA
RTMENT_ID","A6"."LOCATION_ID" "LOCATION_ID","A4"."COUNTRY_ID" "COUNTRY_ID","A7".
"FIRST_NAME" "FIRST_NAME","A7"."LAST_NAME" "LAST_NAME","A7"."SALARY" "SALARY","A
7"."COMMISSION_PCT" "COMMISSION_PCT","A6"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A
5"."JOB_TITLE" "JOB_TITLE","A4"."CITY" "CITY","A4"."STATE_PROVINCE" "STATE_PROVI
NCE","A3"."COUNTRY_NAME" "COUNTRY_NAME","A2"."REGION_NAME" "REGION_NAME" FROM HR
."EMPLOYEES" "A7",HR."DEPARTMENTS" "A6",HR."JOBS" "A5",HR."LOCATIONS" "A4",HR."C
OUNTRIES" "A3",HR."REGIONS" "A2" WHERE "A7"."DEPARTMENT_ID"="A6"."DEPARTMENT_ID"
AND "A6"."LOCATION_ID"="A4"."LOCATION_ID" AND "A4"."COUNTRY_ID"="A3"."COUNTRY_I
D" AND "A3"."REGION_ID"="A2"."REGION_ID" AND "A5"."JOB_ID"="A7"."JOB_ID") "A1" W
HERE "A1"."EMPLOYEE_ID"=107

SQL> set lin 20000

SQL> print x

X
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "A1"."EMPLOYEE_ID" "EMPLOYEE_ID","A1"."JOB_ID" "JOB_ID","A1"."MANAGER_ID" "MANAGER_ID","A1"."DEPARTMENT_ID" "DEPARTMENT_ID","A1"."LOCATION_ID" "LOCATION_ID","A1"."COUNTRY_ID" "COUNTRY_ID","A1"."FIRST_NAME" "FIRST_NAME","A1"."LAST_NAME" "LAST_NAME","A1"."SALARY" "SALARY","A1"."COMMISSION_PCT" "COMMISSION_PCT","A1"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A1"."JOB_TITLE" "JOB_TITLE","A1"."CITY" "CITY","A1"."STATE_PROVINCE" "STATE_PROVINCE","A1"."COUNTRY_NAME" "COUNTRY_NAME","A1"."REGION_NAME" "REGION_NAME" FROM  (SELECT "A7"."EMPLOYEE_ID" "EMPLOYEE_ID","A7"."JOB_ID" "JOB_ID","A7"."MANAGER_ID" "MANAGER_ID","A7"."DEPARTMENT_ID" "DEPARTMENT_ID","A6"."LOCATION_ID" "LOCATION_ID","A4"."COUNTRY_ID" "COUNTRY_ID","A7"."FIRST_NAME" "FIRST_NAME","A7"."LAST_NAME" "LAST_NAME","A7"."SALARY" "SALARY","A7"."COMMISSION_PCT" "COMMISSION_PCT","A6"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A5"."JOB_TITLE" "JOB_TITLE","A4"."CITY" "CITY","A4"."STATE_PROVINCE" "STATE_PROVINCE","A3"."COUNTRY_NAME" "COUNTRY_NAME","A2"."REGION_NAME" "REGION_NAME" FROM HR."EMPLOYEES" "A7",HR."DEPARTMENTS" "A6",HR."JOBS" "A5",HR."LOCATIONS" "A4",HR."COUNTRIES" "A3",HR."REGIONS" "A2" WHERE "A7"."DEPARTMENT_ID"="A6"."DEPARTMENT_ID" AND "A6"."LOCATION_ID"="A4"."LOCATION_ID" AND "A4"."COUNTRY_ID"="A3"."COUNTRY_ID" AND "A3"."REGION_ID"="A2"."REGION_ID" AND "A5"."JOB_ID"="A7"."JOB_ID") "A1" WHERE "A1"."EMPLOYEE_ID"=107

Use SQL Developer to format the SQL Statement:

How to fix SQL Developer Unsupported JDK Version. (You are attempting to run with java …”

Change the Value for “SetJavaHome” in %APPDATA%\sqldeveloper\<version>\product.conf.

Sample code for encryption-only SSL using JDBC/thin driver with Oracle database

Working Code & Compilation

import java.sql.*;
import java.util.Properties;
import oracle.jdbc.pool.OracleDataSource;
import oracle.security.pki.OraclePKIProvider;
import java.security.Security;

public class SSLTestV2 {
    public static void main(String[] args) throws SQLException {
        Connection conn = getConnection();
        conn.close();
    }

    public static Connection getConnection() throws SQLException {
        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=solaris)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=orcl1)))");
        Properties props = new Properties();
        props.setProperty("user", "donghua");
        props.setProperty("password", "Passw0rd");
        Security.insertProviderAt(new OraclePKIProvider(), 3);
        props.setProperty("oracle.net.wallet_location","(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/wallet)))");
        ods.setConnectionProperties(props);

        Connection conn = ods.getConnection();
        DatabaseMetaData dbmd = conn.getMetaData();
        System.out.println(dbmd.getDatabaseProductVersion());
        System.out.println("JDBC driver: " + dbmd.getDriverVersion());
        System.out.println("JDBC URL: " + dbmd.getURL());
        conn.setAutoCommit(false);
        return conn;
    }
}

oracle@solaris112:~$ /usr/java/bin/javac -cp /u01/app/oracle/product/12.1.0/dbhome_1/jdbc/lib/ojdbc7.jar:/u01/app/oracle/product/12.1.0/dbhome_1/jlib/oraclepki.jar SSLTestV2.java
oracle@solaris112:~$ /usr/java/bin/java -cp .:/u01/app/oracle/product/12.1.0/dbhome_1/jdbc/lib/ojdbc7.jar:/u01/app/oracle/product/12.1.0/dbhome_1/jlib/oraclepki.jar SSLTestV2
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
JDBC driver: 12.1.0.2.0
JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=solaris)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=orcl1)))
oracle@solaris112:~$

Not working code with possible errors:

root@solaris112:~# /usr/java/bin/java -version
java version "1.7.0_60"
Java(TM) SE Runtime Environment (build 1.7.0_60-b19)
Java HotSpot(TM) Client VM (build 24.60-b09, mixed mode)

oracle@solaris112:~$ $ORACLE_HOME/jdk/bin/java -version
java version "1.6.0_75"
Java(TM) SE Runtime Environment (build 1.6.0_75-b13)
Java HotSpot(TM) Client VM (build 20.75-b01, mixed mode)
oracle@solaris112:~$

import java.sql.*;
import java.util.Properties;
import oracle.jdbc.pool.OracleDataSource;

public class SSLTestV1 {
    public static void main(String[] args) throws SQLException {
        Connection conn = getConnection();
        conn.close();
    }

    public static Connection getConnection() throws SQLException {
        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=solaris)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=orcl1)))");
        Properties props = new Properties();
        props.setProperty("user", "donghua");
        props.setProperty("password", "Passw0rd");
        props.setProperty("oracle.net.ssl_cipher_suites","(SSL_DH_anon_WITH_3DES_EDE_CBC_SHA, SSL_DH_anon_WITH_RC4_128_MD5,SSL_DH_anon_WITH_DES_CBC_SHA)");
        ods.setConnectionProperties(props);

        Connection conn = ods.getConnection();
        DatabaseMetaData dbmd = conn.getMetaData();
        System.out.println(dbmd.getDatabaseProductVersion());
        System.out.println("JDBC driver: " + dbmd.getDriverVersion());
        System.out.println("JDBC URL: " + dbmd.getURL());
        conn.setAutoCommit(false);
        return conn;
    }
}

oracle@solaris112:~$ $ORACLE_HOME/jdk/bin/javac -cp /u01/app/oracle/product/12.1.0/dbhome_1/jdbc/lib/ojdbc7.jar SSLTest.java
warning: oracle/jdbc/pool/OracleDataSource.class(oracle/jdbc/pool:OracleDataSource.class): major version 51 is newer than 50, the highest major version supported by this compiler.
It is recommended that the compiler be upgraded.
1 warning

oracle@solaris112:~$ $ORACLE_HOME/jdk/bin/java -cp .:/u01/app/oracle/product/12.1.0/dbhome_1/jdbc/lib/ojdbc7.jar SSLTest
Exception in thread "main" java.lang.UnsupportedClassVersionError: oracle/jdbc/pool/OracleDataSource : Unsupported major.minor version 51.0
        at java.lang.ClassLoader.defineClass1(Native Method)
        at java.lang.ClassLoader.defineClassCond(ClassLoader.java:637)
        at java.lang.ClassLoader.defineClass(ClassLoader.java:621)
        at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:141)
        at java.net.URLClassLoader.defineClass(URLClassLoader.java:283)
        at java.net.URLClassLoader.access$000(URLClassLoader.java:58)
        at java.net.URLClassLoader$1.run(URLClassLoader.java:197)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
        at SSLTest.getConnection(SSLTest.java:12)
        at SSLTest.main(SSLTest.java:7)

oracle@solaris112:~$ java -cp .:/u01/app/oracle/product/12.1.0/dbhome_1/jdbc/lib/ojdbc7.jar SSLTest
Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Received fatal alert: handshake_failure
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:752)
        at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:666)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:566)
        at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:317)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:241)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:184)
        at SSLTest.getConnection(SSLTest.java:20)
        at SSLTest.main(SSLTest.java:7)
Caused by: javax.net.ssl.SSLHandshakeException: Received fatal alert: handshake_failure
        at sun.security.ssl.Alerts.getSSLException(Alerts.java:192)
        at sun.security.ssl.Alerts.getSSLException(Alerts.java:154)
        at sun.security.ssl.SSLSocketImpl.recvAlert(SSLSocketImpl.java:1959)
        at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1077)
        at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1312)
        at sun.security.ssl.SSLSocketImpl.writeRecord(SSLSocketImpl.java:702)
        at sun.security.ssl.AppOutputStream.write(AppOutputStream.java:122)
        at oracle.net.ns.Packet.send(Packet.java:419)
        at oracle.net.ns.ConnectPacket.send(ConnectPacket.java:241)
        at oracle.net.ns.NSProtocolStream.negotiateConnection(NSProtocolStream.java:157)
        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:264)
        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
        ... 8 more

oracle@solaris112:~$ java -cp .:/u01/app/oracle/product/12.1.0/dbhome_1/jdbc/lib/ojdbc7.jar SSLTest
Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Remote host closed connection during handshake
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:752)
        at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:666)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:566)
        at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:317)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:241)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:184)
        at SSLTest.getConnection(SSLTest.java:20)
        at SSLTest.main(SSLTest.java:7)
Caused by: javax.net.ssl.SSLHandshakeException: Remote host closed connection during handshake
        at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:946)
        at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1312)
        at sun.security.ssl.SSLSocketImpl.writeRecord(SSLSocketImpl.java:702)
        at sun.security.ssl.AppOutputStream.write(AppOutputStream.java:122)
        at oracle.net.ns.Packet.send(Packet.java:419)
        at oracle.net.ns.ConnectPacket.send(ConnectPacket.java:241)
        at oracle.net.ns.NSProtocolStream.negotiateConnection(NSProtocolStream.java:157)
        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:264)
        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
        ... 8 more
Caused by: java.io.EOFException: SSL peer shut down incorrectly
        at sun.security.ssl.InputRecord.read(InputRecord.java:482)
        at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:927)
        ... 17 more