COMPRESS TABLESPACE 11g

compressionoracle

I have quite big datapump dump file and tried to load it into compressed tablespace that is created with "DEFAULT COMPRESS FOR OLTP" because of lack of free space. Anyway, when I look at "compressed" dba_segments and compare them with source segments they have the same size.
How does this compress feature work? I expected lower size of compressed tables but it seems doesn't work this way. P.S. Datapump job is not finished due to no free space left.

Best Answer

The datapump export has the complete DDL for every object you exported. So while the default for the tablespace is to compress, this can (and will) be overridden by any explicit setting for a table.

Check the COMPRESSION column on xxx_TABLES for the schema(s) you are importing to see if you are truly getting any compression.

You will need to use "transform=segment_attributes" variants on import to ensure that you strip off the storage elements of your objects to that they pick up the default you set at tablespace level.

Related Solutions

How to Create Tablespace in Oracle 11g

The answer to your question can be found in the SQL Language Reference (excerpt follows).

Creating Basic Tablespaces: Examples

This statement creates a tablespace named tbs_01 with one data file:

CREATE TABLESPACE tbs_01 DATAFILE 'tbs_f2.dbf' SIZE 40M ONLINE;

It sound like you are new to Oracle databases. Oracle provides a wealth of documentation at http://docs.oracle.com/en/database. Specifically, I recommend reading their excellent introduction to the database called the Concepts Guide.

Sql-server – Compress backup file using T-SQL

Backup compression was introduced in SQL 2008 Enterprise, and in SQL2008R2 and later, added to Standard Edition.

When creating a backup, you can specify the WITH COMPRESSION keyword, which will ensure that the database backup size is compressed to approximately a similar size as a zipped 'normal' backup file.

For SQL2005 or older, the best way really (other than using a specific tool like RedGate) is to ensure that xp_cmdshell is enabled on the instances, and then use a command line to compact using for e.g. WinRar.

I use a cmd file that looks something like this:

@echo off
Set "winrarPath=C:\Program Files\WinRAR"
"%winrarPath%\winrar.exe" a -r "ZippedBackup.zip" "BackupFile.BAK"

You can then execute this cmd file from your 2000 / 2005 instances. You can also play around with passing %1-type variables to the cmd file, if your filenames are not generic.

Best Answer

Related Solutions

How to Create Tablespace in Oracle 11g

Sql-server – Compress backup file using T-SQL

Related Question