| Shrinking a database after a cut-down [message #166352] |
Wed, 05 April 2006 20:09  |
rgoldwrx
Messages: 11
Registered: April 2006
|
Junior Member |
|
|
Hi all,
This is the situation
- we are using oracle 9.2.0.1
- get a copy of a production database 80gb and put into another db
- run a cutdown script that culls about 70% of the data, for purposes of use as test databasese
What steps do we need to take to shrink this database now. I recalled an export and a import (with some option), then an export and then another reimport.
We need to shrink the tables that have had data removed as well as shrink the tablespaces (data files?). The resulting database will have a very small growth factor.
Is there a relatively easy way to achieve this?
Thanks for any advice.
|
|
|
|
| Re: Shrinking a database after a cut-down [message #166376 is a reply to message #166352] |
Thu, 06 April 2006 01:14  |
aciolac
Messages: 242
Registered: February 2006
|
Senior Member |
|
|
At first I want to notice You that it is very difficult to drop file from database without loosing information(You must be sure that no one segment like TABLE or INDEX have not extents on this table before dropping). So, easiest way is to short them.
Oracle have the space managment that permit very easy to increase size of database but decrease size of database is harder(except Oracle 10G with ASM). Shorting of database performs by next steps:
1)The logic of space managment for tables are so, that even if You delete some data from table, size of them are not decrease(see documentation Oracle for more detailed description). For decreasing of table size, You must execute:
SQL>alter table your_table_name move;
After this command table become shorter, and You will be know really size of dates in Your database for the next calculations. Optionally You can move the table to other tablespace. Also this operation make indexes on this table unusable, so You must execute for every index of table(this operation is not necessarily to execute now, but execute only once at the ent of all operations):
SQL> alter index your_index_name rebuild;
This command will make index usable and will decrease size of index.
2)Even after shorting table size you cannot short datafiles. In Oracle databases You can short datafiles only if they have free space at the end of file(and You can short only until the last occupied block). You must create new tablespace(for temporar use) and to perform next set of operations:
a)To find table that have higest number of extents at the end of datafiles(this is the simplest method to find optimal table for our operations - mere efficient methods are more complex).
b)move them to the new created tablespace:
SQL>alter table your_table_name move tablespace new_created_tblsp;
Optionally, if Your tablespace permits, You can perform a)-b) until Your newly tablespace have free space - this will make shorting more effective. This thing make necessary to create our temporar tablespace so big so much possible(ideally to can move into them all tables from shorting tablespace).
c)to short datafile:
SQL> alter datafile 'your_datafile_name' resize 111M;
(number 111 is the ocasional).
Ideally You will can to short tablespace to the size of tables which will be palces on them.At the a) and c) will be good to write SQL command which helps to perform this operations, but I have no time now to create them - this task will perform You.
d)Move tables from temporarily created tablespace to the initial tablespace.
Perform a) -d) until the initial tablespace will be shorted enough(or until You will have time to short them  ). After that, rebuild Your indexes as are described in 1).
Regards.
|
|
|
|
|
|
|
|
|
|
| Re: Shrinking a database after a cut-down [message #166918 is a reply to message #166352] |
Mon, 10 April 2006 05:04 |
alanm
Messages: 284
Registered: March 2005
|
Senior Member |
|
|
Hi,
what you are trying to achieve is to move the High water mark for the datafiles. One way to do this is to export and import, I think the other way is to perform an alter table/index rebuild. I believe this will do what you need.
rgs
Alan.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
