SQLcl :sql Results with the SET SQLFORMAT

 

[oracle@srv1 ~]$ sql / as sysdba


[oracle@srv1 ~]$ sql / as sysdba


SQLcl: Release 21.4 Production on Sun Oct 20 21:00:22 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0



SQLcl :sql  Results with the SET SQLFORMAT
 

Oracle SQLcl :sql  Results with the SET SQLFORMAT

/u01/app/oracle/product/19.0.0/db_1/sqlcl/bin
sql /nolog


SQLcl: Release 21.4 Production on Sat Sep 07 08:14:05 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected.
SQL> show version
Oracle SQLDeveloper Command-Line (SQLcl) version: 21.4.12.0 build: 21.4.12.44.1717
SQL>

=====


sqlcl 
 
sqlcl available from [Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production;


# which java
/bin/java

 
to install JAVA

yum install -y java-1.8.0-openjdk


Trying other mirror.
Resolving Dependencies
--> Running transaction check
---> Package java-1.8.0-openjdk.x86_64 1:1.8.0.332.b09-1.el7_9 will be installed
--> Processing Dependency: java-1.8.0-openjdk-headless(x86-64) = 1:1.8.0.332.b09-1.el7_9 for package: 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: libjava.so(SUNWprivate_1.1)(64bit) for package: 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: libjvm.so(SUNWprivate_1.1)(64bit) for package: 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: xorg-x11-fonts-Type1 for package: 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: libgif.so.4()(64bit) for package: 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: libjava.so()(64bit) for package: 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: libjvm.so()(64bit) for package: 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64
--> Running transaction check
---> Package giflib.x86_64 0:4.1.6-9.el7 will be installed
---> Package java-1.8.0-openjdk-headless.x86_64 1:1.8.0.332.b09-1.el7_9 will be installed
--> Processing Dependency: copy-jdk-configs >= 3.3 for package: 1:java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: tzdata-java >= 2021e for package: 1:java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: jpackage-utils for package: 1:java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: lksctp-tools(x86-64) for package: 1:java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: pcsc-lite-libs(x86-64) for package: 1:java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64
---> Package xorg-x11-fonts-Type1.noarch 0:7.5-9.el7 will be installed
--> Processing Dependency: ttmkfdir for package: xorg-x11-fonts-Type1-7.5-9.el7.noarch
--> Processing Dependency: ttmkfdir for package: xorg-x11-fonts-Type1-7.5-9.el7.noarch
--> Running transaction check
---> Package copy-jdk-configs.noarch 0:3.3-10.el7_5 will be installed
---> Package javapackages-tools.noarch 0:3.4.1-11.el7 will be installed
--> Processing Dependency: python-javapackages = 3.4.1-11.el7 for package: javapackages-tools-3.4.1-11.el7.noarch
--> Processing Dependency: libxslt for package: javapackages-tools-3.4.1-11.el7.noarch
---> Package lksctp-tools.x86_64 0:1.0.17-2.el7 will be installed
---> Package pcsc-lite-libs.x86_64 0:1.8.8-8.el7 will be installed
---> Package ttmkfdir.x86_64 0:3.0.9-42.el7 will be installed
---> Package tzdata-java.noarch 0:2022a-1.el7 will be installed
--> Running transaction check
---> Package libxslt.x86_64 0:1.1.28-6.0.1.el7 will be installed
---> Package python-javapackages.noarch 0:3.4.1-11.el7 will be installed
--> Processing Dependency: python-lxml for package: python-javapackages-3.4.1-11.el7.noarch
--> Running transaction check
---> Package python-lxml.x86_64 0:3.2.1-4.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================================================================================================================================================
 Package                                                    Arch                                  Version                                                Repository                                        Size
================================================================================================================================================================================================================
Installing:
 java-1.8.0-openjdk                                         x86_64                                1:1.8.0.332.b09-1.el7_9                                ol7_x86_64_latest                                314 k
Installing for dependencies:
 copy-jdk-configs                                           noarch                                3.3-10.el7_5                                           ol7_x86_64_latest                                 21 k
 giflib                                                     x86_64                                4.1.6-9.el7                                            ol7_x86_64_latest                                 39 k
 java-1.8.0-openjdk-headless                                x86_64                                1:1.8.0.332.b09-1.el7_9                                ol7_x86_64_latest                                 33 M
 javapackages-tools                                         noarch                                3.4.1-11.el7                                           ol7_x86_64_latest                                 72 k
 libxslt                                                    x86_64                                1.1.28-6.0.1.el7                                       ol7_x86_64_latest                                241 k
 lksctp-tools                                               x86_64                                1.0.17-2.el7                                           ol7_x86_64_latest                                 87 k
 pcsc-lite-libs                                             x86_64                                1.8.8-8.el7                                            ol7_x86_64_latest                                 34 k
 python-javapackages                                        noarch                                3.4.1-11.el7                                           ol7_x86_64_latest                                 31 k
 python-lxml                                                x86_64                                3.2.1-4.el7                                            ol7_x86_64_latest                                758 k
 ttmkfdir                                                   x86_64                                3.0.9-42.el7                                           ol7_x86_64_latest                                 47 k
 tzdata-java                                                noarch                                2022a-1.el7                                            ol7_x86_64_latest                                190 k
 xorg-x11-fonts-Type1                                       noarch                                7.5-9.el7                                              ol7_x86_64_latest                                521 k

Transaction Summary
================================================================================================================================================================================================================
Install  1 Package (+12 Dependent packages)

Total download size: 35 M
Installed size: 116 M
Downloading packages:
(1/13): copy-jdk-configs-3.3-10.el7_5.noarch.rpm                                                                                                                                         |  21 kB  00:00:00
(2/13): giflib-4.1.6-9.el7.x86_64.rpm                                                                                                                                                    |  39 kB  00:00:00
(3/13): java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64.rpm                                                                                                                              | 314 kB  00:00:00
(4/13): java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64.rpm                                                                                                                     |  33 MB  00:00:00
(5/13): javapackages-tools-3.4.1-11.el7.noarch.rpm                                                                                                                                       |  72 kB  00:00:00
(6/13): libxslt-1.1.28-6.0.1.el7.x86_64.rpm                                                                                                                                              | 241 kB  00:00:00
(7/13): lksctp-tools-1.0.17-2.el7.x86_64.rpm                                                                                                                                             |  87 kB  00:00:00
(8/13): pcsc-lite-libs-1.8.8-8.el7.x86_64.rpm                                                                                                                                            |  34 kB  00:00:00
(9/13): python-javapackages-3.4.1-11.el7.noarch.rpm                                                                                                                                      |  31 kB  00:00:00
(10/13): python-lxml-3.2.1-4.el7.x86_64.rpm                                                                                                                                              | 758 kB  00:00:00
(11/13): ttmkfdir-3.0.9-42.el7.x86_64.rpm                                                                                                                                                |  47 kB  00:00:00
(12/13): tzdata-java-2022a-1.el7.noarch.rpm                                                                                                                                              | 190 kB  00:00:00
(13/13): xorg-x11-fonts-Type1-7.5-9.el7.noarch.rpm                                                                                                                                       | 521 kB  00:00:00
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                                            29 MB/s |  35 MB  00:00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : libxslt-1.1.28-6.0.1.el7.x86_64                                                                                                                                                             1/13
  Installing : python-lxml-3.2.1-4.el7.x86_64                                                                                                                                                              2/13
  Installing : python-javapackages-3.4.1-11.el7.noarch                                                                                                                                                     3/13
  Installing : javapackages-tools-3.4.1-11.el7.noarch                                                                                                                                                      4/13
  Installing : ttmkfdir-3.0.9-42.el7.x86_64                                                                                                                                                                5/13
  Installing : xorg-x11-fonts-Type1-7.5-9.el7.noarch                                                                                                                                                       6/13
  Installing : lksctp-tools-1.0.17-2.el7.x86_64                                                                                                                                                            7/13
  Installing : pcsc-lite-libs-1.8.8-8.el7.x86_64                                                                                                                                                           8/13
  Installing : copy-jdk-configs-3.3-10.el7_5.noarch                                                                                                                                                        9/13
  Installing : giflib-4.1.6-9.el7.x86_64                                                                                                                                                                  10/13
  Installing : tzdata-java-2022a-1.el7.noarch                                                                                                                                                             11/13
  Installing : 1:java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64                                                                                                                                 12/13
  Installing : 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64                                                                                                                                          13/13
  Verifying  : tzdata-java-2022a-1.el7.noarch                                                                                                                                                              1/13
  Verifying  : giflib-4.1.6-9.el7.x86_64                                                                                                                                                                   2/13
  Verifying  : xorg-x11-fonts-Type1-7.5-9.el7.noarch                                                                                                                                                       3/13
  Verifying  : copy-jdk-configs-3.3-10.el7_5.noarch                                                                                                                                                        4/13
  Verifying  : python-javapackages-3.4.1-11.el7.noarch                                                                                                                                                     5/13
  Verifying  : python-lxml-3.2.1-4.el7.x86_64                                                                                                                                                              6/13
  Verifying  : 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64                                                                                                                                           7/13
  Verifying  : libxslt-1.1.28-6.0.1.el7.x86_64                                                                                                                                                             8/13
  Verifying  : pcsc-lite-libs-1.8.8-8.el7.x86_64                                                                                                                                                           9/13
  Verifying  : 1:java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64                                                                                                                                 10/13
  Verifying  : javapackages-tools-3.4.1-11.el7.noarch                                                                                                                                                     11/13
  Verifying  : lksctp-tools-1.0.17-2.el7.x86_64                                                                                                                                                           12/13
  Verifying  : ttmkfdir-3.0.9-42.el7.x86_64                                                                                                                                                               13/13

Installed:
  java-1.8.0-openjdk.x86_64 1:1.8.0.332.b09-1.el7_9

Dependency Installed:
  copy-jdk-configs.noarch 0:3.3-10.el7_5         giflib.x86_64 0:4.1.6-9.el7                java-1.8.0-openjdk-headless.x86_64 1:1.8.0.332.b09-1.el7_9         javapackages-tools.noarch 0:3.4.1-11.el7
  libxslt.x86_64 0:1.1.28-6.0.1.el7              lksctp-tools.x86_64 0:1.0.17-2.el7         pcsc-lite-libs.x86_64 0:1.8.8-8.el7                                python-javapackages.noarch 0:3.4.1-11.el7
  python-lxml.x86_64 0:3.2.1-4.el7               ttmkfdir.x86_64 0:3.0.9-42.el7             tzdata-java.noarch 0:2022a-1.el7                                   xorg-x11-fonts-Type1.noarch 0:7.5-9.el7

Complete!


JAVA installed !!!!




]# which java
/bin/java


if required !!  
 export JAVA_HOME=/bin/java


=======================================================================================================================================================================



 su - oracle
Last login: Wed Jul 13 05:00:56 EDT 2022 on pts/0



 which sql
/u01/app/oracle/product/12.2.0/dbhome_1/bin/sql


[oracle@irac02 ~]$ sql /nolog

SQLcl: Release 12.2.0.1.0 RC on Wed Jul 13 05:33:33 2022    <<<<<<< SQLcl

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


idle sqlplus>




in tnsnames.ora

pdb9=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ibrac02.xxxxxxxx)(PORT=1523))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=pdb9)))

on unix prompt 
sql /nolog

SQLcl: Release 12.2.0.1.0 RC on Wed Jul 13 05:18:45 2022

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


idle sqlplus> conn test/vihaan123@pdb9
Connected.



or 

Via EZ Connect

 sql /nolog

SQLcl: Release 12.2.0.1.0 RC on Wed Jul 13 05:23:45 2022

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


idle sqlplus>
idle sqlplus> CONN test/vihaan123@//irac02.XXXXX:1523/pdb9
Connected.



primary:test@vihcdbd8-vihcdbd8 sqlplus> SET SQLFORMAT insert;
primary:test@vihcdbd8-vihcdbd8 sqlplus> SELECT * FROM emp;


REM INSERTING into EMP
SET DEFINE OFF;
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_timestamp('17-11-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),5000,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_timestamp('01-05-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),2850,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_timestamp('09-06-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),2450,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_timestamp('02-04-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_timestamp('03-12-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_timestamp('20-02-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),1600,300,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_timestamp('22-02-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),1250,500,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_timestamp('28-09-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),1250,1400,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_timestamp('08-09-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),1500,0,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_timestamp('03-12-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),950,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_timestamp('17-12-12 00:00','DD-MON-RR HH.MI.SSXFF AM'),800,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_timestamp('23-01-13 00:00','DD-MON-RR HH.MI.SSXFF AM'),1300,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_timestamp('19-04-14 00:00','DD-MON-RR HH.MI.SSXFF AM'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7655,'ADAMS','CLERK',7788,to_timestamp('13-07-14 00:00','DD-MON-RR HH.MI.SSXFF AM'),1100,null,20);

14 rows selected.



====


Examples 



SELECT /*ansiconsole*/ * FROM emp where rownum <3 csv="" delimited="" emp="" fixed="" from="" html="" insert="" json-formatted="" json="" loader="" primary:test="" rownum="" select="" sqlplus="" text="" vihcdbd8-vihcdbd8="" where="" xml=""> <3;
<3 csv="" delimited="" emp="" fixed="" from="" html="" insert="" json-formatted="" json="" loader="" primary:test="" rownum="" select="" sqlplus="" text="" vihcdbd8-vihcdbd8="" where="" xml="">SELECT /*csv*/ * FROM emp where rownum <3 00:00="" 7698="" 7839="" primary:test="" sqlplus="" vihcdbd8-vihcdbd8="">  <3;
<3 csv="" delimited="" emp="" fixed="" from="" html="" insert="" json-formatted="" json="" loader="" primary:test="" rownum="" select="" sqlplus="" text="" vihcdbd8-vihcdbd8="" where="" xml=""><3 00:00="" 7698="" 7839="" primary:test="" sqlplus="" vihcdbd8-vihcdbd8="">SELECT /*loader*/ * FROM emp where rownum <3 00:00:00="" 7698="" 7839="" no="" primary:test="" rows="" selected="" sqlplus="" vihcdbd8-vihcdbd8="">  <3;
<3 csv="" delimited="" emp="" fixed="" from="" html="" insert="" json-formatted="" json="" loader="" primary:test="" rownum="" select="" sqlplus="" text="" vihcdbd8-vihcdbd8="" where="" xml=""><3 00:00="" 7698="" 7839="" primary:test="" sqlplus="" vihcdbd8-vihcdbd8=""><3 00:00:00="" 7698="" 7839="" no="" primary:test="" rows="" selected="" sqlplus="" vihcdbd8-vihcdbd8="">SELECT /*delimited*/ * FROM emp where rownum <3 00:00="" 7698="" 7839="" primary:test="" sqlplus="" vihcdbd8-vihcdbd8="">  <3;
<3 csv="" delimited="" emp="" fixed="" from="" html="" insert="" json-formatted="" json="" loader="" primary:test="" rownum="" select="" sqlplus="" text="" vihcdbd8-vihcdbd8="" where="" xml=""><3 00:00="" 7698="" 7839="" primary:test="" sqlplus="" vihcdbd8-vihcdbd8=""><3 00:00:00="" 7698="" 7839="" no="" primary:test="" rows="" selected="" sqlplus="" vihcdbd8-vihcdbd8=""><3 00:00="" 7698="" 7839="" primary:test="" sqlplus="" vihcdbd8-vihcdbd8="">SELECT /*insert*/ * FROM emp where rownum <3 00:00="" am="" define="" emp="" hh.mi.ssxff="" insert="" inserting="" into="" null="" off="" primary:test="" rem="" set="" sqlplus="" to_timestamp="" values="" vihcdbd8-vihcdbd8="">  <3;
<3 csv="" delimited="" emp="" fixed="" from="" html="" insert="" json-formatted="" json="" loader="" primary:test="" rownum="" select="" sqlplus="" text="" vihcdbd8-vihcdbd8="" where="" xml=""><3 00:00="" 7698="" 7839="" primary:test="" sqlplus="" vihcdbd8-vihcdbd8=""><3 00:00:00="" 7698="" 7839="" no="" primary:test="" rows="" selected="" sqlplus="" vihcdbd8-vihcdbd8=""><3 00:00="" 7698="" 7839="" primary:test="" sqlplus="" vihcdbd8-vihcdbd8=""><3 00:00="" am="" define="" emp="" hh.mi.ssxff="" insert="" inserting="" into="" null="" off="" primary:test="" rem="" set="" sqlplus="" to_timestamp="" values="" vihcdbd8-vihcdbd8="">SELECT /*json*/ * FROM emp where rownum <3 00:00="" columns="" deptno="" empno="" ename="" hiredate="" items="" job="" mgr="" name="" primary:test="" results="" sal="" sqlplus="" type="" vihcdbd8-vihcdbd8="">  <3;
<3 csv="" delimited="" emp="" fixed="" from="" html="" insert="" json-formatted="" json="" loader="" primary:test="" rownum="" select="" sqlplus="" text="" vihcdbd8-vihcdbd8="" where="" xml="">SELECT /*json-formatted*/ * FROM emp where rownum  <3; <3 --------------="" ----------="" ---------="" 00:00="" 01-05-11="" 10="" 17-11-11="" 2850="" 30="" 5000="" 7698="" 7839="" blake="" comm="" deptno="" empno="" ename="" hiredate="" job="" king="" manager="" mgr="" president="" primary:test="" sal="" sqlplus="" vihcdbd8-vihcdbd8=""> 
<3 csv="" delimited="" emp="" fixed="" from="" html="" insert="" json-formatted="" json="" loader="" primary:test="" rownum="" select="" sqlplus="" text="" vihcdbd8-vihcdbd8="" where="" xml="">SELECT /*xml*/ * FROM emp where rownum  <3;
<3 encoding="UTF8" version="1.0" xml="">
<3 csv="" delimited="" emp="" fixed="" from="" html="" insert="" json-formatted="" json="" loader="" primary:test="" rownum="" select="" sqlplus="" text="" vihcdbd8-vihcdbd8="" where="" xml=""><3 00:00="" 7698="" 7839="" primary:test="" sqlplus="" vihcdbd8-vihcdbd8=""><3 00:00:00="" 7698="" 7839="" no="" primary:test="" rows="" selected="" sqlplus="" vihcdbd8-vihcdbd8=""><3 00:00="" 7698="" 7839="" primary:test="" sqlplus="" vihcdbd8-vihcdbd8=""><3 00:00="" am="" define="" emp="" hh.mi.ssxff="" insert="" inserting="" into="" null="" off="" primary:test="" rem="" set="" sqlplus="" to_timestamp="" values="" vihcdbd8-vihcdbd8=""><3 00:00="" columns="" deptno="" empno="" ename="" hiredate="" items="" job="" mgr="" name="" primary:test="" results="" sal="" sqlplus="" type="" vihcdbd8-vihcdbd8=""><3 --------------="" ----------="" ---------="" 00:00="" 01-05-11="" 10="" 17-11-11="" 2850="" 30="" 5000="" 7698="" 7839="" blake="" comm="" deptno="" empno="" ename="" hiredate="" job="" king="" manager="" mgr="" president="" primary:test="" sal="" sqlplus="" vihcdbd8-vihcdbd8=""><3 encoding="UTF8" version="1.0" xml="">SELECT /*html*/ * FROM emp where rownum  <3;
<3 csv="" delimited="" emp="" fixed="" from="" html="" insert="" json-formatted="" json="" loader="" primary:test="" rownum="" select="" sqlplus="" text="" vihcdbd8-vihcdbd8="" where="" xml=""><3 00:00="" 7698="" 7839="" primary:test="" sqlplus="" vihcdbd8-vihcdbd8=""><3 00:00:00="" 7698="" 7839="" no="" primary:test="" rows="" selected="" sqlplus="" vihcdbd8-vihcdbd8=""><3 00:00="" 7698="" 7839="" primary:test="" sqlplus="" vihcdbd8-vihcdbd8=""><3 00:00="" am="" define="" emp="" hh.mi.ssxff="" insert="" inserting="" into="" null="" off="" primary:test="" rem="" set="" sqlplus="" to_timestamp="" values="" vihcdbd8-vihcdbd8=""><3 00:00="" columns="" deptno="" empno="" ename="" hiredate="" items="" job="" mgr="" name="" primary:test="" results="" sal="" sqlplus="" type="" vihcdbd8-vihcdbd8=""><3 --------------="" ----------="" ---------="" 00:00="" 01-05-11="" 10="" 17-11-11="" 2850="" 30="" 5000="" 7698="" 7839="" blake="" comm="" deptno="" empno="" ename="" hiredate="" job="" king="" manager="" mgr="" president="" primary:test="" sal="" sqlplus="" vihcdbd8-vihcdbd8=""><3 encoding="UTF8" version="1.0" xml=""><3 html="">SELECT /*text*/ * FROM emp where rownum  <3;


======
<3 csv="" delimited="" emp="" fixed="" from="" html="" insert="" json-formatted="" json="" loader="" primary:test="" rownum="" select="" sqlplus="" text="" vihcdbd8-vihcdbd8="" where="" xml="">
set sqlformat ansiconsole

set highlighting on
set highlighting keyword foreground blue
set highlighting identifier foreground magenta
set highlighting string foreground green
set highlighting number foreground cyan
set highlighting comment foreground yellow

set highlighting identifier foreground magenta
<3 csv="" delimited="" emp="" fixed="" from="" html="" insert="" json-formatted="" json="" loader="" primary:test="" rownum="" select="" sqlplus="" text="" vihcdbd8-vihcdbd8="" where="" xml="">
<3 csv="" delimited="" emp="" fixed="" from="" html="" insert="" json-formatted="" json="" loader="" primary:test="" rownum="" select="" sqlplus="" text="" vihcdbd8-vihcdbd8="" where="" xml="">history Show list of executed statements, 1 per line
history full Show list of executed statements, as entered (multilin
history usage Same as "history" but also displays number of executions
history time Same as "history" but also displays execution time
history clear Erases the history buffer
history # Loads statement with number # into SQLcl buffer
show history Shows the current history settings

crsctl status

more script 
https://github.com/freddenis/oracle-scripts/blob/master/rac-status.sh 






./crsstatus.sh

#!/bin/sh

CRS_HOME=/u01/app/19.0.0/grid
#$CRS_HOME/bin/crsctl stat res -t -init\

$CRS_HOME/bin/crsctl stat res -t \
  | awk -v t="$t" '$0 !~ "Cluster Resources" && $0 !~ "Local Resources" \
      { \
        if ($0 ~ "Name") { \
          printf "%-45s %-10s %-15s %-14s %s %s\n", $1, $2, $3, $4, $5, $6
          while (++k <= 120) { \
            printf("-") \
          } \
          printf ("\n") \
        } \
        if (NF == 1) {\
          rs=$0 \
        } \
        else if (NR != 2) { \
          if ($1 ~ "^[0-9]") { \
            if ($0 ~ "Shutdown" || (rs ~ "svc$" && $3 == "OFFLINE" )) { \
              l = "$CRS_HOME/olsnodes -n"
              cmd[NR] = l
              while (l | getline line) {
                split(line,r," ")
                if (r[2] == $1) {
                  NODE = r[1]
                }
              }
              printf "%-45s %-10s %-15s %-14s %s %s\n", rs, $2, $3, NODE, $4, $5, "", $6, $7 \
            } \
            else { \
              printf "%-45s %-10s %-15s %-14s %s %s\n", rs, $2, $3, $4, $5, $6, $7 \
            } \
          } \
          else { \
            printf "%-45s %-10s %-15s %-14s %s %s\n", rs, $1, $2, $3, $4, $5, $6, $7 \
          } \
        } \
      }'





====

Name                                          Target     State           Server         State details
------------------------------------------------------------------------------------------------------------------------
ora.asm                                       ONLINE     ONLINE          ikrac01        Started,STABLE
ora.cluster_interconnect.haip                 ONLINE     ONLINE          ikrac01        STABLE
ora.crf                                       ONLINE     ONLINE          ikrac01        STABLE
ora.crsd                                      ONLINE     OFFLINE         STABLE
ora.cssd                                      ONLINE     ONLINE          ikrac01        STABLE
ora.cssdmonitor                               ONLINE     ONLINE          ikrac01        STABLE
ora.ctssd                                     ONLINE     ONLINE          ikrac01        OBSERVER,STABLE
ora.diskmon                                   OFFLINE    OFFLINE         STABLE
ora.drivers.acfs                              ONLINE     ONLINE          ikrac01        STABLE
ora.evmd                                      ONLINE     ONLINE          ikrac01        STABLE
ora.gipcd                                     ONLINE     ONLINE          ikrac01        STABLE
ora.gpnpd                                     ONLINE     ONLINE          ikrac01        STABLE
ora.mdnsd                                     ONLINE     ONLINE          ikrac01        STABLE
ora.storage                                   ONLINE     ONLINE          ikrac01        STABLE

Find Row Count Of All Partitions for oracle Table

--- create test table 


 https://asktom.oracle.com/pls/apex/asktom.search?tag=daywise-partition-automatically

create table sales
  ( ts   timestamp,
    id   int,
    amt  number,
    product int,
    customer int,
    item_cnt int,
    terminal int,
    operator int,
    credit_card int
      )
partition by range ( ts )
interval ( numtodsinterval(1,'HOUR') )
(
  partition p1 values less than ( timestamp '2022-04-01 00:00:00' )
);



insert /*+ APPEND */ into sales (ts,id,amt,product,customer)
   select date '2022-04-01' + rownum / 240, rownum, dbms_random.value(1,100), dbms_random.value(1,100),dbms_random.value(1,100)
   from dual
   connect by level ;

 
 
 set line 300 pagesize 300
 col high_value format a60
 col PARTITION_NAME for a20
select partition_name, high_value from dba_tab_partitions
where table_name = 'SALES'
order by partition_position;




define table_owner='SCOTT'
define TABLE_NAME='SALES'

set serverout on size 1000000
set verify off
declare
sql_stmt varchar2(1024);
row_count number;
cursor get_tab is
select table_name,partition_name
from dba_tab_partitions
where table_owner=upper('&&TABLE_OWNER') 
and table_name='&&TABLE_NAME'
;
begin
dbms_output.put_line('Checking Record Counts for table_name');
dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER.lst ....');
dbms_output.put_line('....');
for get_tab_rec in get_tab loop
BEGIN
sql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name||' partition ( '||get_tab_rec.partition_name||' )';

EXECUTE IMMEDIATE sql_stmt INTO row_count;
dbms_output.put_line('Table '||rpad(get_tab_rec.table_name||'('||get_tab_rec.partition_name||')',50)||' '||TO_CHAR(row_count)||' rows.');
exception when others then
dbms_output.put_line
('Error counting rows for table '||get_tab_rec.table_name);
END;
end loop;
end;
/
set verify on


....
Table SALES(P1)                                          0 rows.
Table SALES(SYS_P67687)                                  9 rows.
Table SALES(SYS_P67688)                                  10 rows.
Table SALES(SYS_P67689)                                  10 rows.
Table SALES(SYS_P67690)                                  10 rows.
Table SALES(SYS_P67691)                                  10 rows.

User has no SELECT privilege on V$SQL_PLAN_STATISTICS_ALL

User has no SELECT privilege on V$SQL_PLAN_STATISTICS_ALL User has no SELECT privilege on V$SESSION

error 


 select * from table(dbms_xplan.display_cursor(null,null,format=>'ADVANCED +ALLSTATS LAST, IOSTATS -PROJECTION -OUTLINE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SQL_PLAN_STATISTICS_ALL

====

Grant below to user  !!!


grant select on v_$sql_plan to scott ;

grant select on v_$session to scott ;

grant select on v_$sql_plan_statistics_all to scott ;

grant select on v_$sql to scott ;

===

Now all good .


set linesize 150 pagesize 300
select * from table(dbms_xplan.display_cursor(null,null,format=>'ADVANCED +ALLSTATS LAST, IOSTATS -PROJECTION -OUTLINE'));SQL> SQL>

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8trj2kacqhm6f, child number 0
-------------------------------------
select count(*) from t where a = 42 and b=42

Plan hash value: 2966233522

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |    22 (100)|          |      1 |00:00:00.01 |      75 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |     6 |            |          |      1 |00:00:00.01 |      75 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |      1 |     6 |    22   (0)| 00:00:01 |    100 |00:00:00.01 |      75 |
---------------------------------------------------------------------------------------------------------------------

Oracle Objects metric

from 
https://github.com/xtender/xt_scripts/blob/master/tops/sessmetric.sql


set linesize 500 
col time_interval       format a19
col username            format a25
col osuser              format a20
col action              format a25
col module              format a30
col sql_exec_start      format a14
col PHYSICAL_READS      heading "Phy reads"
col PHYSICAL_READ_PCT   format 999.90
col LOGICAL_READ_PCT    format 999.90
col PE_OBJECT           format a40
col PO_OBJECT           format a40


with v as (
          select--+ no_merge
             begin_time
            ,end_time
            ,intsize_csec/100       as seconds
            ,session_id             as sid
           -- ,session_serial_num     as serial#
			,m.SERIAL_NUM as serial#
            ,cpu
            ,physical_reads
            ,logical_reads
            ,pga_memory
            ,hard_parses
            ,soft_parses
            ,physical_read_pct
            ,logical_read_pct
            ,dense_rank()over(order by cpu            desc) cpu_rnk
            ,dense_rank()over(order by physical_reads desc) phy_reads_rnk
            ,dense_rank()over(order by logical_reads  desc) logical_reads_rnk
          from gv$sessmetric m
          where m.cpu>0 or m.PHYSICAL_READS>0
)
select 
    to_char(begin_time,'hh24:mi:ss')
  ||' - '
  ||to_char(end_time,'hh24:mi:ss') time_interval
   ,v.seconds
   ,s.sid
   ,s.serial#
   ,s.username
   ,s.osuser
   ,substr(s.action,1,25) action
   ,substr(s.module,1,30) module
   ,s.sql_id
     ,nvl2( pe.owner
           ,pe.owner
            ||'.'||pe.OBJECT_NAME
            ||nvl2(pe.PROCEDURE_NAME,'.'||pe.PROCEDURE_NAME,'')
           ,''
          )                                        as pe_object
     ,nvl2( po.owner
           ,po.owner
            ||'.'||po.OBJECT_NAME
            ||nvl2(po.PROCEDURE_NAME,'.'||po.PROCEDURE_NAME,'')
           ,null
          )                                        as po_object
   ,cpu
   ,physical_reads
   ,logical_reads
   ,pga_memory
   ,hard_parses
   ,soft_parses
   ,physical_read_pct
   ,logical_read_pct
-- _IF_ORA11_OR_HIGHER 
    ,to_char(s.sql_exec_start,'dd/mm hh24:mi:ss')   as sql_exec_start
from  v
     ,gv$session s
     ,dba_procedures pe
     ,dba_procedures po
where v.sid     = s.sid
  and v.serial# = s.serial#
  and pe.OBJECT_ID    (+)    = s.PLSQL_ENTRY_OBJECT_ID
  and pe.SUBPROGRAM_ID(+)    = s.PLSQL_ENTRY_SUBPROGRAM_ID
  and po.OBJECT_ID    (+)    = s.PLSQL_OBJECT_ID
  and po.SUBPROGRAM_ID(+)    = s.PLSQL_SUBPROGRAM_ID
  and(   v.cpu_rnk           <=10
      or v.phy_reads_rnk     <=10
      or v.logical_reads_rnk <= 10
     )
order by 
      cpu_rnk
     ,phy_reads_rnk
     ,logical_reads_rnk
/

col time_interval       clear
col username            clear
col osuser              clear
col action              clear
col module              clear
col sql_exec_start      clear
col PHYSICAL_READS      clear
col PHYSICAL_READ_PCT   clear
col LOGICAL_READ_PCT    clear
col PE_OBJECT           clear
col PO_OBJECT           clear

How much CPU a session consuming at a given time in oracle....

https://stackoverflow.com/questions/58820965/how-much-cpu-a-session-consuming-at-a-given-time-in-oracle
Roger Cornejo


-- uncomment based on your requirement 

set linesize 2000
var order_by varchar2(10); 

-- begin :order_by := 'ELAP'; end;
--/

 -- begin :order_by := 'IO'; end;
 --/


 --begin :order_by := 'CPU'; end;
 -- /

 --begin :order_by := 'GET'; end;
 -- /

 begin :order_by := 'READ'; end;
 /

 --begin :order_by := 'EXEC'; end;
 -- /

 --begin :order_by := 'PARSE'; end;
  --/

  --begin :order_by := 'MEM'; end;
 /


 --begin :order_by := 'VERS'; end;
 -- /
 
 
 --begin :order_by := 'ELAP_EXEC'; end;
  --/

 --begin :order_by := 'SNAP'; end;
 -- /

set numf 9999999999999999999
col "Tot Wait"  		for 99999999999999999   
col IOWAIT  			for 99999999999999999
col CLWAIT  			for 99999999999999999
col BASELINE_PLAN_NAME 	for a15
col SCHEMA  			for a20
col sql_text 			for a70 wrap
col "Time Per Exec" 	for a10

select ord ord
,  case 
       when nvl(:order_by, 'GET') in ('ELAP' , '1') then 'elapsed_sec' 
       when nvl(:order_by, 'GET') in ('CPU'  , '2') then 'cpu_sec'
       when nvl(:order_by, 'GET') in ('IO'   , '3') then 'iowait'
       when nvl(:order_by, 'GET') in ('GET'  , '4') then 'buffer_gets'
       when nvl(:order_by, 'GET') in ('READ' , '5') then 'disk_reads'
       when nvl(:order_by, 'GET') in ('EXEC' , '6') then 'executions'
       when nvl(:order_by, 'GET') in ('PARSE', '7') then 'parse_calls'
       when nvl(:order_by, 'GET') in ('MEM'  , '8') then 'sharable_mem'
       when nvl(:order_by, 'GET') in ('VERS' , '9') then 'version_count' 
       when nvl(:order_by, 'GET') in ('ELAP_EXEC' , '10') then 'time_per_exec' 
       when nvl(:order_by, 'GET') in ('SNAP' , '11') then 'snap_id' 
       else 'buffer_gets'
  end order_by
, schema  
, sql_id
, plan_hash_value "Plan Hash Value"
,      (select 
        max(to_number(extractvalue(
        xmltype(other_xml),'other_xml/info[@type="plan_hash_2"]'))) plan_hash_2
        from   dba_hist_sql_plan hp
        where  hp.sql_id          = main_query.sql_id
        and    hp.plan_hash_value = main_query.plan_hash_value
        and    hp.other_xml is not null) plan_hash_2
, (select max(last_refresh_time) from gv$sql_monitor sm where sm.sql_id = main_query.sql_id and sm.sql_plan_hash_value = main_query.plan_hash_value) monitor_last_refresh_time
, time_per_exec "Time Per Exec"
, executions "Exec-utions"
, clock_time "Clock Time"
, px_servers_execs "px servers execs"
, sql_text
, buffer_gets "Buffer Gets"
, fetches
, rows_processed "rows processed"
, round(rows_processed / nullif(fetches, 0)) "rows per fetch" 
, end_of_fetch_count "end of fetch count"
, sorts
, disk_reads "disk reads"
, tot_wait "Tot Wait"
, iowait
, clwait
, apwait
, ccwait
, direct_writes "direct writes"
, elapsed_sec "Elap-sed (Sec)"
, cpu_sec "CPU Sec"
, plsql_sec "PL/SQL sec"
, plsexec_time "pls exec time"
, javexec_time "java exec time"
, sharable_mem "shar-able mem"
-- per exec calculations
, case when executions > 0 then buffer_gets/executions else 0 end "Buffer Gets per exec"
, case when executions > 0 then fetches/executions else 0 end  "Fetches Gets per exec"
, case when executions > 0 then rows_processed/executions else 0 end  "rows per exec"
, case when executions > 0 then sorts/executions else 0 end  "sorts per exec"
, case when executions > 0 then disk_reads/executions else 0 end  "disk reads per exec"
, case when executions > 0 then tot_wait/executions else 0 end  "Tot Wait per exec"
, case when executions > 0 then iowait/executions else 0 end  "iowait per exec"
, case when executions > 0 then clwait/executions else 0 end  "clwait  per exec"
, case when executions > 0 then apwait/executions else 0 end  "apwait per exec"
, case when executions > 0 then ccwait/executions else 0 end  "ccwait  per exec"
, case when executions > 0 then direct_writes/executions else 0 end  "direct writes  per exec"
, case when executions > 0 then elapsed_sec/executions else 0 end  "Elap-sed (Sec)  per exec"
, case when executions > 0 then cpu_sec/executions else 0 end  "CPU Sec per exec"
, case when executions > 0 then plsql_sec/executions else 0 end  "PL/SQL sec  per exec"
, case when executions > 0 then plsexec_time/executions else 0 end  "pls exec time  per exec"
, case when executions > 0 then javexec_time/executions else 0 end  "java exec time per exec"
, case when executions > 0 then sharable_mem/executions else 0 end  "shar-able mem per exec"
-- per row calculations
, case when rows_processed > 0 then buffer_gets/rows_processed else 0 end "Buffer Gets per row"
, case when rows_processed > 0 then fetches/rows_processed else 0 end  "Fetches Gets per row"
, case when rows_processed > 0 then rows_processed/rows_processed else 0 end  "rows per row"
, case when rows_processed > 0 then sorts/rows_processed else 0 end  "sorts per row"
, case when rows_processed > 0 then disk_reads/rows_processed else 0 end  "disk reads per row"
, case when rows_processed > 0 then tot_wait/rows_processed else 0 end  "Tot Wait per row"
, case when rows_processed > 0 then iowait/rows_processed else 0 end  "iowait per row"
, case when rows_processed > 0 then clwait/rows_processed else 0 end  "clwait  per row"
, case when rows_processed > 0 then apwait/rows_processed else 0 end  "apwait per row"
, case when rows_processed > 0 then ccwait/rows_processed else 0 end  "ccwait  per row"
, case when rows_processed > 0 then direct_writes/rows_processed else 0 end  "direct writes  per row"
, case when rows_processed > 0 then elapsed_sec/rows_processed else 0 end  "Elap-sed (Sec)  per row"
, case when rows_processed > 0 then cpu_sec/rows_processed else 0 end  "CPU Sec per row"
, case when rows_processed > 0 then plsql_sec/rows_processed else 0 end  "PL/SQL sec  per row"
, case when rows_processed > 0 then plsexec_time/rows_processed else 0 end  "pls exec time  per row"
, case when rows_processed > 0 then javexec_time/rows_processed else 0 end  "java exec time per row"
, case when rows_processed > 0 then sharable_mem/rows_processed else 0 end  "shar-able mem per row"
, loaded_versions "loaded vers-ions" 
, version_count "ver-sion count"
, loads
, invalidations "invalid-ations"
, parse_calls "parse calls"
, module 
, command_type_name
, to_char(min_time, 'dd/mm/yyyy HH24:MI:SS') min_time
, to_char(max_time ,'dd/mm/yyyy HH24:MI:SS') max_time
, min_snap_id "Min Snap Id"
, max_snap_id "Max Snap Id"
, sql_profile
, Baseline_plan_name -- does not work for 10g
from
(
select schema  
   , plan_hash_value
   , sql_id
   , rownum ord
   , sub.elapsed_sec
   , CASE 
     WHEN elapsed_sec > 86399
          THEN elapsed_sec || ' sec' 
     WHEN elapsed_sec <= 86399
          THEN to_char(to_date(round(elapsed_sec) ,'SSSSS'), 'HH24:MI:SS') 
     END as clock_time
   , case when executions <> 0
     then CASE 
     WHEN round(elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs))) > 86399
          THEN round(elapsed_sec/(executions)*decode(px_servers_execs, 0, 1, px_servers_execs)) || ' sec' 
     WHEN round(elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs))) <= 86399
          THEN to_char(to_date(round(elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs))) ,'SSSSS'), 'HH24:MI:SS') 
     END 
     end as time_per_exec
   , cpu_sec
   , plsql_sec
   , executions
   , buffer_gets
   , sharable_mem
   , loaded_versions
   , version_count
   , module 
   , fetches
   , end_of_fetch_count
   , sorts
   , px_servers_execs
   , loads
   , invalidations
   , parse_calls
   , disk_reads
   , rows_processed
   , iowait
   , clwait
   , apwait
   , ccwait
   , tot_wait
   , direct_writes
   , plsexec_time
   , javexec_time
   , (select max(DBMS_LOB.SUBSTR(sql_text, 3800)) from dba_hist_sqltext st where st.sql_id = sub.sql_id) sql_text
   , (select max(name) from dba_hist_sqltext st, audit_actions aa where st.sql_id = sub.sql_id and aa.action = st.command_type)   command_type_name
   , min_time
   , max_time
   , min_snap_id
   , max_snap_id
   , sql_profile
   , (select nvl(min(sql_plan_baseline), 'none') from v$sql sql where sql.sql_id  = sub.sql_id and sql.plan_hash_value = sub.plan_hash_value) Baseline_plan_name -- does not work for 10g
from
   ( -- sub to sort before rownum
     select
        sql_id
        , plan_hash_value
        , round(sum(elapsed_time_delta)/1000000) as elapsed_sec
        , round(sum(cpu_time_delta)    /1000000) as cpu_sec 
        , round(sum(plsexec_time_delta)/1000000) as plsql_sec 
        , sum(executions_delta) as executions
        , sum(buffer_gets_delta) as buffer_gets      
        , sum(sharable_mem) as sharable_mem
        , sum(loaded_versions) as loaded_versions
        , sum(version_count) as version_count
        , max(module) as module 
        , sum(fetches_delta) as fetches
        , sum(end_of_fetch_count_delta) as end_of_fetch_count
        , sum(sorts_delta) as sorts
        , sum(px_servers_execs_delta) as px_servers_execs
        , sum(loads_delta) as loads
        , sum(invalidations_delta) as invalidations
        , sum(parse_calls_delta) as parse_calls
        , sum(disk_reads_delta) as disk_reads
        , sum(rows_processed_delta) as rows_processed
        , sum(iowait_delta) as iowait
        , sum(clwait_delta) as clwait
        , sum(apwait_delta) as apwait
        , sum(ccwait_delta) as ccwait
        , sum(iowait_delta) + sum(clwait_delta) + sum(apwait_delta) + sum(ccwait_delta) as tot_wait
        , sum(direct_writes_delta) as direct_writes
        , sum(plsexec_time_delta) as plsexec_time
        , sum(javexec_time_delta) as javexec_time
        , max(parsing_schema_name) as schema
        , max(snap.end_INTERVAL_TIME) max_time
        , min(snap.end_INTERVAL_TIME) min_time
        , min(stat.snap_id) min_snap_id
        , max(stat.snap_id) max_snap_id
        , min(nvl(sql_profile, 'none')) sql_profile     
     from
        dba_hist_snapshot snap
        , dba_hist_sqlstat stat
     where 1=1
          and nvl(:order_by, 'GET') like '%' 
          and snap.dbid = stat.dbid
          and snap.instance_number = stat.instance_number
          and snap.snap_id = stat.snap_id
        --  and snap.snap_id between nvl(:start_snap_id, snap.snap_id) and nvl(:end_snap_id, snap.snap_id)
        --  and nvl(parsing_schema_name,'%') like nvl(upper(:username), nvl(parsing_schema_name,'%')  )
        --  and sql_id = nvl(:sql_id, sql_id)
        --  and nvl(plan_hash_value,0) = nvl(:plan_hash_value, nvl(plan_hash_value,0))
        --   and nvl(module,'x') like nvl(:module, nvl(module,'x'))
        --  and stat.instance_number = nvl(:inst_id, stat.instance_number)
        --  and decode(:days_back_only_Y_N,'Y', end_INTERVAL_TIME, trunc(sysdate-:days_back) ) >= trunc(sysdate-:days_back)
        --  and (trunc(begin_INTERVAL_TIME, 'MI') >=  to_date(nvl(:sam_tm_str_MM_DD_YYYY_HH24_MI, to_char(begin_interval_time, 'MM_DD_YYYY_HH24_MI')),'MM_DD_YYYY_HH24_MI') 
        --  and trunc(end_interval_time, 'MI') <= to_date(nvl(:sam_tm_end_MM_DD_YYYY_HH24_MI, to_char(end_interval_time, 'MM_DD_YYYY_HH24_MI')),'MM_DD_YYYY_HH24_MI'))
        --  and (to_number(to_char(begin_INTERVAL_TIME, 'HH24')) between nvl(:begin_hour, 0) and  nvl(:end_hour, 24) 
        --  or to_number(to_char(begin_INTERVAL_TIME, 'HH24')) between nvl(:begin_hour2, nvl(:begin_hour, 0)) and  nvl(:end_hour2, nvl(:end_hour, 24)))
group by sql_id, plan_hash_value --, force_matching_signature  -- , stat.instance_number
order by 
  case 
       when nvl(:order_by, 'GET') in ('ELAP' , '1') then elapsed_sec 
       when nvl(:order_by, 'GET') in ('CPU'  , '2') then cpu_sec 
       when nvl(:order_by, 'GET') in ('IO'   , '3') then iowait 
       when nvl(:order_by, 'GET') in ('GET'  , '4') then buffer_gets 
       when nvl(:order_by, 'GET') in ('READ' , '5') then disk_reads 
       when nvl(:order_by, 'GET') in ('EXEC' , '6') then executions 
       when nvl(:order_by, 'GET') in ('PARSE', '7') then parse_calls 
       when nvl(:order_by, 'GET') in ('MEM'  , '8') then sharable_mem 
       when nvl(:order_by, 'GET') in ('VERS' , '9') then version_count 
       when nvl(:order_by, 'GET') in ('ELAP_EXEC' , '10') then 
	   case when executions <> 0 
	   then elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs)) 
	   else elapsed_sec end 
       when nvl(:order_by, 'GET') in ('SNAP' , '11') then min_snap_id 
       else buffer_gets
  end desc
   ) sub
where 1=1
  and rownum <= 10
) main_query
where 1=1
 -- and nvl(upper(sql_text), '%') like nvl(upper(:sql_text), '%')
 -- and nvl(command_type_name, 'x') like nvl(:command_type_name, nvl(command_type_name, 'x'))
order by 
  case 
       when nvl(:order_by, 'GET') in ('ELAP' , '1') then elapsed_sec 
       when nvl(:order_by, 'GET') in ('CPU'  , '2') then cpu_sec 
       when nvl(:order_by, 'GET') in ('IO'   , '3') then iowait 
       when nvl(:order_by, 'GET') in ('GET'  , '4') then buffer_gets  -- essentially an overall workload ordering
       when nvl(:order_by, 'GET') in ('READ' , '5') then disk_reads 
       when nvl(:order_by, 'GET') in ('EXEC' , '6') then executions 
       when nvl(:order_by, 'GET') in ('PARSE', '7') then parse_calls 
       when nvl(:order_by, 'GET') in ('MEM'  , '8') then sharable_mem 
       when nvl(:order_by, 'GET') in ('VERS' , '9') then version_count 
       when nvl(:order_by, 'GET') in ('ELAP_EXEC' , '10') then case when executions <> 0 then elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs)) else elapsed_sec end 
       when nvl(:order_by, 'GET') in ('SNAP' , '11') then min_snap_id 
       else buffer_gets
  end desc
;

                 ORD ORDER_BY      SCHEMA               SQL_ID             Plan Hash Value          PLAN_HASH_2 MONITOR_L Time Per E          Exec-utions Clock Time                           px servers execs SQL_TEXT                                                                         Buffer Gets              FETCHES       rows processed       rows per fetch   end of fetch count             SORTS           disk reads           Tot Wait             IOWAIT             CLWAIT               APWAIT               CCWAIT        direct writes       Elap-sed (Sec)  CPU Sec    PL/SQL sec        pls exec time       java exec time        shar-able mem Buffer Gets per exec Fetches Gets per exec        rows per exec       sorts per exec  disk reads per exec Tot Wait per exec      iowait per exec     clwait  per exec      apwait per exec     ccwait  per exec direct writes  per exec Elap-sed (Sec)  per exec     CPU Sec per exec PL/SQL sec  per exec pls exec time  per exec java exec time per exec shar-able mem per exec  Buffer Gets per row Fetches Gets per row      rows per row        sorts per row   disk reads per row     Tot Wait per row            iowait per row      clwait  per row       apwait per row      ccwait  per row direct writes  per row Elap-sed (Sec)  per row      CPU Sec per row  PL/SQL sec  per row pls exec time  per row java exec time per row shar-able mem per row     loaded vers-ions    ver-sion count                LOADS       invalid-ations          parse calls MODULE               COMMAND_TYPE_NAME             MIN_TIME            MAX_TIME                     Min Snap Id          Max Snap Id SQL_PROFILE                                                      BASELINE_PLAN_N
-------------------- ------------- -------------------- ------------- -------------------- -------------------- --------- ---------- -------------------- -------------------------------------------- -------------------- ---------------------------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------------ ------------------ ------------------ -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------------------- ------------------------ -------------------- -------------------- ----------------------- ----------------------- ---------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ---------------------- ----------------------- -------------------- -------------------- ---------------------- ---------------------- --------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------------------------------- ---------------------------- ------------------- ------------------- -------------------- -------------------- ---------------------------------------------------------------- ---------------
                                                                                                                                                                                            (:sig IS NULL AND
                                                                                                                                                                                             ((:existingSQL IS NOT NULL AND :newSQL IS NOT NULL) OR
                                                                                                                                                                                              (:existingSQL IS NOT NULL AND
                                                                                                                                                                                               :newSQL IS NULL AND

User Tablespaces

alter session enable parallel query;	
	  
define owner='SCOTT'		  --- change if required 
with all_ts_user as
   (
   select tablespace_name 		from dba_lobs   			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_clusters  			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_indexes 			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_rollback_segs 			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_tables  			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_object_tables 			where 1=1 and OWNER='&owner'         union all
   select def_tablespace_name 	        from dba_part_tables  			where 1=1 and OWNER='&owner'         union all
   select def_tablespace_name 	        from dba_part_indexes 			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_tab_partitions  		where 1=1 and TABLE_OWNER='&owner'   union all
   select tablespace_name 		from dba_ind_partitions  		where 1=1 and index_OWNER='&owner'   union all
   select tablespace_name 		from dba_tab_subpartitions 		where 1=1 and TABLE_OWNER='&owner'   union all
   select tablespace_name 		from dba_ind_subpartitions  		where 1=1 and index_OWNER='&owner'   union all
   select def_tablespace_name 	        from dba_part_lobs 			where 1=1 and TABLE_OWNER='&owner'   union all
   select tablespace_name 		from dba_lob_partitions   		where 1=1 and TABLE_OWNER='&owner'   union all
   select tablespace_name 		from dba_lob_subpartitions  		where 1=1 and TABLE_OWNER='&owner'   union all
   select tablespace_name 		from dba_subpartition_templates 	where 1=1 and USER_NAME='&owner'     union all
   select tablespace_name 		from dba_lob_templates  		where 1=1 and USER_NAME='&owner'     union all
   select tablespace_name 		from dba_segments     			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_extents   			where 1=1 and OWNER='&owner'         union all
   select tablespace_name 		from dba_undo_extents 			where 1=1 and OWNER='&owner'
   )
   select distinct tablespace_name from all_ts_user
alter session disable parallel query;



TABLESPACE_NAME
------------------------------
USERS
DATA

	
alter session enable parallel query;	

set pagesize 300 	  
define owner='SCOTT'	
		  
with all_possible_ts as
   (
   select 'dba_lobs' From1 			,tablespace_name from dba_lobs   where 1=1 and OWNER= '&owner'         					union all
   select 'dba_clusters'			,tablespace_name from dba_clusters  where 1=1 and OWNER= '&owner'                 		union all
   select 'dba_indexes' 			,tablespace_name from dba_indexes where 1=1 and OWNER= '&owner'                   		union all
   select 'dba_rollback_segs' 			,tablespace_name from dba_rollback_segs where 1=1 and OWNER= '&owner'             		union all
   select 'dba_tables'				,tablespace_name from dba_tables  where 1=1 and OWNER= '&owner'                   		union all
   select 'dba_object_tables' 			,tablespace_name from dba_object_tables where 1=1 and OWNER= '&owner'              		union all
   select 'dba_part_tables' 			,def_tablespace_name from dba_part_tables  where 1=1 and OWNER= '&owner'          		union all
   select 'dba_part_indexes' 			,def_tablespace_name from dba_part_indexes where 1=1 and OWNER= '&owner'          		union all
   select 'dba_tab_partitions' 			,tablespace_name from dba_tab_partitions  where 1=1 and TABLE_OWNER= '&owner'          	union all
   select 'dba_ind_partitions' 			,tablespace_name from dba_ind_partitions  where 1=1 and index_OWNER= '&owner'          	union all
   select 'dba_tab_subpartitions' 		,tablespace_name from dba_tab_subpartitions where 1=1 and TABLE_OWNER= '&owner'        	union all
   select 'dba_ind_subpartitions' 		,tablespace_name from dba_ind_subpartitions  where 1=1 and index_OWNER= '&owner'       	union all
   select 'dba_part_lobs'			,def_tablespace_name from dba_part_lobs where 1=1 and TABLE_OWNER= '&owner'            	union all
   select 'dba_lob_partitions' 			,tablespace_name from dba_lob_partitions   where 1=1 and TABLE_OWNER= '&owner'         	union all
   select 'dba_lob_subpartitions'		,tablespace_name from dba_lob_subpartitions  where 1=1 and TABLE_OWNER= '&owner'       	union all
   select 'dba_subpartition_templates' 	        ,tablespace_name from dba_subpartition_templates where 1=1 and USER_NAME= '&owner'     	union all
   select 'dba_lob_templates' 			,tablespace_name from dba_lob_templates  where 1=1 and USER_NAME= '&owner'            	union all
   select 'dba_segments ' 			,tablespace_name from dba_segments     where 1=1 and OWNER= '&owner'              		union all
   select 'dba_extents' 			,tablespace_name from dba_extents   where 1=1 and OWNER= '&owner'                 		union all
   select 'dba_undo_extents' 			,tablespace_name from dba_undo_extents where 1=1 and OWNER= '&owner'
   )
   select distinct * from all_possible_ts