Oracle sqlplus query output into file

Quite useful, I had the case where I needed to provide a CSV file to an external party from our Oracle 10gdatabase. No problem, there are loads of CSV export tools out there. But it then transpired that the CSV’s that I was generating were using quote (“) marks to identify fields e.g. “field1″,”field2″ and so on…

When I ran the script in something like SQL Developer it would spool the file and also spool the query as well into the file no matter what I specified. Along with this it also wouldn’t recognise usage of variables in SQL Developer. Basically what I’m saying here is write the query in something like SQL Developer by all means but for writting and debugging a CSV spooler etc… just save the hassle and go straight to SQL*Plus.

First of all lets start with how to spool the CSV file without leading line breaks, no quote marks etc… the below is the SQL script to run, we’ll need to save this in order to run it later.

c:\edit tes.sql

set echo off
set termout off
set feedback off
set linesize 100
set pagesize 0
set sqlprompt ”
set trimspool on
set show off
spool c:\emp.csv

select * from mas_pasien;

spool off

Continue reading


Install Guest Addition VirtualBox for Linux

As mentioned in the section called “Some terminology”, the Guest Additions are designed to be installed inside a virtual machine after the guest operating system has been installed. They consist of device drivers and system applications that optimize the guest operating system for better performance and usability. Please see the section called “Supported guest operating systems” for details on what guest operating systems are fully supported with Guest Additions by VirtualBox.

The VirtualBox Guest Additions for all supported guest operating systems are provided as a single CD-ROM image file which is called VBoxGuestAdditions.iso. This image file is located in the installation directory of VirtualBox. To install the Guest Additions for a particular VM, you mount this ISO file in your VM as a virtual CD-ROM and install from there.

Each version of VirtualBox, even minor releases, ship with their own version of the Guest Additions. While the interfaces through which the VirtualBox core communicates with the Guest Additions are kept stable so that Guest Additions already installed in a VM should continue to work when VirtualBox is upgraded on the host, for best results, it is recommended to keep the Guest Additions at the same version.

there is tips for installing guest addition for linux :

Continue reading

Unexpected error about USB 2.0

It’s really strange (again). I had successfully started the guest OS, and shut down at least 2 times (because I remember installing the new version addition). Last I saved the state and now I tried to resume it, I got this error:

Failed to open a session for the virtual machine Windows 7. Implementation of the USB 2.0 controller not found! Because the USB 2.0 controller state is part of the saved VM state, the VM cannot be started. To fix this problem, either install the ‘Oracle VM VirtualBox Extension Pack’ or disable USB 2.0 support in the VM settings (VERR_NOT_FOUND). Unknown error creating VM (VERR_NOT_FOUND).

What’s the meaning of it? I had already several times of startup/shutdown, yet it complains about USB 2.0 support if I save it.

Recently when I opened a saved Windows 7 virtual machine in VirtualBox 4.0.2 after the update, it showed a message about ‘ Oracle VM VirtualBox Extension Pack’ for better USB2.0 support. Its because of updated 4.0.2 version and features. The warning message did not give proper  information of downloading and installing  this package. This small guide shows how to download and install Oracle VM VirtualBox Extension Pack in Windows host machine.  Here is the message,

Implementation of the USB 2.0 controller not found!
The device will be disabled. You can ignore this warning but there will be no USB 2.0 support in your VM. To fix this issue, either install the ‘Oracle VM VirtualBox Extension Pack’ or disable USB 2.0 support in the VM settings

Its always better to have a guest operating system inside desktop virtualization software with USB 2.0 support for faster data transfer.

Continue reading

ORA-15041: diskgroup space exhausted

The following are the errors seen during tablespace creation in an Oracle DB with ASM.

2010-02-09 23:27:35.187 ERROR rcu: oracle.sysman.assistants.rcu.backend.action.AbstractAction::handleNonIgnorableError: Received Non-Ignorable Error: ORA-01119: error in creating database file ‘+DATA’
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup space exhausted

The related disk group could have run out of space, due to more number of tablespaces, in this case there were 84. Firstly look for space, and appropriately cleanup some of them or just new disks.

To Check:
select NAME, OS_MB, TOTAL_MB, FREE_MB from v$asm_disk;

To Cleanup:
drop tablespace <NAME> including contents and datafiles;

To Increase Disk Space:
alter diskgroup DISK1 rebalance power 11;
alter diskgroup DISK2 add disk ‘path’;
alter diskgroup test_dg add disk ‘/dev/hdb10′,’/dev/hdb11’;

asmcmd Utility

Moving a datafile from File System to ASM

Moving datafiles from file system to ASM can be done in different ways, one of them using the RMAN copy command.

These are the steps:

1. Check where to build a new file system based tablespace:

[oracle@rac1 ~]$ cd /u01/oradata/racdb
[oracle@rac1 ~]$ df -k .
Filesystem           1K-blocks      Used Available Use% Mounted on
31457280  17540576  13819976  56% /u01

2. Connect to sqlplus and create a new tablespace

[oracle@rac1 racdb]$ sqlplus / as sysdba

SQL*Plus: Release – Production on Tue Jun 19 06:07:50 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> create tablespace trtst datafile ‘/u01/oradata/racdb/trtst01.dbf’ size 150M;

Tablespace created.
Continue reading

Moving from ASM storage back to filesystem

Whilst it is not very common to move from ASM to filesystem I needed to prepare a regression plan for the migration of a 450 datafile database in the event of problems during the migration or shortly afterwards.

The example below is from a small database called TEST and I used filestore in /app/oracle whereas normally I would have a /oradata/SID/ filesystem

The details below should be enough to get someone through the migration and the sql scripts can be modified to be more generic if necessary but it is not too hard to use them as they are now.

List the datafiles and we will add a second datafile to the users tablespace to provide an example of an issue that can occur.

SYS@TEST SQL>select file_name from dba_data_files;  +DATA/test/datafile/undotbs1.301.697649965

mkdir /app/oracle/TEST

Some tablespaces may have multiple datafiles which would end up with the same name using the code above. However as a quick circumvention I manually identified those tablespaces using this sql and then edited the new data file names

select tablespace_name,count(file_name) from dba_data_files group by tablespace_name ORDER BY 2

Continue reading