Tuesday, May 28, 2019
Sunday, May 26, 2019
Oracle Flashback Query: Recovering - using AS OF TIMESTAMP
Microsoft Windows [Version 10.0.17763.503]
(c) 2018 Microsoft Corporation. All rights reserved.
C:\Users\rajam>sqlplus sys as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 7 18:09:40 2019
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> set lines 100
SQL> set pages 50
SQL> create table flash_test as select * from emp;
Table created.
SQL> select * from flash_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select count(*) from flash_test;
COUNT(*)
----------
14
SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') ddate,dbms_flashback.get_system_change_number() scn from dual;
DDATE SCN
-------------------- ----------
07-06-2019 18:11:12 12292660
SQL> delete from flash_test;
14 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from flash_test;
no rows selected
SQL> -- to view the data of the table at the specified timestamp
SQL> select count(*) from flash_test as of timestamp to_timestamp('07-06-2019 18:11:12','dd-mm-yyyy hh24:mi:ss');
COUNT(*)
----------
14
SQL> -- to view the data of the table at the specified scn
SQL> select count(*) from flash_test as of scn 12292660;
COUNT(*)
----------
14
SQL> -- To view the data of the table as it was 8 minutes ago
SQL> select systimestamp,count(*) from flash_test as of timestamp (systimestamp -interval '8' minute);
SYSTIMESTAMP COUNT(*)
--------------------------------------------------------------------------- ----------
07-JUN-19 06.19.21.585000 PM +05:30 14
SQL> -- to convert scn to timestamp and timestamp to scn
SQL> select scn_to_timestamp(12292660) ddate, timestamp_to_scn(to_timestamp('07-06-2019 18:11:12','dd-mm-yyyy hh24:mi:ss')) scn from dual;
DDATE SCN
--------------------------------------------------------------------------- ----------
07-JUN-19 06.11.11.000000000 PM 12292660
SQL> select * from flash_test as of scn 12292660;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Microsoft Windows [Version 10.0.17134.765]
(c) 2018 Microsoft Corporation. All rights reserved.
C:\Users\rajam>sqlplus system/admin
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 27 10:18:03 2019
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> set define off
SQL> set lines 200
SQL> SELECT * FROM EMP_DETAILS;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
---------------------------------------------------------------------------
27-MAY-19 10.18.28.935000 AM +05:30
SQL> UPDATE EMP_DETAILS SET COMM=500 WHERE EMPNO=7782;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM EMP_DETAILS;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 500 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
---------------------------------------------------------------------------
27-MAY-19 10.23.03.328000 AM +05:30
SQL> SELECT * FROM EMP_DETAILS AS OF TIMESTAMP TO_TIMESTAMP('27-MAY-2019 10:19:34 AM','DD-MON-YYYY HH:MI:SS AM');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> SELECT * FROM EMP_DETAILS AS OF TIMESTAMP TO_TIMESTAMP('27-MAY-2019 10:19:34 AM','DD-MON-YYYY HH:MI:SS AM') WHERE EMPNO=7782;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
SQL>
Monday, May 20, 2019
ETL Testing - Oracle
Source : SYSTEM.EMP_SOURCE
Target : SYSTEM.EMP_TARGET
/* TC01: Metadata check - Structure Validation */
SQL> DESC EMP_SOURCE;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> DESC EMP_TARGET;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL>
/* TC02: Count Validation */
SQL> SELECT 'SOURCE',COUNT(*) AS CNT FROM SYSTEM.EMP_SOURCE
2 UNION ALL
3 SELECT 'TARGET',COUNT(*) AS CNT FROM SYSTEM.EMP_TARGET;
'SOURC CNT
------ ----------
SOURCE 14
TARGET 14
SQL>
/* TC03: Duplicate Check */
SQL> SELECT EMPNO,COUNT(*) FROM SYSTEM.EMP_TARGET GROUP BY EMPNO HAVING COUNT(*)>1;
no rows selected
SQL>
/* TC04: NOT NULL Validation */
SQL> SELECT EMPNO,ENAME FROM SYSTEM.EMP_TARGET WHERE (EMPNO IS NULL OR ENAME IS NULL);
no rows selected
SQL>
/* TC05: Default Check */
-- NA
/* TC06: Data Validation */
SQL> -- source minus target
SQL> SELECT EMPNO,ENAME,SAL,COMM,HIREDATE,DEPTNO FROM SYSTEM.EMP_SOURCE
2 MINUS
3 SELECT EMPNO,ENAME,SAL,COMM,HIREDATE,DEPTNO FROM SYSTEM.EMP_TARGET;
no rows selected
SQL> -- target minus source
SQL> SELECT EMPNO,ENAME,SAL,COMM,HIREDATE,DEPTNO FROM SYSTEM.EMP_TARGET
2 MINUS
3 SELECT EMPNO,ENAME,SAL,COMM,HIREDATE,DEPTNO FROM SYSTEM.EMP_SOURCE;
no rows selected
SQL>
/* TC07: Audit Columns Validation */
-- NA
ETL Testing - SQL Server 2012
-- Creating Temp Table
USE DBNAME;
SELECT [*]/[COLUMNS LIST] INTO #TEMP_TABLE FROM TABLE_NAME;
SELECT * FROM #TEMP_TABLE;
DROP TABLE #TEMP_TABLE;
USE DBNAME;
SELECT [*]/[COLUMNS LIST] INTO #TEMP_TABLE FROM TABLE_NAME;
SELECT * FROM #TEMP_TABLE;
DROP TABLE #TEMP_TABLE;
Monday, May 6, 2019
Unix Basic Commands - 1
[oracle@ODIGettingStarted ~]$ echo $HOME
/home/oracle
[oracle@ODIGettingStarted ~]$ pwd
/home/oracle
[oracle@ODIGettingStarted ~]$ uname
Linux
[oracle@ODIGettingStarted ~]$ uname -i
x86_64
[oracle@ODIGettingStarted ~]$ uname -n
ODIGettingStarted
[oracle@ODIGettingStarted ~]$ uname -rs
Linux 2.6.39-400.17.1.el6uek.x86_64
[oracle@ODIGettingStarted ~]$ uname --help
Usage: uname [OPTION]...
Print certain system information. With no OPTION, same as -s.
-a, --all print all information, in the following order,
except omit -p and -i if unknown:
-s, --kernel-name print the kernel name
-n, --nodename print the network node hostname
-r, --kernel-release print the kernel release
-v, --kernel-version print the kernel version
-m, --machine print the machine hardware name
-p, --processor print the processor type or "unknown"
-i, --hardware-platform print the hardware platform or "unknown"
-o, --operating-system print the operating system
--help display this help and exit
--version output version information and exit
Report uname bugs to bug-coreutils@gnu.org
GNU coreutils home page: <http://www.gnu.org/software/coreutils/>
General help using GNU software: <http://www.gnu.org/gethelp/>
For complete documentation, run: info coreutils 'uname invocation'
[oracle@ODIGettingStarted ~]$ date
Mon May 6 05:33:10 EDT 2019
[oracle@ODIGettingStarted ~]$ cal
May 2019
Su Mo Tu We Th Fr Sa
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
[oracle@ODIGettingStarted ~]$ cal 02 2019
February 2019
Su Mo Tu We Th Fr Sa
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28
[oracle@ODIGettingStarted ~]$ clear
[oracle@ODIGettingStarted ~]$
[oracle@ODIGettingStarted ~]$ ls
Desktop Documents Downloads mountshared.sh Music openssl-1.0.1g openssl-1.0.1g.tar.gz oradiag_oracle oraInventory Pictures Public Templates Videos
[oracle@ODIGettingStarted ~]$ ls -a
. .bash_profile~ .dmrc .gconfd .gtk-bookmarks .local .odi oraInventory .sqldeveloper .vboxclient-display.pid .xsession-errors.old
.. .bashrc Documents .gnome2 .gvfs mountshared.sh .odi_jdk Pictures .ssh .vboxclient-draganddrop.pid
.abrt .cache Downloads .gnome2_private .ICEauthority .mozilla openssl-1.0.1g Public Templates .vboxclient-seamless.pid
.bash_history .config .emacs .gnote .icons Music openssl-1.0.1g.tar.gz .pulse .themes Videos
.bash_logout .dbus .esd_auth .gnupg .imsettings.log .nautilus .oracle_jre_usage .pulse-cookie .thumbnails .viminfo
.bash_profile Desktop .gconf .gstreamer-0.10 .lesshst .ocm oradiag_oracle .recently-used.xbel .vboxclient-clipboard.pid .xsession-errors
[oracle@ODIGettingStarted ~]$ ls -i
1455738 Desktop 1455739 Downloads 1455743 Music 1455875 openssl-1.0.1g.tar.gz 1456101 oraInventory 1455741 Public 1455745 Videos
1455742 Documents 1456088 mountshared.sh 1594624 openssl-1.0.1g 1455943 oradiag_oracle 1455744 Pictures 1455740 Templates
[oracle@ODIGettingStarted ~]$
[oracle@ODIGettingStarted ~]$ ls -l
total 4452
drwxr-xr-x. 3 oracle oracle 4096 Jun 17 2016 Desktop
drwxr-xr-x. 2 oracle oracle 4096 Aug 28 2013 Documents
drwxr-xr-x. 2 oracle oracle 4096 May 2 02:20 Downloads
-rw-rw-r--. 1 oracle oracle 56 Aug 30 2013 mountshared.sh
drwxr-xr-x. 2 oracle oracle 4096 Aug 28 2013 Music
drwxrwxr-x. 22 oracle oracle 4096 Apr 6 14:16 openssl-1.0.1g
-rw-rw-r--. 1 oracle oracle 4509047 Apr 7 2014 openssl-1.0.1g.tar.gz
drwxr-xr-x. 3 oracle oracle 4096 Aug 29 2013 oradiag_oracle
drwxrwx---. 4 oracle oracle 4096 Jun 20 2014 oraInventory
drwxr-xr-x. 2 oracle oracle 4096 Aug 28 2013 Pictures
drwxr-xr-x. 2 oracle oracle 4096 Aug 28 2013 Public
drwxr-xr-x. 2 oracle oracle 4096 Aug 28 2013 Templates
drwxr-xr-x. 2 oracle oracle 4096 Aug 28 2013 Videos
[oracle@ODIGettingStarted ~]$ ls Documents
[oracle@ODIGettingStarted ~]$ ls Desktop
Information about this machine OBE_Files ODI 12c Studio.desktop SQL Developer.desktop startOGGSource.sh~ Stop OGG.desktop stopOGGSource.sh~
Information about this machine~ ODI 12c Getting Started Guide.pdf ODI and OGG Demo UI.desktop Start OGG.desktop startOGGTarget.sh~ stopOGG.sh~ stopOGGTarget.sh~
[oracle@ODIGettingStarted ~]$ ls /home/oracle/Desktop
Information about this machine OBE_Files ODI 12c Studio.desktop SQL Developer.desktop startOGGSource.sh~ Stop OGG.desktop stopOGGSource.sh~
Information about this machine~ ODI 12c Getting Started Guide.pdf ODI and OGG Demo UI.desktop Start OGG.desktop startOGGTarget.sh~ stopOGG.sh~ stopOGGTarget.sh~
[oracle@ODIGettingStarted ~]$ ls -l Desktop
total 3652
-rw-rw-r--. 1 oracle oracle 1098 Jun 17 2016 Information about this machine
-rw-rw-r--. 1 oracle oracle 1098 Oct 21 2015 Information about this machine~
drwxrwxr-x. 5 oracle oracle 4096 Dec 16 2015 OBE_Files
-rwxr-xr-x. 1 oracle oracle 3683519 Oct 21 2015 ODI 12c Getting Started Guide.pdf
-rwxrwxr-x. 1 oracle oracle 243 Oct 16 2015 ODI 12c Studio.desktop
-rwxrwxr-x. 1 oracle oracle 234 Dec 30 2014 ODI and OGG Demo UI.desktop
-rwxrwxr-x. 1 oracle oracle 333 Jun 20 2014 SQL Developer.desktop
-rwxrwxr-x. 1 oracle oracle 159 Dec 30 2014 Start OGG.desktop
-rw-r--r--. 1 oracle oracle 95 Feb 20 2014 startOGGSource.sh~
-rw-r--r--. 1 oracle oracle 92 Feb 20 2014 startOGGTarget.sh~
-rwxrwxr-x. 1 oracle oracle 158 Dec 30 2014 Stop OGG.desktop
-rw-r--r--. 1 oracle oracle 94 Feb 20 2014 stopOGG.sh~
-rw-r--r--. 1 oracle oracle 94 Feb 20 2014 stopOGGSource.sh~
-rw-r--r--. 1 oracle oracle 95 Feb 20 2014 stopOGGTarget.sh~
[oracle@ODIGettingStarted ~]$ ls -ld Desktop
drwxr-xr-x. 4 oracle oracle 4096 May 6 06:05 Desktop
[oracle@ODIGettingStarted ~]$ ls -R Desktop
Desktop:
Information about this machine OBE_Files ODI 12c Studio.desktop Sample Start OGG.desktop startOGGTarget.sh~ stopOGG.sh~ stopOGGTarget.sh~
Information about this machine~ ODI 12c Getting Started Guide.pdf ODI and OGG Demo UI.desktop SQL Developer.desktop startOGGSource.sh~ Stop OGG.desktop stopOGGSource.sh~
Desktop/OBE_Files:
Complex_Files ODI Exchange XML
Desktop/OBE_Files/Complex_Files:
Purchase_sample.txt
Desktop/OBE_Files/ODI Exchange:
bundle.xml
Desktop/OBE_Files/XML:
GEO_DIM.dtd GEO_DIM.xml
Desktop/Sample:
[oracle@ODIGettingStarted ~]$
[oracle@ODIGettingStarted ~]$ uname;cal;date
Linux
May 2019
Su Mo Tu We Th Fr Sa
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
Mon May 6 05:48:51 EDT 2019
[oracle@ODIGettingStarted ~]$
/home/oracle
[oracle@ODIGettingStarted ~]$ pwd
/home/oracle
[oracle@ODIGettingStarted ~]$ uname
Linux
[oracle@ODIGettingStarted ~]$ uname -i
x86_64
[oracle@ODIGettingStarted ~]$ uname -n
ODIGettingStarted
[oracle@ODIGettingStarted ~]$ uname -rs
Linux 2.6.39-400.17.1.el6uek.x86_64
[oracle@ODIGettingStarted ~]$ uname --help
Usage: uname [OPTION]...
Print certain system information. With no OPTION, same as -s.
-a, --all print all information, in the following order,
except omit -p and -i if unknown:
-s, --kernel-name print the kernel name
-n, --nodename print the network node hostname
-r, --kernel-release print the kernel release
-v, --kernel-version print the kernel version
-m, --machine print the machine hardware name
-p, --processor print the processor type or "unknown"
-i, --hardware-platform print the hardware platform or "unknown"
-o, --operating-system print the operating system
--help display this help and exit
--version output version information and exit
Report uname bugs to bug-coreutils@gnu.org
GNU coreutils home page: <http://www.gnu.org/software/coreutils/>
General help using GNU software: <http://www.gnu.org/gethelp/>
For complete documentation, run: info coreutils 'uname invocation'
[oracle@ODIGettingStarted ~]$ date
Mon May 6 05:33:10 EDT 2019
[oracle@ODIGettingStarted ~]$ cal
May 2019
Su Mo Tu We Th Fr Sa
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
[oracle@ODIGettingStarted ~]$ cal 02 2019
February 2019
Su Mo Tu We Th Fr Sa
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28
[oracle@ODIGettingStarted ~]$ clear
[oracle@ODIGettingStarted ~]$
[oracle@ODIGettingStarted ~]$ ls
Desktop Documents Downloads mountshared.sh Music openssl-1.0.1g openssl-1.0.1g.tar.gz oradiag_oracle oraInventory Pictures Public Templates Videos
[oracle@ODIGettingStarted ~]$ ls -a
. .bash_profile~ .dmrc .gconfd .gtk-bookmarks .local .odi oraInventory .sqldeveloper .vboxclient-display.pid .xsession-errors.old
.. .bashrc Documents .gnome2 .gvfs mountshared.sh .odi_jdk Pictures .ssh .vboxclient-draganddrop.pid
.abrt .cache Downloads .gnome2_private .ICEauthority .mozilla openssl-1.0.1g Public Templates .vboxclient-seamless.pid
.bash_history .config .emacs .gnote .icons Music openssl-1.0.1g.tar.gz .pulse .themes Videos
.bash_logout .dbus .esd_auth .gnupg .imsettings.log .nautilus .oracle_jre_usage .pulse-cookie .thumbnails .viminfo
.bash_profile Desktop .gconf .gstreamer-0.10 .lesshst .ocm oradiag_oracle .recently-used.xbel .vboxclient-clipboard.pid .xsession-errors
[oracle@ODIGettingStarted ~]$ ls -i
1455738 Desktop 1455739 Downloads 1455743 Music 1455875 openssl-1.0.1g.tar.gz 1456101 oraInventory 1455741 Public 1455745 Videos
1455742 Documents 1456088 mountshared.sh 1594624 openssl-1.0.1g 1455943 oradiag_oracle 1455744 Pictures 1455740 Templates
[oracle@ODIGettingStarted ~]$
[oracle@ODIGettingStarted ~]$ ls -l
total 4452
drwxr-xr-x. 3 oracle oracle 4096 Jun 17 2016 Desktop
drwxr-xr-x. 2 oracle oracle 4096 Aug 28 2013 Documents
drwxr-xr-x. 2 oracle oracle 4096 May 2 02:20 Downloads
-rw-rw-r--. 1 oracle oracle 56 Aug 30 2013 mountshared.sh
drwxr-xr-x. 2 oracle oracle 4096 Aug 28 2013 Music
drwxrwxr-x. 22 oracle oracle 4096 Apr 6 14:16 openssl-1.0.1g
-rw-rw-r--. 1 oracle oracle 4509047 Apr 7 2014 openssl-1.0.1g.tar.gz
drwxr-xr-x. 3 oracle oracle 4096 Aug 29 2013 oradiag_oracle
drwxrwx---. 4 oracle oracle 4096 Jun 20 2014 oraInventory
drwxr-xr-x. 2 oracle oracle 4096 Aug 28 2013 Pictures
drwxr-xr-x. 2 oracle oracle 4096 Aug 28 2013 Public
drwxr-xr-x. 2 oracle oracle 4096 Aug 28 2013 Templates
drwxr-xr-x. 2 oracle oracle 4096 Aug 28 2013 Videos
[oracle@ODIGettingStarted ~]$ ls Documents
[oracle@ODIGettingStarted ~]$ ls Desktop
Information about this machine OBE_Files ODI 12c Studio.desktop SQL Developer.desktop startOGGSource.sh~ Stop OGG.desktop stopOGGSource.sh~
Information about this machine~ ODI 12c Getting Started Guide.pdf ODI and OGG Demo UI.desktop Start OGG.desktop startOGGTarget.sh~ stopOGG.sh~ stopOGGTarget.sh~
[oracle@ODIGettingStarted ~]$ ls /home/oracle/Desktop
Information about this machine OBE_Files ODI 12c Studio.desktop SQL Developer.desktop startOGGSource.sh~ Stop OGG.desktop stopOGGSource.sh~
Information about this machine~ ODI 12c Getting Started Guide.pdf ODI and OGG Demo UI.desktop Start OGG.desktop startOGGTarget.sh~ stopOGG.sh~ stopOGGTarget.sh~
[oracle@ODIGettingStarted ~]$ ls -l Desktop
total 3652
-rw-rw-r--. 1 oracle oracle 1098 Jun 17 2016 Information about this machine
-rw-rw-r--. 1 oracle oracle 1098 Oct 21 2015 Information about this machine~
drwxrwxr-x. 5 oracle oracle 4096 Dec 16 2015 OBE_Files
-rwxr-xr-x. 1 oracle oracle 3683519 Oct 21 2015 ODI 12c Getting Started Guide.pdf
-rwxrwxr-x. 1 oracle oracle 243 Oct 16 2015 ODI 12c Studio.desktop
-rwxrwxr-x. 1 oracle oracle 234 Dec 30 2014 ODI and OGG Demo UI.desktop
-rwxrwxr-x. 1 oracle oracle 333 Jun 20 2014 SQL Developer.desktop
-rwxrwxr-x. 1 oracle oracle 159 Dec 30 2014 Start OGG.desktop
-rw-r--r--. 1 oracle oracle 95 Feb 20 2014 startOGGSource.sh~
-rw-r--r--. 1 oracle oracle 92 Feb 20 2014 startOGGTarget.sh~
-rwxrwxr-x. 1 oracle oracle 158 Dec 30 2014 Stop OGG.desktop
-rw-r--r--. 1 oracle oracle 94 Feb 20 2014 stopOGG.sh~
-rw-r--r--. 1 oracle oracle 94 Feb 20 2014 stopOGGSource.sh~
-rw-r--r--. 1 oracle oracle 95 Feb 20 2014 stopOGGTarget.sh~
[oracle@ODIGettingStarted ~]$ ls -ld Desktop
drwxr-xr-x. 4 oracle oracle 4096 May 6 06:05 Desktop
[oracle@ODIGettingStarted ~]$ ls -R Desktop
Desktop:
Information about this machine OBE_Files ODI 12c Studio.desktop Sample Start OGG.desktop startOGGTarget.sh~ stopOGG.sh~ stopOGGTarget.sh~
Information about this machine~ ODI 12c Getting Started Guide.pdf ODI and OGG Demo UI.desktop SQL Developer.desktop startOGGSource.sh~ Stop OGG.desktop stopOGGSource.sh~
Desktop/OBE_Files:
Complex_Files ODI Exchange XML
Desktop/OBE_Files/Complex_Files:
Purchase_sample.txt
Desktop/OBE_Files/ODI Exchange:
bundle.xml
Desktop/OBE_Files/XML:
GEO_DIM.dtd GEO_DIM.xml
Desktop/Sample:
[oracle@ODIGettingStarted ~]$
[oracle@ODIGettingStarted ~]$ uname;cal;date
Linux
May 2019
Su Mo Tu We Th Fr Sa
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
Mon May 6 05:48:51 EDT 2019
[oracle@ODIGettingStarted ~]$
Thursday, May 2, 2019
Extract Vowels from a given string
SQL> SELECT LISTAGG (vowels_temp, ',') WITHIN GROUP (ORDER BY pos) AS vowels
2 FROM (SELECT pos,
3 CASE
4 WHEN alpha IN ('a',
5 'e',
6 'i',
7 'o',
8 'u')
9 THEN
10 alpha
11 ELSE
12 NULL
13 END
14 AS vowels_temp
15 FROM ( SELECT LEVEL AS pos, SUBSTR ('rajasekhar', LEVEL, 1) AS alpha
16 FROM DUAL
17 CONNECT BY LEVEL <= LENGTH ('rajasekhar')))
18 WHERE vowels_temp IS NOT NULL;
VOWELS
--------------------------------------------------------------------------------
a,a,e,a
SQL>
2 FROM (SELECT pos,
3 CASE
4 WHEN alpha IN ('a',
5 'e',
6 'i',
7 'o',
8 'u')
9 THEN
10 alpha
11 ELSE
12 NULL
13 END
14 AS vowels_temp
15 FROM ( SELECT LEVEL AS pos, SUBSTR ('rajasekhar', LEVEL, 1) AS alpha
16 FROM DUAL
17 CONNECT BY LEVEL <= LENGTH ('rajasekhar')))
18 WHERE vowels_temp IS NOT NULL;
VOWELS
--------------------------------------------------------------------------------
a,a,e,a
SQL>
Installing Certificates in Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 using orapki
[oracle@ODIGettingStarted ~]$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 2 02:09:32 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT UTL_HTTP.request ('https://demo.proctoru.com/api/',NULL,NULL,NULL) FROM DUAL;
SELECT UTL_HTTP.request ('https://demo.proctoru.com/api/',NULL,NULL,NULL) FROM DUAL
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1720
ORA-29024: Certificate validation failure
ORA-06512: at line 1
SQL>
[oracle@ODIGettingStarted ~]$ pwd
/home/oracle
[oracle@ODIGettingStarted ~]$ cd /u01/app/oracle/product/11.2.0/orcl/owm
[oracle@ODIGettingStarted owm]$ mkdir proctoru_sbox
[oracle@ODIGettingStarted owm]$ cd proctoru_sbox
[oracle@ODIGettingStarted proctoru_sbox]$ ls -ltr
total 0
[oracle@ODIGettingStarted proctoru_sbox]$ pwd
/u01/app/oracle/product/11.2.0/orcl/owm/proctoru_sbox
[oracle@ODIGettingStarted proctoru_sbox]$ mv /home/oracle/Downloads/StarfieldRootCertificateAuthority-G2.crt /u01/app/oracle/product/11.2.0/orcl/owm/proctoru_sbox/
[oracle@ODIGettingStarted proctoru_sbox]$ mv /home/oracle/Downloads/StarfieldSecureCertificateAuthority-G2.crt /u01/app/oracle/product/11.2.0/orcl/owm/proctoru_sbox/
[oracle@ODIGettingStarted proctoru_sbox]$ ls -ltr
total 8
-rw-rw-r--. 1 oracle oracle 1822 May 2 02:10 StarfieldSecureCertificateAuthority-G2.crt
-rw-rw-r--. 1 oracle oracle 1422 May 2 02:10 StarfieldRootCertificateAuthority-G2.crt
[oracle@ODIGettingStarted proctoru_sbox]$ orapki wallet create -wallet . -pwd welcome123 -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
[oracle@ODIGettingStarted proctoru_sbox]$ orapki wallet display -wallet . -pwd c269065d96de5de8ca8264773eebe1
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Found Auto Login (AL) wallet.
Option -pwd is not recognized and ignored.
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
[oracle@ODIGettingStarted proctoru_sbox]$ orapki wallet add -wallet . -pwd welcome123 -trusted_cert -cert StarfieldRootCertificateAuthority-G2.crt
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
[oracle@ODIGettingStarted proctoru_sbox]$ orapki wallet add -wallet . -pwd welcome123 -trusted_cert -cert StarfieldSecureCertificateAuthority-G2.crt
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
[oracle@ODIGettingStarted proctoru_sbox]$ orapki wallet display -wallet . -pwd welcome123
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: CN=Starfield Root Certificate Authority - G2,O=Starfield Technologies\, Inc.,L=Scottsdale,ST=Arizona,C=US
Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Subject: CN=Starfield Secure Certificate Authority - G2,OU=http://certs.starfieldtech.com/repository/,O=Starfield Technologies\, Inc.,L=Scottsdale,ST=Arizona,C=US
Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
[oracle@ODIGettingStarted proctoru_sbox]$ ls -ltr
total 24
-rw-rw-r--. 1 oracle oracle 1822 May 2 02:10 StarfieldSecureCertificateAuthority-G2.crt
-rw-rw-r--. 1 oracle oracle 1422 May 2 02:10 StarfieldRootCertificateAuthority-G2.crt
-rw-------. 1 oracle oracle 5960 May 2 02:25 ewallet.p12
-rw-------. 1 oracle oracle 6037 May 2 02:25 cwallet.sso
[oracle@ODIGettingStarted proctoru_sbox]$ pwd
/u01/app/oracle/product/11.2.0/orcl/owm/proctoru_sbox
[oracle@ODIGettingStarted proctoru_sbox]$
[oracle@ODIGettingStarted proctoru_sbox]$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 2 02:32:58 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT UTL_HTTP.request ('https://demo.proctoru.com/api/',NULL,'file:/u01/app/oracle/product/11.2.0/orcl/owm/proctoru_sbox','') FROM DUAL;
UTL_HTTP.REQUEST('HTTPS://DEMO.PROCTORU.COM/API/',NULL,'FILE:/U01/APP/ORACLE/PRO
--------------------------------------------------------------------------------
<!DOCTYPE html>
<html>
<head>
<meta charset='utf-8'>
<meta content='IE=edge' http-equiv='X-UA-Compatible'>
<meta content='width=device-width, initial-scale=1' name='viewport'>
<link rel="shortcut icon" type="image/x-icon" href="https://assets-demo.proctoru
.com/assets/meta/favicon-32x32-facc4a9f835655772abef063786bd570d1b3b8fc494eea220
8abb4e85a98f180.png" sizes="32x32" />
<link rel="shortcut icon" type="image/x-icon" href="https://assets-demo.proctoru
.com/assets/meta/favicon-16x16-662172a770cbd3817731ddb46880a4e731672842f5ae4d3a4
UTL_HTTP.REQUEST('HTTPS://DEMO.PROCTORU.COM/API/',NULL,'FILE:/U01/APP/ORACLE/PRO
--------------------------------------------------------------------------------
af9b3d880b4278f.png" sizes="16x16" />
<link rel="apple-touch-icon" type="image/png" href="https://assets-demo.proctoru
.com/assets/meta/touch-icon-57x57-bb97d27e2c44dbd5b84c117542bf28eee1e6af2c153efc
2b8d806681533f0d67.png" sizes="57x57" />
<link rel="apple-touch-icon" type="image/png" href="https://assets-demo.proctoru
.com/assets/meta/touch-icon-76x76-70d3a48fb797b33bce31027cc70f4d47e4a0382293f2aa
caf192ec81d538b779.png" sizes="76x76" />
<link rel="apple-touch-icon" type="image/png" href="https://assets-demo.proctoru
.com/assets/meta/touch-icon-120x120-9ba7d4cca27e9f135b1467c98fad559b72ec06f68256
cc6dbfc5d43ab3de54b5.png" sizes="120x120" />
<link rel="apple-touch-icon" type="image/png" href="https://assets-demo.proctoru
UTL_HTTP.REQUEST('HTTPS://DEMO.PROCTORU.COM/API/',NULL,'FILE:/U01/APP/ORACLE/PRO
--------------------------------------------------------------------------------
.com/assets/meta/touch-icon-152x152-f643fffcb751d22dceaa9a675cf97fdf7e1ccac9b85c
3a87afe46fc019f16356.png" sizes="152x152" />
<link rel="apple-touch-icon" type="image/png" href="https://assets-demo.proctoru
.com/assets/meta/touch-icon-180x180-515c36dd490fd322b035b2a0ee04613ef22565652dc4
2213a31332d4f7f447c3.png" sizes="180x180" />
<meta name="csrf-param" content="authenticity_token" />
<meta name="csrf-token" content="q1SAdLLChG5/NIKJ0oETvVf144/wr8QVKV51DqRcRVZsIDv
9OYJQeQLWo18/dlz31sWMozI4tv42c7E/86rlVg==" />
<meta name="action-cable-url" content="/cable" />
<meta content='no-cache' name='turbolinks-cache-control'>
UTL_HTTP.REQUEST('HTTPS://DEMO.PROCTORU.COM/API/',NULL,'FILE:/U01/APP/ORACLE/PRO
--------------------------------------------------------------------------------
<title>Sorry, the page you were looking for doesn't exist (404) | ProctorU</
title>
<link rel="stylesheet" media="al
SQL>
"*****************************************************
[oracle@ODIGettingStarted ~]$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 2 02:09:32 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user scott identified by tiger;
User created.
SQL> grant connect,resource to scott;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ODIGettingStarted proctoru_sbox]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 2 02:36:52 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT UTL_HTTP.request ('https://demo.proctoru.com/api/',NULL,'file:/u01/app/oracle/product/11.2.0/orcl/owm/proctoru_sbox','') FROM DUAL;
SELECT UTL_HTTP.request ('https://demo.proctoru.com/api/',NULL,'file:/u01/app/oracle/product/11.2.0/orcl/owm/proctoru_sbox','') FROM DUAL
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1720
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1
SQL> exit
[oracle@ODIGettingStarted ~]$ pwd
/home/oracle
[oracle@ODIGettingStarted ~]$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 2 02:46:54 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> --use-passwords
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'proctoru.xml',
description => 'to access proctoru_sbox wallet using scott user',
principal => 'SCOTT',
is_grant => TRUE,
privilege => 'use-client-certificates',
start_date => NULL,
end_date => NULL);
COMMIT;
END;
/SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_wallet_acl (
acl => 'proctoru.xml',
wallet_path => 'file:/u01/app/oracle/product/11.2.0/orcl/owm/proctoru_sbox');
COMMIT;
END;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ODIGettingStarted ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 2 02:48:07 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 2 02:09:32 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT UTL_HTTP.request ('https://demo.proctoru.com/api/',NULL,NULL,NULL) FROM DUAL;
SELECT UTL_HTTP.request ('https://demo.proctoru.com/api/',NULL,NULL,NULL) FROM DUAL
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1720
ORA-29024: Certificate validation failure
ORA-06512: at line 1
SQL>
[oracle@ODIGettingStarted ~]$ pwd
/home/oracle
[oracle@ODIGettingStarted ~]$ cd /u01/app/oracle/product/11.2.0/orcl/owm
[oracle@ODIGettingStarted owm]$ mkdir proctoru_sbox
[oracle@ODIGettingStarted owm]$ cd proctoru_sbox
[oracle@ODIGettingStarted proctoru_sbox]$ ls -ltr
total 0
[oracle@ODIGettingStarted proctoru_sbox]$ pwd
/u01/app/oracle/product/11.2.0/orcl/owm/proctoru_sbox
[oracle@ODIGettingStarted proctoru_sbox]$ mv /home/oracle/Downloads/StarfieldRootCertificateAuthority-G2.crt /u01/app/oracle/product/11.2.0/orcl/owm/proctoru_sbox/
[oracle@ODIGettingStarted proctoru_sbox]$ mv /home/oracle/Downloads/StarfieldSecureCertificateAuthority-G2.crt /u01/app/oracle/product/11.2.0/orcl/owm/proctoru_sbox/
[oracle@ODIGettingStarted proctoru_sbox]$ ls -ltr
total 8
-rw-rw-r--. 1 oracle oracle 1822 May 2 02:10 StarfieldSecureCertificateAuthority-G2.crt
-rw-rw-r--. 1 oracle oracle 1422 May 2 02:10 StarfieldRootCertificateAuthority-G2.crt
[oracle@ODIGettingStarted proctoru_sbox]$ orapki wallet create -wallet . -pwd welcome123 -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
[oracle@ODIGettingStarted proctoru_sbox]$ orapki wallet display -wallet . -pwd c269065d96de5de8ca8264773eebe1
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Found Auto Login (AL) wallet.
Option -pwd is not recognized and ignored.
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
[oracle@ODIGettingStarted proctoru_sbox]$ orapki wallet add -wallet . -pwd welcome123 -trusted_cert -cert StarfieldRootCertificateAuthority-G2.crt
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
[oracle@ODIGettingStarted proctoru_sbox]$ orapki wallet add -wallet . -pwd welcome123 -trusted_cert -cert StarfieldSecureCertificateAuthority-G2.crt
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
[oracle@ODIGettingStarted proctoru_sbox]$ orapki wallet display -wallet . -pwd welcome123
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: CN=Starfield Root Certificate Authority - G2,O=Starfield Technologies\, Inc.,L=Scottsdale,ST=Arizona,C=US
Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Subject: CN=Starfield Secure Certificate Authority - G2,OU=http://certs.starfieldtech.com/repository/,O=Starfield Technologies\, Inc.,L=Scottsdale,ST=Arizona,C=US
Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
[oracle@ODIGettingStarted proctoru_sbox]$ ls -ltr
total 24
-rw-rw-r--. 1 oracle oracle 1822 May 2 02:10 StarfieldSecureCertificateAuthority-G2.crt
-rw-rw-r--. 1 oracle oracle 1422 May 2 02:10 StarfieldRootCertificateAuthority-G2.crt
-rw-------. 1 oracle oracle 5960 May 2 02:25 ewallet.p12
-rw-------. 1 oracle oracle 6037 May 2 02:25 cwallet.sso
[oracle@ODIGettingStarted proctoru_sbox]$ pwd
/u01/app/oracle/product/11.2.0/orcl/owm/proctoru_sbox
[oracle@ODIGettingStarted proctoru_sbox]$
[oracle@ODIGettingStarted proctoru_sbox]$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 2 02:32:58 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT UTL_HTTP.request ('https://demo.proctoru.com/api/',NULL,'file:/u01/app/oracle/product/11.2.0/orcl/owm/proctoru_sbox','') FROM DUAL;
UTL_HTTP.REQUEST('HTTPS://DEMO.PROCTORU.COM/API/',NULL,'FILE:/U01/APP/ORACLE/PRO
--------------------------------------------------------------------------------
<!DOCTYPE html>
<html>
<head>
<meta charset='utf-8'>
<meta content='IE=edge' http-equiv='X-UA-Compatible'>
<meta content='width=device-width, initial-scale=1' name='viewport'>
<link rel="shortcut icon" type="image/x-icon" href="https://assets-demo.proctoru
.com/assets/meta/favicon-32x32-facc4a9f835655772abef063786bd570d1b3b8fc494eea220
8abb4e85a98f180.png" sizes="32x32" />
<link rel="shortcut icon" type="image/x-icon" href="https://assets-demo.proctoru
.com/assets/meta/favicon-16x16-662172a770cbd3817731ddb46880a4e731672842f5ae4d3a4
UTL_HTTP.REQUEST('HTTPS://DEMO.PROCTORU.COM/API/',NULL,'FILE:/U01/APP/ORACLE/PRO
--------------------------------------------------------------------------------
af9b3d880b4278f.png" sizes="16x16" />
<link rel="apple-touch-icon" type="image/png" href="https://assets-demo.proctoru
.com/assets/meta/touch-icon-57x57-bb97d27e2c44dbd5b84c117542bf28eee1e6af2c153efc
2b8d806681533f0d67.png" sizes="57x57" />
<link rel="apple-touch-icon" type="image/png" href="https://assets-demo.proctoru
.com/assets/meta/touch-icon-76x76-70d3a48fb797b33bce31027cc70f4d47e4a0382293f2aa
caf192ec81d538b779.png" sizes="76x76" />
<link rel="apple-touch-icon" type="image/png" href="https://assets-demo.proctoru
.com/assets/meta/touch-icon-120x120-9ba7d4cca27e9f135b1467c98fad559b72ec06f68256
cc6dbfc5d43ab3de54b5.png" sizes="120x120" />
<link rel="apple-touch-icon" type="image/png" href="https://assets-demo.proctoru
UTL_HTTP.REQUEST('HTTPS://DEMO.PROCTORU.COM/API/',NULL,'FILE:/U01/APP/ORACLE/PRO
--------------------------------------------------------------------------------
.com/assets/meta/touch-icon-152x152-f643fffcb751d22dceaa9a675cf97fdf7e1ccac9b85c
3a87afe46fc019f16356.png" sizes="152x152" />
<link rel="apple-touch-icon" type="image/png" href="https://assets-demo.proctoru
.com/assets/meta/touch-icon-180x180-515c36dd490fd322b035b2a0ee04613ef22565652dc4
2213a31332d4f7f447c3.png" sizes="180x180" />
<meta name="csrf-param" content="authenticity_token" />
<meta name="csrf-token" content="q1SAdLLChG5/NIKJ0oETvVf144/wr8QVKV51DqRcRVZsIDv
9OYJQeQLWo18/dlz31sWMozI4tv42c7E/86rlVg==" />
<meta name="action-cable-url" content="/cable" />
<meta content='no-cache' name='turbolinks-cache-control'>
UTL_HTTP.REQUEST('HTTPS://DEMO.PROCTORU.COM/API/',NULL,'FILE:/U01/APP/ORACLE/PRO
--------------------------------------------------------------------------------
<title>Sorry, the page you were looking for doesn't exist (404) | ProctorU</
title>
<link rel="stylesheet" media="al
SQL>
"*****************************************************
[oracle@ODIGettingStarted ~]$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 2 02:09:32 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user scott identified by tiger;
User created.
SQL> grant connect,resource to scott;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ODIGettingStarted proctoru_sbox]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 2 02:36:52 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT UTL_HTTP.request ('https://demo.proctoru.com/api/',NULL,'file:/u01/app/oracle/product/11.2.0/orcl/owm/proctoru_sbox','') FROM DUAL;
SELECT UTL_HTTP.request ('https://demo.proctoru.com/api/',NULL,'file:/u01/app/oracle/product/11.2.0/orcl/owm/proctoru_sbox','') FROM DUAL
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1720
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1
SQL> exit
[oracle@ODIGettingStarted ~]$ pwd
/home/oracle
[oracle@ODIGettingStarted ~]$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 2 02:46:54 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> --use-passwords
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'proctoru.xml',
description => 'to access proctoru_sbox wallet using scott user',
principal => 'SCOTT',
is_grant => TRUE,
privilege => 'use-client-certificates',
start_date => NULL,
end_date => NULL);
COMMIT;
END;
/SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_wallet_acl (
acl => 'proctoru.xml',
wallet_path => 'file:/u01/app/oracle/product/11.2.0/orcl/owm/proctoru_sbox');
COMMIT;
END;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ODIGettingStarted ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 2 02:48:07 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Subscribe to:
Posts (Atom)