How to monitor the execution status of sqlldr with the pv command

About sqlldr

Oracle's sqlldr utility is used when loading a file into a table sqlldr outputs the following progress to the standard output, so you can check the execution status. However, if the number of loads is large, it is not possible to know how long it will take to complete the load.

SQL*Loader: Release 12.1.0.2.0 -Production on Thu January 23 14:36:14 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Use path:Conventional type
You have reached the commit point.-Number of logical records 64
You have reached the commit point.-Number of logical records 128
You have reached the commit point.-Number of logical records 192
You have reached the commit point.-Number of logical records 256
You have reached the commit point.-Number of logical records 320
You have reached the commit point.-Number of logical records 384
You have reached the commit point.-Number of logical records 448
You have reached the commit point.-Number of logical records 512
You have reached the commit point.-Number of logical records 576

So I wondered if I could use the Linux pv command to monitor the execution status.

About the pv command

Pv command is available on Linux The pv command is a handy tool that runs between piped commands and displays the current status and estimated time to completion from the total amount of data and the amount of data that has passed through the pipe.

For example, if you want to compress a large file with the gzip command

$ gzip bigfile

Will create a compressed bigfile.gz However, it doesn't output any message during execution, so I don't know when it will end.

So, using the pv command,

$ pv bigfile | gzip -c > bigfile.gz

When you execute

$ pv bigfile | gzip -c > bigfile.gz
129MB 0:00:15 [92.8MB/s] [===========>                       ] 36% ETA 0:00:31

The total amount, elapsed time, passing amount per second, current execution status (gauge and%), remaining time are displayed as

As you can see, pv is very convenient, but it monitors the amount of data that passes through the pipe, so it can only support piped command formats.

Connect sqlldr and pv

As mentioned earlier, sqlldr is currently writing to standard output how many loads it has loaded. If only this number of cases can be taken out and piped to pv, it is quite so.

The number of records is sufficient, not the number of bytes to load, as long as you know the execution status. So, let's create a shell script that executes a simple awk, read the standard output of sqlldr, and output the number of characters equivalent to the number of cases.

loadcount


#!/bin/bash
awk -F'number' '
BEGIN { SV = 0; }
NF==2{
    for (i = 0; i < $2 - SV; i++) {
        printf("1");
    }
    SV = $2;
}'

This script needs to be executable, so give it execute permission after saving it in the editor

$ chmod +x loadcount

The processing contents are as follows -When the number of fields (NF) is 2 (= when the number of cases is yyy) with the character string "number of cases" as a delimiter, the "number of cases" Extract the trailing string ($ 2) -The 1-byte character "1" is output for the number of differences from the number of items retrieved last time. In other words, when sqlldr loads 1000 records, 1000 "1" s are output.

If the input is a pipe, the pv command does not know the total amount, in which case you can specify the total amount with the -s option. In this case, the total amount is the number of records in the file to be loaded. The number of records (number of lines) can be obtained using the wc command.

$ wc -l File name

The final form is as follows

$ sqlldr USER/PASS@SID control=t1.ctl | ./loadcount | pv -p -t -e -s `cat t1.csv|wc -l` > /dev/null
0:00:20 [========>                                            ] 17% ETA 0:01:33

-USER / PASS @ SID is a descriptor for connecting to Oracle -T1.ctl is an input control file for sqlldr -T1.csv is the data file to be loaded

I was able to successfully monitor the execution status of sqlldr

Recommended Posts

How to monitor the execution status of sqlldr with the pv command
How to pass the execution result of a shell command in a list in Python
How to delete the specified string with the sed command! !! !!
Check the memory status of the server with the Linux free command
Check the operating status of the server with the Linux top command
How to pass the execution result of a shell command in a list in Python (non-blocking version)
[sh] How to store the command execution result in a variable
How to get the ID of Type2Tag NXP NTAG213 with nfcpy
[EC2] How to install chrome and the contents of each command
How to check the version of Django
[Linux] How to use the echo command
How to calculate Use% of df command
How to use the Linux grep command
How to use CUT command (with sample)
How to crop the lower right part of the image with Python OpenCV
How to find out the number of CPUs without using the sar command
[Introduction to Python] How to sort the contents of a list efficiently with list sort
[Image recognition] How to read the result of automatic annotation with VoTT
How to find the area of the Voronoi diagram
Prepare the execution environment of Python3 with Docker
How to specify the NIC to scan with amazon-dash
How to try the friends-of-friends algorithm with pyfof
How to specify attributes with Mock of python
How to implement "named_scope" of RubyOnRails with Django
How to measure execution time with Python Part 1
How to Learn Kaldi with the JUST Corpus
From the introduction of pyethapp to the execution of contract
How to measure execution time with Python Part 2
A memorandum of how to execute the! Sudo magic command in Jupyter Notebook
Checklist on how to avoid turning the elements of numpy's array with for
Note: How to get the last day of the month with python (added the first day of the month)
The story of making soracom_exporter (I tried to monitor SORACOM Air with Prometheus)
How to get a list of files in the same directory with python
[Introduction to Python] How to get the index of data with a for statement
How to return to the command from the state where you can not enter interactive mode with python of git bash
How to know the port number of the xinetd service
How to write a GUI using the maya command
How to get the number of digits in Python
I want to grep the execution result of strace
[Python] How to specify the download location with youtube-dl
How to start the code written in Atom with one command without starting teminal
How to get started with Visual Studio Online ~ The end of the environment construction era ~
Linux user addition, how to use the useradd command
How to output CSV of multi-line header with pandas
How to use the grep command and frequent samples
Try to get the contents of Word with Golang
[Blender] How to dynamically set the selection of EnumProperty
Preparing the execution environment of PyTorch with Docker November 2019
How to infer MAP estimate of HMM with PyStruct
[Python] Summary of how to specify the color of the figure
How to infer MAP estimate of HMM with OpenGM
How to hit the document of Magic Function (Line Magic)
How to access the global variable of the imported module
A memo on how to overcome the difficult problem of capturing FX with AI
[Python] How to rewrite the table style with python-pptx [python-pptx]
Get the operation status of JR West with Python
How to count the number of occurrences of each element in the list in Python with weight
Visualize the appreciation status of art works with OpenCV
How to learn structured SVM of ChainCRF with PyStruct
(Remember quickly) How to use the LINUX command line
linux / c> link> Get the execution result of the shell command in the C program> I was taught how to use popen ()