Eliminate Multiple Datafiles within Tablespace 2

[rasprer]

Since my question basically involves an Exp/Imp issue..I will address it here.

I have some instances where I have mulitiple datafiles within a tablespace. I actually want to only have one datafile and eliminate the others since they are basically unused.

My question is this...I have exported a user with the compress=y option. When I initialize the import, will it recreate the additional datafiles or will it only create 1 which is what I want. Also, for instance I do have a tablespace currently with a size of 384mb with mulitple database files..1 being 128Mb with 127MB being used, and second file 256Mb with 67Mb used. When creating the tablespace...should I initially size the tablespace between 384-400MB.

Suggestions and advises please.

J

 

[engineer2100]

A guess from the novice...may be because these tables had some data freshly inserted into them and last time the statistics didnot view this as a potential candidate or the tables didnot succeed to pass the "threshold" test.

 

[rasprer]

engineer...no data has been inserted. I am actually testing on a development server which has no activity. Thanks for your response.

 

[SantaMufasa]

Rasprer,

My mom always taught me, "It is a woman's prerogative to change her mind." What gender is your server?

Is there any possibility that your numeric "assessment factor" changed? Are the new tables from the same schema that you assessed previously?

If not, we may need to call in either Las Vegas's, New York's, or Miami's Crime Scene Investigators.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 23:25 (01Feb05) UTC (aka "GMT" and "Zulu"),
@ 16:25 (01Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[carp]

Doubtless there's a reason for this. I just don't know what it is!

 

[rasprer]

Mufasa...I know it makes it all confusing since I am a newbee, but NO, the assessement factor has not changed and the tables are all from the same schema.

Another question...how high is high in regards to 'Est Savings' value...are we saying that the hundreds are high. I have tables reporting values of 5,16,25,30 and some in the hundreds.

 

[carp]

It's all very relative. But I would certainly reorganize the tables with the higher values first. The lower the value, the less likely you are to actually realize much benefit.

 

[rasprer]

Carp..Understood.

Update!! ...as I continue to run "swiss_cheese.REORG", the assestment report is getting smaller and smaller. In some cases the same tables are still being reported but with smaller values for 'Est Savings'...(which is good, i guess).

Question is...how often should I run this script. Since little gain is benefited from an 'Est Savings' value, I guess there is no need to continue to execute the swiss_cheese.REORG until the assestment report no longer prints any tables. Is this correct.

 

[carp]

I believe once should do it. After the first reorg, I doubt if there is much gain to be realized.

 

[rasprer]

Carp...can you clarify my results below.

Before a REORG, my application and works table reported..

Table Current Est. Est. Space Chained
Name Blocks Blocks Savings Used Rows
------------------------- ---------- ---------- ---------- ---------- --------
APPLICATION 7,215 6,442 773 0 0
WORKS 23,295 19,437 3,858 0 0

After REORG it reported...
Table Current Est. Est. Space Chained
Name Blocks Blocks Savings Used Rows
------------------------- ---------- ---------- ---------- ---------- --------
APPLICATION 7,185 6,416 769 0 0
WORKS 23,295 19,437 3,858 0 0


..I understand this these are all relative and estimates, but the 'EST SAVINGS' is a concern...or should it be? If you also look at the WORKS table it is unchanged with a high 'EST SAVINGS' value. Altough the 'EST SAVINGS' still reports a HIGH VALUE and UNCHANGED, can I just assume that REORG is complete?

 

[SantaMufasa]

Rasprer,

IMHO, a reorg is a reorg is a reorg. Once you have done it, it should be good to go until a moderate amount of INSERTs, UPDATEs, and DELETEs occur and you notice that your assessments are again exceeding your comfort range.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 01:18 (02Feb05) UTC (aka "GMT" and "Zulu"),
@ 18:18 (01Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[rasprer]

Understood again..Thanks Mufasa.

 

[rasprer]

Mufasa and Carp.

I merely want to thank you greatly for your efforts in providing this solution. Yes, it is a 'freebee', Thus, I am even more THANKFUL for taking the time to tend to this thread.

It is no surprise why both of you are highly regarded in the Tek-Tips forum.

And Carp...to you. I have executed your code with no issues..it is time for me to say... "Atta Boy"!!!


Again..Thanks.

 

[carp]

rasperer -
Any time! And when you, SantaMufasa, I, and possibly others wind up with a potentially useful tool, it's a grand day indeed!

 

[engineer2100]

Let me also pass on my gratitude to both Mufasa and Carp. This thread was indeed very informative.

-Engi