Top SQLs by Disk Read via V$SQLAREA

with base_data as   (   select   v.HASH_VALUE,   disk_reads,   executions,   v.CPU_TIME,   sql_text,   v.FIRST_LOAD_TIME  from v$sqlarea v  order by 2 desc, 3 desc  )  select  sysdate snaptime,  null hash_value,  sum(disk_reads) disk_reads,  sum(executions) executions,  sum(cpu_time)   cpu_time,  '!ALL SQL SUMMARY' SQL_TEXT,  NULL first_load_time  from base_data  union all  select    sysdate,  HASH_VALUE,  disk_reads,  executions,  CPU_TIME,  sql_text,  FIRST_LOAD_TIME from base_data  where rownum <=20

Clear log file via inline edit

Trim off first 1000000 lines # No backup perl -i -ne 'print unless 1 ..1000000' server.log # With backup perl -i.bkp -ne 'print unless 1 ..1000000' server.log

du : Sort by size and display in human

Not terribly economic but functional du -sk * | sort -nr | cut -f2 | xargs du -sh

Steve's Tip for Getting Toad Windows to Display

F2

Using Xming (against solaris)

Needed a portable x-server to run oracle installers etc on a solaris box without vnc installed So dragged down xming from http://www.straightrunning.com/XmingNotes/ Installed into a temp directory - the app is portable Included the local version of plink when prompted,  declined integration with the desktop (portable!) Needed the optional font package - installed on top of the xming install but stripped it down to the TTF font directory to save space Create a session by using the xlaunch.exe program - which is a wizard to create a .xlaunch file to store parameters to feed to xming.exe xlaunch.exe Chose one window, display 0, start a program, program = /usr/bin/gnome-session - note the path ! - other options /usr/openwin/bin/xterm, /usr/openwin/bin/twm (solaris default windows manager) using putty connect to <servername> login as <username> password <password and to hell with security though the password does seem to blank out when the wizard is re-run> Clipboard TURN OFF THE ACCESS CONTROL if NOT relying on plink No other params Save everything to a .xlaunch file To run without the wizard - xlaunch.exe -run config.xlaunch To re-edit - xlaunch.exe -run config.xlaunch Even the clipboard worked

Viewing a Refcursor in TOAD

Run a PL/SQL block in the SQL editor as follows:<br />declare<br />begin<br /> OPEN :pv_cur FOR 'select * from dual';<br />end;<br /><br />Or if you have a function returning a refcursor<br /><br />declare<br />BEGIN<br /> :pv_cur := function_name(param1,param_etc);<br />END;<br /><br />TOAD will prompt you for the datatype/value of ':pv_cur'. <br />Change the type to 'Cursor' and leave the value blank, hit OK. <br />The ref cursor will be displayed in the standard data grid below, as if you had run a query.<br /><br /><br />

New database for crowd

Tough to find this one as its not in the install directories /var/opt/atlassian-crowd-home/crowd.cfg.xml See also http://confluence.atlassian.com/display/CROWD/Important+Directories+and+Files

Oracle: Checking a column is numeric

select * from table1 where ltrim(rtrim(translate(col1,'0123456789-.',' '))) is not null

Perl one-liner to summarise file volumes by hour

ls -l /u02/volestlivelogs01/oradata/ESTDB/archive/*.dbf | sed -e "s/:/ /g" -e "s/  / /g" | perl -lane 'BEGIN {%hash=();} $hash{"$F[5]$F[6]-$F[7]:00"}+= $F[4]; END {foreach $k (sort keys %hash) {print "$k " . int($hash{$k}/(1024*1024)) . " Mb"; } }'

Lost msconfig.exe

The location is not in the path - rather it is held in a registry key Create a .reg file with the following contents Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\MSCONFIG.EXE] @="C:\\WINDOWS\\PCHealth\\HelpCtr\\Binaries\\MSConfig.exe"

Where did my oratab go (on Solaris)

/var/opt/oracle/oratab

Is my Oracle db in archivelog mode

To verify that the database is in no archive log mode log into the sqlplus as sys and issue following command and u must get the following output archive log list To enable the database in archive log mode following parameters must be set in init.ora file of instance log_archive_dest_1=<path of the destination where u want the archive logs to be placed> log_archive_dest_state_1=enable log_archive_start=true( this parameter is deprecated in Oracle 10g) log_archive_format=arc_%t_%s_%r.dbf

RPM - Listing in date order

Useful for looking at recent changes rpm -qa --last | less rpm -qa --queryformat '%{installtime} (%{installtime:date}) %{name}\n'

OEM Agent - Cleaning out

On the Repos Server SELECT target_name FROM sysman.MGMT_TARGETS where 1=1 and target_type = 'oracle_emd' and target_name like '%mis%' order by 1 commit execute sysman.mgmt_admin.cleanup_agent('target_name:port>'); If this hangs then kill it and run ... update sysman.MGMT_TARGETS_DELETE set delete_complete_time = sysdate where target_name like '%slofosdb01.odl.com:3872%' On the Agent Machine rm - rf $OracleHome/sysman/emd/upload rm -rf $OracleHome/sysman/emd/recv rm -rf $OracleHome/sysman/emd/state Resecure the connection - emctl secure agent Restart the agent - emctl start agent Upload info - emctl upload

Speaking Imap (in telnet)

http://support.microsoft.com/kb/189326 Telnet IP address of Mail server (Exchange) 143 LOGIN NTDOMAIN/NTACCOUNT/ALIAS PASSWORD ? LIST "" "*" ? Select Folder (where Folder is the mailbox folder that you want, such as Inbox or Deleted Items) ? FETCH message number> All (where message number is 1, 2, 3, and so on) ? FETCH message number Body (where message number is 1, 2, 3, and so on) ? LOGOUT

MS Server 2003 : Getting an RDP session

To use the command line hacks, you might need to run them from another server if your local operating system doesn't include the commands. You will also need to make sure that you are logged onto that server with an administrative account. The easiest way to do that is just map a drive (you don't have to use a drive letter unless you choose to) net use /user:[username] \\servername\share Here's a command line hack that you can use to figure out what sessions are connected to the server. Note that you could substitute the IP address for the server name. query session /server:servername Now we know that the session ID of the offending session is 2. We can use that in the next step, which is using the reset command to log off that user. reset session [ID] /server:servername

NTLM in Firefox

Go to your Firefox address bar and type about:config. Look for the key called network.automatic-ntlm-auth.trusted-uris. Set that key’s value to a comma separated list of servers you want NTLM auth for. Name of each server must be exactly the same as in the URL used Seem to need to restart Firefox for it to take effect

Adding an 11G RAC service

. oraenv NDEV1C srvctl add service -d NDEV1C-s "OTIIDEV" -r "NDEV1C1,NDEV1C2" -P BASIC srvctl status service -d NDEV1C-s "OTIIDEV" srvctl start service -d NDEV1C-s "OTIIDEV" srvctl stop service -d NDEV1C-s "OTIIDEV" srvctl remove service -d NDEV1C-s "OTIIDEV" srvctl remove service -d NDEV1C-s "OTIIDEV" -r "NDEV1C1,NDEV1C2" alter system set  service_names='NDEV1C.ODLS.COM,OTIIDEV.ODLS.COM' scope=both alter system register

Grep for rows after and before a pattern

Gnu gre. grep -A 2 ORA-01555 bdump/alert_LIVE1.log | grep -B2 SYS.EXU9R -A = After -B = Before

SmartCVS - ignoring files in foundation edition

Create the file .cvsignore in your home directory with exclusion pattern per line For windows this is %USERPROFILE% and you need to rename the file to .cvsignore at the command prompt as explorer does not like files with only suffixes

Find with Grep

find . -exec grep -H -n -i 'string' {} \;

FoxyProxy

FoxyProxy is a Firefox addin that can automatically switch web proxy based on string matches to the entered URL. Trying this out with Portable Tor - seems a goody

The Illusionist (2009)

Sylvain Chomet (of Belleville Rendez-Vous - a great film) has a current project The Illusionist based on a Jacques Tati script - out in 2009 - one to watch for :)

Removing Duplicates in Excel 2007

Removing Duplicates in Excel 2007 1. To the right of your data, copy the heading from the column where you want to find unique values. 2. Select a cell in your data set. 3. In Excel 2007, choose the Advanced icon from the Sort & Filter group of the Data ribbon. 4. Choose Copy to another Location 5. In the Copy To box, specify the copy of your heading. In the Figure, this is cell D1 6. Click the box for Unique Records Only 7. Click OK"

Always show year with ls

ls -l --time-style=long-iso

Old rope : VNC Over SSH

Always forget how to do this and have to research it again. This time the scenario is getting an X session on a db server to install oracle software, but the sys admin has locked down the ports so that only the conventional ones (ssh) are open to the outside (well inside the intranet) world (found out using /sbin/iptables --list) . At least vnc is still installed. Of course the box is open for ssh - so using the very handy tool plink we first set off a vncserver process on the remote box noting down the port being used Then run the following from the command line on my local windows workstation plink.exe -ssh -L 5900:localhost:5903 REMOTE_USER@REMOTE_SERVER -pw PASSWORD This tunnels from the local 5900 port to the remote 5903 port on REMOTE_SERVER by opening a remote session for REMOTE_USER with PASSWORD Then by opening a vnc viewer locally looking at localhost:5900 we get the desired vnc session. Fini

Shuttle KPC

Also see koolu

What is BlogThis! ?

Clicking BlogThis! creates a mini-interface to Blogger prepopulated with a link to the web page you are visiting, as well as any text you have highlighted on that page. Add additional text if you wish and then publish or post from within BlogThis!

BlackBerry: Receiving messages auto-filed in subfolders

I wasn't receiving messages that were being filed by a rule I had set up in outlook - looks like by default the BlackBerry only replicates Inbox and Sent Items - but found a fix here - summary below How To - Enable Folder Redirection with Inbox sub-folders From the Home screen, go to Messages. Click the trackwheel and select Options - Email Settings. Click the trackwheel and select Folder Redirection. In the Folder Redirection menu, expand the Mailbox by highlighting it, clicking trackwheel and selecting Expand. Under the Mailbox, expand the Inbox by highlighting it, clicking the trackwheel and selecting Expand. Highlight the folder on which you want to enable redirection. Click the trackwheel and select Change Option. Note: If you are unable to select Expand for any of the folders, or if the Change Option menu option is unavailable, try turning Wireless Reconcile on the handheld off and then on again. You can find Wireless Reconcile in Messages - Options - Email Reconciliation. Click the trackwheel and select Save to save your changes. Fini