Hello! I'm Defron and this is my blog.

Data Privacy Day: Passwords

Part One in a five-part exposé for Data Privacy Day

Data Privacy Day: Smartphones

Part two in a five-part exposé for Data Privacy Day

Data Privacy Day: Web Browsing

Part three in a five-part exposé for Data Privacy Day

Thursday, March 7, 2013

Oracle iSQL*Plus Overview

This post is a bit different from my usual ones. It's specifically aimed at classmates in one of my classes I'm taking right now where we are learning Oracle Database. I found out the school has an iSQL*Plus web access license and it's publicly accessible (so you can use it from home), so anyone can use it any time.

iSQL*Plus is a web interface to SQL*Plus. Pretty much everything will work in iSQL*Plus that will work in SQL*Plus, but not quite everything.

Login and Use

 Logging in to iSQL*Plus is similar to logging in to SQL*Plus, except you use your web browser. You'll navigate to the iSQL*Plus web server and enter your username, password and connect identifier (if applicable, for the school iSQL*Plus server it is). It works with any browser. I've used it in the latest releases of Chrome, Firefox, and Opera with no problem. The login screen looks like this:

Once you've logged in, you'll be presented with a nice web interface. Here's me executing a simple command:

 As you noticed, by default it'll display the output as standard HTML of your commands below the enter field. Also by default it'll paginate the output, so you only see a number of lines at a time, 24 by default. This can be changed in preferences, but first on the main workplace UI:

Your SQL Commands stay in the box until you clear it by either manually deleting it or using the "Clear" button.

The "Execute" button executes all the commands in your textbox, by default showing the output below.

The "Load Script" button will take you to a page where you can browse for a *.sql file to upload. After you've located it, press the "load" button and it'll put the contents of the file in the text box. It's kinda pointless since you can just copy and paste the contents faster most of the time.

The "Save Script" button will auto-generate a file called "myscript.sql" and present a download prompt, which is pretty nifty.

The "Cancel" button will terminate any running commands, useful if your commands are being unresponsive.

You may notice a "History" tab on the top-right corner. This will store a history of your last few executions of commands. You can then reload them, which is useful if you accidentally hit the "clear" button. Do note: the History is cleared upon logout.


 Preferences are where you can change various default settings. Most notably are:

In Interface Configuration:
  1. The amount of scripts to save in "History" from the current session (by default 10).
  2. The default size of the script input text box (pointless in modern browsers that let you expand it yourself)
  3. Whether to display the output below the text box or generate a downloadable html file (this is the closest equivalent to spooling, which isn't available in the iSQL*Plus interface)
  4. Option to have everything display on one page or multiple pages (and set the number of lines per page)
In Script Formatting:
  1. Option to have line numbers (kinda nifty)
  2. Option to display commands in the output by default (equivalent to running SET ECHO ON at the top of every execution)
There are many other options too. Here's Oracle's page on iSQL*Plus preferences and the equivalent SET commands

Differences Between iSQL*Plus and SQL*Plus

Most things in SQL*Plus will also work in iSQL*Plus, as I've noted. One notable exception is spooling. If you try to spool you'll see this:
spool on 
SP2-0850: Command "spool" is not available in iSQL*Plus 
Any other command that can't be run will similarly kick up an error like that and the rest of your script will execute properly. Spooling doesn't work because, obviously, you cannot set a save location on the server from your web browser. that'd be a security nightmare! The closest equivalent is to have the output generate an isqlplus.html file instead of displaying the command results below the text box.

By far, my favorite feature is that you can edit multiple lines very easily in iSQL*Plus and recall history. It's much nicer in that respect than SQL*Plus is. Likewise, the commands don't disappear after execution, so if you made a small mistake, it's easy to fix and re-execute. Very nice.

The other nice thing is any user can set preferences for many set commands, which can save you time.

The main downside is that there are no TNS alias, so your connect identifier has to be the full, proper connect identifier normally contained in the TNS.ora file

You should also be careful of something: iSQL*Plus doesn't care if you don't end SQL commands with a semicolon. If you plan on using this elsewhere or submitting it, you'll need to make sure you include them, as otherwise it may not work for someone else.

One thing to note is that iSQL*Plus has a pretty aggressive timeout/auto-logoff setting. So if you just leave it open for some time, you'll probably be forceably be logged out, so do make note of that.


Well, that's pretty much all there is to iSQL*Plus. Just make sure to log out. It's a great way to practice your SQL off-campus.

Saturday, March 2, 2013

Expanding C: Partition on Win2k3 and Remote GParted

As I mentioned in my last post, the C: partition on the Windows server at work had become completely full. I immediately did some temporary stopgaps to hold her over until I could properly repartition her. Today was that day.

Repartitioning modern Windows (Server 2008+, Vista+) is no problem, as you can do it with the included disk management utility. XP and Server 2003(R2) are different, as the disk management utility isn't nearly as capable. This server is a Windows Server 2003R2, so I had to use a third-party utility. For home users, the EaseUS Partition Manager family is pretty good. For a corporate server, though, it's $160. I wasn't approved for spending that (For good reason, the server is slated for replacement in a year or so, so the money would have been wasted in the long-run), so had to go with free options.

I ended up going with two different tools: one for shrinking the data partition and one for expanding the system partition. I used GParted to shrink the data partition and ExtPart to expand the C: drive. The reason being that Windows doesn't really like GParted and sometimes it'll require a repair action from the Windows disk when messing with the C: partition. I didn't want to deal with that and ExtPart is a small, simple, free utility for extending a partition (hence the name) from within Windows.

The day started at 8:40 am. I fired up CloneZilla and cloned the hard drive. If there was a power outage or some other freak accident during the repartitioning, I could then simply restore the image in a short time. I always recommend imaging your system before repartitioning for this reason. There are lots of disk cloning tools, I like CloneZilla. I tried doing this the day before, but the version of CloneZilla I had didn't work with my server's RAID card (a SAS 6/iR). I brought a freshly burned copy of the latest CloneZilla release and it recognized my drive just fine. This ran until 12:35 pm.

Next it was partitioning time. I inserted the GParted Live CD and got busy. Unfortunately Dell thought it was a good idea to make the data partition an extended partition. This means I'd have to do an extra action: First shrink the data partition and then shrink the extended partition it resides in. This means it'll take more time. I figured it to be done by 2:30 pm originally, but this would make it take longer. I figured it'd be done by 5:30 pm (it ended up beating my expectations by finishing at just shy of 4:50 pm). I didn't want to stay at work until 5:30 pm, time to get remote access to the GParted Live CD.

This actually proved pretty easy. First I configured the network, which GParted Live includes a nice desktop shortcut to do. Next I opened up a terminal. GParted Live is based on Debian so I did a sudo apt-get update && sudo apt-get x11vnc... This didn't work. Turns out GParted comments out the repos from sources.list. So I did a sudo nano /etc/apt/sources.list and un-commented the repos.

So now to install and run x11vnc:
user@debian:~$ sudo apt-get update && sudo apt-get install x11vnc
user@debian:~$ x11vnc -forever -display :0
The installation pulled a few packages besides x11vnc: libvncserver0, libxssl, openssl, tcl, tcl8.5, tk, tk8.5, and x11vnc-data. It only ended up taking 11.2 MB of more space, so no big deal, my server has plenty of RAM.

The forever flag tells x11vnc server to remain running after a client disconnects. Without that, as soon as you disconnect the first time, x11vnc will stop running. I planned on connecting a few times so I could do periodic check-ins on how GParted is progressing. The display :0 flag tells x11vnc to show the current session instead of creating a new one. It would be useless to VNC in to check on GParted's progress if I was given a new session. I also didn't want to risk x11vnc disconnecting and me being SOL, so I also decided to enable ssh on GParted. This is simple.

First, we need to set a password and configure hosts.allow so I can ssh in. This is done with sudo passwd user to create a password for the user 'user'. Without this you'd have to allow for passwordless login for ssh, which would require more configuration. Easier to create a simple password. Next you need to edit hosts.allow by doing nano /etc/hosts.allow. Add sshd subnetblock. (don't for get the period!) to the end. In my case it was sshd 192.168.1. that I needed to add. Now I just restarted networking and started ssh

sudo /etc/init.d/networking restart && /etc/init.d/ssh start

End result? I drove home at 3:00 pm and started checking on it. I use LogMeIn to remote into the office and then fired up TightVNC to check on GParted

 And then a bit later I saw:

Success! Done! Well, with the GParted part. I drove to the office to finish it up, since GParted will hang after ejecting the CD, and the server itself hangs on a setup in the preboot environment due to an alert I cannot disable.

Upon Reboot, Windows did a consistency check on the data partition, no biggie. I then rebooted again and was almost done. Now I need to expand the C:\ drive with ExtPart
C:\Documents and Settings\Administrator>cd C:\Dell\ExtPart
C:\Dell\ExtPart>extpart c: 15325
That's the default directory ExtPart creates when "installed" (it's a self-extracting archive when you download it). As far as the extpart syntax: it's simple. You specify the drive letter and then the amount you want to expand it in megabytes. In my case, that's c: and 15325. The end result? 26.9 GiB C: partition and 16.4 GiB of free space. I call that a rousing success for an honest day's work.