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

 

[LKBrwnDBA]

Import will not create any datafiles, only use those already allocated for a tablespace.

To remove datafiles you have to drop and re-create the tablespace allocating the datafile(s) sized accordingly.






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

 

[rasprer]

LK... Thanks for the clarification. I didn't realize what I just wrote. Yes..I agree with you that the export file will not create a datafile. I would merely have to recreate the tablespace and size according.

So..can assume that if I have a tablespace sized of 384mb with mulitple database files..1 being 128Mb with 127MB being used, and second file 256Mb with 67Mb making the Tablespace 54% Used... When creating the tablespace...should I initially size the tablespace between 384-400MB.

 

[SantaMufasa]

First, Oracle import never creates files. You must create them explicitly during either a “CREATE TABLESPACE…” or an “ALTER TABLESPACE…ADD DATAFILE…” command.

Second, "COMPRESS=y" simply means, "Place the entire contents of the table into a single, gigantic extent upon import." This is rarely a desirable convention.

If you have a tablespace for which the underlying files are unused (or even used, but you wish to consolidate), then the best way to deal with this adjustment is to:

1) Create a new tablespace with just the file allocations you wish. (I recommend creating the tablespace datafile(s) with AUTOEXTEND so that Oracle allocates space on a just-in-time, as-needed basis.)

2) MOVE your objects from the old tablespace to the new tablespace. (I recommend using “ALTER TABLE <tablename> MOVE PARALLEL NOLOGGING TABLESPACE <new ts name>;” for tables and “ALTER INDEX <indexname> REBUILD PARALLEL TABLESPACE <new ts name>;”)

3) Once you confirm that the old tablespace is empty, issue a “DROP TABLESPACE <old ts name>;”

4) “Erase” or “rm” the files that supported the old tablespace.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 20:21 (19Jan05) UTC (aka "GMT" and "Zulu"),
@ 13:21 (19Jan05) 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]

Mufasa...I was going to use your conventions of recreating a new tablespace and using the 'alter command' to move to the new tablespace, but I HAVE to use the existing 'Tablespace' names that I have currently. (don't ask why).

Also..the reason why i am exporting with compress=Y, is because I thought this would eliminate fragmentation that exists. Am I wrong?

Advise.

 

[SantaMufasa]

Rasprer,

exp/imp eliminates fragmentation regardless of "compress=...". By far, the fastest method to eliminate fragmentation is a slight variation on the command I gave you above:

ALTER TABLE <tablename> MOVE PARALLEL NOLOGGING;

By not specifying a different tablespace, Oracle uses the same tablespace, re-writing defragmented data.

If you must use the same tablespace name, then you can either:

1) export
2) drop tablespace
3) re-create same tablespace (with just the limited files you wish)
4) import back into newly created tablespace.

Or

1) Do my above "MOVE tables" suggestion twice:
"old ts-name to new ts-name", drop/re-create old ts-name, then "new ts-name to old ts-name".

The benefit of this second option is that MOVE is lightening fast...much faster than export/import.

Let us know your results,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 20:35 (19Jan05) UTC (aka "GMT" and "Zulu"),
@ 13:35 (19Jan05) 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]

Wow..I just got educated. This is the first I have heard of
"ALTER TABLE <tablename> MOVE PARALLEL NOLOGGING". I'll give you option 2 a shot.

THis is what I will do..I will run the "ALTER TABLE <tablename> MOVE PARALLEL NOLOGGING" command on all tablespaces. Then, for those Tablespaces with Multiple databafiles, I will create a 'temp tablespace' move the existing object from the old to the temp, and recreate my existing tablespace, and move again from temp to Old.

Is this GOOD.

 

[rasprer]

Mufusa...Also, would you happen to have a script that list all the tablespaces that are indeed fragmented? I am actually guessing that my tablespaces are fragmented since no maintainance has been done for about 3yrs.

Your assistance would be great.

 

[SantaMufasa]

J,

Carp and I were working on such a script some months ago. Carp built it as a web-based item, and I got tangled up in my underwear when I tried to turn his script into a vanilla character-based item. So, may I invite Carp to jump back into the fray on this, hoping that he can post a vanilla, character-based script to deal with disclosing fragmentation.

I do have a script, however, called "SwissCheeseRemoval.sql" that creates a SQL script to batch up all of your table/index defragmentation:

Section 1 -- "SwissCheeseRemoval.sql":

set echo off
set feedback off
accept old_tsname prompt "Enter the name of the old (source) tablespace: "
accept new_tsname prompt "Enter the name of the new (target) tablespace: "
set pagesize 0
set verify off
set trimspool on
spool TempMove&old_tsname..sql
prompt Set echo on
prompt set feedback off
select	'alter table '||owner||'.'||segment_name||' /* size: '||sum(bytes)||
	' */ move parallel nologging tablespace &new_tsname;'
from dba_extents
where tablespace_name = upper('&old_tsname') and segment_type = 'TABLE'
group by owner,segment_name
order by sum(bytes) desc,owner, segment_name
/
select	'alter index '||owner||'.'||segment_name||' /* size: '||sum(bytes)||
	' */ rebuild parallel tablespace &new_tsname;'
from dba_extents
where tablespace_name = upper('&old_tsname') and segment_type = 'INDEX'
group by owner,segment_name
order by owner, sum(bytes) desc, segment_name
/
prompt set feedback on
spool off
prompt
prompt Wrote 'TempMove&old_tsname..sql'
prompt
set pagesize 35

Section 2 -- Sample invocation of "SwissCheeseRemoval.sql":

******************************************************************************************
SQL> @swisscheeseremoval
Enter the name of the old (source) tablespace: data1
Enter the name of the new (target) tablespace: data1
Set echo on
set feedback off
alter table TEST.F0901 /* size: 68157440 */ move parallel nologging tablespace data1;
alter table DHUNT.TEMPOUTPUT /* size: 3145728 */ move parallel nologging tablespace data1;
alter table DHUNT.EDAD_PERSON /* size: 589824 */ move parallel nologging tablespace data1;
alter table TEST.P_HOLDING /* size: 131072 */ move parallel nologging tablespace data1;
alter table TEST.TEST_TEXT /* size: 131072 */ move parallel nologging tablespace data1;
alter table DBO.TABLE_A /* size: 65536 */ move parallel nologging tablespace data1;
alter index DHUNT.ORGUNIT_N1 /* size: 131072 */ rebuild parallel tablespace data1;
alter index DHUNT.ORGUNIT_N2 /* size: 131072 */ rebuild parallel tablespace data1;
alter index DHUNT.ORGUNIT_PK /* size: 131072 */ rebuild parallel tablespace data1;
alter index DHUNT.ALM_FAMILY_PK_DDP /* size: 65536 */ rebuild parallel tablespace data1;

set feedback on

Wrote 'TempMovedata1.sql'

Note in the above that this script handles both table and index rebuilding/defragmentation. Also, you can use this same script to MOVE tables and indexes to another tablespace.

Let us know how it works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:54 (19Jan05) UTC (aka "GMT" and "Zulu"),
@ 14:54 (19Jan05) 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]

Mufasa...Also, If I wanted to rebuild indexes within the tablespace, can i use the following code:
"ALTER INDEX <indexname> MOVE PARALLEL NOLOGGING

 

[SantaMufasa]

J,

No...notice the code that my "SwissCheese..." script uses, above:

alter index <index_name> [b]rebuild[/b] parallel;

So, use rebuild instead of move. ("Move" on an index will generate a syntax error.) This code, above, will rebuild/defragment indexes within the same tablespace.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 22:10 (19Jan05) UTC (aka "GMT" and "Zulu"),
@ 15:10 (19Jan05) 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]

Works for me..U ask. Mufasa..Yes it does. But is there any script that determines if a tablespace is indeed fragmented.

 

[rasprer]

Mufasa...Please excuse my last post. I didn't read you last post thoroughly. I hope Carp can assist with the script.

In any event...will running the script only (1)once ensure that the tablespace is defragmented. Or is running the script multiple times more beneficial.

 

[SantaMufasa]

J,

Running once squeezes all the "air" out of the table/index. Running it again in a week (month, 6 months, et cetera) will again squeeze the "air" out that has occurred since the last defragmentation. Running the script once today should help tons. Running the script a second time will not help at all today.

Also, I'll give Carp a phone call and see what the chances are of his coming up with a vanilla fragmentation-report script.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 22:45 (19Jan05) UTC (aka "GMT" and "Zulu"),
@ 15:45 (19Jan05) 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]

Mufasa...HELP!! I have just recreated a temptablespace. I moved all the objects to the temptablespace using your 'Move command'. I am trying to delete the old tablespace so I may recreate it but, I am getting the follwoing error:

ORA-22868: Table with LOBS contain segements in different tablespaces.

 

[SantaMufasa]

J,

This error results from the following set of circumstances:
1) You have a table with a Large-OBject (LOB) column.
2) The table resides in another tablespace.
3) The LOB segment for that table resides in the tablespace you are trying to drop.

Here is what you can do:
1) Find out the name of the table that owns the LOB that resides in the tablespace you are trying to drop.
2) Export that table.
3) Drop that table.
4) Drop the tablespace you are trying to drop.
5) Create the new version of the tablespace.
6) Move the objects from your temptablespace into the newly created tablespace.
7) Import the LOB table.

Let us know your findings and results.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 23:36 (19Jan05) UTC (aka "GMT" and "Zulu"),
@ 16:36 (19Jan05) 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]

Done...Did what you said Mufasa!! Thanks A million.

please get back to me regarding the script from CARP. I know for a fact that our clients will be requesting reports as to the benifit of 'defraging' this tablespace. They always request its original state and the improvements it made. So, I merely do not want to tell them I defragmented a tablespace for the hell of it. I hope u understand. If you do come up with the script...please let me know at asprer@aol.com

Mufasa...THANK YOU!! CARP..Thanking you in advance!!

 

[rasprer]

Mufasa....any news from CARP

 

[SantaMufasa]

J,

I just spoke with Carp on the phone. Here is the process that he and I agreed upon:

1) He will revisit his "SwissCheeseDiagnostic" script presently, making modifications such that it will run simply and easily in SQL*Plus as a text-based script.
2) He will e-mail the script he modifies to me for testing.
3) Following successful testing, I will let Carp (Dave) know of the results.
4) Either he or I will post the tested, working script here on your thread. (Don't expect a script posting until 23:30 GMT/UTC/Zulu at the earliest.)

How does that plan sound?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:47 (20Jan05) UTC (aka "GMT" and "Zulu"),
@ 10:47 (20Jan05) 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]

Plan sounds Good...

Mufasa...Thank you again. Will you be posting the results on this same thread?