Yes, but that's a special case. You are not rebuilding
the index as part of some regular index maintenance.
Jared
On Friday 27 December 2002 04:43, Rachel Carmichael wrote:
Here's a reason:
have you ever tried to find the three duplicate rows in a 12 million
row table without using the
I don't have access to 9.2.0.1 right now. But can you try creating a non-
unique index instead of the unique index. If you create a unique index, it gets
dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it
shouldn't get dropped.
Regards,
Denny
Quoting Rachel
To: Multiple recipients of
list ORACLE-L [EMAIL PROTECTED] wisernet100@y cc:
ahoo.comSubject: Re: Rebuilding
Indexes... Sent by:
[EMAIL PROTECTED]
om
12/27/2002
07:43
Yeah, it sure does, if the index is unique.
Try out this test:
drop table y;
create table y(y number);
create unique index ypkidx on y(y);
alter table y add constraint ypk primary key(y);
alter table y drop primary key;
select table_name, index_name
from user_indexes
where index_name =
Rachel,
Try a pre-created non-unique index. This should remain after the
constraint it dropped, and can be used to enforce the primary key
constraint (not to mention be created in parallel nologging mode.)
hth,
Jack
9.2.0.1 Solaris, and yes, it does drop it
I created a unique index in
Subject: Re:
Rebuilding
Indexes... Sent by:
[EMAIL PROTECTED]
om
12/27/2002
07:43 AM
Please respond
to ORACLE-L
Here's a reason:
have you
:
ahoo.comSubject: Re: Rebuilding
Indexes...
Sent by:
[EMAIL PROTECTED
it'll have to wait until Monday, I'm not at work until then. I'll try
it with a non-unique then
Hey, if it works, it saves me tons of time, I learn something new and I
had fun developing the single SQL statement to rebuild the constraint
and index. Win-win
Rachel
--- Denny Koovakattu [EMAIL
As Denny also suggested. I'm gonna try that on Monday, on my sandbox
database. If this does work in 9i as well (and it should, I hope), I
can just rebuild the unusable partition and not the entire index. The
index build will only have to happen once.
--- Jack Silvey [EMAIL PROTECTED] wrote:
fair enough. I retract the example :)
--- Jared Still [EMAIL PROTECTED] wrote:
Yes, but that's a special case. You are not rebuilding
the index as part of some regular index maintenance.
Jared
On Friday 27 December 2002 04:43, Rachel Carmichael wrote:
Here's a reason:
have you
Rich,
select 'alter index '||owner||'.'||index_name||' rebuild;' from
sys.dba_indexes where owner = 'PUTOWNERNAMEHERE';
is a good start for normal non-bitmap indexes, enhance it to do bitmap,
etc. :)
joe
Richard Huntley wrote:
Anyone have any useful scripts for doing this?
TIA,
Rich
--
Title: Rebuilding Indexes...
Here is the index
rebuilding part of a ksh script I use. Prior to this, I some variable
setting, check the existence and permissions on some files, check the existence
of tablespaces, check that the script is not already running, etc. In this
script, all the
Title: Rebuilding Indexes...
Script "gen_rebuild_idx.sql" on www.evdbt.com/tools.htmgenerates
SQL to rebuildUNUSABLE indexes, index partitions/subpartitions. You
can modify the WHERE clauses as you prefer...
- Original Message -
From:
Richard Huntley
To: Multiple
Though I have published a script for determining indexes that
need to be rebuilt, and then rebuilding them, I have to say that
this is almost never necessary.
Why are you rebuilding indexes? About the only reason for ever
doing so is that the BLEVEL = 5.
goto asktom.oracle.com, and do a
storage parameter difference? are you moving from one tablespace to
another without specifying parameters?
pctfree/pctused influence?
--- Adams, Matthew (GEA, MABG, 088130) [EMAIL PROTECTED]
wrote:
Under what conditions would an 'alter index .. rebuild'
actually case the size of the index
Title: RE: rebuilding indexes
storage parameter difference? None
are you moving from one tablespace to
another without specifying parameters? No
pctfree/pctused influence? none
I should have put this in the original message.
Indexes stayed in original tablespace with identical
Matt,
1) Storage parameter changes? Do you specify storage
at the index or tablespace (or top partition)
level?pctfree go up? initrans go up? bigger INITIAL or
NEXT?
2) did you build it the first time using parallelism
and rebuild using single thread? When a PQ processes
is used to build an
with.
-Original Message- From: Rachel
Carmichael [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 31, 2002 12:49 PM To:
Multiple recipients of list ORACLE-L Subject: Re:
rebuilding indexes
storage parameter difference? are you moving from one
tablespace to another without specifying
parameters?
pctfree
I'm sure it's #6 :)
--- Jack Silvey [EMAIL PROTECTED] wrote:
Matt,
1) Storage parameter changes? Do you specify storage
at the index or tablespace (or top partition)
level?pctfree go up? initrans go up? bigger INITIAL or
NEXT?
2) did you build it the first time using parallelism
and
then the only thing I can think of is that there was heavy activity on
the index prior to the rebuild and the blocks filled and the rebuild
evened it out.
--- Adams, Matthew (GEA, MABG, 088130) [EMAIL PROTECTED]
wrote:
storage parameter difference? None
are you moving from one
Lose 20 blocks in 10 days! Load as much as you
normally do and still lose index width.
no segment size restrictions or tiring delete routines
that never seem to finish. easy-to-follow
substr(column,1,1) update routine that guarentees a
maximum of data loss and a minimum of storage!
email to
it's definitely friday :)
--- Jack Silvey [EMAIL PROTECTED] wrote:
Lose 20 blocks in 10 days! Load as much as you
normally do and still lose index width.
no segment size restrictions or tiring delete routines
that never seem to finish. easy-to-follow
substr(column,1,1) update routine
Mathew,
There has to be something that changed in the index or the storage
parameters. The index storage is basically based on the database block
size, the pctfree, the average length of the index entry, and the number
of rows in the index. If you changed none of these the index should
remain
Matt,
What sort of indexes are these? What is the DML pattern that affect these
indexes? Answers to these questions may give us a clue why this is occuring.
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
The manuals for Oracle are here: http://tahiti.oracle.com
The manual
Title: RE: Rebuilding Indexes Question
I
remember reading somewhere that alter index ... rebuild analyzes index with
estimate default. In case this is not desirable one need to reanalyze an index
after rebuilding.
Alex
Hillman
-Original Message-From: [EMAIL PROTECTED]
[mailto
Title: RE: Rebuilding Indexes Question
I think, rebuilding indexes may use the existing index to build, so that the performancce will improve comparitively doind drop and create indexes...
Nirmal.,
-Original Message-
From: Alex Hillman [SMTP:[EMAIL PROTECTED]]
Sent: Sunday, October
I agree.
If you're really tight on space, you may need to
consider drop/create, otherwise rebuilds generally are
the way to go.
hth
connor
--- Jacques Kilchoer [EMAIL PROTECTED]
wrote: -Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
What are the
Title: RE: Rebuilding Indexes Question
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
What are the advantages/disadvantages to doing a alter index rebuild
(Oracle 8.0.X) versus dropping and recreating the index.
My personal opinion - if your database
Jacques
i think oracle puts exclusive locks on the table on
which you are trying to create an index. that reason
should be good enough to use the rebuild online
option instead of create/drop especially for systems
that require to be available all the time
Deepak
--- Jacques Kilchoer [EMAIL
From 1st hand experience, folks need to be warned that
the last time I did this during normal working hours on an
index on a production table, the order entry department
could no longer commit new orders. They were hung
until the index rebuild completed. So use with caution.
Deepak Thapliyal
and in 9i, almost everything is done online, w/journaling and no
locking.
but thats another week in the future. ;)
joe
Charlie Mengler wrote:
From 1st hand experience, folks need to be warned that
the last time I did this during normal working hours on an
index on a production table, the
Title: RE: Rebuilding Indexes Question
re-building online was carried out for one of our huge indexes. It took lots of resouces and slowed down the system performance. The operation had to be cancelled. Though users can still carry out transactions against the concerned table, Oracle uses
PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Bumoer Stickers [RE: Rebuilding indexes]
Date: Fri, 01 Jun 2001 13:32:25 -0800
On Fri, 1 Jun 2001, Kevin Kostyszyn wrote:
What does RTFM mean?
Beautiful. That's our bumper sticker.
--
Jeremiah Wilton
http
-Original Message-
From: Jeremiah Wilton [SMTP:[EMAIL PROTECTED]]
Sent: Friday, June 01, 2001 12:47 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Rebuilding indexes
On Fri, 1 Jun 2001, Hatzistavrou Giannis wrote:
I don;t quite understand you quoting
Once
On Fri, 1 Jun 2001,Rachel Carmichael scribbled on the wall in glitter crayon:
-THAT one I'll put on my car :)
-
-and my desk. and the developer's face
OK so who's geting them printed?;-) or should i look into it?
--
Bill Shrek Thater Certifiable ORACLE DBA
Telergy, Inc.[EMAIL
what was the date of this cartoon??
At 01:25 PM 6/1/01 -0800, Post, Ethan wrote:
This is getting stupid, sorry I'm going to contribute to this...how about
Calvin (http://www.ucomics.com/calvinandhobbes/viewch.htm) peeing on a SQL
Server Logo, sure beats him peeing on a Ford sign.
- Ethan Post
tzistavrou Yannis
-Original Message-
From: Jeremiah Wilton [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, May 31, 2001 22:15
To: Multiple recipients of list ORACLE-L
Subject: RE: Rebuilding indexes
No need to copy the whole file to the standby when adding a datafile!
/~jwilton
-Original Message-
From: Jeremiah Wilton [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, May 31, 2001 22:15
To: Multiple recipients of list ORACLE-L
Subject:RE: Rebuilding indexes
No need to copy the whole file to the standby when adding a datafile!
Just wait
Jeremiah Wilton wrote:
Vadim Gorbounov mentioned the column 'unrecoverable_change#' in v$datafile.
This looks like an SCN, not a count of unrecoverable changes. Either way, it
seems like a useful way to decide if a datafile shoule be refreshed onto the
standby. I can even imagine
PROTECTED]]
Sent: Friday, June 01, 2001 12:47 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Rebuilding indexes
On Fri, 1 Jun 2001, Hatzistavrou Giannis wrote:
I don;t quite understand you quoting
Once the indexes have all
been rebuilt nologging
I bow to the uber geek :)
but putting either of those bumper stickers on my car in NY could get me
seriously hurt!
From: Jeremiah Wilton [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Rebuilding indexes
Date: Fri, 01 Jun
AN IDIOT?
or
SHUTDOWN ABORT
Don't forget HELP
- Kirti
-Original Message-
From: Jeremiah Wilton [SMTP:[EMAIL PROTECTED]]
Sent: Friday, June 01, 2001 12:47 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Rebuilding indexes
On Fri, 1 Jun 2001
-Original Message-
From: Jeremiah Wilton [SMTP:[EMAIL PROTECTED]]
Sent: Friday, June 01, 2001 12:47 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: Rebuilding indexes
On Fri, 1 Jun 2001, Hatzistavrou Giannis wrote:
I don;t quite understand you
:[EMAIL PROTECTED]]
Sent: Friday, June 01, 2001 12:47 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: Rebuilding indexes
On Fri, 1 Jun 2001, Hatzistavrou Giannis wrote:
I don;t quite understand you quoting
Once the indexes have all
been rebuilt nologging
This is getting stupid, sorry I'm going to contribute to this...how about
Calvin (http://www.ucomics.com/calvinandhobbes/viewch.htm) peeing on a SQL
Server Logo, sure beats him peeing on a Ford sign.
- Ethan Post
--
On Fri, 1 Jun 2001, Kevin Kostyszyn wrote:
What does RTFM mean?
Beautiful. That's our bumper sticker.
--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jeremiah Wilton
INET: [EMAIL PROTECTED]
Fat City Network
had:
ARE YOU AN IDIOT?
or
SHUTDOWN ABORT
Don't forget HELP
- Kirti
-Original Message-
From: Jeremiah Wilton [SMTP:[EMAIL PROTECTED]]
Sent: Friday, June 01, 2001 12:47 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Rebuilding indexes
On Fri, 1
Stickers [RE: Rebuilding indexes]
is that supposed to be printed with really small, or really big font?
'IF YOU CAN READ THIS, YOU ARE A CERTIFIED PIG FUNDAMENT'
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services
?
or
SHUTDOWN ABORT
Don't forget HELP
- Kirti
-Original Message-
From: Jeremiah Wilton [SMTP:[EMAIL PROTECTED]]
Sent: Friday, June 01, 2001 12:47 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Rebuilding indexes
On Fri, 1 Jun 2001, Hatzistavrou Giannis wrote:
I don;t
On Fri, Jun 01, 2001 at 01:32:25PM -0800, Jeremiah Wilton wrote:
On Fri, 1 Jun 2001, Kevin Kostyszyn wrote:
What does RTFM mean?
Beautiful. That's our bumper sticker.
yep, well done!
===
Ray Stell [EMAIL PROTECTED] (540)
recipients of list ORACLE-L
Subject: RE: Bumoer Stickers [RE: Rebuilding indexes]
This is getting stupid, sorry I'm going to contribute to this...how about
Calvin (http://www.ucomics.com/calvinandhobbes/viewch.htm) peeing on a SQL
Server Logo, sure beats him peeing on a Ford sign.
- Ethan Post
Here is the log from a guy last week on LazyDBA, I sent him about 3 emails
asking if he had even bothered to open the manual and never got a response,
check out his final post...
May 16...
How would I upgrade an Oracle 7.3.4 database to an
Oracle 8.1.6 database. I'm new to oracle and I don't
THAT one I'll put on my car :)
and my desk. and the developer's face
From: Jeremiah Wilton [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Bumoer Stickers [RE: Rebuilding indexes]
Date: Fri, 01 Jun 2001 13:32:25 -0800
FOR YOUR INFORMATION
ESIS and EPFAL are now part of Logica. The Internet email addresses of the staff has
changed to the following - [EMAIL PROTECTED] eg [EMAIL PROTECTED] Emails
using the old format will continue to be delivered until 30th June 2001.
Richard,
You could always use the
You might be mistaken, since Oracle Manual (Oracle 8.1.5 Backup and
Recovery) , chapter 16 -- Managing a standby database --- Adding Datafiles
states the following:
Adding a datafile to your primary database generates redo data that, when
applied at your standby, automatically adds the datafile
there IS no way to do that with a standby database. Once you open it for
anything other than read-only (8i) you invalidate the standby status.
From: Richard Huntley [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Rebuilding
Adding a datafile does not cancel recovery - you just
need to 'pause' it, transport the appropriate files to
the standby and resume standby operations.
hth
connor
--- Richard Huntley [EMAIL PROTECTED] wrote:
For those of you that have implemented a standby
database, what method do
you use to
However, if you rebuild all the indexes for schema SALES on PRIMARY, what happens on
STANDBY? Are they rebuilt?
Cheers,
Earl
---
TheOracleDBA
[EMAIL PROTECTED]
On Thu, 31 May 2001 08:16:06
Rachel Carmichael wrote:
there IS no way to do that with a standby database. Once you open it
Hatzistavrou,
Thanks for you reply. It was about 4am when I ran into that and discovered
what I needed to do, however, I knew it would take a while so I just stopped
at that point. But, I was curious to find out if anyone had a BETTER way of
doing it. It seems like a waste if I have to go
they should be, unless you do nologging operations -- which can mess up your
standby database
From: The Oracle DBA [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: Rebuilding indexes
Date: Thu, 31 May 2001 10:05:57 -0800
Edition Generic
Technical Library under White Papers - Center of Expertise Articles
Oracle8i Standby Database
Note:76451.1
Sorry
hth
Rachel
From: Richard Huntley [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Rebuilding
No need to copy the whole file to the standby when adding a datafile! Just wait
for recovery to fail on the new file (ORA-01670), then type on the standby (in
'mount standby database' mode):
SQL alter database create datafile 'filename' as 'filename';
As long as you are using identical paths
On Thu, 31 May 2001,Rachel Carmichael scribbled on the wall in glitter crayon:
-It adds the datafile NAME only, not the file itself. And if the physical
-file has not been moved before the archived log that has that redo in it is
-applied, the recovery dies.
so if you got the file in the right
: Rebuilding indexes
Date: Thu, 31 May 2001 12:15:25 -0800
No need to copy the whole file to the standby when adding a datafile! Just
wait
for recovery to fail on the new file (ORA-01670), then type on the standby
(in
'mount standby database' mode):
SQL alter database create datafile 'filename
Hello, Richard,
If you want to use NOLOGGING operation on primary database, this is
still possible to keep STANDBY in sync. Regularly check output of
select FILE#, UNRECOVERABLE_CHANGE# from v$datafile;
on primary and standby database, and , in case UNRECOVERABLE_CHANGE#
yup -- that's what I used to try to do
From: Thater, William [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Rebuilding indexes
Date: Thu, 31 May 2001 12:09:37 -0800
On Thu, 31 May 2001,Rachel Carmichael scribbled
101 - 166 of 166 matches
Mail list logo
