OraFAQ Forum: Performance Tuning » Reorganization of data/ Eliminate database fragmentation (export, drop and re-import tables)

Home » RDBMS Server » Performance Tuning » Reorganization of data/ Eliminate database fragmentation (export, drop and re-import tables)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Reorganization of data/ Eliminate database fragmentation (export, drop and re-import tables) [message #199873]

Thu, 26 October 2006 09:27

caraless
Messages: 7
Registered: October 2006
Location: Pomigliano D'arco

Junior Member

Good Morning,
I have server unix where installed oracle 9i patch 9.2.0.5. I have one schema INFODBA and I have two tablespace. One tablespace for data and one for index. for the tablespace of the data i have 18 datafile. every datafile is 1000 MB. For the tablespace the index i have 15 datafile. every datafile is 1000 MB. Every night i execute one export of the schema infodba and database full. Now i want drop the tablespace and reimport all for eliminate database fragmentation.
I want know :
is better create one datafile for data of 18 GB or is equal if i execute an import also on 18 datafile of 1000 MB?
when i execute an import on 18 datafile,or import on datafile of 18GB, I eliminate database fragmentation or exist another procedure?
thank you

Report message to a moderator

Re: Reorganization of data/ Eliminate database fragmentation (export, drop and re-import tables) [message #199875 is a reply to message #199873]

Thu, 26 October 2006 09:33

caraless
Messages: 7
Registered: October 2006
Location: Pomigliano D'arco

Junior Member

I forget that when i import the index i change the default tablespace and i insert the tablespace of the index.
My script are :
imp expimp/orasrv9 file=export_infodba.dmp log=imp_1.log fromuser=infodba touser=infodba indexes=n

and after when i change the defaul tablespace for the index the script is:

imp expimp/orasrv9 file=export_infodba.dmp log=imp_2.log fromuser=infodba touser=infodba rows=n grants=n constraints=n indexes=y ignore=y

Is correct??
thank you
Alessandro

Report message to a moderator

Re: Reorganization of data/ Eliminate database fragmentation (export, drop and re-import tables) [message #199878 is a reply to message #199875]

Thu, 26 October 2006 09:41

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> reimport all for eliminate database fragmentation.
First what makes you think there is fragmentation?
In 9i if you are using LMT with uniform extent sizes, you can avoid fragmentation (Almost to be precise. You still cannot avoid block level fragmentation).

>>imp expimp/orasrv9 file=export_infodba.dmp log=imp_1.log fromuser=infodba touser=infodba indexes=n
>>and after when i change the defaul tablespace for the index the script is:
>>imp expimp/orasrv9 file=export_infodba.dmp log=imp_2.log fromuser=infodba touser=infodba rows=n grants=n constraints=n indexes=y ignore=y
>> Is correct??
I fail to see anything wrong here.
But the first import will create some indexes (created by constraints) in the tablspace for table and it seems that you want to avoid that.
I would import just data/noindex/no constraints.
Extract ddl and recrate constraints/indexes (in parallel).

But why all this pain?
There is NO harm in having the index and table data in the same tablespace (as long as you have striped the datafiles across several disks).
Seperating index/table data may be better for ease of administration.
But has nothing to with performance

Report message to a moderator

Re: Reorganization of data/ Eliminate database fragmentation (export, drop and re-import tables) [message #199886 is a reply to message #199878]

Thu, 26 October 2006 10:06

caraless
Messages: 7
Registered: October 2006
Location: Pomigliano D'arco

Junior Member

thank you Mahesh,
My RDBMS is 9i 9.2.0.5 but i don't know if i'm using LMT with uniform extent sizes and if is fragmentation .
When i created the tablespace i had this script :
CREATE TABLESPACE "OIMANLC9_DATA" LOGGING
DATAFILE '/oradata9/iman_lc/tsp1/oimanlc9_tcaed0112.dbf' SIZE 1000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
and after i add other datafile.
For me is important drop two tablespaces (Data and index) and recreate this.
so in the first import :
imp expimp/orasrv9 file=export_infodba.dmp log=imp_1.log fromuser=infodba touser=infodba indexes=n
will create some indexes (created by constraints) in the tablespace for data?
and if i execute this script two times :
imp expimp/orasrv9 file=export_infodba.dmp log=imp_2.log fromuser=infodba touser=infodba rows=n grants=n constraints=n indexes=y ignore=y
and the second time i change the default tablespace for the index?
"Extract ddl and recrate constraints/indexes (in parallel)." ???
Excuse me
i wait your info
thank you
Alessandro

Report message to a moderator

Re: Reorganization of data/ Eliminate database fragmentation (export, drop and re-import tables) [message #199887 is a reply to message #199886]

Thu, 26 October 2006 10:28

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

A regular import will create the indexes in serial. So it might be slow. You can extract ddl (using toad or any gui or DBMS_METADATA or even from import itself using show=y or indexfile option. Please search the forum for examples). Edit this ddl to create the indexes in parallel. If that is too much of work, just ignore it.
>>will create some indexes (created by constraints) in the tablespace for data?
Yes. If the table has any constraints, by default the indexes generated by constraints are created in default tablespace (data).
>>and the second time i change the default tablespace for the index?
Now, the new indexes would be created in index tablespace.
But those indexes (created by primary constraints etc) are still left in data tablespace.

Easiest method is to,
1. Take a good export (with COMPRESS=n).
2. drop the tablesapces.
3. recreate the same tablespaces (data and index).
4. make data as default and revoke RESOURCE role from user.
5. grant quota unlimited on DATA TABLESPACE and INDEX tablespace to the user.
6. Just import (single import would do).
imp user/pass fromuser=x touser=y indexes=y constraints=y.

Now since both data/index tablespaces already exist, the indexes will be created in their intended tablespaces.

Report message to a moderator

Re: Reorganization of data/ Eliminate database fragmentation (export, drop and re-import tables) [message #199893 is a reply to message #199887]

Thu, 26 October 2006 10:53

caraless
Messages: 7
Registered: October 2006
Location: Pomigliano D'arco

Junior Member

Hy Masesh,
than :
I make new export with option COMPRESS=N, after i drop the tablespaces and i recreate the same tablespaces (data and index)
data : 18 datafiles every datafile 1000 MB
index : 15 datafiles every datafile 1000 MB
1)After make data as default and revoke RESOURCE role from user (my user is infodba).
2)Grant quota unlimited on DATA TABLESPACE and INDEX tablespace to the user.
3)Just import (single import would do).

imp expimp/orasrv9 file=export_infodba.dmp log=imp_1.log fromuser=infodba touser=infodba indexes=y constraints=y.

Now the data will be in the tablespace data, the index in the tablespace index and i also elimination fragmentation?
I hope the i understood Embarassed

i wait your info
thank you very much
Alessandro

Report message to a moderator

Re: Reorganization of data/ Eliminate database fragmentation (export, drop and re-import tables) [message #199894 is a reply to message #199893]

Thu, 26 October 2006 10:56

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Instead of Imp or exp you can use move tablespace cmd to avoid fragmenation.

step
1.create new tbs.
2.move all objects to new tbs.
- rebuild all indexes to new tbs.
3.drop old tbs.
4.rename new tbs name to old tbs names.

Report message to a moderator

Re: Reorganization of data/ Eliminate database fragmentation (export, drop and re-import tables) [message #199895 is a reply to message #199893]

Thu, 26 October 2006 11:01

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> and i also elimination fragmentation?
May be or may not be.
First we have no idea whether there is a fragmentation.
This is a good read.
http://www.oracle.com/technology/deploy/availability/pdf/defrag.pdf

Report message to a moderator

Re: Reorganization of data/ Eliminate database fragmentation (export, drop and re-import tables) [message #199963 is a reply to message #199894]

Fri, 27 October 2006 03:39

caraless
Messages: 7
Registered: October 2006
Location: Pomigliano D'arco

Junior Member

Hy Masesh
so i create two tablespace with the same dimension of the tablespace that exist.
Tablespace data 18 datafile every datafile 1000MB
Tablespace index 15 data file every datafile 1000MB
1."move all objects to new tbs??? Embarassed

"

2."rebuild all indexes to new tbs??? Embarassed

"
Pheraps : alter index XXXXXX rebuild tablespace TBSXXXXXX ???

3.drop old tbs.
4.rename new tbs name to old tbs names.

So i avoid fragmentation Razz

thank you very much

Report message to a moderator

Re: Reorganization of data/ Eliminate database fragmentation (export, drop and re-import tables) [message #199986 is a reply to message #199963]

Fri, 27 October 2006 05:31

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Quote:

so i create two tablespace with the same dimension of the tablespace that exist.
Tablespace data 18 datafile every datafile 1000MB
Tablespace index 15 data file every datafile 1000MB

NO need.
Just create a tablespace data with a datafile of 18gb and index tablespace with one datafile of 15Gb (if your OS permits it).
ONly the tablespace name has to be same.

Quote:

1."move all objects to new tbs??? Embarassed "

2."rebuild all indexes to new tbs??? Embarassed "
Pheraps : alter index XXXXXX rebuild tablespace TBSXXXXXX ??

Yes.

Report message to a moderator

Re: Reorganization of data/ Eliminate database fragmentation (export, drop and re-import tables) [message #200036 is a reply to message #199986]

Fri, 27 October 2006 09:54

caraless
Messages: 7
Registered: October 2006
Location: Pomigliano D'arco

Junior Member

Hy,
how can i ask these information?
I needed to know the mode to move the objects from two tablespace (data and index) in the two new tablespace (data and index) and after if need rebuild all indexes to new tbs.
example :
ALTER TABLE CR_ANTENNA MOVE TABLESPACE DATA_MED STORAGE (INITIAL 4096K);

alter index BDB000000.AGAAA00 rebuild tablespace TSB0000001
i wait your info Cool

thank you

Report message to a moderator

Previous Topic:	Check v$sqlarea for shared cursors
Next Topic:	problem when dropping table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 04:50:40 CDT 2025