Feeds:
Posts
Comments

Posts Tagged ‘space managment’

Recently i had to move a schema of size 70GB to another database[Oracle 10.2.0.1]. That was a readonly schema and never had any DML on any of the tables. In the new database i did compress the tables and the size was brought down from 70Gb to 22Gb.

1]Divided the tables based on the size.
2]Created the tablesapces.
3]Pre-created the tables on respective tablespaces.
4]Imported the tables.
5]Compressed and moved big tables from APP_BIG_TABLES_TEMP to APP_BIG_TABLES.
6]Rebuild the primary key indexes of the moved tables.
7]Droped APP_BIG_TABLES_TEMP.
8]Created the indexes.

The tables where create with PCTFREE 0 to take maximum advantage of the space in each extents as i was sure that never an UPDATE would happen on any of these tables.

Compress statement with “move”
======================
ALTER TABLE <table_name> MOVE TABLESPACE <to new tablespace> COMPRESS NOLOGGING PARALLEL (degree N);
eg: ALTER TABLE SALES_INFORMATION MOVE TABLESPACE APP_BIG_TABLES COMPRESS NOLOGGING PARALLEL (degree 8);

COMPRESS alone will only compress the tables, to recalim the space use MOVE in the statement.
My Server had 32 CPUs, but i just used 8. I should leave the rest for other applications.

To move tables with LOB segments:
ALTER TABLE <TABLE_NAME> MOVE TABLESPACE <new_tablespace> LOB(<lob_column_1) STORE AS (TABLESPACE <new_tablespace) LOB(<lob_column_2) STORE AS (TABLESPACE <new_tablespace);

Why did not i compress and move the table in the same tablespace?
COMPRESS and MOVE of tables in the same tablespace will not bring down the datafiles high water mark and i will not be able to resize the datafiles to a smaller size. So i found it easy to drop the very big APP_BIG_TABLES_TEMP once the tables were compressed and moved to APP_BIG_TABLES.

BEFORE COMPRESS
================
OBJECT_TYPE COUNT SIZE(in GB)
—————— ———- ———-
INDEX 110 7.73681641
TABLE 55 62.6555786
———-
sum 70.392395
AFTER COMPRESS
===============
OBJECT_TYPE COUNT SIZE(in GB)
—————— ———- ———-
INDEX 110 2.375
TABLE 55 19.8398438
———-
sum 22.2148438

Expert are always welcome for their valuable comment or suggestion for the above post.

 

Advertisements

Read Full Post »