October 27, 2009

ORA-27069 and ORA-01114

When creating a new object on a database (e.g. an index, a table) you get a set of ORA-27069 errors, each of them accompanied by ORA-01114. The description for these errors is:

ORA-27069 skgfdisp: attempt to do I/O beyond the range of the file
Cause: This is an internal error. The range of blocks being read or written is
outside the range of the file, additional information indicates the starting block
number, number of blocks in I/O, and the last valid block in the file.
Action: Check for a trace file and contact Oracle Support Services.

ORA-01114: IO error writing block to file string (block # string)
Cause: The device on which the file resides is probably offline. If the file is a temporary file, then it is also possible that the device has run out of space. This could happen because disk space of temporary files is not necessarily allocated at file creation time.
Action: Restore access to the device or remove unnecessary files to free up space.

How to work it out:

This can happen for a multitude or reasons: damaged storage, lack of storage space, etc. On Windows, namely Windows Server 2003 running a 32-bit file system, this can happen when one of the files in your tablespace reaches or crosses 4GB, 8GB, 12GB etc, so this may be the first thing to check.

You can use Oracle Enterprise Manager to check this: http://download.oracle.com/docs/html/B14399_01/rev_precon_db.htm#CACDJBBF

If, in fact, the problem is related to having a file with the characteristics above, you have plenty of options. For example, if you have an 8GB file in the USERS tablespace, you may have trouble adding data to that tablespace (which is the default for user tables, indexes, etc). To solve it, you can:
- set the file's maximum size below the size limit that started causing problems (e.g. 8GB)
- add more files to the tablespace where that file resides, so the tablespace can grow without creating very large files
- move indexes to a specific table space (ALTER INDEX REBUILD TABLESPACE ;)
- furthermore, you can create specific tablespaces for specific objects and move them there, which is way better than having every single object cramping up your default tablespace


All in all, the best way to solve this problem is to correctly manage your storage, tablespaces and files. Oracle ASM can help you with this. If you're running older versions, start here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/physical.htm

June 18, 2009

Format large HDD as FAT32 for your PS3 or DVD

Do you have a PS3 or DVD/Bluray player on which you'd like to use your external USB HDD, but it's capacity (160GB, 250GB, 320GB, 500GB, 1TB, whatever) results in the drive being formatted in NTFS, and therefore your console or disc player can't read it?

You have to format it in FAT32 so your Playstation or DVD can read it, but as you probably noticed already, you can't do this in Windows XP: http://support.microsoft.com/kb/314463

But you can simply grab a free utility, like EASEUS Partition Manager, format the external USB HDD to FAT32 and you're good to go! :)

May 8, 2009

Copy SQL Server database tables

Ever needed to copy an SQL Server database, but get only its data and leave out everything else?
Here's a T-SQL script that you can use to copy all the database's tables' data, and leave out everything else (indexes, partitions, programmability components, etc...)
The script will go through all tables in the 'dbo' schema of the source database (named 'sourceDatabase' in this example), create a copy of the table in the 'dbo' schema of the destination database (named 'targetDatabase') and insert all data from the source table in the destination table (by using a SELECT INTO).

Here's the code:

DECLARE @tableName varchar(300)
DECLARE @sqlStmt nvarchar(300)

DECLARE curTables CURSOR LOCAL FOR
SELECT table_name FROM sourceDatabase.INFORMATION_SCHEMA.tables


OPEN curTables

FETCH NEXT FROM curTables INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlStmt = 'SELECT * INTO targetDatabase.dbo.' + @tableName + ' FROM sourceDatabase.dbo.' + @tableName
EXEC sp_executesql @sqlStmt

FETCH NEXT FROM curTables INTO @tableName
END

CLOSE curTables
DEALLOCATE curTables