Oracle tips

 1. Query how to get table name by constraint name.

SELECT owner,
       table_name
FROM   dba_constraints
WHERE  constraint_name = <<your CONSTRAINT name>>

 2. Query how to get primary by table name.

SELECT cols.table_name,
       cols.column_name,
       cols.position,
       cons.status,
       cons.owner
FROM   all_constraints cons,
       all_cons_columns cols
WHERE  cols.table_name = '<<your TABLE NAME name>>'
       AND cons.constraint_type = 'P'
       AND cons.constraint_name = cols.constraint_name
       AND cons.owner = cols.owner
ORDER  BY cols.table_name,
          cols.position; 

3. Query how to show running processes in Oracle DB

SELECT sess.process, 
       sess.status, 
       sess.username, 
       sess.schemaname, 
       SQL.sql_text 
FROM   v$session sess, 
       v$sql SQL 
WHERE  SQL.sql_id(+) = sess.sql_id 
       AND sess.username = 'YOUR_SCHEMA' 
       AND sess.status = 'ACTIVE';

4. Query check SID and SERIAL FOR KILL SESSION

SELECT sid, 
       serial#, 
       status, 
       server 
FROM   v$session 
WHERE  username = 'YOUR_SCHEMA'; 

5. Query kill process.

ALTER SYSTEM kill SESSION 'SID,SERIAL'; 

6. Query check all constraint in Oracle.

SELECT table_name,
       constraint_name,
       status,
       owner
FROM   all_constraints; 

7. Query constraint name or table name etc.

SELECT table_name,
       constraint_name,
       status,
       owner
FROM   all_constraints
WHERE  constraint_name = 'YOUR_CONSTRAINT_NAME';

8. Flashback Query

If you for error delete rows on oracle, you can do data recovery with that query and depending on the number of  transaction you have been on databases, if you made a lot of transaction the retention time is short. if you truncate the table you can't data recovery with that query.

SELECT * 
FROM   your_name_table AS OF timestamp(systimestamp - interval '60' minute); 

8. DB Link in Oracle 11g

Run next command from SQL*Plus or JDeveloper.

CREATE DATABASE LINK your_link_name 

CONNECT TO your_remote_db 

IDENTIFIED BY your_password_remote_db 

USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST =your_remote_ip)(PORT = 1521)) (CONNECT_DATA = (SID = your_schema)))'; 

Check remote table:

SELECT * 
FROM   your_table_remote@your_link_name; 

9. Database name

SELECT ora_database_name
FROM   dual; 

10. Database Schema

SELECT Sys_context('USERENV', 'current_schema')
FROM   dual; 

11. SID

SELECT Sys_context('USERENV', 'SID')
FROM   dual; 

12. Check if exist store procedure

SELECT *
FROM   all_objects
WHERE  object_type = 'PROCEDURE'
       AND object_name = 'YOUR_PROCEDURE_NAME'
       AND owner = 'YOUR_SCHEMA_NAME'; 

References:https://stackoverflow.com/questions/5247858/get-table-name-by-constraint-name/5247901

debconf: DbDriver "config": /var/cache/debconf/config.dat is locked by another process: Resource temporarily unavailable

 I share solution next error ubuntu 20.04.

Run the next command.

sudo fuser -v /var/cache/debconf/config.dat

Kill process.

sudo kill -9 PID

Next:

sudo apt update;

sudo apt upgrade;

That it is.

References.

https://askubuntu.com/questions/136881/debconf-dbdriver-config-config-dat-is-locked-by-another-process-resource-t

ERROR: dependency ‘curl’ is not available for package ‘httr’ + R Studio + Ubuntu 20.04

When i try to install tidyverse packages on R studio on ubuntu i have nexts errors, i share solutions.

ERROR: configuration failed for package ‘xml2’

Solution:

sudo apt-get install libxml2-dev

ERROR: dependency ‘curl’ is not available for package ‘httr’

ERROR: configuration failed for package ‘curl’

Solution:

sudo apt install r-cran-curl

I try again.

References:

https://stackoverflow.com/questions/52082543/curl-package-not-available-for-several-r-packages

https://github.com/r-lib/xml2/issues/223

CXF: No message body writer found for class - Spring boot 2

When i try integrate CXF with spring boot i have next error.

For solution next error add next dependency on your pom.

<dependency>
   <groupId>org.codehaus.jackson</groupId>
   <artifactId>jackson-jaxrs</artifactId>
   <version>1.9.13</version>
</dependency>
<dependency>
   <groupId>org.codehaus.jackson</groupId>
   <artifactId>jackson-xc</artifactId>
   <version>1.9.13</version>
</dependency>

And try again

This site can’t provide a secure connection + Angular + VPN + Ubuntu 20.04

Sometimes when you using VPN connection with Ubuntu and run angular project command ng serve get next error.

I share 2 solutions:

Solution 1

1. run next command on your angular project.

ng serve --ssl true --ssl-cert "./ssl/localhost.crt" --ssl-key "./ssl/localhost.key"

Use incognito google chrome to go to http://localhost:4200

This solution work for me using Ubuntu 20.04

Solution 2

2. Go to developer tools Chrome 3 dots > More tools > Developer tools

2.1 Right click on the refresh button > select > Empty Cache and Hard Reload

2.2 Then type http://localhost:4200

References:

https://stackoverflow.com/questions/40430501/127-0-0-1-this-site-can-t-provide-a-secure-connection

https://stackoverflow.com/questions/57803161/how-to-connect-to-angular-nodejs-http-localhost4200-in-google-chrome

https://stackoverflow.com/questions/52846426/unable-to-get-angular-ng-serve-command-to-establish-an-https-context

N: Skipping acquire of configured file 'main/binary-arm64/Packages' - Ubuntu 20.10

 I share solution next error.

Edit file from path: 

sudo nano /etc/apt/sources.list.d/vscode.list

Comment or change next line on file.

Original line comment or update:

deb [arch=amd64,arm64,armhf] http://packages.microsoft.com/repos/vscode stable main

To:

deb [arch=amd64] http://packages.microsoft.com/repos/vscode stable main

That it is.

References: https://stackoverflow.com/questions/65306968/vs-code-n-skipping-acquire-of-configured-file-main-binary-arm64-packages/65310278#65310278

Query Working days monday to friday and saturday 1/2 + Oracle

I share query to get working days monday to friday and saturday 1/2, for example if you hire an employee start to work on (mm/dd/yyyy) 10/15/2020 then you to pay until 10/31/2020, the working day to pay is 13.5, because to omit sunday and sum 1/2 for saturday. 

Query:

SELECT

(SELECT (( TRUNC( to_date('10/31/2020', 'mm/dd/yyyy'), 'IW' ) - TRUNC( to_date('10/15/2020', 'mm/dd/yyyy'), 'IW' ) ) * 5 / 7

       + LEAST( to_date('10/31/2020', 'mm/dd/yyyy') - TRUNC( to_date('10/31/2020', 'mm/dd/yyyy'), 'IW' ) + 1, 5 )

       - LEAST( to_date('10/15/2020', 'mm/dd/yyyy') - TRUNC( to_date('10/15/2020', 'mm/dd/yyyy'), 'IW' ) + 1, 5 )) + 1

          AS WeekDaysDifference

FROM   dual)

+(

WITH t 

     AS (SELECT to_date('10/15/2020', 'mm/dd/yyyy') start_date, 

                to_date('10/31/2020', 'mm/dd/yyyy') end_date 

         FROM   dual) 

SELECT (Count(*)/2) numSaturday

FROM   (SELECT To_char(start_date + ( LEVEL - 1 ), 'fmday') dt 

        FROM   t 

        CONNECT BY LEVEL <= end_date - start_date + 1) 

WHERE  dt IN ( 'saturday' )) workingDays FROM DUAL;

Test:

References:

https://stackoverflow.com/questions/25400025/count-the-no-of-saturdays-and-sundays-in-date-range-oracle

https://stackoverflow.com/questions/12932965/sql-to-return-the-number-of-working-days-between-2-passed-in-dates

https://stackoverflow.com/questions/43632677/function-to-get-number-of-weekdays-between-two-dates-excluding-holidays/43633234#43633234

https://stackoverflow.com/questions/44203389/oracle-days-between-two-date-and-exclude-weekdays-how-to-handle-negative-number