| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: ALTER TABLE REBUILD command - LONG ?
--0-149798315-970078573=:15271
Content-Type: text/plain; charset=us-ascii
It will invalidate the index associated with the Primary Key but it leaves the constraint enabled along with any FK's pointing to it from other tables. Procedures which reference moved tables are fine also. Anything which is logical against the table should be fine, packages, procedures, triggers, etc. Indexes depend upon the rowid and they will all change once the table is moved so that is why they become invalid.
I just rebuild the invalid indexes after I move a table and that is all I've had to do.
Michael Henderson wrote:
>
> Plus, you can't seem to move a table with a LONG column. Anyone know how to move a table with these ?
>
> Mike.
>
> Michael Henderson, DBA
> http://www.nzoom.com
> DDI: +64-9-916 6941 Fax: +64-9-308 9614 Mob: 021-465 466
>
> >>> maa25681_at_glaxowellcome.com 09/26 8:22 >>>
> Thanks Waleed and Charlie,
>
> It looks like the ALTER TABLE MOVE command was implemented in 8.1.6. It
> also looks like when you use it, all the indexes on the table become
> invalid, forcing you to do an ALTER INDEX REBUILD.
>
> Thanks, again!
>
> Michael Armstead
> Database Administrator, OCP-Certified
> Corporate & Finance Information Systems
> Glaxo Wellcome
>
> > -----Original Message-----
> > From: Khedr, Waleed [SMTP:Waleed.Khedr_at_FMR.COM]
> > Sent: Monday, September 25, 2000 3:02 PM
> > To: 'ORACLE-L_at_fatcity.com'
> > Cc: Armstead, Michael A
> > Subject: RE: ALTER TABLE REBUILD command
> >
> > There is alter table move_clause in 8.1.6.
> >
> > Regards
> >
> > Waleed
> >
> > -----Original Message-----
> > From: Armstead, Michael A [mailto:maa25681_at_glaxowellcome.com]
> > Sent: Monday, September 25, 2000 2:56 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: ALTER TABLE REBUILD command
> >
> >
> > I heard somewhere that we will soon have the ALTER TABLE REBUILD command.
> > The command will allow tables to be moved from one tablespace to another,
> > defragment a table, provide the ability to modify of the INITIAL
> > parameter,
> > and maybe some other things.
> >
> > Is the rumor true? If so, when is the feature expected to be available?
> >
> > Michael Armstead
> > Database Administrator, OCP-Certified
> > Corporate & Finance Information Systems
> > Glaxo Wellcome
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Armstead, Michael A
> > INET: maa25681_at_glaxowellcome.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Armstead, Michael A
> INET: maa25681_at_glaxowellcome.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Michael Henderson
> INET: MHenders_at_tvnz.co.nz
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
-- Charlie Mengler Maintenance Warehouse charliem_at_mwh.com 10641 Scripps Summit Ct 858-831-2229 San Diego, CA 92131 I got high on the mountain at the ORACLE of Delphi!!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: charliem_at_mwh.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --------------------------------- Do You Yahoo!? Send instant messages & get email alerts with Yahoo! Messenger. --0-149798315-970078573=:15271 Content-Type: text/html; charset=us-ascii <P> It will invalidate the index associated with the Primary Key but it leaves the constraint enabled along with any FK's pointing to it from other tables. Procedures which reference moved tables are fine also. Anything which is logical against the table should be fine, packages, procedures, triggers, etc. Indexes depend upon the rowid and they will all change once the table is moved so that is why they become invalid. <P> <P>I just rebuild the invalid indexes after I move a table and that is all I've had to do. <P> <P>- Brian <P> <B><I>Charlie Mengler <charliem_at_mwh.com></I></B> wrote: <BR> <BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">Has anyone tested what affect the ALTER TABLE MOVE command has on<BR>Primary Key/Foreign Key constraints, triggers, or packages that<BR>reference the table being moved? If so, what other cleanup<BR>actions are required after using this command?<BR><BR>Michael Henderson wrote:<BR>> <BR>> Plus, you can't seem to move a table with a LONG column. Anyone know how to move a table with these ?<BR>> <BR>> Mike.<BR>> <BR>> Michael Henderson, DBA<BR>> http://www.nzoom.com<BR>> DDI: +64-9-916 6941 Fax: +64-9-308 9614 Mob: 021-465 466<BR>> <BR>> >>> maa25681@glaxowellcome.com 09/26 8:22 >>><BR>> Thanks Waleed and Charlie,<BR>> <BR>> It looks like the ALTER TABLE MOVE command was implemented in 8.1.6. It<BR>> also looks like when you use it, all the indexes on the table become<BR>> invalid, forcing you to do an ALTER INDEX REBUILD.<BR>> <! BR>> Thanks, again!<BR>> <BR>> Michael Armstead<BR>> Database Administrator, OCP-Certified<BR>> Corporate & Finance Information Systems<BR>> Glaxo Wellcome<BR>> <BR>> > -----Original Message-----<BR>> > From: Khedr, Waleed [SMTP:Waleed.Khedr_at_FMR.COM]<BR>> > Sent: Monday, September 25, 2000 3:02 PM<BR>> > To: 'ORACLE-L_at_fatcity.com'<BR>> > Cc: Armstead, Michael A<BR>> > Subject: RE: ALTER TABLE REBUILD command<BR>> ><BR>> > There is alter table move_clause in 8.1.6.<BR>> ><BR>> > Regards<BR>> ><BR>> > Waleed<BR>> ><BR>> > -----Original Message-----<BR>> > From: Armstead, Michael A [mailto:maa25681_at_glaxowellcome.com]<BR>> > Sent: Monday, September 25, 2000 2:56 PM<BR>> > To: Multiple recipients of list ORACLE-L<BR>> > Subject: ALTER TABLE REBUILD command<BR>> ><BR>> ><BR>> > I heard somewhere that we will soon have the ALTER TABLE ! REBUILD command.<BR>> > The command will allow tables to be moved from one tablespace to another,<BR>> > defragment a table, provide the ability to modify of the INITIAL<BR>> > parameter,<BR>> > and maybe some other things.<BR>> ><BR>> > Is the rumor true? If so, when is the feature expected to be available?<BR>> ><BR>> > Michael Armstead<BR>> > Database Administrator, OCP-Certified<BR>> > Corporate & Finance Information Systems<BR>> > Glaxo Wellcome<BR>> ><BR>> ><BR>> ><BR>> > --<BR>> > Please see the official ORACLE-L FAQ: http://www.orafaq.com<BR>> > --<BR>> > Author: Armstead, Michael A<BR>> > INET: maa25681@glaxowellcome.com<BR>> ><BR>> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051<BR>> > San Diego, California -- Public Internet access / Mailing Lists<BR>> > -------------------------------------------------------! -------------<BR>> > To REMOVE yourself from this mailing list, send an E-Mail message<BR>> > to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>> > the message BODY, include a line containing: UNSUB ORACLE-L<BR>> > (or the name of mailing list you want to be removed from). You may<BR>> > also send the HELP command for other information (like subscribing).<BR>> <BR>> --<BR>> Please see the official ORACLE-L FAQ: http://www.orafaq.com<BR>> --<BR>> Author: Armstead, Michael A<BR>> INET: maa25681@glaxowellcome.com<BR>> <BR>> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051<BR>> San Diego, California -- Public Internet access / Mailing Lists<BR>> --------------------------------------------------------------------<BR>> To REMOVE yourself from this mailing list, send an E-Mail message<BR>> to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>> the message BODY, ! include a line containing: UNSUB ORACLE-L<BR>> (or the name of mailing list you want to be removed from). You may<BR>> also send the HELP command for other information (like subscribing).<BR>> <BR>> --<BR>> Please see the official ORACLE-L FAQ: http://www.orafaq.com<BR>> --<BR>> Author: Michael Henderson<BR>> INET: MHenders@tvnz.co.nz<BR>> <BR>> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051<BR>> San Diego, California -- Public Internet access / Mailing Lists<BR>> --------------------------------------------------------------------<BR>> To REMOVE yourself from this mailing list, send an E-Mail message<BR>> to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>> the message BODY, include a line containing: UNSUB ORACLE-L<BR>> (or the name of mailing list you want to be removed from). You may<BR>> also send the HELP command for other information (like subscribing).<BR><BR>-- <BR>Charlie Mengler ! Maintenance Warehouse <BR>charliem@mwh.com 10641 Scripps Summit Ct <BR>858-831-2229 San Diego, CA 92131 <BR>I got high on the mountain at the ORACLE of Delphi!!!<BR>-- <BR>Please see the official ORACLE-L FAQ: http://www.orafaq.com<BR>-- <BR>Author: Charlie Mengler<BR>INET: charliem@mwh.com<BR><BR>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051<BR>San Diego, California -- Public Internet access / Mailing Lists<BR>--------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).</BLOCKQUOTE><p><br><hr size=1><b>Do You Yahoo!?</b><br>Received on Wed Sep 27 2000 - 13:16:13 CDT
 |
 |