Re: Newbie question, on reindexing tables

On Fri, 7 Dec 2001 00:42:16 +0100, "Eduard de Vries" <edevries_at_footlocker.com> wrote:

>Hi,
>
>I am new at Oracle so please forgive me if I ask a silly question;-)
>
>OK, the vendor of our application who uses an Oracle database has told me to
>reindex two
>
>tables in his database.
>
>If I look in Oracle storage manager I see that only one tablespace is near
>it's capacity,
>
>the SYSTEM tablespace is at 58.777 of 60.000 MB, which surprises me, we use
>the DB for about
>
>two months, but not thast heavy, but OK.

Uhhhhh - hopefully you are not using the system ts for your data or indexes?

>
>My first question is:I have been told that I need to rebuild two tables but
>how do I know in
>
>what tablespace they are? And if I know the tablespace, can I safely give
>the command ALTER
>
>INDEX tablespace_name REBUILD?

Knowing where the table lives wonīt bring you any further. Important is the index that belongs to the table. So have a look in the ALL_INDEXES view and find the views corresponding to your tables. Now you can do an "ALTER INDEX index_name REBUILD" for all these indexes. If you have it installed on your machine you can use Oracles schema manager to find the tables and the indexes and you can rebuild them from the gui. Please be aware that no one is using the db when you rebuild the indexes. There are ddl locks required which will prevent users from work.

>The db is backup up daily.
>
>Then, when I do this query (see query 1 in below post) it seems to
>indicate that there is plenty of space.

Sorry you didnīt send the queries!
>
>Also, is it normal you have to weekly rebuild the index on heavily used
>tables?
>And if yes, how do I do that?

Indexes on heavily used tables (insert, update, delete) are often out of balance. So it is not uncommon to rebuild them. If you have to do this week by week you can use the dbms_job package to create a database job which gives the sql statements at the right time.

>
>On the disk itself we have plenty of space.
>
>If I run the following query (see query 2 in below post)
>
>That shows me the extends should be set OK, correct?
>So it cannot be a space issue, am I correct?
>
>Anything else I should look at?
>I also have been told I should set up a job to monitor the statistics, that
>should also
>

statistics will not give you any hint to space or performance problems. Create a dbms_job to collect statistics on each table once a week (after the rebuild index above) or if modification rate on your tables is very high, every night and you will be done.

>automatically tell me when I run out of space, or where I start to run into
>issues, is this

For monitoring the database you can use the enterprise manager. If properly configured it will show you any problems before they arise.

>
>true?
>And if yes, where can I find some documentation how to do this?
>Is there a good website, I find some stuff at Oracle but it is all fairly
>complicated, I

Try technet.oracle.com itīs all there. you only have to search.

>will get there eventually but for now, any good tips?
>
>And is there a way to create an ERD schema for the database (only 75 tables)
>for my own reference?

You can try or buy Oracles Designer tool and use the reverse engineering to get the erd model out of the db. But be aware. Itīs no easy going tool ;-) You can use ms-access too, i think, but i have no deep knowledge in this. You need to setup odbc for the latter one.

>
>Thanks a million, and again, sorry if it is a silly Q.
>
>Eduard de Vries
>
>
>info_at_edrox.demon.nl
>

hth
Andy Received on Fri Dec 07 2001 - 02:09:45 CST