Sunday, December 23, 2007

moving-schema-tablesindexes

-1 Move group of TABLE segments (check for unusable indexes after that)
select 'alter table '||owner||'.'||segment_name||' move '||
decode(segment_type,
'TABLE PARTITION','partition '||partition_name,
'TABLE SUBPARTITION','subpartition '||partition_name,null)||' tablespace &NEW_TS_NAME;' sql
from dba_segments
where segment_type like 'TABLE%'
and tablespace_name='&TS_NAME'
and owner='&OWNER'
and segment_name='&SEG_NAME'
;

--2 Move group of INDEX segments
select 'alter index '||owner||'.'||segment_name||' rebuild '||
decode(segment_type,
'INDEX PARTITION','partition '||partition_name,
'INDEX SUBPARTITION','subpartition '||partition_name,null)||' tablespace &NEW_TS_NAME;' sql
from dba_segments
where segment_type like 'INDEX%'
and tablespace_name='&TS_NAME'
and owner='&OWNER'
and segment_name='&SEG_NAME'
;

--3 List segments that will fail to expand
select /*+ all_rows */ segs.*
from
dba_segments segs,
sys.seg& s,
(select ts#,max(length) m from sys.fet& group by ts#) f
where s.ts#=f.ts# and extsize>m
and segs.header_file=s.file# and segs.header_block=s.block#
;

--4 List of fragmented segments
select segs.*
from
dba_segments segs,
(select file#, segblock# from sys.uet&
group by file#, segblock#
having count(*) > 1024
) f
where segs.header_file=f.file# and segs.header_block=f.segblock#
;

20 comments:

Anonymous said...

I drop a leave a response whenever I like a post on a website or I have something to contribute to the conversation.
Usually it is a result of the passion communicated in the post I browsed.

And on this post "moving-schema-tablesindexes".
I was excited enough to drop a comment ;) I actually do have 2 questions for you if you usually do not mind.
Is it just me or do a few of the remarks appear like left by brain dead folks?
:-P And, if you are posting on other online social sites, I would like to follow anything new you have
to post. Could you make a list all of all your public pages like your linkedin profile,
Facebook page or twitter feed?

Feel free to visit my blog post Movers Dayton

Anonymous said...

Hello there! This is my 1st comment here so I just wanted to
give a quick shout out and say I truly enjoy reading your blog posts.
Can you recommend any other blogs/websites/forums that deal with the
same topics? Thank you so much!

My blog post; Movers Dayton

Anonymous said...

I'm curious to find out what blog system you happen to be using? I'm having some minor security problems with my latest
blog and I'd like to find something more secure. Do you have any recommendations?

Here is my web-site ... click through the following page

Anonymous said...

Write more, thats all I have to say. Literally,
it seems as though you relied on the video to make your point.
You clearly know what youre talking about, why waste your intelligence
on just posting videos to your blog when you could be giving us something informative to read?


my page - How Can I lose weight

Anonymous said...

Excellent post. I was checking constantly this blog and I am impressed!
Extremely helpful info specially the last part :) I care for such info a
lot. I was looking for this certain information for a
very long time. Thank you and good luck.

My web-site :: Saffron Extract

Anonymous said...

Excellent post. I was checking constantly this blog and I am impressed!

Extremely helpful info specially the last part :) I care for such info a lot.
I was looking for this certain information for a very
long time. Thank you and good luck.

Here is my webpage; Saffron Extract

Anonymous said...

Aw, this was an incredibly nice post. Spending some time and actual effort to create a
great article… but what can I say… I procrastinate
a whole lot and don't manage to get anything done.

Also visit my webpage; Mito slim

Anonymous said...

Heya are using Wordpress for your site platform?
I'm new to the blog world but I'm trying to get started and create my own.
Do you require any coding expertise to make your own blog?
Any help would be greatly appreciated!

Also visit my web page; Lipo slender

Anonymous said...

Pretty great post. I just stumbled upon your blog and wanted to say that I've really loved browsing your weblog posts. After all I'll be subscribing to your rss feed and I
am hoping you write once more soon!

my blog; Rvtl Anti Aging Solution

Anonymous said...

Good way of telling, and nice post to get information about my presentation focus, which i am going to present in school.


Here is my site :: beyondraspberryketones.com

Anonymous said...

My brother recommended I might like this web site. He was entirely right.

This post actually made my day. You cann't imagine simply how much time I had spent for this info! Thanks!

Here is my blog post ... Nuva CLeanse Diet

Anonymous said...

It's actually a cool and helpful piece of information. I'm
happy that you simply shared this helpful information with us.
Please keep us informed like this. Thanks for sharing.


Here is my blog Garcinia Cambogia Safe

Anonymous said...

Hello! I've been reading your site for a long time now and finally got the bravery to go ahead and give you a shout out from Humble Tx! Just wanted to tell you keep up the good job!

Here is my site Chronic profits

Anonymous said...

Link exchange is nothing else but it is only placing the other
person's web site link on your page at suitable place and other person will also do same in favor of you.

Feel free to surf to my page ... Order Goji Berry

Anonymous said...

It's wonderful that you are getting ideas from this article as well as from our argument made at this place.

My webpage Bulk USB drives

Anonymous said...

Wonderful beat ! I wish to apprentice while you amend your website, how can i subscribe
for a blog web site? The account aided me a acceptable deal.
I were tiny bit familiar of this your broadcast provided brilliant clear idea

Review my weblog ... Cosima revival review

Anonymous said...

I really like it whenever people get together and share ideas.
Great blog, stick with it!

my webpage - greencoffeebeanmaxs.org

Unknown said...

coach outlet
ray ban
coach outlet
lebron james shoes 2015
michael kors outlet
lebron james shoes 12
nike uk
louis vuitton
louis vuitton
nike running shoes
jordan 3 infrared
coach outlet
michael kors
louis vuitton outlet
polo ralph lauren
fitflops
burberry outlet
burberry outlet
michael kors outlet
michael kors outlet clearance
cartier watches
north face outlet
ralph lauren polo outlet
nfl jerseys wholesale
michael kors outlet
jordan 6
toms outlet
longchamp handbags
adidas trainers
nike trainers
polo ralph lauren outlet
michael kors outlet
michael kors outlet clearance
coach canada
christian louboutin sale
louis vuitton outlet stores
michael kors outlet
replica watches
coach factory outlet
kobe 11
20166.4wengdongdong

raybanoutlet001 said...

mlb jerseys
michael kors handbags
michael kors outlet online
oakley sunglasses
ray ban sunglasses
ralph lauren outlet
ray ban sunglasses outlet
nike huarache
polo ralph lauren
ugg boots

Unknown said...

retro jordans
vibram fivefingers
michael kors outlet
nike mercurial vapor
chrome hearts online
nike mercurial
links of london sale
hogan outlet online
converse shoes
michael kors outlet