What is the best way to resize a table that has been growing and will very soon grow out of space. Can you give me a resizing example? i.e.script etc.
Printable View
What is the best way to resize a table that has been growing and will very soon grow out of space. Can you give me a resizing example? i.e.script etc.
Without specific data on your situation no specific answer can be given. Some concepts on table sizing may be helpful. Tables are created with certain inherent parameters (initial extent size, next extent size, percent increase and maxextents being the most important for your situation). These parameters may be explicitly declared for each table during table creation or, if they are not specified, they are set using the default values associated with the tablespace within which the table resides. Some of these parameters may be modified after table creation, but the modifications will apply only to new allocations. If you have a rapidly growing table, it is best to create the table with a very large initial extent so that dynamic extensions are minimized. In your situation it seems that you can either move your data into a new table with explicitly declared storage parameters appropriate for this table, its data, and its growth rate, or you could perhaps prolong the life of your existing table by increasing the value for maxextents. There are a host of remedies for table storage issues, but you should more clearly describe the problem and tell what you anticipate will limit future table growth.
You need to know how fast your table is growing. Keeping records over a period of time or find what is causing growth. Use the ANALYZE TABLE table_name COMPUTE STATISTICS;
command then look in TABS to find the actual number of rows and AVE_ROW_LEN and the BLOCKS used. You then have the information to calculate how big the table needs to be.
Use ALTER TABLE table_name STORAGE (NEXT .....); to increase the next extent to some figure large enough to hold the situation until you have done your calculations then use Export and Import to resize the table. This will set the initial extent large enough to hold all current data but this may not be enough so pre-create the table and import with param IGNORE=Y.