| Radhika |
Posted: Sep 04, 2007 07:09:42 PM |
Total Post: 3
Joined: Oct, 2005
|
I have a quick question. I found tablespace fragmentation on one of the users and Index tablespaces. How can I remove the tablespace fragmentation.
I am on ORACLE 10G USING lOCALLY MANAGED TABLESPACE.
Thanks,
|
|
|
 Mohammed Taj |
| Posted: Sep 05, 2007 12:02:46 AM |  |
|
Total Post: 746
Joined: Jul, 2007
|
Hi,
You can use avoid tbs frgmentation through two ways.
1. export / truncate all objects / import + ignore=y
2. alter tablespace <name> move + rebuild all indexes.
Options 2 is easy and better.
Regards
Taj
|
|
|
|
|
| Radhika |
| Posted: Sep 05, 2007 12:20:20 PM | |
|
Total Post: 3
Joined: Oct, 2005
|
Hi Taj,
Thanks for the reply. Can you please be in detail.
Thanks,
|
|
|
|
|
| Mohammed Taj |
| Posted: Sep 05, 2007 12:46:33 PM | |
|
Total Post: 746
Joined: Jul, 2007
|
Hi,
Option 1.
1.
select distinct owner
from dba_tables
where tablespace_name = '&username';
Note: through this query you will find how many users/schemas are under particular tablespace.
2.
export every schema
eg: exp userid/pwd file=path log=path OWNER=username list
3.
Create New tablespace with different name
SQL> set long 999999
SQL> select dbms_metadata.get_ddl('TABLESPACE','tbsname') from dual;
Note: Through this query you will find complete DDL for new tablespace some like old tbs.
4.
Make new tablespace to default tablespace for database.
alter database default tablespace 'newtbsname';
5.
import all exported schemas.
eg:
imp userid/pwd file=path log=path FROMUSER=<username> TOUSER=<username>
6.
rename new tablespace name to old tablespace name.
alter tablespace <newtbsname> rename to <oldtbsname>;
Option 2
1.
Create new tablespace
2.
write script to move all tables to new tablespace.
eg:
alter table <tablename> move tablespace <newtbsname>;
3.
write script to rebuild all indexes to new tablespace.
eg:
alter index <indexname> rebuild tablespace <newtbsname>;
Hope this helps
Any questions please post here or write mail at star_taj@yahoo.com
Regards
Taj
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
