Square DBA - Oracle DBA Talk

Moving tables to a new tablespace

Need to move a table to a new tablespace?  That is just what I needed to do today.  I have a couple if hot datafiles that I need to relieve the load on.  Basically I have one staging tablespace for loading and manipulating data beforethe data is moved to the production tables.  I have several larger jobs that are now utilizing this tablespace.  There are two problems with my current setup.  Multiple jobs are hitting the tablespace at the same time and the tablespace is on a single RAID group.  This particualr server has two RAID groups so I created multiple stagin tablespaces (one for each job).  I also split the datafiles for these new tablespace across both RAID groups.  Move the tables was easy.

For my non-partitioned tables I used:

ALTER TABLE O1DW.STG_OSCAR_CALLPROC
MOVE TABLESPACE STG_CSE;

and for my partitioned tables I used:

ALTER TABLE O1DW.STG_OSCAR
MOVE PARTITION STOP_EVENT TABLESPACE STG_CSE; 

Reclaim lost space with tune2fs

File system Reserved Block Count is supposed to reduce Linux file system defragmentation, to allow root user login for maintenance or to allow Linux system logging facility to function properly in case file system running low of free disk space.

File system utility called tune2fs can be used to tune Linux ext2 / ext3 file system parameters, such as adjusting file system reserved block count, frequency of file system force-check based, maximal time between two file system checks, behavior of the kernel code when errors are detected, overriding the default ext3 journal parameters, etc.

tune2fs -l /dev/hda

or

dumpe2fs -h /dev/vg0/lvol1

could be used to inspect a file system superblock information. Take note on these fields of both tune2fs and dumpe2fs command output:

Reserved block count: 3399024
Free blocks: 4997248
Free inodes: 42473835
First block: 0
Block size: 4096

The block size is measured in byte unit. In this case, it’s 4,096 byte or 4KB. So, the Reserved Block Count: 3399024 is equal to 13,922,402,304 byte or roughly equivalent to 14GB.

Now, if execute the command

tune2fs -m 0 /dev/vg0/lvol1

will set the percentage of file system reserved block count to 0% for /dev/vg0/lvol1 file system (the first column of df command output). It’s OK to totally disable file system reserved block count, if the file system is not used by root user account or storing system log and system/program temp files (e.g. /var and /tmp). For example, file system dedicated to Oracle datafiles are safe to disable file system reserved block count.

So, if you’re running a Linux machine with 320GB storage array and haven’t tune the file system reserved block count, you may tune it now to claim back as much as 16GB of free disk space!

Moving an Oracle tablespace

Today I needed to move a tablespace between mount points. My primary mount points were starting to fill up and I needed to make some breathing room. I recently added secondary storage to the server. I say secondary because the storage just added is on less spindles and slightly slower disks. The idea is to move less accessed data to the secondary storage. One on of my first moves involves the USERS tablespace. In this particular database the USERS tablespace is simple used to store temp type tables and is not used for anything “production”. Here are the steps I followed.

In SQLPlus:

alter tablespace USERS offline;

OS Level:

cd /u01/app/oracle/oradata/dw2

cp users01.dbf /storage/oradata/dw2/users01.dbf

Back in SQLPlus:

alter tablespace USERS
rename datafile /u01/app/oracle/oradata/dw2/users01.dbf
to /storage/oradata/dw2/users01.dbf;

alter tablespace USERS online;