Export + Drop Tables and then Import

[htran]

Hi all,

I have two tables which were initially created to hold temp data. When those two tables were created, we did not define any of the max extents size.

Today, the business requirements have changed and we are adding more data into those two tables more than ever before. As a result, I am getting the following error messages:

ORA-01631: max # extents (100) reached in table XXXXXXX

What I am trying to do is to Export the data from those two tables, drop them, create new tables with the pre-defined extents and then import the data back onto the new tables. Could someone please help me with these steps.

I was reading some materials on the net and there were so many suggestions which confused me. I would like to ensure that when the new tables are created, I won't lose any data. All the components of the tables are exported and then imported back in.

Thanks....

 

[SantaMufasa]

Tran,

You do not need to export/drop/import to fix your problem...In fact, if you follow that process, you will still have the same 100 maximum extents as before.

To solve you problem, use the following command:

ALTER TABLE <table_name> STORAGE (MAXEXTENTS UNLIMITED);

Using UNLIMITED yields a maximum extent count of 32,767; you can set the value smaller than 32,767 if you choose.

Let us know if you are satisfied with this solution.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[htran]

I forgot to mention that those two tables are the second instance in my database. Therefore, it is not possible to do the

ALTER TABLE TABLE_NAME STORAGE (MAXEXTENTS UNLIMITED);

Also, I do not want to change the maxextents to unlimited due to the performance issue of my database.

I thought that when a table is created, you can not change the storage parameters. Is this true?

My database is Oracle 8i and it is running under Windows NT Server.

Thanks...

 

[LKBrwnDBA]

Don't know what you mean by "second instance in my database"?

Why is Mufasa's 'ALTER TABLE..' not possible?

Also you could modify to enlarge the NEXT extent:

TRUNCATE TABLE <MyTable> DROP STORAGE;
ALTER TABLE <MyTAble> STORAGE (NEXT 10M);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

...those two tables are the second instance in my database. Therefore, it is not possible to do the...ALTER TABLE...

I, too, Tran, do not know what this means. Could you help us understand?

I do not want to change the maxextents to unlimited due to the performance issue of my database.

Tran, the "MAXEXTENTS" parameter has absolutely zero impact on performance (except for DROP TABLE).

I thought that when a table is created, you can not change the storage parameters.

The only storage parameter that you cannot change on an existing table is "INITIAL".

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sbix]

Uhm.... looks like a DB migrated from a federated DB2... with the DBA too.