My Experiences with Databases & More

Oracle-MySQL-SQL SERVER-Python-Azure-AWS-Oracle Cloud-GCP etc

  • Enter your email address to follow this blog and receive notifications of new posts by email.

  • Total Views

    • 558,488 hits
  • $riram $anka

    The experiences, Test cases, views, and opinions etc expressed in this website are my own and does not reflect the views or opinions of my employer. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.Product and company names mentioned in this website may be the trademarks of their respective owners.

Archive for September, 2022

Docker Part 1 – Install Docker CE and Docker Compose in Oracle Linux Server 8.6 – OCI-IAAS

Posted by Sriram Sanka on September 29, 2022

In this Post, lets see how to Install Docker CE . I am using Oracle Linux Server 8.6 (Free Instance Offered by Oracle Cloud) .

Docker lets you build, test, and deploy applications quickly, Docker is a software platform that allows you to build, test, and deploy applications quickly. Docker packages software into standardized units called containers that have everything the software needs to run including libraries, system tools, code, and runtime. Using Docker, you can quickly deploy and scale applications into any environment and know your code will run.

Docker Compose is a tool that was developed to help define and share multi-container applications. With Compose, we can create a YAML file to define the services and with a single command, can spin everything up or tear it all down.

The big advantage of using Compose is you can define your application stack in a file, keep it at the root of your project repo (it’s now version controlled), and easily enable someone else to contribute to your project. Someone would only need to clone your repo and start the compose app

Note : If there is no repo available in your OS, try adding EPEL repo and Try again.

[root@certbot conf.d]# dnf config-manager --add-repo=
Adding repo from:

Once the Repo is Added, we are good to go …..

run this command “dnf install docker-ce docker-ce-cli”

[root@certbot conf.d]# dnf install docker-ce docker-ce-cli
Last metadata expiration check: 0:00:57 ago on Wed 21 Sep 2022 08:05:23 PM GMT.
Dependencies resolved.
 Package                                                     Architecture                             Version                                                                      Repository                                           Size
Installing:                                               x86_64                                   1.6.8-3.1.el8                                                                docker-ce-stable                                     33 M
 docker-ce                                                   x86_64                                   3:20.10.18-3.el8                                                             docker-ce-stable                                     21 M
 docker-ce-cli                                               x86_64                                   1:20.10.18-3.el8                                                             docker-ce-stable                                     30 M
Installing dependencies:
 container-selinux                                           noarch                                   2:2.188.0-1.module+el8.6.0+20721+d8d917a9                                    ol8_appstream                                        59 k
 docker-ce-rootless-extras                                   x86_64                                   20.10.18-3.el8                                                               docker-ce-stable                                    4.6 M
 fuse-common                                                 x86_64                                   3.3.0-15.0.2.el8                                                             ol8_baseos_latest                                    22 k
 fuse-overlayfs                                              x86_64                                   1.9-1.module+el8.6.0+20721+d8d917a9                                          ol8_appstream                                        73 k
 fuse3                                                       x86_64                                   3.3.0-15.0.2.el8                                                             ol8_baseos_latest                                    55 k
 fuse3-libs                                                  x86_64                                   3.3.0-15.0.2.el8                                                             ol8_baseos_latest                                    95 k
 libcgroup                                                   x86_64                                   0.41-19.el8                                                                  ol8_baseos_latest                                    70 k
 libslirp                                                    x86_64                                   4.4.0-1.module+el8.6.0+20721+d8d917a9                                        ol8_appstream                                        70 k
 slirp4netns                                                 x86_64                                   1.2.0-2.module+el8.6.0+20721+d8d917a9                                        ol8_appstream                                        54 k
Installing weak dependencies:
 docker-scan-plugin                                          x86_64                                   0.17.0-3.el8                                                                 docker-ce-stable                                    3.8 M
Enabling module streams:
 container-tools                                                                                      ol8

Transaction Summary
Install  13 Packages

Total download size: 92 M
Installed size: 365 M
Is this ok [y/N]: y
Downloading Packages:
(1/13): docker-ce-20.10.18-3.el8.x86_64.rpm                                                                                                                                                                   12 MB/s |  21 MB     00:01
(2/13): docker-ce-rootless-extras-20.10.18-3.el8.x86_64.rpm                                                                                                                                                  8.1 MB/s | 4.6 MB     00:00
(3/13): docker-scan-plugin-0.17.0-3.el8.x86_64.rpm                                                                                                                                                           9.5 MB/s | 3.8 MB     00:00
(4/13): docker-ce-cli-20.10.18-3.el8.x86_64.rpm                                                                                                                                                               10 MB/s |  30 MB     00:02
(5/13): fuse-common-3.3.0-15.0.2.el8.x86_64.rpm                                                                                                                                                               78 kB/s |  22 kB     00:00
(6/13): fuse3-3.3.0-15.0.2.el8.x86_64.rpm                                                                                                                                                                    650 kB/s |  55 kB     00:00
(7/13): fuse3-libs-3.3.0-15.0.2.el8.x86_64.rpm                                                                                                                                                               7.8 MB/s |  95 kB     00:00
(8/13): container-selinux-2.188.0-1.module+el8.6.0+20721+d8d917a9.noarch.rpm                                                                                                                                  11 MB/s |  59 kB     00:00
(9/13): fuse-overlayfs-1.9-1.module+el8.6.0+20721+d8d917a9.x86_64.rpm                                                                                                                                         10 MB/s |  73 kB     00:00
(10/13):                                                                                                                                                              9.9 MB/s |  33 MB     00:03
(11/13): libcgroup-0.41-19.el8.x86_64.rpm                                                                                                                                                                    206 kB/s |  70 kB     00:00
(12/13): libslirp-4.4.0-1.module+el8.6.0+20721+d8d917a9.x86_64.rpm                                                                                                                                           219 kB/s |  70 kB     00:00
(13/13): slirp4netns-1.2.0-2.module+el8.6.0+20721+d8d917a9.x86_64.rpm                                                                                                                                        2.2 MB/s |  54 kB     00:00
Total                                                                                                                                                                                                         27 MB/s |  92 MB     00:03
Docker CE Stable - x86_64                                                                                                                                                                                     85 kB/s | 1.6 kB     00:00
Importing GPG key 0x621E9F35:
 Userid     : "Docker Release (CE rpm) <>"
 Fingerprint: 060A 61C5 1B55 8A7F 742B 77AA C52F EB6B 621E 9F35
 From       :
Is this ok [y/N]: Y
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                                                                                                     1/1
  Installing       : docker-scan-plugin-0.17.0-3.el8.x86_64                                                                                                                                                                             1/13
  Running scriptlet: docker-scan-plugin-0.17.0-3.el8.x86_64                                                                                                                                                                             1/13
  Installing       : docker-ce-cli-1:20.10.18-3.el8.x86_64                                                                                                                                                                              2/13
  Running scriptlet: docker-ce-cli-1:20.10.18-3.el8.x86_64                                                                                                                                                                              2/13
  Running scriptlet: container-selinux-2:2.188.0-1.module+el8.6.0+20721+d8d917a9.noarch                                                                                                                                                 3/13
  Installing       : container-selinux-2:2.188.0-1.module+el8.6.0+20721+d8d917a9.noarch                                                                                                                                                 3/13
  Running scriptlet: container-selinux-2:2.188.0-1.module+el8.6.0+20721+d8d917a9.noarch                                                                                                                                                 3/13
  Installing       : fuse3-libs-3.3.0-15.0.2.el8.x86_64                                                                                                                                                                                 4/13
  Running scriptlet: fuse3-libs-3.3.0-15.0.2.el8.x86_64                                                                                                                                                                                 4/13
  Installing       :                                                                                                                                                                                 5/13
  Running scriptlet:                                                                                                                                                                                 5/13
  Installing       : libslirp-4.4.0-1.module+el8.6.0+20721+d8d917a9.x86_64                                                                                                                                                              6/13
  Installing       : slirp4netns-1.2.0-2.module+el8.6.0+20721+d8d917a9.x86_64                                                                                                                                                           7/13
  Running scriptlet: libcgroup-0.41-19.el8.x86_64                                                                                                                                                                                       8/13
  Installing       : libcgroup-0.41-19.el8.x86_64                                                                                                                                                                                       8/13
  Running scriptlet: libcgroup-0.41-19.el8.x86_64                                                                                                                                                                                       8/13
  Installing       : fuse-common-3.3.0-15.0.2.el8.x86_64                                                                                                                                                                                9/13
  Installing       : fuse3-3.3.0-15.0.2.el8.x86_64                                                                                                                                                                                     10/13
  Installing       : fuse-overlayfs-1.9-1.module+el8.6.0+20721+d8d917a9.x86_64                                                                                                                                                         11/13
  Running scriptlet: fuse-overlayfs-1.9-1.module+el8.6.0+20721+d8d917a9.x86_64                                                                                                                                                         11/13
  Installing       : docker-ce-rootless-extras-20.10.18-3.el8.x86_64                                                                                                                                                                   12/13
  Running scriptlet: docker-ce-rootless-extras-20.10.18-3.el8.x86_64                                                                                                                                                                   12/13
  Installing       : docker-ce-3:20.10.18-3.el8.x86_64                                                                                                                                                                                 13/13
  Running scriptlet: docker-ce-3:20.10.18-3.el8.x86_64                                                                                                                                                                                 13/13
  Running scriptlet: container-selinux-2:2.188.0-1.module+el8.6.0+20721+d8d917a9.noarch                                                                                                                                                13/13
  Running scriptlet: docker-ce-3:20.10.18-3.el8.x86_64                                                                                                                                                                                 13/13
  Verifying        :                                                                                                                                                                                 1/13
  Verifying        : docker-ce-3:20.10.18-3.el8.x86_64                                                                                                                                                                                  2/13
  Verifying        : docker-ce-cli-1:20.10.18-3.el8.x86_64                                                                                                                                                                              3/13
  Verifying        : docker-ce-rootless-extras-20.10.18-3.el8.x86_64                                                                                                                                                                    4/13
  Verifying        : docker-scan-plugin-0.17.0-3.el8.x86_64                                                                                                                                                                             5/13
  Verifying        : fuse-common-3.3.0-15.0.2.el8.x86_64                                                                                                                                                                                6/13
  Verifying        : fuse3-3.3.0-15.0.2.el8.x86_64                                                                                                                                                                                      7/13
  Verifying        : fuse3-libs-3.3.0-15.0.2.el8.x86_64                                                                                                                                                                                 8/13
  Verifying        : libcgroup-0.41-19.el8.x86_64                                                                                                                                                                                       9/13
  Verifying        : container-selinux-2:2.188.0-1.module+el8.6.0+20721+d8d917a9.noarch                                                                                                                                                10/13
  Verifying        : fuse-overlayfs-1.9-1.module+el8.6.0+20721+d8d917a9.x86_64                                                                                                                                                         11/13
  Verifying        : libslirp-4.4.0-1.module+el8.6.0+20721+d8d917a9.x86_64                                                                                                                                                             12/13
  Verifying        : slirp4netns-1.2.0-2.module+el8.6.0+20721+d8d917a9.x86_64                                                                                                                                                          13/13

  container-selinux-2:2.188.0-1.module+el8.6.0+20721+d8d917a9.noarch              docker-ce-3:20.10.18-3.el8.x86_64            docker-ce-cli-1:20.10.18-3.el8.x86_64
  docker-ce-rootless-extras-20.10.18-3.el8.x86_64                             docker-scan-plugin-0.17.0-3.el8.x86_64          fuse-common-3.3.0-15.0.2.el8.x86_64          fuse-overlayfs-1.9-1.module+el8.6.0+20721+d8d917a9.x86_64
  fuse3-3.3.0-15.0.2.el8.x86_64                                               fuse3-libs-3.3.0-15.0.2.el8.x86_64              libcgroup-0.41-19.el8.x86_64                 libslirp-4.4.0-1.module+el8.6.0+20721+d8d917a9.x86_64


Enable and Start the docker Service

[root@certbot conf.d]# systemctl enable docker
Created symlink /etc/systemd/system/ → /usr/lib/systemd/system/docker.service.
[root@certbot conf.d]# systemctl start docker

Command “Docker PS” shows the running dockers.

[root@certbot conf.d]# docker ps

Lets Install Docker Compose as follows

[root@certbot conf.d]# curl -L "$(uname -s)-$(uname -m)" -o docker-compose
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
100 11.2M  100 11.2M    0     0  44.9M      0 --:--:-- --:--:-- --:--:-- 44.9M
[root@certbot conf.d]# mv docker-compose /usr/local/bin && sudo chmod +x /usr/local/bin/docker-compose
[root@certbot conf.d]# docker-compose version
docker-compose version 1.23.2, build 1110ad01
docker-py version: 3.6.0
CPython version: 3.6.7
OpenSSL version: OpenSSL 1.1.0f  25 May 2017
[root@certbot conf.d]#
Add user to docker group (if not already added)
sudo usermod -aG docker $USER
2. create a symbolic link to /usr/bin using the following command
$ sudo ln -s /usr/local/bin/docker-compose /usr/bin/docker-compose
3. Restart docker service
$ sudo service docker restart

Lets see the Available Commands with Docker help, We can discuss more on these commands in the Next Part

See 'docker --help'.

Usage:  docker [OPTIONS] COMMAND

A self-sufficient runtime for containers

      --config string      Location of client config files (default "/root/.docker")
  -c, --context string     Name of the context to use to connect to the daemon (overrides DOCKER_HOST env var and default context set with "docker context use")
  -D, --debug              Enable debug mode
  -H, --host list          Daemon socket(s) to connect to
  -l, --log-level string   Set the logging level ("debug"|"info"|"warn"|"error"|"fatal") (default "info")
      --tls                Use TLS; implied by --tlsverify
      --tlscacert string   Trust certs signed only by this CA (default "/root/.docker/ca.pem")
      --tlscert string     Path to TLS certificate file (default "/root/.docker/cert.pem")
      --tlskey string      Path to TLS key file (default "/root/.docker/key.pem")
      --tlsverify          Use TLS and verify the remote
  -v, --version            Print version information and quit

Management Commands:
  app*        Docker App (Docker Inc., v0.9.1-beta3)
  builder     Manage builds
  buildx*     Docker Buildx (Docker Inc., v0.7.1-docker)
  config      Manage Docker configs
  container   Manage containers
  context     Manage contexts
  image       Manage images
  manifest    Manage Docker image manifests and manifest lists
  network     Manage networks
  node        Manage Swarm nodes
  plugin      Manage plugins
  scan*       Docker Scan (Docker Inc., v0.12.0)
  secret      Manage Docker secrets
  service     Manage services
  stack       Manage Docker stacks
  swarm       Manage Swarm
  system      Manage Docker
  trust       Manage trust on Docker images
  volume      Manage volumes

  attach      Attach local standard input, output, and error streams to a running container
  build       Build an image from a Dockerfile
  commit      Create a new image from a container's changes
  cp          Copy files/folders between a container and the local filesystem
  create      Create a new container
  diff        Inspect changes to files or directories on a container's filesystem
  events      Get real time events from the server
  exec        Run a command in a running container
  export      Export a container's filesystem as a tar archive
  history     Show the history of an image
  images      List images
  import      Import the contents from a tarball to create a filesystem image
  info        Display system-wide information
  inspect     Return low-level information on Docker objects
  kill        Kill one or more running containers
  load        Load an image from a tar archive or STDIN
  login       Log in to a Docker registry
  logout      Log out from a Docker registry
  logs        Fetch the logs of a container
  pause       Pause all processes within one or more containers
  port        List port mappings or a specific mapping for the container
  ps          List containers
  pull        Pull an image or a repository from a registry
  push        Push an image or a repository to a registry
  rename      Rename a container
  restart     Restart one or more containers
  rm          Remove one or more containers
  rmi         Remove one or more images
  run         Run a command in a new container
  save        Save one or more images to a tar archive (streamed to STDOUT by default)
  search      Search the Docker Hub for images
  start       Start one or more stopped containers
  stats       Display a live stream of container(s) resource usage statistics
  stop        Stop one or more running containers
  tag         Create a tag TARGET_IMAGE that refers to SOURCE_IMAGE
  top         Display the running processes of a container
  unpause     Unpause all processes within one or more containers
  update      Update configuration of one or more containers
  version     Show the Docker version information
  wait        Block until one or more containers stop, then print their exit codes

Run 'docker COMMAND --help' for more information on a command.

To get more help with docker, check out our guides at


Posted in DevOps, Docker, Docker CE, Docker Compose, Linux | Tagged: , , , | Leave a Comment »

Install & Configure SSL for Apache-Nginx using Lets Encrypt-CertBot.

Posted by Sriram Sanka on September 28, 2022

One can Install Apache and Nginx using YUM Or DNF in the Selected Unix flavor whereas by default it is a non-secure sub-domain when you access.

Lets encrypt offers free SSL which can be configured to get the SSL for your domain irrespective of Private Or Public Domains. In case of Private Domains , you just need to add a text Entry to Pass the Validations.

For this I am using Oracle Cloud Instance(Always Free).

Change the Host Name using hostnamectl as below

[root@certbot ~]# hostnamectl set-hostname
[root@certbot ~]# hostname

As its the Initial Login after Instance provision , run the yum update and Install Apache and/or Nginx as per your choice.

Run Yum Update and make sure everything updated without any issues.

Install Apache-httpd using yum repo.

Try Access the IP/Hostname to see the Installed Apache Default Page.

Install Nginx using Yum

Try to Access Nginx from the Browser

Enable EPEL Repo to Configure Snap and Certbot

Enable the Socket and run the below to Install certbot ,

systemctl enable --now snapd.socket
ln -s /var/lib/snapd/snap /snap
snap install certbot --classic

Restart your session to get the Certbot ,You can Configure SSL for Nginx Or Apache as below, Add an Entry in you domain controller for your IP matching with the Host Name Configured.

Adding Domain Entry for the HostName

You can Either Configure SSL and Install Or Choose the certonly Option to Get the Certificates only, you can configure your SSL.conf as per your webserver configuration
Also In case , your System is not internet facing, You can choose the Preferred Challenges as either http or DNS You can review the Supported Challenged here

certbot --nginx -d <subdomain>
certbot --apache -d <subdomain>

Make Sure you have a Virtual_Host entry available with the domain you chose., Otherwise it will fail.

<VirtualHost *:80>
    ServerAdmin webmaster@localhost
    DocumentRoot /var/www/html

    ErrorLog ${APACHE_LOG_DIR}/error.log
    CustomLog ${APACHE_LOG_DIR}/access.log combined
certbot --apache -d

Reload your browser session to see the Installed Certificate .

Apache with SSL Configured

Thanks for your visit, Hope you like it.

Posted in Apache, Installation, Linux, Nginx, Security, ssl | Tagged: , , , , , , | Leave a Comment »

Python Basics – Part 1

Posted by Sriram Sanka on September 17, 2022

Language Introduction

Python is a dynamic, interpreted (bytecode-compiled) language. There are no type declarations of variables, parameters, functions, or methods in source code. This makes the code short and flexible, and you lose the compile-time type checking of the source code. Python tracks the types of all values at runtime and flags code that does not make sense as it runs.

In the Below Sections I have attached couple of reference Documents and Practice Notes for your reference. To obtain the contents, Rename the file Extension from txt to “ipynb” , which can be accessed using Jupyter Or Anaconda etc.

String Split


Split the string input_str = ‘Kumar_Ravi_003’ to the person’s second name, first name and unique customer code. In this example, second_name= ‘Kumar’, first_name= ‘Ravi’, customer_code = ‘003’.

input_str = input('data')
first_name = input_str[6:10]
second_name = input_str[0:5]
customer_code = input_str[-3:]

string -lstrip()

input_str = input('Enter Input : ')
final_str = input_str.lstrip()

List is a collection which is ordered and changeable. Allows duplicate members.

Tuple is a collection which is ordered and unchangeable. Allows duplicate members.

Set is a collection which is unordered, unchangeable*, and unindexed. No duplicate members.

Dictionary is a collection which is ordered** and changeable. No duplicate members.

List to String


Convert a list [‘Pythons syntax is easy to learn’, ‘Pythons syntax is very clear’] to a string using ‘&’. The sample output of this string will be:

Pythons syntax is easy to learn & Pythons syntax is very clear

Note that there is a space on both sides of ‘&’ (as usual in English sentences).

l =[]
l.append('Pythons syntax is easy to learn')
l.append(' Pythons syntax is very clear')
print('This is the List ',l)
input_str = l
string_1 = " & ".join(input_str)
print('This is Combined String ',string_1)


Posted in Anaconda, Python | Tagged: , , | Leave a Comment »

SQL Formatter – Using Java/PL/SQL by Sayan Malakshinov

Posted by Sriram Sanka on September 17, 2022

It is very easy to Format SQL using Third Party tools, Recently I found one source to get the SQL Formatted from the Sql prompt without connecting to any Third Party GUI Tools.

I prefer to use SQLPLUS more than GUI tools., You can see the code formatted beautifully and very readable.

Step 1: Load oracle.dbtools-common.jar into the Database.

Microsoft Windows [Version 10.0.19044.2006]
(c) Microsoft Corporation. All rights reserved.

C:\Users\Dell>loadjava -u sriram/zaqmlp123 C:\app\Dell\product\21c\dbhomeXE\jlib\oracle.dbtools-common.jar

Step 2: Grant required java Privileges to the User and Create Java and Write a wrapper Package using the java

C:\Users\Dell>sqlplus /nolog

SQL*Plus: Release - Production on Sat Sep 17 16:52:53 2022

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

SQL> conn sriram@xepdb1
Enter password:
SQL> exec dbms_java.grant_permission( 'SRIRAM', 'SYS:java.lang.RuntimePermission', 'oracle.DbmsJavaScriptUser', '' );

PL/SQL procedure successfully completed.

SQL> exec dbms_java.grant_permission( 'SRIRAM', 'SYS:java.lang.RuntimePermission', 'accessClassInPackage.jdk.nashorn.internal.runtime', '' );

PL/SQL procedure successfully completed.

SQL> exec dbms_java.grant_permission( 'SRIRAM', 'SYS:java.lang.reflect.ReflectPermission', 'suppressAccessChecks', '' );

PL/SQL procedure successfully completed.

SQL> select
SQL> ed
Wrote file afiedt.buf

  2  import;
  3  import java.sql.Connection;
  4  import java.sql.DriverManager;
  5  import java.sql.SQLException;
  6  import oracle.sql.BLOB;
  7  import oracle.sql.CLOB;
  8  import;
  9  import;
 10  public class SQLFormatter {
 11      private static String getStackTrace(Exception e) {
 12         StringWriter writer = new StringWriter();
 13         PrintWriter printWriter = new PrintWriter( writer );
 14         e.printStackTrace( printWriter );
 15         printWriter.flush();
 16         return writer.toString();
 17      }
 18      public static Format getFormat() {
 19 format = new;
 20          format.options.put("singleLineComments", Format.InlineComments.CommentsUnchanged);
 21          format.options.put("kwCase", Format.Case.UPPER);
 22          format.options.put("idCase", Format.Case.NoCaseChange);
 23          format.options.put("adjustCaseOnly", false);
 24          format.options.put("formatThreshold", 1);
 25          format.options.put("alignTabColAliases", false);
 26          format.options.put("alignTypeDecl", true);
 27          format.options.put("alignNamedArgs", true);
 28          format.options.put("alignEquality", false);
 29          format.options.put("alignAssignments", true);
 30          format.options.put("alignRight", false);
 31          format.options.put("identSpaces", 3);
 32          format.options.put("useTab", false);
 33          format.options.put("breaksComma", Format.Breaks.Before);
 34          format.options.put("breaksProcArgs", false);
 35          format.options.put("breaksConcat", Format.Breaks.Before);
 36          format.options.put("breaksAroundLogicalConjunctions", Format.Breaks.Before);
 37          format.options.put("breaksAfterSelect", true);
 38          format.options.put("commasPerLine", 1);
 39          format.options.put("breakOnSubqueries", true);
 40          format.options.put("breakAnsiiJoin", true);
 41          format.options.put("breakParenCondition", true);
 42          format.options.put("maxCharLineSize", 120);
 43          format.options.put("forceLinebreaksBeforeComment", false);
 44          format.options.put("extraLinesAfterSignificantStatements", Format.BreaksX2.Keep);
 45          //format.options.put("flowControl", Format.FlowControl.IndentedActions);
 46          format.options.put("spaceAroundOperators", true);
 47          format.options.put("spaceAfterCommas", true);
 48          //format.options.put("spaceAroundBrackets", Format.Space.Default);
 49          return format;
 50      }
 51    public static String format(String str)
 52    {
 53      String res;
 54      try {
 55         Format f = SQLFormatter.getFormat();
 56         res = f.format(str);
 57         }
 58      catch (Exception e){
 59         res = "Error: " + e.getMessage() + " [ " + SQLFormatter.getStackTrace(e) + " ]";
 60      }
 61      return res;
 62    }
 63    public static CLOB formatClob(oracle.sql.CLOB clob)
 64    throws SQLException
 65    {
 66      String str = clob.getSubString(1, (int) clob.length());
 67      String res = SQLFormatter.format(str);
 68      Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 69      CLOB resClob = CLOB.createTemporary(conn, false, BLOB.DURATION_SESSION);
 70      resClob.setString(1L, res);
 71      return resClob;
 72    }
 73* }
 74  /

Java created.

SQL> select
SQL> ed
Wrote file afiedt.buf

  1  create or replace package SQLFormatter as
  2    FUNCTION Format(str in varchar2) RETURN VARCHAR2
  3    AS LANGUAGE JAVA NAME 'SQLFormatter.format(java.lang.String) return java.lang.String';
  4    FUNCTION FormatClob(str in clob) RETURN CLOB
  5    AS LANGUAGE JAVA NAME 'SQLFormatter.formatClob(oracle.sql.CLOB) return oracle.sql.CLOB';
  6* end;
  7  /

Package created.
SQL> desc SQLFormatter
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 STR                            VARCHAR2                IN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 STR                            CLOB                    IN

SQL> select  sql_id||' - '||sql_text as "UnClear" from v$sqlarea where sql_id='gngtvs38t0060'
  2  union
  3  select sql_id||' - '||SQLFormatter.format(sql_text) "Readable" from v$sqlarea where sql_id='gngtvs38t0060'
  4  /

gngtvs38t0060 - SELECT /*+ CONNECT_BY_FILTERING */ s.privilege# FROM sys.sysauth$ s        CONNECT BY s.grantee# = PRIOR
 s.privilege#                                 AND (s.privilege# > 0 OR s.privilege# = -352)
 START WITH (s.privilege# > 0 OR s.privilege# = -352) AND s.grantee# IN     (SELECT c1.privilege# FROM sys.codeauth$ c1
WHERE c1.obj# = :1)            UNION                                                                      SELECT c2.priv
ilege# FROM sys.codeauth$ c2 WHERE c2.obj# = :2              ORDER BY 1 ASC

gngtvs38t0060 - SELECT /*+ CONNECT_BY_FILTERING */
   sys.sysauth$ s
   s.grantee# = PRIOR s.privilege#
         s.privilege# > 0
      OR s.privilege# =-352
         s.privilege# > 0
      OR s.privilege# =-352
AND s.grantee# IN (
         sys.codeauth$ c1
         c1.obj# =:1
   sys.codeauth$ c2
   c2.obj# =:2

2 rows selected.

Refer for more info.

Hope this is useful for you as well 🙂

Posted in formatter, sql | Tagged: , , , , | Leave a Comment »

Query for Top 10 by Version Count:

Posted by Sriram Sanka on September 13, 2022

set linesize 100
set pagesize 100
(SELECT substr(sql_text,1,40) sql,
        version_count, executions, hash_value,address
  WHERE version_count > 20
 ORDER BY version_count DESC)
WHERE rownum  <=10

Posted in Uncategorized | Leave a Comment »

Query for Top 10 by Sharable Memory:

Posted by Sriram Sanka on September 13, 2022

set linesize 100
set pagesize 100
(SELECT substr(sql_text,1,40) sql,
        sharable_mem, executions, hash_value,address
  WHERE sharable_mem > 1048576
 ORDER BY sharable_mem DESC)
WHERE rownum  <=10

Posted in Uncategorized | Leave a Comment »

Query for the Top 10 by Parse Calls:

Posted by Sriram Sanka on September 13, 2022

set linesize 100
set pagesize 100
(SELECT substr(sql_text,1,40) sql,
        parse_calls, executions, hash_value,address
  WHERE parse_calls > 1000
 ORDER BY parse_calls DESC)
WHERE rownum  <=10

Posted in Uncategorized | Leave a Comment »

Query for Top 10 by Executions:

Posted by Sriram Sanka on September 13, 2022

set linesize 100
set pagesize 100
(SELECT substr(sql_text,1,40) sql,
        executions, rows_processed, rows_processed/executions "Rows/Exec",
  WHERE executions > 100
 ORDER BY executions DESC)
WHERE rownum  <=10

Posted in Performance, sql | Tagged: , | Leave a Comment »

Query for Top 10 by Physical Reads:

Posted by Sriram Sanka on September 13, 2022

set linesize 100
set pagesize 100
(SELECT substr(sql_text,1,40) sql,
        disk_reads, executions, disk_reads/executions "Reads/Exec",
  WHERE disk_reads > 1000
 ORDER BY disk_reads DESC)
WHERE rownum  <=10

Posted in Performance, sql | Tagged: , , | Leave a Comment »

Query for Top 10 by Buffer Gets:

Posted by Sriram Sanka on September 12, 2022

set linesize 100
set pagesize 100
(SELECT substr(sql_text,1,40) sql,
        buffer_gets, executions, buffer_gets/executions "Gets/Exec",
  WHERE buffer_gets > 10000
 ORDER BY buffer_gets DESC)
WHERE rownum <=10

Posted in Buffer Gets, sql | Tagged: , , | Leave a Comment »

Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more .... ¯\_(ツ)_/¯

Thinking Out Loud

Michael T. Dinh, Oracle DBA

Notes On Oracle

by Mehmet Eser

Oracle Diagnostician

Performance troubleshooting as exact science


get sum oracle stuffs

Data Warehousing with Oracle

Dani Schnider's Blog


Oracle DBA's Daily Work

Welcome everyone!! The idea of this blog is to help the DBA in their daily tasks. Enjoy.

Anand's Data Stories

Learn. Share. Repeat.

Tanel Poder's blog: Core IT for geeks and pros

Oracle Performance Tuning, Troubleshooting, Internals

Yet Another OCM

Journey as an Oracle Certified Master

Sharing Knowledge is ultimate key to Gaining knowledge...

Neil Chandler's DB Blog

A resource for Database Professionals

DBA Kevlar

Tips, tricks, (and maybe a few rants) so more DBA's become bulletproof!

OraExpert Academy

Consulting and Training

%d bloggers like this: