Reorganizing the DB.. the tricky way
Rick Denoire
found out that it is time to reorganize data again: 70% of migrated
records (spanning 2 blocks), lots of non-contiguous extents, large
fragmentation, index stagnation etc.
Using
"alter table <table_name> move" and
"alter index <index_name> rebuild parallel recalculate statistics
nologging"
gets the segment rewritten but it does not eliminate fragmentation
*between* the segments. In order to do that, one would have to take
all tables and indexes out of their current tablespace into a kind of
scratch tablespace, then back again.
Well, this would take about one week or longer. So I am wondering if I
could just add a new file to the same tablespace and move the objects
to this file, so they could just stay there and there would not exist
any need to move then back. Selecting which objects are contained in
one specific file can be done joining dba_extents and dba_data_files
through the file_id. But I just don't know how to target a specific
file while moving objects.
Using export/import ist not feasible, since this operation lasts even
longer and the DB would not be available for work.
Any hint?
Rick Denire
Ana C. Dent
Rick Denoire wrote:
> I analyzed the DB at our company (aprox. 180 GB, Oracle 8.1.7) and
> found out that it is time to reorganize data again: 70% of migrated
> records (spanning 2 blocks), lots of non-contiguous extents, large
> fragmentation, index stagnation etc.
>
> Using
> "alter table <table_name> move" and
> "alter index <index_name> rebuild parallel recalculate statistics
> nologging"
> gets the segment rewritten but it does not eliminate fragmentation
> *between* the segments. In order to do that, one would have to take
> all tables and indexes out of their current tablespace into a kind of
> scratch tablespace, then back again.
>
> Well, this would take about one week or longer. So I am wondering if I
> could just add a new file to the same tablespace and move the objects
> to this file, so they could just stay there and there would not exist
> any need to move then back. Selecting which objects are contained in
> one specific file can be done joining dba_extents and dba_data_files
> through the file_id. But I just don't know how to target a specific
> file while moving objects.
1) Oracle really does NOT care about OS files per se and
neither should you. Oracle primarily cares about tablespaces.
2) Why does it matter the name of the tablespace where the tables
and indexes reside? If it does not matter then simply create a
new tablespace & move the objects into it. I see no requirement to
move the object a second time "then back again"
3) If you add a new file to the existing tablepsace, stop worrying
about what object is in which file. Simply "reorg" the objects.
Oracle will make them contiguous as long as contiguous free space
exists within the tablespace.
Anton Buijs
Rick Denoire <100....@germanynet.de> schreef in berichtnieuws
pv709v0o2lr18vtt2...@4ax.com...
| I analyzed the DB at our company (aprox. 180 GB, Oracle 8.1.7) and
| found out that it is time to reorganize data again: 70% of migrated
| records (spanning 2 blocks), lots of non-contiguous extents, large
| fragmentation, index stagnation etc.
|
| Using
| "alter table <table_name> move" and
| "alter index <index_name> rebuild parallel recalculate statistics
| nologging"
| gets the segment rewritten but it does not eliminate fragmentation
| *between* the segments. In order to do that, one would have to take
| all tables and indexes out of their current tablespace into a kind of
| scratch tablespace, then back again.
|
| Well, this would take about one week or longer. So I am wondering if I
| could just add a new file to the same tablespace and move the objects
| to this file, so they could just stay there and there would not exist
| any need to move then back. Selecting which objects are contained in
| one specific file can be done joining dba_extents and dba_data_files
| through the file_id. But I just don't know how to target a specific
| file while moving objects.
Non-contiguous extents? Do you mean that extent 2 is not allocating the
blocks beside extent 1?
Unless you are speaking about - lets say - over 1000 extents for a
table/index in a Dictionary managed tablespace (DMT): I don't see why that's
a problem. It's a myth.
You can find many good scripts to detect when indexes need to be rebuild
(index browing) (like www.ixora.com.au)
To my knowledge you can't say in with datafile (or blocks) the table must
allocate space. You can only specify a tablespace.
My advice would be to rebuild the tables into another tablespace. And make
that tablespace a Locally managed tablespace (LMT). You can search this
newsgroup for discussions about DMT versus LMT. I personally have good
experiences with LMT. A table/index with 10.000 extents is no problem in a
LMT (although I still would try to keep a segment <100 extents).
I should consider to change some other storage parameters too, like
pctfree/pctused to avoid row chaining/migration in the future. You need to
make some calculations.
Visit www.orapub.com and download Technical paper #104 Oracle 7 Server Space
Management (free registration). Although it covers V7 it is still valid
(maybe not on details). It gives a very good calculation of the extra IO
involved for a full tablescan of a table when it has allocated 5 or 1000
extents. The proposed space management strategy is now in fact enforced with
LMT.
|
Rick Denoire
>1) Oracle really does NOT care about OS files per se and
>neither should you. Oracle primarily cares about tablespaces.
Whether I should care: See my answer to 2)
>2) Why does it matter the name of the tablespace where the tables
>and indexes reside? If it does not matter then simply create a
>new tablespace & move the objects into it. I see no requirement to
>move the object a second time "then back again"
When the developers create an index, they were asked to put them in
the corresponding tablespace for indexes. If its name change, they
will get an error. There is no such thing like a "default tablespace
for indexes".
Besides that, tablespace names were put in a number of sql statements
that developers have prepared to recreate objects if needed. OK, if
only the default tablespace is going to be used, that is a bad
practice.
>3) If you add a new file to the existing tablepsace, stop worrying
>about what object is in which file. Simply "reorg" the objects.
>Oracle will make them contiguous as long as contiguous free space
>exists within the tablespace.
Yes, but free space in the files won't be contiguous, so fragmentation
at the tablespace level will remain. That is what I am trying to
avoid.
Bye
Rick Denoire
Rick Denoire
>Non-contiguous extents? Do you mean that extent 2 is not allocating the
>blocks beside extent 1?
I mean that some free space or space allocated to a different segment
will lie in between.
>To my knowledge you can't say in with datafile (or blocks) the table must
>allocate space. You can only specify a tablespace.
But one could resize all OTHER files to a mininum, so Oracle would
have to put the table into a specific file... Dirty trick, I guess.
>My advice would be to rebuild the tables into another tablespace. And make
>that tablespace a Locally managed tablespace (LMT).
Actually, while writing this article, they are being rebuild into a
different, locally managed tablespace. But: It has a different name. I
would have then to change the default tablespace for all relevant
users.
The problem is that there is no legal way to rename a tablespace in
Oracle 8.1.7.
>Visit www.orapub.com and download Technical paper #104 Oracle 7 Server Space
>Management (free registration). Although it covers V7 it is still valid
>(maybe not on details). It gives a very good calculation of the extra IO
>involved for a full tablescan of a table when it has allocated 5 or 1000
>extents. The proposed space management strategy is now in fact enforced with
>LMT.
>|
Thanks for the hint. I will check that.
Bye
Rick Denoire
DA Morgan
Create brand new tablespaces and make them LMT with uniform extent sized
to handle the tables that will be moved to them. Then move tables on a
selective basis over a period of time.
Daniel Morgan
DA Morgan
Ouch. You need to get someone in there to teach your developers how to
work with Oracle. Tablespace names in SQL statements? Sounds like a bunch
of SQL Serve retreads that never learned Oracle concepts and architecture
and are still building temp tables.
The entire concept of developers choosing tablespaces, as they have no
knowledge of I/O is wrong. The blind placement of indexes in tablespaces
with names related to where the tables are without knowledge of I/O on te
server is wrong.
I would urge you to take a big step back away from the table and review
the postings here at c.d.o. over the last year with respect to both of
these issues before proceeding.
It is easy now to see why you have such a huge problem with fragmentation,
chaining, etc. Someone is going to need to take control of this or the
problems will grow bigger and bigger or you should consider polishing your
resume and getting out before it all collapses around you.
Daniel Morgan
Anton Buijs
Rick Denoire <100....@germanynet.de> schreef in berichtnieuws
a1n09vobklb122f9n...@4ax.com...
| "Anton Buijs" <aamm...@xs4all.nl> wrote:
|
| >Non-contiguous extents? Do you mean that extent 2 is not allocating the
| >blocks beside extent 1?
|
| I mean that some free space or space allocated to a different segment
| will lie in between.
I still don't see why you consider this a problem, but you are going to read
paper #104 helps.
| >To my knowledge you can't say in with datafile (or blocks) the table must
| >allocate space. You can only specify a tablespace.
|
| But one could resize all OTHER files to a mininum, so Oracle would
| have to put the table into a specific file... Dirty trick, I guess.
Not a dirty trick, just a trick, and you can influence where the blocks will
be alloced. But you can't switch to LMT then.
| >My advice would be to rebuild the tables into another tablespace. And
make
| >that tablespace a Locally managed tablespace (LMT).
|
| Actually, while writing this article, they are being rebuild into a
| different, locally managed tablespace. But: It has a different name. I
| would have then to change the default tablespace for all relevant
| users.
|
| The problem is that there is no legal way to rename a tablespace in
| Oracle 8.1.7.
Yes, it's a problem when tablespace names are in scripts. I solved this once
in a situation by having defined variables in the scripts (because the
production db had many tableespaces to accomodate different sizes, where the
development db just had 1 table and 1 index tablespace; in the login.sql
script I arranged all these variables to get the proper value).
Solution is to rebuild twice. When the original tablespace is moved empty,
you can recreate it as LMT and move all its segments back.
Anton Buijs
3E906EA1...@exxesolutions.com...
| Ouch. You need to get someone in there to teach your developers how to
| work with Oracle. Tablespace names in SQL statements? Sounds like a bunch
| of SQL Serve retreads that never learned Oracle concepts and architecture
| and are still building temp tables.
| The entire concept of developers choosing tablespaces, as they have no
| knowledge of I/O is wrong. The blind placement of indexes in tablespaces
| with names related to where the tables are without knowledge of I/O on te
| server is wrong.
I don't know sqlserver but saw much applications build with Oracle Designer.
I want developers to deliver create table and create index scripts WITH
tablespace names that Designer can generate with the script (but without
storage clause). Ofcourse I talk with them about it. Do you think I am
manually going to add it for 200 tables and 500 constraints or indexes?
|
| I would urge you to take a big step back away from the table and review
| the postings here at c.d.o. over the last year with respect to both of
| these issues before proceeding.
|
| It is easy now to see why you have such a huge problem with fragmentation,
| chaining, etc. Someone is going to need to take control of this or the
| problems will grow bigger and bigger or you should consider polishing your
| resume and getting out before it all collapses around you.
|
| Daniel Morgan
|
Fragmentation is probably caused (just guessing) because the initial setup
was wrong choosen as we can judge it now, maybe it was ok at the moment the
database was created. Now its up to Rick to take action and reorganize what
has grown wrong in x years. Lucky we have "alter table move" available
today.
oracleuser
Rick Denoire
>Ouch. You need to get someone in there to teach your developers how to
>work with Oracle. Tablespace names in SQL statements? Sounds like a bunch
>of SQL Serve retreads that never learned Oracle concepts and architecture
>and are still building temp tables.
>The entire concept of developers choosing tablespaces, as they have no
>knowledge of I/O is wrong. The blind placement of indexes in tablespaces
>with names related to where the tables are without knowledge of I/O on te
>server is wrong.
If developers need a new index, and I told them to use a specific
tablespace to acommodate that index, how are they going to get that
done without explicitly putting the name of the tablespace in the SQL
statement? So you say that is wrong. You are surely more experienced
that I am, so please let me know how to do it the right way.
>I would urge you to take a big step back away from the table and review
>the postings here at c.d.o. over the last year with respect to both of
>these issues before proceeding.
Give me some adequate keywords to search for. It is not easy to target
the question of the policy of using tablespace names by developers vs.
administrators with the help of these limited search engines like
"google".
>It is easy now to see why you have such a huge problem with fragmentation,
>chaining, etc. Someone is going to need to take control of this or the
>problems will grow bigger and bigger or you should consider polishing your
>resume and getting out before it all collapses around you.
That is a quite dramatic exageration. I don't feel that things will
break down on me.
By the way, while setting up a new database, I was called by one of
the developers who wanted to populated some tables and he asked me the
names of the tablespaces. I told him: "You are making assumptions you
shouldn't - bad style". He answered: "Well, where are those indexes
supposed to go". I had to tell him the corresponding tablespace for
indexes. What would you have done in such a situation?
Bye
Rick Denoire
Niall Litchfield
news:a1n09vobklb122f9n...@4ax.com...
> "Anton Buijs" <aamm...@xs4all.nl> wrote:
>
>
> >Non-contiguous extents? Do you mean that extent 2 is not allocating the
> >blocks beside extent 1?
>
> I mean that some free space or space allocated to a different segment
> will lie in between.
This would be normal, and would not normally be somehing to worry about. In
fact the second situation (two extents from different segements next to each
other in Oracle's view of the disk - and maybe even on disk) will always
happen and is never ever a problem. Ever. (Did I say it wasn't a problem).
You only need to worry about the first situation (free space dotted here
there and everywhere) if objects can't use the free space, this can be
avoided entirely by using uniform extent allocation in locally managed
tablespaces (or by enforcing the equivalent policy on your developers for
DMTs).
> Actually, while writing this article, they are being rebuild into a
> different, locally managed tablespace. But: It has a different name. I
> would have then to change the default tablespace for all relevant
> users.
spool off the result of SELECT 'ALTER USER '||USERNAME||' DEFAULT
TABLESPACE '||<NEW_TABLESPACE_NAME>||';' FROM DBA_USERS WHERE USERNAME NOT
IN ('SYS','SYSTEM'); to a sql script.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
Rick Denoire
>
>Fragmentation is probably caused (just guessing) because the initial setup
>was wrong choosen as we can judge it now, maybe it was ok at the moment the
>database was created. Now its up to Rick to take action and reorganize what
>has grown wrong in x years. Lucky we have "alter table move" available
>today.
One of the most popular "hobbies" of the developers is to add new
column to existing tables and to populate these using update. Don't
you think that - assuming the blocks have no more space free for the
longer rows - this would cause ALL blocks to become migrated? In
consequence, every input request for one record would cause 2 read
operations, so making things 100% slower...
Don't know how to avoid this. One can't just set PCTFREE to 40% for
every table, just in case one developer decides to add one column with
large fields.
Bye
Rick Denoire
Anton Buijs
| "Anton Buijs" <aamm...@xs4all.nl> wrote:
| One of the most popular "hobbies" of the developers is to add new
| column to existing tables and to populate these using update. Don't
| you think that - assuming the blocks have no more space free for the
| longer rows - this would cause ALL blocks to become migrated? In
| consequence, every input request for one record would cause 2 read
| operations, so making things 100% slower...
|
| Don't know how to avoid this. One can't just set PCTFREE to 40% for
| every table, just in case one developer decides to add one column with
| large fields.
|
| Bye
| Rick Denoire
Increasing pctfree would cause waste of space. You can't avoid many migrated
rows when columns are added, I agree. So once in a while you need to reorg
such tables, not very frequently, but it can be necessary now or then.
There is a performance penalty but I won't say it gets 100% slower. Maybe
the 2nd block was necessary too, to read the other rows matching the query,
it could be read anyway by the OS or because of the Oracle settings
(multiblock reads), required by another query. The total picture is always
much more complex than looking at 1 session.
DA Morgan
I would argue that in a production database there is no place for ALTER TABLE
ADD column as is being assumed here. That to do so is irresponsible. The
existing data should be dumped to a temp table, the table truncated, then
modified, and the data returned to the table in combination with whatever values
are required by the modification.
Daniel Morgan
Anton Buijs
| Anton Buijs wrote:
|
| I would argue that in a production database there is no place for ALTER
TABLE
| ADD column as is being assumed here. That to do so is irresponsible. The
| existing data should be dumped to a temp table, the table truncated, then
| modified, and the data returned to the table in combination with whatever
values
| are required by the modification.
|
| Daniel Morgan
|
Don't agree it is irresponsible, it is the real world! The production DBA is
always the last to know that a column has to be added, no time to wait for
the proper moment to take the application down for a while so you must add
the column NOW. And do the reorg later. Your or mine scenario: table is
unavailable for some time. And in 9i I'am in favour of adding the column and
reorg later: we can really do alter table move online, without downtime, if
I recall it well from the New features course (unfortunately we only have a
small 9i playground at this moment, but real 9i db's are coming soon)
Rick Denoire
>1) Oracle really does NOT care about OS files per se and
>neither should you. Oracle primarily cares about tablespaces.
One file has grown to about 30 GB in size, which I don't like at all.
I prefer having files about 8 GB because I am more flexible trying to
distribute them. But it happens that I can't resize this 30 GB file,
although it is less than 50% full. The reason is that some extents are
located at the upper end of the file. So I would prefer moving these
objects first. This is a case where one has to care about files.
If I could move exactly those objects preventing me from resizing the
file, then I would not have to move about 14 GB of data - which takes
a long time.
Perhaps there is a way to locate/identify the segments located at the
upper end of the file, I just don't know how.
Bye
Rick Denoire
Rick Denoire
>Create brand new tablespaces and make them LMT with uniform extent sized
>to handle the tables that will be moved to them. Then move tables on a
>selective basis over a period of time.
That is a fine advise, but I am afraid I will have to move things back
again - because of the tablespace name.
Two things I am concerned about are: if I use "alter table <tab-name>
online", I suppose that the table is still available while being
moved, but what is the price I have to pay? I mean, what is the
disadvantage of using the "online" option? I would rather do it in a
way that I can regain space of deleted records. If using the "online"
option prevents this, I prefer the "offline" method.
And 2: How can I avoid queries to fail due to indexes being in the
UNUSUABLE state until the table is completely moved and the index is
rebuilt?
Put together: How can I reorganize the DB without affecting its
availability?
Bye
Rick Denoire
DA Morgan
I stand on irresponsible. 'Real-world' should not be a convenient euphimism for
bad practices.
DBAs are responsible for the integrity of the production environment. Developers
can make as big a mess of development as they wish but ultimately the DBA must
control production. If it is otherwise the DBA should head for the door and a
new job or suffer silently every evening and weekend they are working for free.
Creating objects and altering objects should always be owned by the DBA when
things move from testing to production. DBAs should stay out of development and
developers should stay out of production. The result of blurring of these lines
can be found in far too many places.
But yes the table will be unavailable. And that is something that should be
factored into the mix by development when they decide to modify the object.
Which makes this an excellent place to add that development teams that don't
include a DBA capable of thinking this far in advance are doomed to create
messes.
Daniel Morgan
DA Morgan
What is required here requires either taking things off-line and doing the
dirty work on Christmas Eve (or similar) or doing it meticulously over a
period of time.
The end result should be that the old tablespaces, and their associated
datafiles, are dropped. That is your space recovery.
Daniel Morgan
Connor McDonald
You could write a really nifty database level trigger to catch errors,
diagnose the error message number for a tablespace error and log the
attempt and the sql
hth
connor
--
=========================
Connor McDonald
http://www.oracledba.co.uk
"Some days you're the pigeon, some days you're the statue"
Niall Litchfield
news:3e90a143$0$49105$e4fe...@news.xs4all.nl...
> DA Morgan <damo...@exxesolutions.com> schreef in berichtnieuws
> 3E909DD0...@exxesolutions.com...
> | Anton Buijs wrote:
> |
> | I would argue that in a production database there is no place for ALTER
> TABLE
> | ADD column as is being assumed here. That to do so is irresponsible. The
> | existing data should be dumped to a temp table, the table truncated,
then
> | modified, and the data returned to the table in combination with
whatever
> values
> | are required by the modification.
> |
> | Daniel Morgan
> |
>
> Don't agree it is irresponsible, it is the real world! The production DBA
is
> always the last to know that a column has to be added, no time to wait for
> the proper moment to take the application down for a while so you must
add
> the column NOW. And do the reorg later.
When this happens (and it does) it is a failure of the change management
process. In fact adding anything NOW to production is an
organisational/process failure. If you have some work done on your house the
builders don't just turn up and expect to start laying bricks NOW, they and
you as the owner schedule the work for an appropriate time. If they don't
they are a bunch of cowboys.
Norman Dunbar
>> Perhaps there is a way to locate/identify the segments located at the
>> upper end of the file, I just don't know how.
SELECT tablespace_name,
owner,
segment_name,
block_id as start_block,
blocks as num_blocks,
block_id + blocks -1 as end_block,
bytes/1024 as size_kb,
NULL as free
FROM dba_extents
WHERE file_id = &file_id
UNION
SELECT tablespace_name,
NULL,
NULL,
block_id as start_block,
blocks as num_blocks,
block_id + blocks -1 as end_block,
bytes/1024 as size_kb,
'Free' as free
FROM dba_free_space
WHERE file_id = &&file_id
ORDER BY start_block DESC;
Cheers,
Norman.
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
I cannot accept (much) credit for this script, I have a funny feeling it
is one of Howard's originally. I used to use it when I was trying to
reorg a DMT so that I could identify the extents at the top end of a
datafile and shift them prior to a resize downwards of that datafile.
Sounds like this is what you need !
Cheers,
Norm.
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
Richard Foote
news:3e90888b$0$4850$cc9e...@news.dial.pipex.com...
> "Rick Denoire" <100....@germanynet.de> wrote in message
> news:a1n09vobklb122f9n...@4ax.com...
> > "Anton Buijs" <aamm...@xs4all.nl> wrote:
> >
> >
> > >Non-contiguous extents? Do you mean that extent 2 is not allocating the
> > >blocks beside extent 1?
> >
> > I mean that some free space or space allocated to a different segment
> > will lie in between.
>
> This would be normal, and would not normally be somehing to worry about.
In
> fact the second situation (two extents from different segements next to
each
> other in Oracle's view of the disk - and maybe even on disk) will always
> happen and is never ever a problem. Ever. (Did I say it wasn't a problem).
> You only need to worry about the first situation (free space dotted here
> there and everywhere) if objects can't use the free space, this can be
> avoided entirely by using uniform extent allocation in locally managed
> tablespaces (or by enforcing the equivalent policy on your developers for
> DMTs).
>
Hi Niall,
One important point you forgot to mention, there are no issues per se with
having extents from different segments intermingled ;)
I went to a presentation by Quest not long ago where they demonstrated how
you could detect segments that are spread all over a tablespace and how you
could reorg the tablespace and place all like objects next to each other.
My question of why you would do such a thing was met with a somewhat
incredulous look ....
Cheers
Richard
Richard Foote
> DA Morgan <damo...@exxesolutions.com> wrote:
>
>
> >Create brand new tablespaces and make them LMT with uniform extent sized
> >to handle the tables that will be moved to them. Then move tables on a
> >selective basis over a period of time.
>
> That is a fine advise, but I am afraid I will have to move things back
> again - because of the tablespace name.
>
> Two things I am concerned about are: if I use "alter table <tab-name>
> online", I suppose that the table is still available while being
> moved, but what is the price I have to pay? I mean, what is the
> disadvantage of using the "online" option? I would rather do it in a
> way that I can regain space of deleted records. If using the "online"
> option prevents this, I prefer the "offline" method.
Hi Rick,
Note that you can *not* move tables online (not with the alter table command
anyway). The only exception to this and why the syntax is there is to cater
for Index Organised tables which are effectively "rebuilt" with the alter
table online command.
>
> And 2: How can I avoid queries to fail due to indexes being in the
> UNUSUABLE state until the table is completely moved and the index is
> rebuilt?
>
Generally, queries won't fail, they'll just run like a three legged dog.
> Put together: How can I reorganize the DB without affecting its
> availability?
>
Only reorg if necessary. Some of the reasons you provide sound a little
questionable.
If availability is a vitally important consideration and a reorg is a must,
then check out the dbms_redefinition package which does allow you to move
and reorg tables without impacting availability. It's a bit cumbersome but
it might meet your needs.
Cheers
Richard
> Bye
> Rick Denoire
Richard Foote
> DA Morgan <damo...@exxesolutions.com> wrote:
>
< snip>
> If developers need a new index, and I told them to use a specific
> tablespace to acommodate that index, how are they going to get that
> done without explicitly putting the name of the tablespace in the SQL
> statement? So you say that is wrong. You are surely more experienced
> that I am, so please let me know how to do it the right way.
>
Hi Rick
Unless we're talking a "play" sandbox database where performance is not
really of concern, I would question developers creating any objects as such.
In any "production" level database, the DBA should really be creating all
objects to ensure that basic tuning and capacity management issues are
considered.
When developers create a "new index", do they understand and consider
appropriate pctfree, inittrans/maxtrans, buffer pools, freelists,
partitioning, local/global, no/logging, no/compress, statistics, bitmap,
etc. ? Do they appreciate I/O balancing or storage/capacity management,
etc...
Make sense ?
Cheers
Richard
Rick Denoire
>Note that you can *not* move tables online (not with the alter table command
>anyway). The only exception to this and why the syntax is there is to cater
>for Index Organised tables which are effectively "rebuilt" with the alter
>table online command.
I think that I confused moving tables with rebuilding indexes. Indexes
can be rebuilt online.
>> And 2: How can I avoid queries to fail due to indexes being in the
>> UNUSUABLE state until the table is completely moved and the index is
>> rebuilt?
>>
>
>Generally, queries won't fail, they'll just run like a three legged dog.
Hm. I remember a collegue who called me because something was failing
exactly due to an index being rebuilt. Perhaps he was using the name
of the index explicitly in some manner.
>If availability is a vitally important consideration and a reorg is a must,
>then check out the dbms_redefinition package which does allow you to move
>and reorg tables without impacting availability. It's a bit cumbersome but
>it might meet your needs.
dbms_redefinition package? That is new to me! I will go after that.
Bye
Rick Denoire
Anton Buijs
| dbms_redefinition package? That is new to me! I will go after that.
|
| Bye
| Rick Denoire
Don't, it's that new 9i feature where you can really move the table with
only a very short lasting lock table.
And you said you are running V8.1.7 so you can only use alter table move....