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.

Awk: Print all but the last field

The other day I had a list of files, and needed to extract a list of the directories containing those files. So, in more general terms, I needed everything but the last field of each line.   That can be done in awk with

awk -F/ '{$NF=""; print $0}' filename 

i.e., you set the Nth field to null, then print the modified item.   You can do that with other fields as well -- $1, $(NF-1), and so on.

But that changes all the slashes to spaces. I knew there were no spaces in my list, so I fixed it with sed:

awk -F/ '{$NF=""; print $0}' filename | sed s/\ /\//g 

I could also have used the OFS (output field separator) directive in awk, but that doesn't work within a one-liner.

awk -F/ 'BEGIN { OFS = "/"}{ $NF=""; print $0 }' filename 

You can also edit the output record separator the same way -- for example, if you're sedawking a batch script, perhaps you want to add semicolons.

awk -F/ 'BEGIN { OFS = "/"; ORS=";\n"}{ $NF=""; print $0 }' filename 

 (h/t One Tip Per Day and GNU)