A much easier way to ream out an Oracle schema

This self-concatenating method is much better than the shell-based solution I wrote about a few years ago.

set heading off
set pagesize 0
select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS','') || ';'  from user_objects;

Execute the output, then

purge recyclebin;
select * from user_objects

Very satisfying.  Much less fiddly.

No, you can't edit Font Book collections

After vigorous searching for "how to delete a font from a group in fontbook" and "how to remove a font from a collection in Font Book" and such, I've concluded that there is no such feature.

These are not the options you're looking for.  Move along.

Did you misfile a useless symbol font in your "Stencils" folder?  Well, valued Mac user, you'll just have to deal with the consequences of your actions.  Apparently you used to be able to drag the offending font onto the desktop and letting it go poof.  However, that's definitely not working for me on 10.11 El Cap.

There's a simple workaround:  The collections themselves can be deleted.  Rename the contaminated collection and create an empty one with the desired name.  Move all the stuff you actually want into the new collection, then delete the renamed contaminated collection via the File menu.

I fully acknowledge that this is going to be useful to precisely nobody I know. Maybe Hugh.  I'm just posting it in the vain hope that it will short-circuit the next person's fruitless search for a nonexisting feature.

Editing cells in SQL Developer

I rarely edit my databases directly, and of those rare edits, I'd say that less than 1% are done to individual cells.  Usually, it's because I need to twiddle a knob that Liferay has buried in an XML CLOB. So when I do need an editable grid, I can only remember how to get to it by browsing, which is incredibly tedious.

But there's a better way!

1. Find the offending cell using the query worksheet, then copy the WHERE clause.
2. In the query, hold down ⌘ (Ctrl on Windows) while mouse hovering over the table name.  The table name becomes a link.
   
   
   
3. Click on that link.  (Once you know about this behavior, you can ⌘-click without hovering.)
4. Now you're in the data grid for the selected table.  Paste the WHERE clause from your buffer and hit Enter.
   
   
5. Double click on any record to edit it, or right-click on the row to use Single Record View.
6. YOU WIN.  EVERYBODY WINS.

For the sake of completeness, here's the tedious way:

1. In the connections browser, expand the schema.
2. Expand Tables.
3. Scroll, scroll, scroll down to the desired table.  Wait, you missed it.  Scroll back up.
4. Click on the table name.  This opens the data grid, as in step 4 above.

h/t That Jeff Smith

Check when your password will expire

I hate to even admit it, but there are still some places where my password is managed locally.  (Ugh.  Maybe I should have put a trigger warning on this post.)  Anyway, this means that it's occasionally useful to know when I last changed my password and/or when I will need to do it again.

It turns out that chage, which I had thought was really only useful for root, provides a way for non-root users to check their own accounts.

somesrvr kexline@somesrvr ~
$chage -l kexline
Last password change     : Mar 02, 2015
Password expires     : Mar 06, 2015
Password inactive     : never
Account expires     : never
Minimum number of days between password change     : 0
Maximum number of days between password change     : 4
Number of days of warning before password expires   : 2

Oracle password expiry without DBA privs

Here's how to get password expiration information without DBA privileges.

SQL> select * from user_password_limits;

RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7

7 rows selected.

SQL> select account_status,expiry_date from user_users;

ACCOUNT_STATUS EXPIRY_DA
-------------------------------- ---------
OPEN 03-JAN-15

At some point, I need to collect all my DBA-free Oraclejitsu into a single document.  But I say the same thing to that that I say to death:  "Not today."

Shell one-liner: Get your effective Ghostscript path

I amuse me.

gs -? | grep \/ | grep : | sed 's/\ //g' |  sed 's/:/\n/g' | grep -v ^$ | xargs ls -ld 2>&1 |  grep -v "No such" | awk '{print $NF}' | tr '\n' ':'

Ghostscript's helpfile mentions its font path.  However, it's not presented in a usable format, and worse, it includes directories that don't exist.  This cleans it up and spits it back out in a format that you can paste into a path configuration.

Destroy a schema from the inside

Oracle does not provide a way for a user to pave over his own schema.  If you don't have dba privileges, this means you have to remove your schema by destroying each object.

This is a VERY rough recipe that I just wrote for a coworker.  It assumes you have Oracle SQLDeveloper and a shell handy.

1. sql> select /*csv*/ 'DROP ',user_objects.object_type,' ',user_objects.object_name from user_objects where object_type in ('INDEX','TABLE');
2. F5 (run as script)
3. Select everything in your output window and copy it to /tmp/objects
4. bash> cat /tmp/objects | grep -v OBJECT_NAME | sed s/\"//g | sed s/\,//g | sed s/$/\;/ > /tmp/drop.sql
5. In SQLNavigator, clear your query and output screens (pencil eraser)
6. File -> Open /tmp/drop.sql
7. F5 (run as script) -- drops may cascade, which will cause errors later in the script; ignore
8. sql> purge recyclebin;
9. sql> select * from user_objects;  -- should return 0 rows now.

This can easily be adapted to whatever Oracle client and text editor you have handy.  In place of the seds and greps, use a basic text editor to remove the header line, remove all commas and quotes, and add a semicolon to each line.