OSX Yosemite: Write into NTFS USB stick

Suppose you have an NTFS formatted USB stick labelled "USB", then

1. vi /etc/fstab
   
2. Eject then re-insert the USB stick
3. The device does no longer shows up in the Finder under Devices, but you can access it under /Volumes, and it has become writeable

Oracle Average Active Sessions per hour

From A Tour of the AWR Tables

select
   to_char(round(sub1.sample_time, 'HH24'), 'YYYY-MM-DD HH24:MI') as sample_hour,
   round(avg(sub1.on_cpu),1) as cpu_avg,
   round(avg(sub1.waiting),1) as wait_avg,
   round(avg(sub1.active_sessions),1) as act_avg,
   round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
   ( -- sub1: one row per second, the resolution of SAMPLE_TIME
     select
        sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
        count(*) as active_sessions
     from
        dba_hist_active_sess_history
     where
        sample_time > sysdate - (&hours/24)
     group by
        sample_id,
        sample_time
   ) sub1
group by
   round(sub1.sample_time, 'HH24')
order by
   round(sub1.sample_time, 'HH24')
;

Linux load average

Examining Linux load average

Oracle: select from object type

select treat(user_data as prov_db.prov_msg_type).request_id request_id from prov_db.qt_prov_msg;

Oracle: ORA-38301: can not perform DDL/DML over objects in Recycle Bin

As of 10.2g, Oracle introduced a recycle bin for dropped objects, in order to recover them with flashback.
So, if for example, you drop a table like in

DROP TABLE RADIUS_DB.RADACCT3;

Then you won't be able to drop related contraints that were renamed following the table drop, like BIN$7Cd8os82uGngQAB/AQANPQ==$0

To see objects in recycle bin:

select * from dba_recyclebin order by droptime desc;

To remove the BIN* constraints, you need to remove the dropped table from the recycle bin:

purge table radius_db.radacct3;

Oracle: schemas/tables measurements

Size of schemas:

SELECT owner, sum(bytes)/1024/1024 "Size in MiB"
FROM dba_segments
WHERE owner not in ('GPRS_TEST_DB', 'GPRS_TRIAL_DB', 'MSG_DB', 'PARAM_DB', 'PERF_DB', 'TASK_DB', 'VAS_INDICATOR_DB', 'VAS_LOG_DB', 'VAS_MULTIMSI_DB', 'VAS_PROPERTY_DB', 'XDB')
and owner like '%_DB'
group by owner
order by owner;

Size of tables

SELECT owner, segment_type, segment_name, sum(bytes)/1024/1024 "Size in MiB"
FROM dba_segments
WHERE segment_type like 'TABLE%'
and segment_name not like 'BIN%'
group by owner, segment_type, segment_name;

Num_rows of table

select owner, table_name, num_rows
from dba_tables
WHERE owner not in ('GPRS_TEST_DB', 'GPRS_TRIAL_DB', 'MSG_DB', 'PARAM_DB', 'PERF_DB', 'TASK_DB', 'VAS_INDICATOR_DB', 'VAS_LOG_DB', 'VAS_MULTIMSI_DB', 'VAS_PROPERTY_DB', 'XDB')
and owner like '%_DB'
and table_name ='HT_CO_OFFER'
order by owner;

Count indexes per table

SELECT owner, table_name, count(index_name)
FROM all_indexes
WHERE owner not in ('GPRS_TEST_DB', 'GPRS_TRIAL_DB', 'MSG_DB', 'PARAM_DB', 'PERF_DB', 'TASK_DB', 'VAS_INDICATOR_DB', 'VAS_LOG_DB', 'VAS_MULTIMSI_DB', 'VAS_PROPERTY_DB', 'XDB')
and owner like '%_DB'
group by owner, table_name
order by owner;

OSX: convert Windows text file into OSX format

A text file created under Windows and using accents shows bad accents under OSX, e.g. amitiÈ instead or amitié or opËre instead of opère.
This is due to the encoding, which is Latin1 under Windows, and UTF8 under OSX.

Fortunately, OSX has a built in conversion tool to fix this:
Launch Terminal
> iconv -f latin1 -t utf8 BD_anais.txt > BD_anais2.txt

Google drive: Host some CSS/JS files

This is how to store some web files in what Google calls the new Drive as of today, 25-09-2014.
Say we want to fetch some CSS file from a folder on Google Drive.

1. Right click on the folder and select "Share..."

2. Select "More..." in the popup dialog

3. Check the "Public on the web" box

4. A public URL is proposed in the following dialog

5. The folder URL is of the form:
   
   https://drive.google.com/folderview?id=0B7uYPyj_NiVxYVltWjZ0VUVULTQ&usp=sharing.
   
   In order to use it in a web page, pick the id from the proposed URL and re-use it in the following new URL:
   https://googledrive.com/host/0B7uYPyj_NiVxYVltWjZ0VUVULTQ
   
6. Then add the filename to be used, e.g.
   
   https://googledrive.com/host/0B7uYPyj_NiVxYVltWjZ0VUVULTQ/toto.css

Et voila.

Oracle: Dump data into CSV file

Tom Kyte gives details on how to do that here:
http://tkyte.blogspot.fr/2009/10/httpasktomoraclecomtkyteflat.html

NB: See the flat script as a base and use chr(59) if you want to have ';' instead of tabs in the output.

Oracle: Handle error codes with sqlplus

Tom Kyte explains it here:
http://tkyte.blogspot.fr/2010/04/new-thing-about-sqlplus.html

1. Start with

   set errorlogging on identifier toto
   delete from sperrorlog where identifier = 'toto'
   /
   

2. Then use some sqlplus commands
3. And terminate with

   col nb_errors new_value nb_errors
   select count(*) as nb_errors from sperrorlog where identifier = 'toto'
   /
   exit nb_errors

This allows for testings the return code of sqlplus in a bash / perl script.

NB: See http://ss64.com/ora/syntax-sqlplus.html for the sqlplus syntax

Oracle: ORA-00600: internal error code, arguments: [kqd-objerror$]

If this error occurs when trying to drop a trigger, e.g.

DROP TRIGGER RADIUS_DB.RADACCT_RADACCTID;

1. Find the object_id of the trigger

   select object_id from dba_objects where object_name='RADACCT_RADACCTID';

2. Insert the object_id into objerror$

   insert into sys.objerror$ values(126915);
   commit;

3. shutdown abort
4. startup
5. Retry to drop the trigger, this should be OK now.

   DROP TRIGGER RADIUS_DB.RADACCT_RADACCTID;

Bash: delete last n lines of a file

head -n -100000 FILE > NEWFILE

Oracle: using statspack

Use the steps as described in:
http://www.oracledistilled.com/oracle-database/performance/installing-and-configuring-statspack/
which are:
1) Create the tablespace to hold the Statspack tables.

CREATE TABLESPACE statspack_db_data DATAFILE 
  '/home/oracle/oracle/oradata/CRMMBQT/statspack_db_data_01.dbf' SIZE 500M AUTOEXTEND ON NEXT 10M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL uniform size 1M
SEGMENT SPACE MANAGEMENT AUTO;

2) Run the ORACLE_HOME/rdbms/admin/spcreate.sql script to create the PERFSTAT schema.

cd /home/oracle/oracle/product/11.2.0/dbhome_1/rdbms/admin
> sqlplus / as sysdba
SQL> @spcreate

A list of available table spaces will be presented. Type in the name of the table space created earlier and hit the enter key.
When selecting a temporary table space for the PERFSTAT user, keep the default tablespace as proposed.

3) Execute a snapshot w/ level 7:

exec statspack.snap(i_snap_level => 7);

4) Run a report:
ORACLE_HOME\rdbms\admin\spreport.sql is used to generate an instance level report.
You will then pick a beginning snapshot and an ending snapshot, as well as a file name to save results.

cd /home/oracle/oracle/product/11.2.0/dbhome_1/rdbms/admin
> sqlplus / as sysdba
SQL> @spreport

Oracle: drop interval partitions

source: http://prutser.wordpress.com/2010/01/11/dropping-interval-partitions/

-- Count number of partitions: 905
select count(*) from dba_tab_partitions where table_name = 'AT_PROCESS_LOG';

-- List of partitions to drop: keep 30 latest partitions
select * from (
select partition_name, interval, high_value, partition_position
from dba_tab_partitions where table_name = 'AT_PROCESS_LOG'
order by partition_position desc
) where 1 < partition_position and partition_position < 905 - (30-1);

Drop syntax examples:

-- Drop partition w/ high_value = 16-03-2012
alter table monitor_db.at_process_log drop partition for (to_date('15-03-2012','DD-MM-YYYY')); 

-- Drop partition from name
alter table monitor_db.at_process_log drop partition SYS_P61;

Oracle: Random UUID

v_uuid RAW(16) := sys.dbms_crypto.randombytes(16);

Delete files older than

E.g. Delete files in current directory older than 90 days :

find . -mtime +90 -delete

perl -c equivalent in python

You actually need to compile the file like in:

python -m py_compile toto.py

Mavericks: mount network drive at startup

1. Go to System Preferences > Users & Groups
2. Push "Login Items" tabbed button
3. Drag the network drive from desktop into the "Login Items" list (e.g. Public here)

Finally, drag the network drive from desktop into the Finder left pane to make it available from Finder.

CentOS: yum hangs on "Loading mirror speeds from cached hostfile"

> sudo yum install cpan
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
...

Disable this behavior with:

> vi /etc/yum/pluginconf.d/fastestmirror.conf

enable=0

CentOS: missing repo

If you get this error with yum:

yum install ...
Error: Cannot find a valid baseurl for repo: base

Then:

vi /etc/yum.repos.d/CentOS-Base.repo

and add

baseurl=http://vault.centos.org/6.3/os/i386/

given that 6.3 is your CentOS version

CentOS / VirtualBox: copy paste shit from/to Windows and urxvt

#! /usr/bin/perl

sub on_sel_grab {
    my $query = quotemeta $_[0]->selection;
    $query =~ s/\n/\\n/g;
    $query =~ s/\r/\\r/g;
    system( "echo -en " . $query . " | xsel -ibp" );
}

I called it clipboard, saved it to /usr/lib64/urxvt/perl/, and enabled in ~/.Xdefaults:

urxvt*perl-ext-common:      default,matcher,clipboard

Perl: Epoch to date

perl -e 'print scalar gmtime 1404982047'
Thu Jul 10 08:47:27 2014

Set up Word 2010 / 2013 styles

If when re-opeing your doc, Word decided to turn your existing styles into shit (e.g. 3.4 became 1.49), start all the way from the start:

• Enable Developer menu: File | Options | Customize Ribbon then tick the Developer checkbox in the right hand side list
  
  
• Then in the new Developer menu, push the Document Template button, and untick the Automatically update document styles checkbox
  
• Then follow this tutorial: How to create numbered headings or outline numbering in Word

Blocked loading mixed active content

A workaroung in FF is:
Go to about:config
and
security.mixed_content.block_active_content = false

Javascript: force client to reload javascript files

Ctrl+F5

or add a version information in file specification, and change it every time the js file is updated

<script type="text/javascript" src="file.js?1.1"></script>

OSX: from 10.9.2 to 10.9.3

Install the OS X Mavericks 10.9.3 Update (Combo)
Use MultiBeast 6.3 with this conf.

Reboot, et voila:

XEmacs: show ^M (\r)

M-x find-file-literally /path/to/file

Oracle: Caching techniques to call functions in queries

Efficient Function Calls From SQL

HTC Pico/Explorer: Extend external memory after CM10.2 install

Use this link to download MiniTool Partition Wizard Home Edition

http://download.cnet.com/MiniTool-Partition-Wizard-Home-Edition/3000-2094_4-10962200.html?part=dl-6285158&subj=dl&tag=button

1. Connect your SD card to PC through USB mode
2. Run the downloaded file (pwhe8.exe)
3. Select your SD card 2nd partition and right click to select DELETE option
4. Again select your 2nd partition (unallocated space at this moment) and right click to select CREATE option
5. Select partition type as ext4 and drive as primary (Not logical) from popup window
6. At last select the apply button from tool bar (first button)

Done - you created a EXT4 partition

 Then install Mounts2Sd from Google Play, then
1. After installing the app, open it and allow root access
2. Configure install script
3. Enable applications, data , cache, etc.
4. Reboot

Oracle: Stop a datapump job run via crontab

Show datapump jobs

select * from dba_datapump_jobs;

Stop a datapump job

DECLARE
   handle NUMBER;
BEGIN
    -- Format: DBMS_DATAPUMP.attach('[job_name]','[owner_name]');
    handle := DBMS_DATAPUMP.attach('SYS_EXPORT_FULL_02','SYSTEM');
    DBMS_DATAPUMP.STOP_JOB(handle, 1, 0);
END;

Perl md5

perl -MDigest::MD5 -e 'print Digest::MD5::md5_hex("mbqt")'

Use quotes in a perl one-liner

 cat /media/host/vbox.txt | perl -e 'while (<>) { print "'"'"'$1'"'"',\n" if /^(\d+)/; }'

Oracle: select from list of values

select distinct column_value from table(sys.odcinumberlist(1,1,2,3,3,4,4,5));

or

select column_value from table(sys.dbms_debug_vc2coll('One', 'Two', 'Three', 'Four'));

ORA-01555: snapshot too old: rollback segment number 61 with name "_SYSSMU61_1706556987$" too small

SQL> select max(maxquerylen) from v$undostat;

26687

Add 20% to this value and update UNDO_RETENTION parameter.

SQL> ALTER SYSTEM SET UNDO_RETENTION = 32024;

Update CPAN perl packages

cpan upgrade /(.*)/

Mavericks 10.9.2: network lost after upgrade

Symptom: network stopped working suddenly after Mavericks upgrade
History:

1. Updated Mavericks to 10.9.2 using Combo update
2. Re-installed conf. using MultiBeast 6.2.1
3. Reboot
4. Network KO
5. Repair permissions: sudo diskutil repairPermissions /
6. Reboot

Oracle PL/SLQ: On Emulating FINALLY

On Emulating FINALLY by Steven Feuerstein

Oracle: monitoring long tasks

SELECT start_time, username, target, sofar blocks_read, totalwork total_blocks, round(time_remaining/60) minutes FROM v$session_longops WHERE sofar <> totalwork;

Oracle: remove duplicate records

Tom said: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15258974323143

Git tips from the trenches

https://ochronus.com/git-tips-from-the-trenches/#null

PL/SQL: how to deal with nullable variables in query

where decode(column, variable, 0) is not null

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Find the blocking session port, and then kill the process listening on that port.

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT 
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, 
V$PROCESS P, V$SQL SQ 
WHERE L.OBJECT_ID = O.OBJECT_ID 
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR 
AND S.SQL_ADDRESS = SQ.ADDRESS;

[root@dwh1 ~]# netstat -ap | grep 22735
tcp        1      0 dwh1.prod.mbqt:ncube-lm     dwh1.prod.mbqt:22735        CLOSE_WAIT  19050/oracledwhmbqt 

[root@dwh1 ~]# kill -9 19050

VirtualBox: force guest IP address

> sudo vi /etc/sysconfig/network-scripts/ifcfg-eth0

DEVICE=eth0
HWADDR=08:00:27:B5:56:05
NM_CONTROLLED=no
ONBOOT=yes
BOOTPROTO=static
#IPADDR=192.168.2.8
IPADDR=192.168.1.101
NETMASK=255.255.255.0
TYPE=Ethernet
DNS1=8.8.8.8
DNS2=8.8.4.4

Then restart network

> sudo service network restart

Bulk Processing with BULK COLLECT and FORALL

Oracle magazine article by Steven Feuerstein

/usr/bin/perl^M: bad interpreter

One perlish way to fix this:

>./fm_test.pl
-bash: ./fm_test.pl: /usr/bin/perl^M: bad interpreter: No such file or directory

is:

> perl -pi -e 'tr[\r][]d' ./fm_test.pl

Another way is to use dos2unix

Compute files size

find . -name "*" -ls | awk '{total += $7} END {print total}'

Vim backspace issue

If backspace leaves ^? in vim, this is most probably because of "stty erase ^H" being present either in your .bashrc or .bash_profile.
Remove it and Vim will be happy with backspace.