Oracle binaries or patch download from Linux command line

By Brian Fitzgerald

Introduction

Our objective is to download Oracle binaries or patches directly to Linux using the bash shell. For binaries, we will log in to Oracle and accept the license agreement in a Windows browser, and then perform the download in Linux. For patches, we will log in to MOS. The technique is useful for cloud or on-prem.

Linux preparations

Complete the following preparations.

Check your Linux version

$ uname -r
3.10.0-1062.9.1.el7.x86_64

wget download from oracle may fail on Red Hat Enterprise Linux el5 or lower.

Check your ssl version

$ openssl version
OpenSSL 1.0.2k-fips 26 Jan 2017

wget download from oracle may fail on openssl version 0.9 or lower.

Install wget

If necessary, install wget

sudo -i -u root
yum -y install wget

cd to your download area:

Redirect cat standard output to a file.

[grid@ora19c ~]$ mkdir download
[grid@ora19c ~]$ cd download/
[grid@ora19c download]$ cat > cook.txt

Let the cat session wait there.

Windows preparations

browser

Chrome is used in this example.

cookie plugin

  • Install the EditThisCookie plugin.
  • Right click EditThisCookie and navigate to Options
  • For export format, select “Netscape HTTP Cookie File”

cook

other browsers

Export methods exist for other browsers. For example, in Firefox, you may use “Export Cookies” by Rotem Dan. Also, you may try your browser’s builtin inspection tools.

binaries download page

  • Login to oracle.com with your ID and password.
  • Navigate to the download page. For example:

https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html

Export cookies

Complete the cookie export and start the download within 30 minutes:

  • From Chrome click the download icon.
  • When prompted, accept the license agreement.
  • Click the large download button.

accept

  • Interrupt the download.
  • Click the EditThisCookie icon
  • In the EditThisCookie, clear the search box,
  • Enter “oracle.com”
  • Using the scrollbar, check that these cookies are present:
domain name
.oracle.com oraclelicense
edelivery.oracle.com OAMAuthnCookie_edelivery.oracle.com:443

export   oamauth

  • If OAMAuthnCookie_edelivery.oracle.com:443 is missing, it could be because:
    • you did not start the download
    • you did not search oracle.com
  • Click the export button. Note the confirmation message:

exported

  • Paste the cookies into your Linux cat session.
  • Type ctrl-d (end of file) into the cat session.
  • Check Linux cookies file cook.txt with egrep:
[grid@ora19c download]$ egrep 'oraclelicense|OAMAuthnCookie_edelivery' cook.txt
.oracle.com     TRUE    /       FALSE   1566398899      oraclelicense   152015
edelivery.oracle.com    FALSE   /       TRUE    0       OAMAuthnCookie_edelivery.oracle.com:443 0ba9acafe65e911747b1cc43daf2bb6269988c0b%7EbOV%2F2lmNMEtBQ0AQibaanasshtknkgx1dWreuPjqkvXEFXKl3n3wj1zmKsG1lkM56uv%2F2cWPSSrH3HRWYEsWaDh2eGTSS8fizlEZF01NQdIF6hEG8rp5qkdvQv3twKXCO%2BpAgxiixhlxb61xlMvNDiLiv8JpDY97yvTSf1QyDxuY7Fjs03Qb0Zaq6i88NPsqkq0gu1C8fEbwEZbgW55YAUVQQdKfOpN4yR2iXenEJfiiKmHTCISZRvPNzgPf0JxPK3fVnvLsAO9HEFBsu%2Fwx0uUsYqe%2FHPJJwkFjBLudqkD8bO84t7HTCED80o%2BrxzcOJ%2BE%2BuvlaqItiidQ7asFuHQanPUS10CeTEXXWzK3h4Lni4Gp29wKtnQ6ADJfPET%2FCnKehcnEsB9lYeQliSvdhWkRD9v3Azr5SYkhEdsDCF4z%2FWXP1dOT6gB9oQVn1352FcJbnA8Zo6ulnXPhKdwJE21SiskmOAelR0GVvA9M3DNU%2FokD1s2Msy293j9goob%2Fpj0IvtsTyuxOdRc1p4gV1XyZbulYn8q1q6Fnu7SpEq7hxiKh6TjY1737uh2w%2FTO3G%2F1QviFlrG%2BK1Ts76KNp45lYwefYA7U29v9W4H4gJ4Cs%3D

Download

In Linux, start the download with wget:

[grid@ora19c download]$ wget --load-cookies=cook.txt 'https://download.oracle.com/otn/linux/oracle19c/190000/LINUX.X64_193000_grid_home.zip'

Downloading patches from MOS

The procedure for downloading patches from My Oracle Support is simpler because you don’t have to acknowledge the license agreement and then start and interrupt the download. However, MOS download links contain the “&” and “?” characters, so you need to quote the URL.

To download a patch from MOS:

  • Login to MOS
  • open EditThisCookie
  • clear the URL window and enter “oracle.com”
  • click the export button
  • In your shell, issue cat > /tmp/cook.txt
  • Paste your shell into the cat
  • Type ctrl-D
  • navigate to the patch download link. For example:

dl.patch

  • hover your mouse over the download button and right-click
  • select “Copy link address”
  • paste the link into your shell between quotes, such as in this example:
wget --load-cookies=/tmp/cook.txt ''
  • after paste:
 wget --load-cookies /tmp/cook.txt 'https://updates.oracle.com/Orion/Services/download/p30501926_180000_Linux-x86-64.zip?aru=23242022&patch_file=p30501926_180000_Linux-x86-64.zip'
  • You can specify the output file using the “-O” option. i.e.
-O p30501926_180000_Linux-x86-64.zip
  • Putting it all together, you get:
wget --load-cookies=/tmp/cook.txt 'https://updates.oracle.com/Orion/Services/download/p30501926_180000_Linux-x86-64.zip?aru=23242022&patch_file=p30501926_180000_Linux-x86-64.zip' -O p30501926_180000_Linux-x86-64.zip
  • Press enter to start the download

Cookies file check

For MOS downloads, there is only one required cookie, namely Oracle_updates_auth

grep Oracle_updates_auth  /tmp/cook.txt
.updates.oracle.com     TRUE    /       FALSE   1582699648      Oracle_updates_auth     ZDA1YzU4OThkODUyM2Y3NjgzZWUxYmNhNGQzMDFjN2UtNTM2MTZjNzQ2NTY0NWY1ZmY5ZDVkMDZmZGZkMWQyMzBkY2FmZGI5NDdlOWE3NWVkMWY1NWVjYjYzZmE4ZDc3ZjAwN2JkYjMzNzViYjAyMmZkZjUzNmRkYWE3OWRiYzg4NDIzY2JlY2JkMzcwMjk5OWQyZDNlMWYzYTAzZTFhYjU
oracle:/ora_local/download/oracle/PSU_PATCHES

Special characters in the URL

You should quote the URL. Otherwise:

  • If ‘&’ appears in the URL, the shell will interpret the string to the right of the ‘&’ as a separate command.
  • If ‘?’ appears in the URL, the shell may interpret it as a metacharacter.

An example of this problem while downloading a Release Update from MOS:

wget --load-cookies /tmp/cook.txt https://updates.oracle.com/Orion/Services/download/p30501926_180000_Linux-x86-64.zip?aru=23242022&patch_file=p30501926_180000_Linux-x86-64.zip -O p30501926_180000_Linux-x86-64.zip
[1] 13367
--2020-02-25 14:15:12--  https://updates.oracle.com/Orion/Services/download/p30501926_180000_Linux-x86-64.zip?aru=23242022
bash: -O: command not found...
Resolving updates.oracle.com (updates.oracle.com)... oracle:/ora_local/download/oracle/PSU_PATCHES
=>141.146.44.51
Connecting to updates.oracle.com (updates.oracle.com)|141.146.44.51|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://aru-akam.oracle.com/adcarurepos/vol/patch14/PLATFORM/CORE/Linux-x86-64/R600000000071008/p30501926_180000_Linux-x86-64.zip?FilePath=/adcarurepos/vol/patch14/PLATFORM/CORE/Linux-x86-64/R600000000071008/p30501926_180000_Linux-x86-64.zip&File=p30501926_180000_Linux-x86-64.zip&params=UGw0Nnppc2RUZnBGTkx2aUEraURsUTphcnU9MjMyNDIwMjImZW1haWw9YmYyMTkwQG55dS5lZHUmZmlsZV9pZD0xMDY0NzI4MTYmcGF0Y2hfZmlsZT1wMzA1MDE5MjZfMTgwMDAwX0xpbnV4LXg4Ni02NC56aXAmdXNlcmlkPW8tYmYyMTkwQG55dS5lZHUmc2l6ZT0xMTQ4Mjg4MjMmY29udGV4dD1BQDEwK0hAYWFydXZtdHAwNy5vcmFjbGUuY29tK1BAJmRvd25sb2FkX2lkPTQxMjI3Mzc2Mw@@&AuthParam=1582658233_7e9698372721c6852f3ca053d3a8e443 [following]
--2020-02-25 14:15:13--  https://aru-akam.oracle.com/adcarurepos/vol/patch14/PLATFORM/CORE/Linux-x86-64/R600000000071008/p30501926_180000_Linux-x86-64.zip?FilePath=/adcarurepos/vol/patch14/PLATFORM/CORE/Linux-x86-64/R600000000071008/p30501926_180000_Linux-x86-64.zip&File=p30501926_180000_Linux-x86-64.zip&params=UGw0Nnppc2RUZnBGTkx2aUEraURsUTphcnU9MjMyNDIwMjImZW1haWw9YmYyMTkwQG55dS5lZHUmZmlsZV9pZD0xMDY0NzI4MTYmcGF0Y2hfZmlsZT1wMzA1MDE5MjZfMTgwMDAwX0xpbnV4LXg4Ni02NC56aXAmdXNlcmlkPW8tYmYyMTkwQG55dS5lZHUmc2l6ZT0xMTQ4Mjg4MjMmY29udGV4dD1BQDEwK0hAYWFydXZtdHAwNy5vcmFjbGUuY29tK1BAJmRvd25sb2FkX2lkPTQxMjI3Mzc2Mw@@&AuthParam=1582658233_7e9698372721c6852f3ca053d3a8e443
Resolving aru-akam.oracle.com (aru-akam.oracle.com)... 104.124.100.114
Connecting to aru-akam.oracle.com (aru-akam.oracle.com)|104.124.100.114|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 114828823 (110M) [application/zip]
Saving to: ‘p30501926_180000_Linux-x86-64.zip?aru=23242022’

100%[=========================================================================================================================================================================>] 114,828,823 42.1MB/s   in 2.6s

2020-02-25 14:15:15 (42.1 MB/s) - ‘p30501926_180000_Linux-x86-64.zip?aru=23242022’ saved [114828823/114828823]

Notice that:

  • the shell interpreted
patch_file=p30501926_180000_Linux-x86-64.zip -O

as a command and printed this error:

bash: -O: command not found...
  • The file got saved as ‘p30501926_180000_Linux-x86-64.zip?aru=23242022’

Solution:
Quote the URL.

 wget --load-cookies /tmp/cook.txt 'https://updates.oracle.com/Orion/Services/download/p30501926_180000_Linux-x86-64.zip?aru=23242022&patch_file=p30501926_180000_Linux-x86-64.zip'

Special characters in the output file name

You may find that wget saves the file to an unexpected name. For example:

ls
p30501926_180000_Linux-x86-64.zip?aru=23242022&patch_file=p30501926_180000_Linux-x86-64.zip

Solution:

Specify the output file using “-O”, as previously explained.

Summary

Using Chrome, you can log in to Oracle and accept the license agreement. Next, using EditThisCookie, you can export the needed cookies. Finally, using wget and the –load-cookies option, you can start your download.

The possible benefit is that the download will run faster on the Linux  server than on your Windows host. Also, direct download saves you an additional transfer step from Windows.

X windows for Oracle DBAs

By Brian Fitzgerald

Introduction

This is for DBAs on who want to use Oracle X-windows based tools such as OUI or DBCA. Techniques described here can lead to a smooth installation experience. Beyond just a cookbook, this blog article also demonstrates the principles behind X forwarding. Red Hat Linux on AWS EC2 is presented here as an example platform.

In hurry? Skip to tl;dr, at the end.

X-windows

The X windows system consists of a server, a display, and clients. The X server accepts requests for graphical output from the clients and renders those requests on the display.  In older usage, the server and the client processes usually ran on the same host, a desktop Unix system. In the present context, the X server runs on your Windows PC and displays to your screen. The X server usually listens on TCP port 6000.

In X-windows lingo, applications and utilities are called clients. In the present context, clients run on the Oracle host system, an instance of AWS EC2. Examples of clients include xterm and Oracle Universal Installer (OUI), which run on separate server hardware. Our objective, therefore, is to have  an Oracle installation tool, such as OUI, and connect back to the X server on your PC so you can view and control the graphical user interface (GUI). Paradoxically, the X server does not run on “server” hardware, but on your windows PC. This table should clear up any confusion.

Description X Server X Client
Hardware PC server
Operating system Windows Linux
Has display monitor yes no
X server port (default 6000) binds
listens
accepts connections
connects to
Software Xming xterm, dbca, etc.

The client application connects to the X server via sshd on Linux and via PuTTY on your PC.

AWS EC2

Red Hat Linux on Amazon EC2 is presented as an example platform. AWS EC2 instances are built from an available Amazon Machine Image (AMI). For an Oracle Database installation, be sure to choose an AMI with a supported processor type, such as Intel x86_64 (not ARM), and a supported operating system, such as Red Hat (not Ubuntu or Amazon Linux).

The final step in EC2 launch is to select an existing key pair or create and download a new key pair. A key pair file from AWS has extension .pem.

Linux packages

Linux package installs are required before you can run X clients. In addition, it is helpful to install basic troubleshooting tools.

After you launch a new EC2 instance, run yum update.

# yum -y update

Running yum update may get you a few new tools, such as lsipc and lsmem.

Depending on the AMI, you may find that basic Linux tools are missing. Installing those tools makes troubleshooting easier. For example:

# yum -y install nc lsof unzip strace traceroute

You want to set up a working X windows environment, but before you do, notice three things from the login shell:

[ec2-user@ip-172-31-89-75 ~]$ echo $DISPLAY

[ec2-user@ip-172-31-89-75 ~]$ netstat -ntl | grep :60..
  1. The DISPLAY environment variable is not set
  2. There is no listener in the 6000 range
  3. No file .Xauthority is present

Now install the X windows packages.

# yum -y install motif xorg-x11-utils xterm xauth

xterm here is for testing X windows.

In /etc/ssh/sshd_config, check:

X11Forwarding yes
X11UseLocalhost no

If necessary, edit /etc/ssh/sshd_config and then issue:

service sshd restart

After installing xauth, login using a new PuTTY session. On the first login, the following output will appear:

/usr/bin/xauth: file /home/ec2-user/.Xauthority does not exist

Now recheck:

[ec2-user@ip-172-31-89-75 ~]$ echo $DISPLAY
localhost:10.0
[ec2-user@ip-172-31-89-75 ~]$ netstat -ntl | grep :60..
tcp        0      0 127.0.0.1:6010          0.0.0.0:*               LISTEN
tcp6       0      0 ::1:6010                :::*                    LISTEN
  1. The DISPLAY environment variable is set
  2. There is a new listener in the 6000 range.
  3. A new .Xauthority file exists

The “10” in DISPLAY refers to the offset from 6000. When you set your DISPLAY to localhost:10.0, all X clients will connect to port 6010 by default.

Notice which process is listening;

[root@ip-172-31-88-44 ~]# lsof -Pi :6010
COMMAND  PID     USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
sshd    1351 ec2-user    9u  IPv6  20649      0t0  TCP localhost:6010 (LISTEN)
sshd    1351 ec2-user   10u  IPv4  20650      0t0  TCP localhost:6010 (LISTEN)

The sshd process is listening on port 6010. You are going to run X-client application on the Linux box. The client will connect to port 6010. The sshd process will forward the X-requests to your PC.

X server

There are several MS Windows-based X-servers available, Xming, for example. By default, the X server listens on port 6000. Confirmation:

C:\>netstat -na | findstr :6000 | findstr LISTEN
TCP 0.0.0.0:6000 0.0.0.0:0 LISTENING

X server issues

Can’t load font

A message such as this appears

xterm: cannot load font -misc-fixed-medium-r-semicondensed--13-120-75-75-c-60-iso10646-1

Solution:

If you are using Xming, then install Xming fonts. For example: Xming-fonts-7-7-0-10-setup.exe

xdpyinfo

Behind the scenes, OUI runs xdpyinfo. If xdpyinfo is missing, this message will appear:

ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyinfo exist under PATH variable.

To get xdpyinfo, install package xorg-x11-utils. An example of normal OUI output is:

Checking monitor: must be configured to display at least 256 colors.   Actual 16777216    Passed

Warning: No xauth data; using fake authentication data for X11 forwarding.

Symptom:

You run ssh -X and observe a warning message such as:

brian@PQHKEA * xauth $ ssh -X -i ~/.ssh/acme-key.pem ec2-user@3.84.232.118
Warning: No xauth data; using fake authentication data for X11 forwarding.
Last login: Sat Nov 7 18:31:30 2020 from pool-98-113-87-97.nycmny.fios.verizon.net

The message is from the local ssh client, not the remote host. You can confirm this by referring to package https://github.com/openssh/openssh-portable, file clientloop.c.

Fix:

xauth add :${DISPLAY#*:} . `mcookie`

Explanation.

:${DISPLAY#*:} gives you the DISPLAY value without the IP address.

mcookie a 128-bit random hexadecimal number for use with the X authority system

xterm issue

message:

Warning: locale not supported by C library, locale unchanged

Solution:

export LC_ALL=C

X libraries

The manual may instruct you to install certain X windows runtime libraries, or OUI may so instruct you. The library list depends on the Oracle product and the version. Example:

yum -y install libX11 libXau libXi libXtst libXrender

If you have installed xterm, then you already have these dependent libraries.

mwm

mwm is the Motif Window Manager. It lets you move and resize windows. Without a window manager, you may encounter situations where you have a dialog box waiting for input but is inaccessible because it is behind another window. Here is the X root window after dragging three windows to non-overlapping locations.

mwm

The shell commands leading up to this were:

xterm & disown
mwm & disown
unzip -q ~/download/LINUX.X64_193000_db_home.zip
./runInstaller

Network

The X windows display technique described here assumes that you can connect via the secure shell (ssh) from your PC directly to the Oracle server host. The AWS EC2 instance has a Public IP address that is reachable from your PC.

The X windows protocol is a very “chatty” protocol, meaning that clients make a high number of small server requests. Chattiness makes every button press on the GUI respond very slowly. Having a fast network connection is essential. One might give some thought to enabling SSH compression; however, SSH compression does not mitigate the chattiness.

putty.compression

Enabling SSH compression will usually not result in a noticeable improvement in responsiveness.

Running an X windows client across sudo

In a prior section, xterm worked fine from the login shell (ec2-user). In practice, you will sudo to the oracle account to execute runInstaller or dbca. You may also sudo to the grid account to run gridSetup.sh. First, test xterm from the login account:

[ec2-user@ip-172-31-82-194 ~]$ echo $DISPLAY
localhost:11.0
[ec2-user@ip-172-31-82-194 ~]$ xterm
(it works)

One thing you will notice here in this case is that the offset in the DISPLAY variable is 11. The offset is not always 10, and depends on what port is available for X forwarding when you log in.

[ec2-user@ip-172-31-82-194 ~]$ netstat -ntl | grep :60..
tcp        0      0 127.0.0.1:6010          0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:6011          0.0.0.0:*               LISTEN
tcp6       0      0 ::1:6010                :::*                    LISTEN
tcp6       0      0 ::1:6011                :::*                    LISTEN

In this example, port 6010 was is in use when I logged in, so sshd started listening on port 6011 and set offset to 11.

If you try to run an X client across a sudo, you will get this error:

[ec2-user@ip-172-31-82-194 ~]$ sudo -i -u oracle
[oracle@ip-172-31-82-194 ~]$ xterm
xterm: Xt error: Can't open display:
xterm: DISPLAY is not set
[oracle@ip-172-31-82-194 ~]$ echo $DISPLAY

The DISPLAY environment setting did not carry over to the sudo shell. You can try setting DISPLAY, but you will get a different error:

[oracle@ip-172-31-82-194 ~]$ export DISPLAY=localhost:11.0
[oracle@ip-172-31-82-194 ~]$ xterm
PuTTY X11 proxy: wrong authorisation protocol attemptedError: Can't open display: localhost:11.0

To fix the authorization error, copy .Xauthority from the login account to the oracle account:

[ec2-user@ip-172-31-82-194 ~]$ sudo cp -up ~/.Xauthority ~oracle/.Xauthority
[ec2-user@ip-172-31-82-194 ~]$ sudo chown oracle:` id -gn oracle ` ~oracle/.Xauthority
[ec2-user@ip-172-31-82-194 ~]$ sudo -i -u oracle
[oracle@ip-172-31-82-194 ~]$ xterm

To automate the copying, I added this code to the login user .bash_profile:

if [ -z "$SUDO_USER" -a -f ~/.Xauthority ] ; then
    for u in oracle grid ; do
        if id $u > /dev/null 2>&1 ; then
            eval hd=~$u
            sudo cp -up ~/.Xauthority $hd/.Xauthority
            sudo chown $u:$(id -gn $u) $hd/.Xauthority
        fi
    done
fi

To automate setting DISPLAY, I added this code to the oracle .bash_profile:

if [ -z "$DISPLAY" ] ; then
    for port in {6010..6019} ; do
        if netstat -ntl | grep -q :$port ; then
            offset=$(( port - 6000 ))
            export DISPLAY=localhost:$offset.0
            break
        fi
    done
fi

To summarize, before sudo, copy .Xauthority from the login account to oracle. After sudo, set the DISPLAY environment variable. Automate those settings in .bash_profile.

puttygen

To connect PuTTY from Windows to AWS EC2 you need to generate a Putty Private Key (PPK) file. Start with the PEM (Privacy Enhanced Mail) file that you downloaded when you created your EC2 instance. Startup PuTTY Key Generator (puttygen). Load the PEM file. Click Save private key. Provide a name. Example:

us-east-1-key.pem
us-east-1-key.ppk

puttygen

X forwarding with PuTTY

To establish a session with PuTTY, navigate the PuTTY screens and make these settings.

Screen Item Example
Session Host Name (or IP address) 3.86.243.42
Connection->Data Auto-login username ec2-user
Connection->SSH->Auth Private key file for authorization C:\Users\Brian Fitzgerald\.aws\us-east-1-key.ppk
Connection->SSH->X11 Enable X11 forwarding checked
Connection->SSH->X11 X display location localhost:0.0

putty.host

You may save your settings for future reuse.

putty.user

ppk.png

The private key file is the ppk file that you converted from the pem file.

putty.x11

X display location refers to the X server on your PC, and is usually localhost:0.0.

A simple command line example

Instead of using PuTTy, you could use ssh. This example uses bash on Windows Subsystem for Linux. The hypothetical hosts are lcl and rem.

Generate your key

lcl $ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/home/bf/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/bf/.ssh/id_rsa.
Your public key has been saved in /home/bf/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:Jb9cfmEZI8r1FNe611gM0dBLYInq+/CXs35NPpSlB88 bf@lcl
The key's randomart image is:
+---[RSA 2048]----+
|            .o*=o|
|           ....++|
|        . o o =+.|
|         * o +o=+|
|        S + . =O+|
|         o + .++E|
|         .+ . +=.|
|         .o  = oo|
|          .ooo+ .|
+----[SHA256]-----+

Copy your key to remote

lcl $ ssh-copy-id rem
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/home/bf/.ssh/id_rsa.pub"
The authenticity of host 'rem (10.130.33.179)' can't be established.
ECDSA key fingerprint is SHA256:Gr8YlEJrgTQdsNMGXOZoU+6ugg4TPyX5B9lCLTE0g/8.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys


bf@rem's password:

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'rem'"
and check to make sure that only the key(s) you wanted were added.

Set your DISPLAY

Startup Xming, then run:

lcl $ export DISPLAY=127.0.0.1:0.0

ssh using the -X flag

lcl $ ssh -X rem
/usr/bin/xauth:  file /home/bf/.Xauthority does not exist
rem $

test

rem $ xdpyinfo | wc
    287    1136    9544

permit your .Xauthority file

rem $ chmod 750 ~
rem $ chmod 640 ~/.Xauthority

sudo to oracle

rem $ sudo su - oracle
[sudo] password for bf:

copy .Xauthority

oracle@rem $ cp -p ~bf/.Xauthority .Xauthority

set your DISPLAY

oracle@rem $ export DISPLAY=10.130.33.179:10.0

test

oracle@rem $ xdpyinfo | wc
    287    1136    9544

Oracle Universal Installer

oracle@rem $ cd /u01/download/oracle/em13400/
oracle@rem $ ./em13400_linux64.bin -J-Djava.io.tmpdir=/u01/tmp

oui.em

Notes

Display

rem $ echo $DISPLAY
10.130.33.179:10.0

Notice :10.0, not :0.0

Ports

rem $ netstat -nat | grep :60
tcp        0      0 0.0.0.0:6010            0.0.0.0:*               LISTEN
tcp6       0      0 :::6010                 :::*

Notice 6010, not 6000

File

rem $ ls -l .Xauthority
-rw-------. 1 bf dba 100 Jul  6 17:41 .Xauthority

X forwarding via bastion host

You can use ssh from the Windows command line to setup X forwarding via a tunnel at the bastion. Identify the following items:

item example
bastion key file us-east-1-key.pem
arbitrary Windows local port 3333
oracle box private IP address 172.31.95.14
ssh port 22
bastion host user brian
bastion public IP address 52.90.61.241

and use them as follows:

ssh -i keyfile -C -N -L localport:oraclebox:22 bastionuser@bastionhost

For example:

C:\>ssh -i us-east-1-key.pem -N -L 3333:172.31.95.14:22 ec2-user@52.90.61.241
  • The bastion key file is the pem file that you downloaded when you created the EC2 instance
  • The local port is a free port on your PC that you will choose arbitrarily.
  • The oracle box is the private IP address or the oracle box that you want to ssh to.
  • Bastion user is your login on the bastion host, i.e. ec2-user.
  • bastionhost is the public IP address of your bastion host.

Next, in a second cmd window:

set DISPLAY=localhost:0.0
ssh -i us-east-1-key.pem -Y -p 3333 ec2-user@localhost
Warning: No xauth data; using fake authentication data for X11 forwarding.
Last login: Tue Nov 19 02:50:02 2019 from ip-172-31-88-110.ec2.internal
Last login: Tue Nov 19 02:50:02 2019 from ip-172-31-88-110.ec2.internal
ec2-user@ip-172-31-95-14 ~$ xterm

The requirements for the bastion host are different from the oracle box.

  • Establishing ssh trust from the bastion to the oracle box is not required.
  • The tunneling session is not responsible for X forwarding. Option -X is not required.
  • Disabling X11Forwarding at the bastion does not disable X forwarding as described here because the X11 forwarding happens at the Oracle host and in PuTTY.
  • The bastion login user does not have to be the same as the oracle box login user.
  • The bastion host does not have to be the same hardware or operating system architecture as the oracle box. Amazon Linux works.
  • In some bastion EC2 systems, no configuration is required. ssh tunneling works out of the box.
  • If the bastion /etc/ssh/sshd_config has “AllowTcpForwarding no”, then ssh tunneling is disabled.

You do not need to log on to the bastion with an interactive shell. After the tunneling session is established, you will not interact with it.

X windows without X11 forwarding

If you have a connection from Linux to the X11 port, usually 6000, on your PC, then you can attempt X windows without X11 forwarding. You are going to set DISPLAY=pc.dnsname.or.ipaddress:6000

Verify port 6000 with:

C:\>netstat -na | findstr LISTEN | findstr :6000
TCP 0.0.0.0:6000 0.0.0.0:0 LISTENING

“0.0.0.0:6000” refers to listening on port 6000 on all IP addresses on all network interfaces. If you see

 TCP 127.0.0.1:6000 0.0.0.0:0 LISTENING

then your X server will not accept connections from outside your PC.

You can find your IP address with:

C:\>ipconfig | findstr Address
Link-local IPv6 Address . . . . . : fe80::f9f0:5830:cd8e:7502%24
IPv4 Address. . . . . . . . . . . : 192.168.1.7
Link-local IPv6 Address . . . . . : fe80::85d7:7339:ff49:e5e6%51
IPv4 Address. . . . . . . . . . . : 192.168.74.113

The addresses shown in this example are private IP addresses. If the oracle box and the PC are not on the same private network, you cannot refer to this address.

To get the NATted IP address you can use netstat:

[ec2-user@ip-172-31-86-22 ~]$ netstat -nat | grep :22 | grep ESTABLISHED
tcp 0 64 172.31.86.22:22 74.101.107.146:49459 ESTABLISHED
tcp 0 0 172.31.86.22:22 74.101.107.146:54036 ESTABLISHED

or last:

[ec2-user@ip-172-31-86-22 ~]$ last -1ai ec2-user
ec2-user pts/1 Tue Aug 27 09:05 still logged in 74.101.107.146

The PC IP address is, therefore, 74.101.107.146. Instead of the IP address, you may refer to DNS name. The DNS name appears when you login.

Using username "ec2-user".
Authenticating with public key "imported-openssh-key"
Last login: Mon Aug 26 15:57:51 2019 from pool-74-101-107-146.nycmny.fios.verizon.net

or from last:

[ec2-user@ip-172-31-86-22 ~]$ last -1a ec2-user
ec2-user pts/1        Tue Aug 27 09:05   still logged in    pool-74-101-107-146.nycmny.fios.verizon.net

Before attempting runInstaller, test connectivity using nc

[ec2-user@ip-172-31-86-22 ~]$ time nc -v 74.101.107.146 6000
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connection timed out.

real    0m10.029s
user    0m0.012s
sys     0m0.004s

By default, nc times out in 10 seconds.

If the nc connection times out, then an X-windows client will also time out.

[ec2-user@ip-172-31-86-22 ~]$ export DISPLAY=74.101.107.146:0.0
[ec2-user@ip-172-31-86-22 ~]$ time xterm
xterm: Xt error: Can't open display: 74.101.107.146:0.0

real    2m7.366s
user    0m0.021s
sys     0m0.016s

The TCP timeout depends on tcp_syn_retries

[root@ip-172-31-86-22 ~]# cat /proc/sys/net/ipv4/tcp_syn_retries
6

If tcp_syn_retries = 6, then the TCP timeout works out to 127 seconds, calculated thus:

tcp.timeout

You can monitor the connection in a separate window.

[ec2-user@ip-172-31-86-22 ~]$ netstat -nat | grep :6000
tcp        0      1 172.31.86.22:36310      74.101.107.146:6000     SYN_SENT

SYN_SENT lasting for a long time means that the connection is about to time out. runInstaller will take more than twice as long as xterm does

to time out.

[oracle@ip-172-31-86-22 dbhome_1]$ export DISPLAY=74.101.107.146:0.0
[oracle@ip-172-31-86-22 dbhome_1]$ time ./runInstaller
ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyinfo exist under PATH variable.

Can't connect to X11 window server using '74.101.107.146:0.0' as the value of the DISPLAY variable.

real    4m17.524s
user    0m2.835s
sys     0m0.303s

First, xdpyinfo times out:

[ec2-user@ip-172-31-86-22 ~]$ ps -t pts/1
  PID TTY          TIME CMD
 3324 pts/1    00:00:00 bash
11279 pts/1    00:00:00 sudo
11281 pts/1    00:00:00 bash
11311 pts/1    00:00:00 runInstaller
11315 pts/1    00:00:00 xdpyinfo

Then OUI (java) times out:

[ec2-user@ip-172-31-86-22 ~]$ ps -t pts/1
  PID TTY          TIME CMD
 3324 pts/1    00:00:00 bash
11279 pts/1    00:00:00 sudo
11281 pts/1    00:00:00 bash
11311 pts/1    00:00:00 runInstaller
11436 pts/1    00:00:00 perl
11455 pts/1    00:00:01 java

To save time, test your connection using nc, before attempting runInstaller.

dbca issues

dbca NoClassDefFoundError: sun.awt.X11.XToolkit

The DISPLAY environment variable is set, but this messsage appears:

[oracle@ip-172-31-88-246 ~]$ dbca
Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class sun.awt.X11.XToolkit
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:264)
        at java.awt.Toolkit$2.run(Toolkit.java:860)
        at java.awt.Toolkit$2.run(Toolkit.java:855)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.awt.Toolkit.getDefaultToolkit(Toolkit.java:854)
        at com.jgoodies.looks.LookUtils.isLowResolution(LookUtils.java:484)
        at com.jgoodies.looks.LookUtils.(LookUtils.java:249)
        at com.jgoodies.looks.plastic.PlasticLookAndFeel.(PlasticLookAndFeel.java:135)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:348)
        at javax.swing.SwingUtilities.loadSystemClass(SwingUtilities.java:1874)
        at javax.swing.UIManager.setLookAndFeel(UIManager.java:582)
        at oracle.install.commons.util.Application.startup(Application.java:940)
        at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:181)
        at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:198)
        at oracle.assistants.common.base.driver.AssistantApplication.startup(AssistantApplication.java:328)
        at oracle.assistants.dbca.driver.DBConfigurator.startup(DBConfigurator.java:378)
        at oracle.assistants.dbca.driver.DBConfigurator.main(DBConfigurator.java:513)

Cause: The X server is down or not reachable. Troubleshoot as you would an xterm issue, such as:

[oracle@ip-172-31-88-246 ~]$ xterm
xterm: Xt error: Can't open display: localhost:10.0

Client startup step by step

If you follow these steps one at a time and resolve issues as you go, it is less likely that you will encounter difficulty when you try to run an Oracle tool

  • In PuTTY, configure X11 forwarding.
  • Connect PuTTY and log in.
  • Check the DISPLAY environment variable.
  • Test xterm.
  • Test xdpyinfo.
xdpyinfo > /dev/null
  • copy .Xauthority to the oracle account.
  • sudo to oracle.
  • Set the DISPLAY environment variable.
  • Test xterm again.
  • startup mwm.
  • finally, start your Oracle tool. Ex:
./runInstaller

Summary

It is extremely helpful to configure a proper Linux and X windows environment before attempting to run an Oracle tool such as OUI. Commands nc, lsof, strace, traceroute, lsipc, and lsmem were used while writing this blog article. From xorg-x11-utils, xlsclients and xev were used. From motif, mwm was used. xauth is required for X windows. An oracle installation may require unzip and xdpyinfo. Not all needed utilities installed by default. You must install them yourself.

If you Google “x windows forwarding not working” you will find cases where one person’s advice works for one person and not another. Trying random tips and tricks without understanding can lead to your own solutions that don’t work all the time, and enabling unnecessary options. To improve understanding, I have added diagnostic output from netstat and lsof.

Be sure to understand that the X server runs on your PC and listens on port 6000 by default. On the oracle box, sshd listens on port 6000 + an offset, often 10. Setting DISPLAY=localhost:10.0 will lead X clients to connect to the sshd listening on port 6010 on the local host. sshd will forward the X traffic back to the X server on your PC.

It is well to break up the setup and testing into multiple, simple steps, rather than attempting to startup runInstaller in one go.

tl;dr

  • Install needed packages
  • Copy .Xauthority to the oracle account
  • Set the DISPLAY environment variable

 

Notes on private temporary tables

By Brian Fitzgerald

New in 18c, private temporary tables are temporary database objects that are dropped at the end of a transaction or session. Private temporary tables are stored in memory and each one is visible only to the session that created it.

Here are a few findings on private temporary tables (PTT).

Basic operation

Create, insert, and select.

create private temporary table ora$ptt_a
(
 n number
) on commit preserve definition;
insert into ora$ptt_a ( n ) values ( 0 );
select n 
from ora$ptt_a;

Table created.
1 row(s) inserted.

N
0

A PTT cannot be partitioned or index-organized.

Using “on commit drop definition”

Commit drops the PTT if the PTT is declared “on commit drop definition”.

create private temporary table ora$ptt_a
(
n number
) on commit drop definition;
insert into ora$ptt_a ( n ) values ( 0 );
commit;
select n 
from ora$ptt_a;

Table created.
1 row(s) inserted.
Statement processed.
ORA-00942: table or view does not exist

The same applies to rollback:

drop table ora$ptt_a;
create private temporary table ora$ptt_a
(
n number
)
on commit drop definition;
rollback;
select n from ora$ptt_a;
Table dropped.
Table created.
Statement processed.
ORA-00942: table or view does not exist

No commit on create

Creating a PTT does not itself issue a commit, as it would with a conventional table. This fact leads to the primary use case of PTTs:

  • “When an application stores temporary data in transient tables that are populated once, read few times, and then dropped at the end of a transaction or session”

In other words, a PTT can be used as a driving table which you populate once, and then use for multiple queries.

Rollback

Rollback rolls back the insert, but not the create table.

drop table ora$ptt_a;
create private temporary table ora$ptt_a
(
n number
)
on commit preserve definition;
insert into ora$ptt_a ( n ) values ( 4 );
rollback;
select n from ora$ptt_a;

Table dropped.
Table created.
1 row(s) inserted.
Statement processed.
no data found

Alter table

Alter table add column fails with an erroneous ORA-00942:

alter table ora$ptt_a add m number;
ORA-00942: table or view does not exist

ORA-00942 can be taken to mean that Oracle searched for, and did not find a conventional table, and is indicative of a bug.

Views

Views with info about PTTs are:

USER_PRIVATE_TEMP_TABLES

DBA_PRIVATE_TEMP_TABLES

There is no ALL_PRIVATE_TEMP_TABLES.

select * from USER_PRIVATE_TEMP_TABLES;

(results transposed)

SID 2284
SERIAL# 40199
OWNER SQL_RADNMXBEQPEYTXKXEYBLDVRPC
TABLE_NAME ORA$PTT_B
TABLESPACE_NAME TEMP
DURATION SESSION
NUM_ROWS 0
BLOCKS 0
AVG_ROW_LEN 0
LAST_ANALYZED 2/18/2018 23:01
TXN_ID 0
SAVE_POINT_NUM 0

sid, serial# refer to the session that created the PTT.

Parallel

The parallel create option succeeds:

drop table ora$ptt_a;
create private temporary table ora$ptt_a
 (
 d date
 )
 on commit preserve definition 
 parallel 8;

Table dropped. 

Table created.

However, I find the parallel degree nowhere in the catalog.

Altering the parallel degree fails with an erroneous ORA-00942:

alter table ora$ptt_a parallel 4;
ORA-00942: table or view does not exist 

No primary key

You cannot declare a primary key

drop table ora$ptt_a;
create private temporary table ora$ptt_a
(
n number primary key
) 
on commit drop definition;

ORA-14451: unsupported feature with temporary table

You cannot create indexes, defaults, or not null constraints.

Dropping

You can explicitly drop a PTT.

drop table ora$ptt_a;

Table dropped.

Prefix

You can prefix the table name with the owner when referring to it.

drop table SQL_ZZOZIKMDVVNDRUUEJJIJXJMKR.ora$ptt_a;
create private temporary table SQL_ZZOZIKMDVVNDRUUEJJIJXJMKR.ora$ptt_a
on commit preserve definition
as select level n
from dual
connect by level <= 2;

rowid

You can query rowid. dbms_rowid.rowid_object returns a number that is unique per PTT and not in user_objects.

create private temporary table ora$ptt_a
as
select level n from dual connect by level <= 3;

create private temporary table ora$ptt_b
as
select level n from dual connect by level <= 3;
select rowid, n, dbms_rowid.rowid_object(rowid) from ora$ptt_a;

select rowid, n, dbms_rowid.rowid_object(rowid) from ora$ptt_b;
select max(object_id) from user_objects;
Table created.
Table created.
ROWID N DBMS_ROWID.ROWID_OBJECT(ROWID)
AATFyHAABAADFyIAAA 1 5004423
AATFyHAABAADFyIAAB 2 5004423
AATFyHAABAADFyIAAC 3 5004423
ROWID N DBMS_ROWID.ROWID_OBJECT(ROWID)
AATF4HAABAADF4IAAA 1 5004807
AATF4HAABAADF4IAAB 2 5004807
AATF4HAABAADF4IAAC 3 5004807
MAX(OBJECT_ID)
129337

Flashback query

Flashback queries are not allowed on temporary tables. The expected message is:

ORA-30051: VERSIONS clause not allowed here

However flashback query on PTTs fail with an erroneous ORA-00942:

select n from ora$ptt_a
versions between scn 10717996 and 10720679;
ORA-00942: table or view does not exist

Grant

You cannot grant access to a PTT. Grant fails with an erroneous ORA-00942.

grant select on ora$ptt_a to system;
ORA-00942: table or view does not exist

Truncate

You can truncate a PTT. Truncating a PTT does not issue a COMMIT, as it would with a conventional table.

create global temporary table gtt_a
(
n number 
)
on commit delete rows;
create private temporary table ora$ptt_a
(
n number
)
on commit drop definition;
insert into ora$ptt_a
select level from dual 
connect by level <= 10000;
insert into gtt_a
select level from dual 
connect by level < 10000;
select count(*)nptt from ora$ptt_a;
truncate table ORA$PTT_A;
select count(*)nptt from ora$ptt_a;
select count(*)ngtt from gtt_a;
commit;
select count(*)ngtt from gtt_a;

Table created.
Table created.
10000 row(s) inserted.
9999 row(s) inserted.
NPTT
10000
Table truncated.
NPTT
0
NGTT
9999
Statement processed.
NGTT
0

Gather stats

You cannot gather stats on a private temporary table.

begin
dbms_stats.gather_table_stats(null,'ora$ptt_a');
end;
/

ORA-20000: Unable to analyze TABLE "SQL_RADNMXBEQPEYTXKXEYBLDVRPC"."ORA$PTT_A", insufficient privileges or does not exist ORA-06512: at "SYS.DBMS_STATS", line 39094
ORA-06512: at "SYS.DBMS_STATS", line 38371
ORA-06512: at "SYS.DBMS_STATS", line 38530
ORA-06512: at "SYS.DBMS_STATS", line 39076
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SQL", line 1721

You cannot analyze a PTT

analyze table ora$ptt_a estimate statistics;

ORA-00942: table or view does not exist 

PTT statistics normally show 0 rows; however, PTT statistics get populated in the case of create table as select.

create private temporary table ora$ptt_a
as select level n
from dual
connect by level <= 10000;
select num_rows, blocks, avg_row_len
from user_private_temp_tables;
Table created
NUM_ROWS BLOCKS AVG_ROW_LEN
10000 16 4

Metadata

PTTs do not appear in USER_TABLES, USER_SEGMENTS, or USER_OBJECTS.

PTTs do not have an object_id.

No PTT column metadata has been found (so far). However, I would check x$ tables.

Multiple sessions

Multiple sessions by the same user can create a PTT having the same name. The definition and the data are visible only to the creating session.

Name clash

You cannot create a conventional table, or any other object, beginning with “ORA$PTT_”.

create procedure ora$ptt_p is begin null; end;
/

ORA-32463: cannot create an object with a name 
matching private temporary table prefix 

The PTT prefix can be changed using initialization parameter PRIVATE_TEMP_TABLE_PREFIX, but it cannot be modified at the session level.

alter session set PRIVATE_TEMP_TABLE_PREFIX = 'PRIV$TMP_';

ORA-02096: specified initialization parameter is not modifiable 
with this option

To change the PTT prefix at the instance level, issue, for example:

alter system set PRIVATE_TEMP_TABLE_PREFIX = 'PRIV$TMP_' deferred;

Current sessions are unaffected. Future connections will catch the new setting.

PL/SQL

You can use a PTT in an anonymous PL/SQL block

drop table ora$ptt_a;
create private temporary table ora$ptt_a
(
n number
)
on commit preserve definition;
insert into ora$ptt_a ( n ) values ( 7 );
declare
 l_num number;
begin
 select n into l_num from ora$ptt_a;
 dbms_output.put_line('l_num='||l_num);
end;
/
Table dropped.
Table created.
1 row(s) inserted.
l_num=7

A PTT column cannot be used in a type declaration.

drop table ora$ptt_a;
create private temporary table ora$ptt_a
(
n number
)
on commit preserve definition;
declare
 l_num ora$ptt_a.n%type;
begin
 null;
end;
/
Table dropped.
Table created.
ORA-06550: line 2, column 9:
PLS-00201: identifier 'ORA$PTT_A.N' must be declared

You cannot create a procedure that uses a PTT in static PL/SQL.

drop table ora$ptt_a;
create private temporary table ora$ptt_a
(
n number
)
on commit preserve definition;

create or replace procedure pr
as
begin
 insert into ora$ptt_a ( n ) values ( 0 );
end;
/
select line, position, text from user_errors where name = 'PR';

Table dropped. 

Table created.
Error at line: 12
LINE POSITION TEXT
4 14 PL/SQL: ORA-14451: unsupported feature with temporary table
4 2 PL/SQL: SQL Statement ignored

You can write an anonymous PL/SQL block that declares a procedure that uses a PTT.

drop table ora$ptt_a;
create private temporary table ora$ptt_a
(
n number
)
on commit preserve definition;

declare
procedure pr
as
begin
 insert into ora$ptt_a ( n ) values ( 5 );
end pr;
begin
 pr;
end;
/
select n from ora$ptt_a;
Table dropped.
Table created.
1 row(s) inserted.

N
5

Notice the feedback on the insert. I have not seen such feedback in PL/SQL before. This demo was run on Oracle Live SQL.

You can create a package that creates and uses a PTT using dynamic SQL.

drop table ora$ptt_a;
create or replace package ptt_pkg
is
 procedure crptt;
 procedure insptt;
 function pttval
 return number;
end ptt_pkg;
/
create or replace package body ptt_pkg
is
 procedure crptt
 is
 begin
 execute immediate
 q'{create private temporary table ora$ptt_a
 (
 n number
 )
 on commit preserve definition}';
 end crptt;

procedure insptt
 is
 begin
 execute immediate
 q'{insert into ora$ptt_a ( n ) values ( 3 )}';
 end insptt;

function pttval
 return number
 is
 l_num number;
 begin
 execute immediate
 q'{select n
 from ora$ptt_a}' into l_num;
 return l_num;
 end pttval;
end ptt_pkg;
/

declare
 l_num number;
begin
 ptt_pkg.crptt;
 ptt_pkg.insptt;
 l_num := ptt_pkg.pttval;
 dbms_output.put_line('in package l_num='||l_num);
end;
/

Table dropped.
Package created.
Package Body created.
in package l_num=3

An anonymous PL/SQL block will not compile if it refers to an object that does not exist yet. This will not work:

declare
l_num number;
begin
ptt_pkg.crptt;
ptt_pkg.insptt;
select n into l_num
from ora$ptt_a;
end;
/

ORA-06550: line 7, column 6: 
PL/SQL: ORA-00942: table or view does not exist

In-memory

Dan Morgan reports “ORA-14451: unsupported feature with temporary table” in a PDB with inmemory_size set. Please refer to the blog feedback.

Explain plan

You can run explain plan on a statement that uses  PTT.

drop table ora$ptt_a;
create private temporary table ora$ptt_a
 (
 n number
 )
 on commit preserve definition;
explain plan for
select n
from ora$ptt_a;
select * from table ( dbms_xplan.display );
Table dropped.
Table created.
Statement processed.

PLAN_TABLE_OUTPUT
Plan hash value: 2125934360

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS STORAGE FULL| ORA$PTT_A | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Parallel plan

The optimizer can create a parallel plan on a PTT.

drop table ora$ptt_a;
create private temporary table ora$ptt_a
 (
 n number
 )
 on commit preserve definition
 parallel 8;
explain plan for
select n
from ora$ptt_a;
select * from table ( dbms_xplan.display );
Table dropped.
Table created.
Statement processed.

PLAN_TABLE_OUTPUT
Plan hash value: 2895541530

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 13 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1 | 13 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS STORAGE FULL| ORA$PTT_A | 1 | 13 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------

Note
-----
 - dynamic statistics used: dynamic sampling (level=2)
 - Degree of Parallelism is 8 because of table property

Troubleshooting

Query troubleshooting could be impeded by lack of information about the design of the PTT.

Prerequisites

The create table privilege is required to create a PTT. Granting create table permits creating any type of table, not just PTTs. The feature is clearly intended for use in application code at run time. The DBA is faced with a choice: Grant create table to the application run time user, or deny the use of private temporary tables.

Caution on new features

The PTT is a new 18c feature. Users should exercise care and be alert to bugs in PTTs. Bugs tend to appear when multiple lightly used features are combined. Beware of performance, internal error, and corruption bugs. Also, optimization can lead to results errors. Use PTTs when the potential business value outweighs the risk and added testing cost. Exercise conservative practices when using PTTs.

Conclusion

PTTs are a new Oracle Database 18c feature. Before using PTTs it’s a good idea to be aware of the restrictions.