Dynamic Oracle SQL for Searching All Tables in a Schema for Specific Columns and Generating a Union Query

Long title – but it just about sums up what the end result was today.

I had this really annoying stacktrace with an associated exception that pointed to 400 potential tables.

I needed a way to check all those tables that match specific criteria where the length of the string was greater than 2000 characters and output those associated records for further debug analysis.

In this instance, break pointing and code tracing was not an option so I crafted a useful reusable query that can be used many times over for future circumstances.

If you are a user of this query – remember to turn DBMS Output on in Oracle SQL Developer or you will not see the query that is generated so you can copy it into an SQL Worksheet!

DECLARE
    -- Column we are looking for in each table of the schema
    COLUMN_TO_FIND VARCHAR2(256);

    -- Column to include so we can filter results for tables 
    -- that also contain this column as well - aka the table
    -- must contain all three columns
    PRIMARY_KEY_1 VARCHAR2(256);
    PRIMARY_KEY_2 VARCHAR2(256);

    -- Filter results to only the specified schemas so we 
    -- aren't querying system tables or other schemas
    SCHEMA_OWNER VARCHAR2(256);

    -- Helper Variable for Table Name in For Loop
    TABLE_NAME VARCHAR2(256);

    -- Helper Variable for Generation of Constant String Value 
    STATIC_COLUMN VARCHAR2(256);

    -- Helper Variable for Generation of Dynamic SQL Statement
    SQL_STATEMENT VARCHAR2(256);
BEGIN   

    -- To prevent buffer overflow
    dbms_output.enable(null);
    
    -- Edit these to your liking - read description above
    COLUMN_TO_FIND := 'DESCRIPTION';
    PRIMARY_KEY_1 := 'PRIMARY_KEY_1';
    PRIMARY_KEY_2 := 'PRIMARY_KEY_2';
    SCHEMA_OWNER := 'MY_COOL_ORACLE_SCHEMA';
    
    -- Loop through the results of the below SQL query to create 
    -- a query that is outputted into DBMS Output
    FOR item IN 
        (
            -- The query that identifies all the tables that contain the specified columns
            WITH COLUMN_TO_FIND_TABLES AS (SELECT table_name FROM all_tab_columns WHERE column_name = COLUMN_TO_FIND and owner = SCHEMA_OWNER), 
                 PRIMARY_KEY_1_TABLES AS (SELECT table_name FROM all_tab_columns WHERE column_name = PRIMARY_KEY_1 and owner = SCHEMA_OWNER), 
                 PRIMARY_KEY_2_TABLES AS (SELECT table_name FROM all_tab_columns WHERE column_name = PRIMARY_KEY_2 and owner = SCHEMA_OWNER)
            SELECT table_name 
            FROM COLUMN_TO_FIND_TABLES 
            WHERE table_name IN (SELECT * FROM PRIMARY_KEY_1_TABLES) AND 
                  table_name IN (SELECT * FROM PRIMARY_KEY_2_TABLES)
        ) 
    LOOP

        -- Redeclare variable to make things cleaner - personal preference
        TABLE_NAME := item.table_name;
    
        -- Declare the table_name as a static column for the union statement 
        -- so we know where the result is coming from if we want to analyze
        -- the specific record that is being outputted via the union
        STATIC_COLUMN := '''' || TABLE_NAME || '''' || ' AS table_name'; 
        
        -- Build the dynamic sql query that will be outputted into DBMS 
        -- so that we can copy it into an SQL worksheet after it finishes
        --
        -- The final query that is built will display all records where "COLUMN_TO_FIND"
        -- has a length greater than 1000 characters
        SQL_STATEMENT := 'select ' || PRIMARY_KEY_1 || ', ' || PRIMARY_KEY_2 || ', ' ||  STATIC_COLUMN || ' from ' || TABLE_NAME || ' where LENGTH(' || COLUMN_TO_FIND || ') > 1000';
        
        -- Output to DBMS Output for copy paste into SQL Worksheet later
        dbms_output.put_line(SQL_STATEMENT);
        dbms_output.put_line('UNION');
    
    END LOOP;
END;

Utility Script for Drop and Recreate of Temp Tablespaces in Oracle Database

Had a need for this today so did a few google searches and found this nifty guide written at DBAClass.com

So using their initial template I’ve gone ahead and created a reusable utility SQL Script with comments as the instructions for portability.

-- Purpose:
-- If you want to recreate your temp tablespace, then follow below steps. For changing the default tablespace also, below steps can be used.

-- Instructions:
-- 1. Open SQL Developer
-- 2. Open a new SQL Worksheet
-- 3. Login as SYSTEM with the DEFAULT role targeting the intended SID 
-- 4. Follow the below steps that are detailed in the inline comments

-----------------------------------------------
-- Find the existing temp tablespace details --
----------------------------------------------- 

select tablespace_name, file_name from dba_temp_files;

--------------------
-- Example Output --
--------------------

-- TABLESPACE_NAME FILE_NAME
-- TEMP			   /opt/oracle/oradata/MYSID/temp01.dbf

-- We will use the tablespace name here for our delete/drop statements later on and also it's filepath structure

-----------------------------------------------------------
-- Create another Temporary Tablespace TEMP_DELETE_AFTER --
-----------------------------------------------------------

CREATE TEMPORARY TABLESPACE TEMP_DELETE_AFTER TEMPFILE '/opt/oracle/oradata/MYSID/temp_delete_after.dbf' SIZE 2G;

-------------------------------------------
-- Move Default Database temp tablespace --
-------------------------------------------

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_DELETE_AFTER;

COMMIT;

-----------------------------------------------------------
-- If any sessions are using temp space, then kill them. --
-----------------------------------------------------------

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

-- Use the SID and Serial Number of each session in the output and populate in the below SQL command for each open session using the TEMP tablespace.

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

-----------------------------------------------
-- Disconnect your SQL Session and Reconnect --
-----------------------------------------------

----------------------------------------
-- Drop the original temp tablespace. --
----------------------------------------

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

----------------------------
-- Create TEMP tablespace --
----------------------------

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/opt/oracle/oradata/MYSID/temp01.dbf' SIZE 10G;

-------------------------------------
-- Make TEMP as default tablespace --
-------------------------------------

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

COMMIT;

-----------------------------------------------
-- Disconnect your SQL Session and Reconnect --
-----------------------------------------------

-----------------------------------------------------
-- Drop temporary for tablespace TEMP_DELETE_AFTER --
-----------------------------------------------------

DROP TABLESPACE TEMP_DELETE_AFTER INCLUDING CONTENTS AND DATAFILES;

COMMIT;

AWS – EC2 and SystemCtl – Making Sure Docker Starts on Boot

Saving this so I don’t have to find this again.

Context – this is for enabling docker to start on boot of a EC2 virtual machine using Amazon Linux 2.

Apparently I had to not only enable the systemctl service – but also flip a flag for “enable lingering” – found via an obscure Stack Exchange link?

I must be doing something wrong here – it just doesn’t sound correct to have to “enable a lingering” login session.

Commands I used that worked are as follows:

#Enable Docker on Boot
sudo systemctl enable docker.service
sudo systemctl enable containerd.service

#Enable Lingering on loginctl
loginctl enable-linger $USER

Credit at the below Stack Exchange Links:

Utility Script for Reverse SSH Tunneling When Collaborating with Developers Behind Firewalls

Use Case:

  • Collaboration with peers that allow them to directly interface with your local development TCP/IP Ports using an intermediary SSH server

Two Scenarios with Commands Outputted:

  • Command for Scenario 1:
    • As a developer, I want to be able to expose my local development address/port for usage by another developer who is attempting to assist me,
    • My local firewalls are preventing direct connection on the host and port so I will connect to a remote server and expose a tunnel that will allow the developer to connect in this alternative and secure manner
  • Command for Scenario 2:
    • As a developer, I want to be able to assist my fellow developer who has exposed their connection on a remote server using my local computer
    • The remote firewall on the SSH server does not permit me to connect directly to this exposed port that the developer setup so I will establish a port forwarded tunnel that redirects a specified port on my local machine to the remote server and remote port that I am attempting to hit – therefore allowing me to establish the connection to the end developers reverse proxy

I wrote this script up based off prior usage so I won’t have to fiddle with SSH documentation anymore as the syntax behind this concept is very confusing sometimes especially if you only have to execute this once every few months.

The below script has a ton of comments inline describing how to use it and what each variable does.

The formatting may look horrible in WordPress but if you copy paste it into a text file *.sh it will look and make a ton of sense.

# Use Case:
# - Collaboration with peers that allow them to directly 
#   interface with your local development TCP/IP Ports

#########################################################################################
# For further documentation scroll to the bottom of this script where all the variables #
# are described in detail how they work                                                 #
#########################################################################################

# Scenario 1:
# - As a developer, I want to be able to expose my local development address/port for usage 
#   by another developer who is attempting to assist me, 
# - My local firewalls are preventing direct connection on the host and port so I will 
#   connect to a remote server and expose a tunnel that will allow the developer to connect 
#   in this alternative and secure manner

VARIABLE_REMOTE_SSH_SERVER="myRemoteSshServerForCollaboration.com"
VARIABLE_REMOTE_SSH_PORT="22"
VARIABLE_REMOTE_SSH_USER="myRemoteSshUser"

VARIABLE_REMOTE_BIND_ADDRESS="[::]" #Bind on all remote addresses
VARIABLE_REMOTE_BIND_PORT="1234"

VARIABLE_LOCAL_ADDRESS_TO_FORWARD="localhost"
VARIABLE_LOCAL_PORT_TO_FORWARD="1993"

echo ""
echo "########################################################"
echo "## Give this to the user you are trying to connect to ##"
echo "########################################################"
echo ""
echo ssh -fN -o StrictHostKeyChecking=no -R "$VARIABLE_REMOTE_BIND_ADDRESS:$VARIABLE_REMOTE_BIND_PORT:$VARIABLE_LOCAL_ADDRESS_TO_FORWARD:$VARIABLE_LOCAL_PORT_TO_FORWARD" -l $VARIABLE_REMOTE_SSH_USER $VARIABLE_REMOTE_SSH_SERVER -p $VARIABLE_REMOTE_SSH_PORT

# Scenario 2:
# - As a developer, I want to be able to assist my fellow developer who has exposed their connection
#   on a remote server using my local computer 
# - The remote firewall on the SSH server does not permit me to connect directly to this exposed port 
#   that the developer setup so I will establish a port forwarded tunnel that redirects a specified port
#   on my local machine to the remote server and remote port that I am attempting to hit - therefore allowing
#   me to establish the connection to the end developers reverse proxy

VARIABLE_REMOTE_SSH_SERVER="myRemoteSshServerForCollaboration.com"
VARIABLE_REMOTE_SSH_PORT="22"
VARIABLE_REMOTE_SSH_USER="myRemoteSshUser"

VARIABLE_LOCAL_PORT_FOR_RELAY="1993"

VARIABLE_REMOTE_ADDRESS_FOR_RELAY="localhost"
VARIABLE_REMOTE_PORT_FOR_RELAY="1234"

echo ""
echo "########################################################"
echo "## Execute this locally to connect to the user you    ##"
echo "## who is attempting to expose their local dev ports  ##"
echo "########################################################"
echo ""
echo ssh -fN -o StrictHostKeyChecking=no -L $VARIABLE_LOCAL_PORT_FOR_RELAY:$VARIABLE_REMOTE_ADDRESS_FOR_RELAY:$VARIABLE_REMOTE_PORT_FOR_RELAY -l $VARIABLE_REMOTE_SSH_USER $VARIABLE_REMOTE_SSH_SERVER -p $VARIABLE_REMOTE_SSH_PORT

#
# - VARIABLE_REMOTE_SERVER - specifies what remote intermediary 
#   server you will be exposing your local ports to for another 
#   user to connect with/through
# 
# - VARIABLE_REMOTE_USER - specifies what remote intermediary 
#   server user account you will be using in conjunction with 
#   VARIABLE_REMOTE_SERVER variable
#
# - Parameter -l on SSH Command:
#     - login_name - Specifies the user to log in as on the 
#       remote machine. This also may be specified on a per-host 
#       basis in the configuration file.
#
# - Parameter -N on SSH Command:
#     - Do not execute a remote command. This is useful for 
#       just forwarding ports (protocol version 2 only).
#
# - Parameter -f on SSH Command:
#     - Requests ssh to go to background just before command execution. 
#       This is useful if ssh is going to ask for passwords or passphrases, 
#       but the user wants it in the background. This implies -n. The 
#       recommended way to start X11 programs at a remote site 
#       is with something like ssh -f host xterm.
#
# - Parameter -L on SSH Command:
#     - [bind_address:]port:host:hostport
#     - Specifies that the given port on the local (client) host is to be 
#       forwarded to the given host and port on the remote side. This works 
#       by allocating a socket to listen to port on the local side, optionally 
#       bound to the specified bind_address. Whenever a connection is made to 
#       this port, the connection is forwarded over the secure channel, and a 
#       connection is made to host port hostport from the remote machine. 
#       Port forwardings can also be specified in the configuration file. 
#
# - Parameter -R on SSH Command:
#     - Specifies that the given port on the remote (server) host is to be 
#       forwarded to the given host and port on the local side. This works by 
#       allocating a socket to listen to port on the remote side, and whenever 
#       a connection is made to this port, the connection is forwarded over the 
#       secure channel, and a connection is made to host port hostport from the 
#       local machine.
#
# - Parameter -o on SSH Command:
#     - option - Can be used to give options in the format used in the 
#       configuration file. This is useful for specifying options for which 
#       there is no separate command-line flag. For full details of the options 
#       listed below, and their possible values, see ssh_config(5).
#
# - Option StrictHostKeyChecking on SSH Option Command:
#     - StrictHostKeyChecking can be used to control logins to machines whose 
#       host key is not known or has changed. The keyword is described in StrictHostKeyChecking.
#     - Useful with AWS EC2 Instances whose host keys constantly change when destroyed/recreated.
#
# - VARIABLE_REMOTE_BIND_ADDRESS - specifies what hostnames or addresses the reverse
#   connection will be available on for usage by other user later on
#
# - VARIABLE_REMOTE_BIND_PORT - specifies the port the reverse connection will be available
#   on for usage by other user later on
#
# - VARIABLE_LOCAL_ADDRESS_TO_FORWARD - Specifies the local hostname to forward to the remote
#   server that will be exposed on the VARIABLE_REMOTE_BIND_ADDRESS and VARIABLE_REMOTE_BIND_PORT
#
# - VARIABLE_LOCAL_PORT_TO_FORWARD - Specifies the local port to forward to the remote
#   server that will be exposed on the VARIABLE_REMOTE_BIND_ADDRESS and VARIABLE_REMOTE_BIND_PORT
#
# - VARIABLE_REMOTE_PORT_FOR_RELAY - Specifies the remote port we want to connect to and make 
#   available locally via the VARIABLE_LOCAL_ADDRESS_FOR_RELAY and VARIABLE_LOCAL_PORT_FOR_RELAY
# 
# - VARIABLE_LOCAL_ADDRESS_FOR_RELAY - Specifies the local address we want to listen on for 
#   connection to the VARIABLE_REMOTE_PORT_FOR_RELAY on the remote ssh server
#
# - VARIABLE_LOCAL_PORT_FOR_RELAY - Specifies the local port we want to listen on for 
#   connection to the VARIABLE_REMOTE_PORT_FOR_RELAY on the remote ssh server
#
# - VARIABLE_REMOTE_SSH_SERVER/VARIABLE_REMOTE_SSH_PORT/VARIABLE_REMOTE_SSH_USER - Specifies
#   the remote ssh server that we will be connecting to where both scenarios are exposed and 
#   accessible for connectivity by both end users

Quick Tip – Clearing Cache/Cookies for a Specific Website in Chrome Instead of Getting Logged Out of Everything

It’s 2021 – literally everything is a web app now.

“Did you clear your cookies and cache for your web browser?” – It used to be the favorite thing IT Helpdesk said when we had users call in.

Nowadays if you’re like me, you avoid this like the plague because it means you have to log back into all 50 of your corporate applications that are spread out across different URLs if you clear the browser cache.

Today I took some time to research how to do this in a targeted “single website” fashion because I had a rogue cache that was preventing my access to a documentation site on an internal network.

Manual Steps:

  1. Click the three dots on the top right of your Chrome Web Browser and Click “Settings”
  2. Left hand toolbar – click “Privacy and Security”
  3. Middle section – click “Cookies and other site data”
  4. Scroll down – click “See all cookies and site data”
  5. Middle Top there’s a search box called “Search Cookies” – type your domain
    • Example – “google.com”
  6. Click the Trash Can on the sites that you want to reset the cache/cookies on
  7. You’re done! Next time you navigate to this site it’ll be a completely fresh session and require relogin or etc.

Advanced Steps:

  1. Create a bookmark on your toolbar for the following URL:
    • chrome://settings/siteData
  2. Click this bookmark anytime you want to get to this – is a bypass for steps 1-4
  3. Middle Top there’s a search box called “Search Cookies” – type your domain
    • Example – “google.com”
  4. Click the Trash Can on the sites that you want to reset the cache/cookies on
  5. You’re done! Next time you navigate to this site it’ll be a completely fresh session and require relogin or etc.

Thoughts on Recent LastPass Changes with the Free Tier from a Biased Tech Geek

Starting today LastPass will be charging 3 dollars per month for usage across multiple devices.

Personally, I still stand behind how secure, reliable, and versatile their service is and am going to begin paying for their product for our household.

I’ve used the service for a long time now and think it’s a no brainer to continue using them thanks to the great sense of security and organization it has brought my family.

Regarding Recent Bad Press, Competitors, and Track Records

A lot of formidable competitors are popping up on the market as a mass exodus of users are looking for other options. In my opinion, this is attributed to many recent misleading tech articles coming out around tracking and general disgruntled users who don’t believe in paying for services that they receive which is extremely unfortunate.

With surface level reading I’ve done on some of these companies a few of them seem to be flashy startup companies attempting to cash in or companies that in my opinion don’t have enough of a history yet to completely earn my trust due to their lacking historical track record.

To elaborate, what I mean to say is for me personally, these companies haven’t been around long enough to prove that they aren’t going anywhere in a year or two due to bankruptcy and/or dropping support for their products (password managers) or as some of us call it pulling a Google and adding it to the Google Graveyard.

It sounds awfully cliché to say it in this day and age considering everyone’s hate for corporations, but LastPass the product/service has a giant corporate backing from LogMeIn who has been in business for well over 18 years – to me this establishes trust that this application isn’t going anywhere anytime soon – which is something I need in my family as I don’t intend to jump between password managers every year each time a new flashy option comes along.

Regarding Security

For those that bring up the concerns of security – I highly encourage you to subscribe to the train of thought that it is extremely damaging to LogMeIn Corporate for anything security wise to happen with something as serious as a password manager so it’s in their best interest to continually keep this product secure and up to date especially with our current fragile cybersecurity climate.

Regarding Pricing

For those that bring up the concern of having to pay for a service – LogMeIn/LastPass has kept the free tier for 5+ years and it was only a matter of time before things had to change, it’s honestly insane that it was free for as long as it was. If you can’t swing 3 dollars a month due to financial hardships – I completely respect that and maybe the alternative options on the market are for you – but otherwise 3 dollars a month is a small price to pay for an extremely well managed application/service.

Conclusion

In conclusion, if you are thinking about other options, I still think you should highly consider continuing usage of LastPass regardless of the new pricing structure for these reasons – but then again this is only my personal extremely biased tech geek hail corporate opinion. 🙂

Linux Bash – Yum – Installing a Specific Version of a Package or Application

Note to self – never update Visual Studio Code on Linux – aka today was FUN.

I found out via a vague GitHub Issue that Microsoft’s latest versions of VSCode are built using some higher version of “libstdc++” and “CXXABI_1.3.9” that most if not all currently existing versions of Linux are currently not compatible with.

The error in question:

The terminal process failed to launch: A native exception occurred during launch (/lib64/libstdc++.so.6: version `CXXABI_1.3.9' not found (required by /usr/share/code/resources/app/node_modules.asar.unpacked/node-pty/build/Release/pty.node)).

In this below GitHub thread there is a long list of angry developers since 02/04/2021 including an IT representative that works on a Navy Ship that are all just absolutely pissed off – it’s a fun read.

https://github.com/microsoft/vscode/issues/115784

So as a result of this fun exploratory task I had to find a way to revert VSCode to an older – working version that is compatible with my distro.

Here are the steps I took – credit to the Navy IT Tech who goes by “appurist” on this GitHub thread – I used part of his solution and found other solutions to pair together to find the ideal version numbers for my Linux Distro.

  1. Open Terminal
  2. Type the following command:
    • yum list –showduplicates | grep code.x86
  3. This will bring back a list of versions for your desired application that you want to downgrade
  4. Copy your desired version string and create the package name as follows:
    • Structure Example:
      • Package Name
      • Hyphen –
      • Version String
    • Actual Example
      • code-1.52.1-1608137084.el7
  5. Either of the two following command styles:
    • Uninstall Application – Reinstall Application
      • yum -y remove code
      • yum -y install code-1.52.1-1608137084.el7
    • Downgrade Application and Version Lock to prevent updates
      • yum downgrade code-1.52.1-1608137084.el7
      • yum install yum-plugin-versionlock
      • yum versionlock code

Outlook Quick Tip – Getting Caught Up on Emails Received Between a Specific Timeframe

A few weeks ago Robyn and I welcomed our son Mason into the world. We are truly blessed to have this little dude.

As a result, you go on Paternity Leave and put your work life on hold, as you should. 🙂

However, when you get back now there is a mountain of emails to sort through, so today I’m sharing a handy tip I used to sort through all these communications.

Giving credit where credit is due, I found this nice tip on the following website:

https://smallbusiness.chron.com/emails-between-certain-dates-outlook-79638.html

Steps for Usage:

  1. Open Outlook on Windows 10 or Mac OS
  2. Highlight your inbox
  3. Use the following template and swap date values to when OOO started and ended
    • received:>=02/17/2021 AND received:<=03/13/2021
  4. Scroll all the way to the bottom of the email list and work your way up to the top reading and/or replying to emails

Debugging what Event is Preventing a Computer from going to Sleep

The saga continues…

On a previous blog post I detailed my attempts to debug Windows 10 and it’s constant issues keeping a computer from being able to sleep peacefully.

Well, I encountered a new issue and I’m about ready to set Microsoft on fire for their lack of QA Testing.

Here are a list of optimal steps I took to find the culprit:

  1. Open a Command Prompt as Administrator
  2. Type powercfg -requests
  3. Look for offending processes and start googling steps to mitigate based off the *.exe name

Well it turned out this time it was a core Windows process that was causing the issue and it seemed that many many many people have reported this since July 2020, see below identifier message.

EXECUTION:
[PROCESS] \Device\HarddiskVolume2\Windows\System32\MoUsoCoreWorker.exe
USO Worker

I didn’t believe in blindly disabling the “Update Orchestrator” as some people suggested – instead I opted to search for what was actually causing the issue and happened upon this solution from /u/MsWolf88 on the below Reddit post.

So here are the steps I took as a result of /u/MsWolf88’s information:

  1. Fresh reboot of Windows 10 computer
  2. In the Start Menu type “Check for Updates” and open it
  3. Check for any outstanding updates and install them – not necessarily the optional updates.
    • If reboots are required as a result of this step do as the prompts say and resume on the next step after all pending updates are satisfied
  4. Close all windows and applications that may be open – specifically the Windows Update window if still open
  5. In the Start Menu type “Services” and open it
  6. Find “Windows Update” in the menu that comes up – right click it and click restart
  7. In the Start Menu type “Check for Updates” and open it
  8. Check for any outstanding updates and install them – not necessarily the optional updates.
    • If reboots are required as a result of this step do as the prompts say and resume on the next step after all pending updates are satisfied

In my case it turned out that their was a hung update that required a restart of the “Windows Update” service but wouldn’t show it was pending despite many reboots (I shut my computer down manually daily) and after triggering the forceful restart of “Windows Update” service it showed the offending update that needed to be applied.

Now, my computer goes to sleep after one minute as God Microsoft Bill Gates intended and no longer has Insomnia until I find the next stupid QA Issue that Microsoft missed.