,ROW_NUMBER() OVER ( PARTITION BY e.department_id ORDER BY e.salary) AS rownumber

,ROW_NUMBER() OVER ( PARTITION BY e.department_id ORDER BY e.salary) AS rownumber

Oracle Tablespace sizes

select TABLESPACE_NAME "Tablspace", 

 FILE_NAME "Filename", 

 BYTES/1024/1024 "Size MB",

 MAXBYTES/1024/1024 "Maximum Size MB",

 AUTOEXTENSIBLE "Autoextensible"

from SYS.DBA_DATA_FILES

TIME STAMP DIFFERENCE AVG

 select
    avg(extract(second from intrvl)
        + extract(minute from intrvl) * 60
        + extract(hour from intrvl) * 60 * 60
        + extract(day from intrvl) * 60 * 60 * 24) average
from (
 SELECT    ( TIME_STAMP1-TIME_STAMP2) intrvl

  FROM  Z_TABLE   )

oracle ojdbc.jar - maven

<repositories>
    <!-- Repository for ORACLE ojdbc6. -->
    <repository>
        <id>codelds</id>
        <url>https://code.lds.org/nexus/content/groups/main-repo</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc6</artifactId>
        <version>11.2.0.3</version>
    </dependency>
</dependencies>

timestamp_diff

CREATE OR REPLACE function timestamp_diff(a timestamp, b timestamp) return number is 
begin
  return extract (day    from (a-b))*24*60*60 +
         extract (hour   from (a-b))*60*60+
         extract (minute from (a-b))*60+
         extract (second from (a-b));
end;

Update table with data from another table oRACLE

Example - Update table with data from another table

Let's look at an Oracle UPDATE example that shows how to update a table with data from another table.

UPDATE customers
SET c_details = (SELECT contract_date
                 FROM suppliers
                 WHERE suppliers.supplier_name = customers.customer_name)
WHERE customer_id < 1000;

This UPDATE example would update only the customers table for all records where the customer_id is less than 1000. When the supplier_name from the suppliers table matches the customer_name from the customers table, the contract_date from the suppliers table would be copied to the c_details field in the customers table.

Example - Using EXISTS Clause

You can also perform more complicated updates in Oracle.

You may wish to update records in one table based on values in another table. Since you can't list more than one table in the Oracle UPDATE statement, you can use the Oracle EXISTS clause.

For example:

UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.customer_name
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

In this Oracle UPDATE example, whenever a supplier_id matched a customer_id value, the supplier_name would be overwritten to the customer_name from the customers table.

https://www.techonthenet.com/oracle/update.php

Certificate installation Java

keytool -import -alias yakeen -keystore  cacerts  -file yakeen-pilotingeservecomsa.der
keytool -list -v -keystore  cacerts  > sajjad_cacerts.txt

folder :C:\Program Files\Java\jdk1.8.0_121\jre\lib\security

Keytool Java Security

http://magicmonster.com/kb/prg/java/ssl/pkix_path_building_failed.html

PKIX path building failed: SunCertPathBuilderException: unable to find valid certification path to requested target.

SSLHandshakeException: PKIX: unable to find valid certification path to requested target

This solution relates to the following stacktrace:

javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed:
sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target.
    

We experienced this issue when a server changed their HTTPS SSL certificate, and our older version of Java did not recognize the root certificate authority (CA).

If you can access the HTTPS URL in your browser then it is possible to update Java to recognize the root CA.

In your browser, go to the HTTPS URL that Java could not access. Click on the HTTPS certificate chain (there is lock icon in the Internet Explorer, or the domain name left of the URL in firefox) and navigate the certificate hierarchy. At the top there should be a Primary Root CA. This could be missing from your java cacerts file. Note down the Issuer and Serial Number.

To verify the root certificates, determine where the cacerts file is located. By default it is injre/lib/security/cacerts. The default password for this keystore is 'changeit'.

e.g. on my machine, I have both JDK and JRE, here is where they are located.

./jdk1.6.0_24/jre/lib/security/cacerts
./jre1.6.0_24/lib/security/cacerts

Different versions of java can have different cacerts.

If you do not want to modify the default JRE store, you can make a copy, and use the following system properties to specify the location and password.

javax.net.ssl.trustStore
javax.net.ssl.trustStorePassword

Once you have your keystore, dump its contents by using the list option.

keytool -list -v -keystore /path/to/cacerts  > java_cacerts.txt
Enter keystore password:  changeit

In this example, /path/to/cacerts is the location of your cacerts file, and the output of the command will be saved in java_cacerts.txt.

Take a look at java_cacerts.txt. See if it includes the same certificate that is present in the browser by searching for a matching serial number. In the java_cacerts.txt file, the serial number will be in lowercase and without the ":" colon character. If it is not present, then this could be the reason for the error, and we can fix this by adding the certificate found in the browser.

Back in the browser, export the Root CA. Choose the "X.509 Certificate (DER)" type, so the exported file has a der extension.

Assuming the file is called example.der, pick the alias 'example' for this certificate. Next import the file.

keytool -import -alias example -keystore  /path/to/cacerts -file example.der

You will be prompted for a password, use 'changeit'

and respond "yes" on whether to trust this key.

Dump the contents again to verify it contains your new certificate. Restart the JVM and check that it can now access the HTTPS URL. Also remove the java_cacerts.txt dump file.

See also java-samples.com and keytool.

Published: Thursday, 31 March 2011

Oracle jdbc maven

<repositories>
    <!-- Repository for ORACLE ojdbc6. -->
    <repository>
        <id>codelds</id>
        <url>https://code.lds.org/nexus/content/groups/main-repo</url>
    </repository>
</repositories>
<dependencies> 
    <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc6</artifactId>
        <version>11.2.0.3</version>
    </dependency>
</dependencies> 

src:http://stackoverflow.com/questions/1074869/find-oracle-jdbc-driver-in-maven-repository

Alphanumeric check in oracle

  LENGTH(TRIM(TRANSLATE(TEXT_TO_BE_TESTED, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789', ' '))) is not null

Update grouped records in Oracle with an incremental value

 UPDATE  table_t t1
   SET FIELD_NEW = (SELECT rnk
                        FROM (SELECT id_val,
                                     row_number() over (partition by GROUPING_COULUMN
                                                            order by id_val) rnk
                                FROM  table_t ) t2
                       WHERE t2.id_val = t1.id_val)