Reduce Size of TEMP Tablespace

Similar to Reduce Size of UNDO Tablespace, you need to recreate temporary tablespace in order to reduce the size. Issue a resize statement might result error below:

alter database tempfile '/u01/database/temp1.dbf' resize 250M
ORA-03297: file contains used data beyond requested RESIZE value

To recreate the temporary tablespace, you need to create a second temporary tablespace:

1. Create second temporary tablespace
   create temporary tablespace temp2 TEMPFILE '/u01/database/temp2.dbf'size 5M reuse autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
2. Set second temporary tablespace as default
   alter database default temporary tablespace temp2;
3. Drop the first temporary tablepsace
   drop tablespace temp including contents and datafiles;
4. Recreate first temporary tablespace
   create temporary tablespace temp tempfile '/u01/database/TEMP01.dbf' size 300M reuse autoextend on next 50M maxsize unlimited extent management local uniform size 1M;
5. Set first temporary tablespace as default
   alter database default temporary tablespace temp;
6. Drop the second tablespace
   drop tablespace temp2 including contents and datafiles;

Advertisements

Reduce Size of UNDO Tablespace

UNDO tablespace can grow quickly if long and uncommitted transactions are executed, sometime even take out the whole hard disk and crash the database, and lastly database itself not even can be started up.

To reduce the size of the data file (especially when it has taken all your hard disk space), we need to recreate the default UNDO tablespace because the resize data file statement will not work:
alter database datafile 'undo.bdf' resize 100M;

1. Log in as sysdba
   connect / as sysdba
2. Start database in nomount state
   startup nomount;
3. Create temporary UNDO tablespace in anywhere got free space
   create undo tablespace undo2 datafile '/tmp/undo2.dbf' size 50M autoextend off;
4. Set the temporary UNDO tablespace as default UNDO tablespace
   alter system set undo_tablespace = undo2;
5. Drop the original UNDO tablespace.
   drop tablespace undo1 including contents and datafiles;
6. Recreate the original UNDO tablespace.
   create undo tablespace undo1 datafile '/u01/database/undo1.dbf' size 500M autoextend on next 5M maxsize 1000M;
7. Set back the original UNDO tablespace to default UNDO tablespace
   alter system set undo_tablespace = undo1;
8. Drop the temporary UNDO tablespace
   drop tablespace undo2 including contents and datafiles;

CHKCONFIG Equivalence in Ubuntu

CHKCONFIG is a utility for the Red Hat family of Linux, to configure the background services. While in Ubuntu, you can either install CHKCONFIG:

sudo apt-get install chkconfig

Or use the Ubuntu service configuring utility RCCONF:

sudo rcconf

It has a nice console interface for user to enable or disable the services.

SQL Server Cannot Start After Unused For a Long Time

If you’re using SQL Server in Windows XP, you might have problem to start the database service after a long period of unused. You can see error message below log in the error log (check the log directory where you install SQL Server):

The file "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\master.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

This is resulted by a compression feature of Windows XP, which certain files will be compressed after unused for a period of time. To reconfirm the situation, change directory to data directory and right click on any of the MDF or LDF file. Click Properties, at the bottom of General Tab click the Advance button. You can see the Compress contents to save disk space at the Compress or Encrypt attributes section is ticked.

To solve the problem, untick this feature of all the MDF and LDF files in the Data directory, and your database service should be able to start.

Tape Drive Hardware Compression

Tape drive normally come with hardware compression feature. For example an LTO3 tape can store 400GB data with the hardware compression feature disabled, and able to store 800GB data with hardware compression feature enabled. Hardware compression is done at the tape drive, instead of using TAR or ZIP command.

By default, the hardware compression is enabled. To disable it, you can issue command below:

mt -f /dev/st0 compress 0

To enable back the compression feature:

mt -f /dev/st0 compress 1

To disable the default option of hardware compression:

mt -f /dev/st0 defcompression -1

To enable back the default option:

mt -f /dev/st0 defcompression 1

Transfer rate is much higher with hardware compression enabled.