Connect to OCI via GIT Bash Tunneling

1. mkdir .ssh

2. cd .ssh

3. vi config

 

Host *
   ServerAliveInterval 240
   ServerAliveCountMax 20
   StrictHostKeyChecking=no
   ForwardAgent yes
Host P1
   Hostname 123.123.123.123
   LocalForward localhost:9021 abc000021801.a1-prodapp-ash1-a-i-vcn2.abc.com:1521
   KexAlgorithms +diffie-hellman-group14-sha1
Host P2
   Hostname 123.123.123.123
   LocalForward localhost:9022 abc000021802.a1-prodapp-ash1-a-i-vcn2.abc.com:1521
   KexAlgorithms +diffie-hellman-group14-sha1
Host S1
   Hostname 123.123.123.123
   LocalForward localhost:9023 abc000021841.a1-prodapp-phx1-a-i-vcn2.abc.com:1521
   KexAlgorithms +diffie-hellman-group14-sha1
Host S2
   Hostname 123.123.123.123
   LocalForward localhost:9024 abc000021842.a1-prodapp-phx1-a-i-vcn2.abc.com:1521
   KexAlgorithms +diffie-hellman-group14-sha1
Host D1
   Hostname 123.123.123.123
   LocalForward localhost:9025 abc00003324.a1-prodapp-phx1-a-i-vcn2.abc.com:1521
   KexAlgorithms +diffie-hellman-group14-sha1  

4. ssh P1

5. SQL Developer: 

    HostName: localhost

    Port: 9021

    Service Name: ORCL

Pivot

 CREATE TABLE TESTING (  ID VARCHAR2(100),SES VARCHAR2(100),AMOUNT NUMBER,CNT NUMBER);

INSERT INTO TESTING VALUES('abc123','njn-jn',10,9);
INSERT INTO TESTING VALUES('abc123','kjsdb-jj',20,3);
INSERT INTO TESTING VALUES('abc123','jskj-5g',30,6);
COMMIT;
INSERT INTO TESTING VALUES('abc345','njn-jn1',10,9);
INSERT INTO TESTING VALUES('abc345','kjsdb-jj1',20,3);
INSERT INTO TESTING VALUES('abc345','jskj-5g1',30,6);
COMMIT;

SELECT * FROM TESTING;

WITH SET_RN AS
(SELECT ID,SES,AMOUNT,CNT,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ROWID ASC) RN FROM TESTING)
SELECT S.ID,
(SELECT S1.SES FROM SET_RN S1 WHERE S1.ID=S.ID AND S1.RN=1) AS SES1 ,
(SELECT S1.AMOUNT FROM SET_RN S1 WHERE S1.ID=S.ID AND S1.RN=1) AS AMOUNT1, 
(SELECT S1.CNT FROM SET_RN S1 WHERE S1.ID=S.ID AND S1.RN=1) AS CNT1,
(SELECT S2.SES FROM SET_RN S2 WHERE S2.ID=S.ID AND S2.RN=2) AS SES2 ,
(SELECT S2.AMOUNT FROM SET_RN S2 WHERE S2.ID=S.ID AND S2.RN=2) AS AMOUNT2, 
(SELECT S2.CNT FROM SET_RN S2 WHERE S2.ID=S.ID AND S2.RN=2) AS CNT3,
(SELECT S3.SES FROM SET_RN S3 WHERE S3.ID=S.ID AND S3.RN=3) AS SES3 ,
(SELECT S3.AMOUNT FROM SET_RN S3 WHERE S3.ID=S.ID AND S3.RN=3) AS AMOUNT3, 
(SELECT S3.CNT FROM SET_RN S3 WHERE S3.ID=S.ID AND S3.RN=3) AS CNT3
FROM SET_RN S GROUP BY ID;

SQL query to view structure of the table in Oracle

  SELECT A.OWNER,
         A.TABLE_NAME,
         C.COLUMN_NAME,
         C.DATA_TYPE,
         C.DATA_LENGTH,
         C.DATA_PRECISION,
         C.DATA_SCALE,
         C.COLUMN_ID
    FROM ALL_TABLES A, ALL_TAB_COLUMNS C
   WHERE     A.TABLE_NAME = C.TABLE_NAME
         AND A.OWNER = 'SCOTT'
         AND C.OWNER = 'SCOTT'
         AND A.TABLE_NAME = 'EMP'
ORDER BY A.TABLE_NAME, C.COLUMN_ID;