Server Administration :: How To Verify If Rebuild Index Required In Database

Mar 26, 2012

How to verify if rebuild an index is required in database.

View 9 Replies


ADVERTISEMENT

Server Administration :: Index Rebuild Grows Database Size

Oct 29, 2010

Our database size is 100GB and i removed few records from a table and rebuild the index.The size of Index reduced considerably after Index rebuild but now i see our database size increased to 115GB.I know online rebuild creates second index which is also removed after the build is finished then why the increase in database size?Is there a way so it shows up more space ?

select sum(bytes) from dba_segments where owner='abc' and segment_name='abc_index_1';
8GB

ALTER INDEX abc_index_1 REBUILD ONLINE ;

select sum(bytes) from dba_segments where owner='abc' and segment_name='abc_index_1';
2GB

View 6 Replies View Related

Server Administration :: Identify Condition To Rebuild Index

May 27, 2012

In which condition it is required to rebuild the index and how can identified it?

View 12 Replies View Related

Server Administration :: Index And Table Rebuild Method

Jan 17, 2012

The best way to rebuild index and table and also give the reason as why we need to use this method?

View 10 Replies View Related

Server Administration :: Recover Space After Index Rebuild?

Apr 26, 2011

We have separate tablespaces for Tables and Indexes. Also Temp is in different temporary tablespace and UNDO also in UNDO tablespace and Index tablespace contains only Indexes. The tablespace usage for tables is 80% and Index is 91%.There is not enough disk space to allocate for the datafile on our system. I ran a rebuild on one of the Index but now notice the Index tablespace is 98% used soon after the rebuild finished.

How can i free up space for Index tablespace and why does the size of Index tablespace increased after the rebuild.

View 8 Replies View Related

Performance Tuning :: Difference Between Index Rebuild And Index Rebuild Online?

Jan 10, 2011

What is the difference between index rebuild and index rebuild online.

View 3 Replies View Related

Server Administration :: Database Shutdown Is Required Or Not

Apr 24, 2011

We have 2 Unix SunOS server, named as SUNOS01 and SUNOS02, there is some space need to add in SUNOS02 and for that I have to bring Database down on SUNOS02. When I checked I found that the DB is pointing to SUNOS01, i.e. there is no DB on SUNOS02, So, Do I required to shutdown the DB on SUNOS01 or it is not required to do anything from Database side. I mean to say should I ask Unix team to bring down the unix server and add the space.Also, if DB shutdown is not required then what I have to do afetr they have added space and bring the server up from DB side.

View 4 Replies View Related

Server Administration :: Verify Tablespace Space

Jan 9, 2012

Application team requested hosting team to add some space to tablespace as it was exceeding 80% used.Now the hosting team have added the space as per recommendation and the application team wants us to verify if the space was added. How to check the space was added in GB to list of tablesapces ?

View 5 Replies View Related

Server Administration :: Indexes To Be Rebuild?

Jan 30, 2010

How to find indexes which are eligible to rebuild in the database environment? Is there any query to find it?

View 7 Replies View Related

Server Administration :: Start Using Data And Index Compression In 11g Database

Jun 27, 2012

We have a requirement from the customer to start using data and index compression in our 11g database.. Is this something available in Oracle 10g,11g without any additional costs? We are not sure if this will work with our application so we will have to test it in-house, is it possible to compress the existing table data/index to test it out?

View 3 Replies View Related

Performance Tuning :: How To Verify If A Row Exists (index Range Scan)

Oct 18, 2011

In the link below [URL] Thomas kyte has said, use the CBO and select /*+ FIRST_ROWS */ primary_key from table where rownum = 1; it'll read the index and stop at the first row. very fast on a big empty table (as the index is small and empty).

very fast on a big full table as the index is just read to find the first leaf node and then "stop".

It gives faster result if the primary key is used. But what if we have a table with around 1000 million rows and for the predicates there is a index range scan on the table.

What if we have a table say big_table (10000000000 rows) and the sql is something like

select /*+ first_rows */ 1 /* id, attribute_id*/
from big_table
where attribute_name ='Gross Premium'
and value ='10000'
and version_date is null
and rownum=1; --it's taking around 3 min

We observed that in such case there will be a range index scan for the index on the predicates. For a particular id there may be different values for attribute 'Gross Premium' and may have multiple versions.

How I would tune such a query where the purpose is to check if at least 1 records exists in the table for the input?

View 4 Replies View Related

Server Administration :: Monitoring Script Required For Oracle 9.2.0.7.0 On Windows 2000 Server

Apr 8, 2010

My manager is asking to create a SQL scrip, which will provide following information.

Our database is 9.2.0.7.0 on Windows 2000 Server

-How many records Insert / Update or Delete daily in my Oracle database?

- Check Archive log / Redo Log switching information?

-Check database growth ?

View 10 Replies View Related

SQL & PL/SQL :: Analyze And Rebuild The Index?

Apr 11, 2011

I need to Analyze and Rebuild the indexes with a script at once for a schema.

View 30 Replies View Related

Oracle Sys Index Rebuild?

May 28, 2012

Below Table Index are rebuild,This table are relation with sys user or not?after rebuild index, getting error in alert log file

ORA-00600: internal error code, arguments: [psdnop-1], [1410], [], [], [], [], [], [], [], [], [], []
ORA-01410: invalid ROWID

when trying to select * from dba_network_acls getting error massage Rowid invalid

ALTER INDEX CTXSYS.SYS_C003684 REBUILD;
ALTER INDEX CTXSYS.SYS_C003683 REBUILD;
ALTER INDEX CTXSYS.SYS_C003682 REBUILD;
ALTER INDEX CTXSYS.SYS_C003681 REBUILD;

[code]...

It is possible to recreate this index again.......

View 1 Replies View Related

Server Administration :: Privileges Required For Export

Dec 1, 2011

I have one question regarding privileges required for export.I read in a document that

Quote:
If you do not have the system privileges contained in the EXP_FULL_DATABASE role, you cannot export objects contained in another user's schema. For example, you cannot export a table in another user's schema, even if you created a synonym for it.

what are the system privileges required in EXP_FULL_DATABASE role to perform export objects contained in another users schema?

View 2 Replies View Related

SQL & PL/SQL :: Bitmap Index Rebuild - Getting ORA-00054

Nov 15, 2012

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I have a procedure that is run weekly to load the data, for which it calls another procedure. This weekly procedure, disables the index, load the data, and rebuilds the index. During rebuilding of index, its giving ORA-00054: resource busy and acquire with NOWAIT error. I dont have privileges to look for session id, and kill the session. How to avoid this error.

create or replace
PROCEDURE "WCL_WEEKLY_UPLOAD" (
p_event_id IN NUMBER
)
[code]....

As you can see, after the procedure wc_upload, COMMIT is issued, and then the rebuilding of the index is starting. So dont know what is causing the table to lock.

View 6 Replies View Related

Index Rebuild Partition Not Completing?

Nov 20, 2012

Index with following PARTITIONS. Index rebuild is extremely slow. Below 2 Alter index ..rebuild... took 10 hours to complete. Because of this queries which based on this index are really slow.

SYS@DB AS SYSDBA> select partition_name,tablespace_name,bytes/1024 KB from dba_segments where segment_name='KSTN';
PARTITION_NAME    TABLESPACE_NAME           KB
------------------------------ ------------------------------ ----------
REB_IDX_1            TS_REB                     64
REB_IDX_2            TS_REB                     64
REB_IDX_3            INDEX                        64
REB_IDX_4            TS_REB                     64

[code]....

View 7 Replies View Related

How To Rebuild Local Partitioned Index

Jan 29, 2013

2day i was dropping few unwanted index from the data base, By mistake i removed the local partitioned index , So i want to recreate that index.i create the index, will the partitioned index updates when we add partitioned to the tables.

View 2 Replies View Related

Server Administration :: Tablespace Size Required To Be Increased?

Nov 19, 2011

I found some of the tablesapces whose used% exceeded 80%.Based on this output i thought of increasing the datafile.But there are many tablespaces of DATA1 whose autoextensible column some are set to NO, and some are set to YES. I believe we dont need to bother about the tablespace whose Autoextensible column set to YES as Oracle will automatically manage until the Maxsize of it. But some tablesapce of DATA1 are set to NO. Should i recommend to add size for this DATA1 tablespace whose autoextensible are set to YES/NO or leave it as it is.

Please see the column below.

Quote:
FILE_IDTABLESPACE_NAMESIZE(M)USED(M)FREE(M)MAX(M)AUTOEXTENSIBLE% USED
59DATA13276732741.1325.7532767YES100
60DATA13276732741.6925.1932767YES100
61DATA1327673274026.8832767YES100
76DATA13270032193.31506.560NO98
77DATA13270030858.51841.380NO94
78DATA13270029476.563223.310NO90

View 25 Replies View Related

Server Administration :: Convert Global Index To Local Index

Jun 23, 2011

I have a global index and I want to convert it to local index.Is there a way to recreate local index with out dropping the global index.

I can create a local index first and then drop the global index. But is there a way to create it with out dropping the global index, just convert it.

View 5 Replies View Related

Oracle Sys Index Rebuild After Getting Invalid Error Ora-01410

May 26, 2012

Oracle 11.1 version

Oracle sys Index rebuild list
CTXSYS
EXFSYS
FLOWS_030000
FLOWS_FILES
MDSYS
OLAPSYS
ORDSYS
OWBSYS
SI_INFORMTN_SCHEMA
TSMSYS
WKSYS
MDSYS

After rebuild above index im getting error while inserting,update,delete table rows

Error like 'INVALID ROWID'

View 3 Replies View Related

Performance Tuning :: Create Versus Rebuild Index

Jan 27, 2011

I was comparing cost of rebuild vs create index...I carried out the following test

SQL> create table t4 as select * from t1;

Table created.

SQL> create table t5 as select * from t1 where 1=2;

Table created.

SQL> create index i5 on t5(id);

Index created. SQL> select bytes,extents,blocks from user_segments where segment_name='I5';

BYTES EXTENTS BLOCKS
---------- ---------- ----------
65536 1 8

SQL> alter index i5 unusable;

Index altered.

SQL> alter table t5 nologging;

Table altered.

SQL> Alter session set skip_unusable_indexes=True;

Session altered.

SQL> insert /*+ append */ into t5 select * from t1;

563904 rows created.

SQL> commit;

Commit complete.

Now I compared the cost (elapsed time, logical I/O) of the operations

create index i4 on t4(id);
Vs
alter index i5 rebuild online;

Following is the related trace of above 2 steps

create index i4 on t4(id)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 1.17 3.38 9497 7869 335 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.17 3.38 9497 7870 335 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5
[code]....

So which option we shall pick in such cases? {Of course I haven't set 'nologging' for the indices but it is same for both indices we are comparing}

View 2 Replies View Related

Performance Tuning :: Oracle Coalesce Vs Index Rebuild?

Dec 30, 2010

We have large tables 60-70 GB having 120 million records. We have to perform index rebuild frequently which takes significant time to complete and effects database performance too. how we can use index Coalesce? what are its benefits, coalesce results in performance gain?

View 10 Replies View Related

Rebuild Local Index Partition Is Using Full Table Scan

Apr 23, 2012

I am rebuilding some UNUSABLE local index partitions on Oracle 8.1.7.4.0 (64bit) database . The platform is a HPUX machine.

The DDL of the partition table/indexes:
=========================
CREATE TABLE TESTME
( INST_NO CHAR(3) NOT NULL,
ACCT_NO CHAR(16) NOT NULL,
REC_NO CHAR(9) NOT NULL,
TRAN_TYPE CHAR(2) DEFAULT ' ',
STAT CHAR(2) DEFAULT ' ',
[code]...

View 2 Replies View Related

Server Administration :: Minimum Datafile Size Required For Creating Non-system Tablespace?

May 21, 2007

what is the Minimum datafile size required for creating a non-system tablespace?

I am trying to create a tablespace by giving the syntax like,

SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 72k;
create tablespace t1
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required

SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 73k;

Tablespace created.

The blocksize for my database is 4096, as i have heard that the minimum size of the datafile is decided by blocksize, but i want to know that how it is calculated as by giving the above syntax the other values will be default. I am trying the syntax in oracle 9.2.0.1.0 version.

View 13 Replies View Related

Server Administration :: While Creating Index ORA-00603 / ORACLE Server Session Terminated By Fatal Error

Jul 26, 2010

While creating the index we are getting the error "ORA-00603: ORACLE server session terminated by fatal error".

We have the space in tablespaces and also in the file systesm.

View 13 Replies View Related

Server Administration :: How To Get Block_number Of Index

Jan 4, 2012

If i know a rowid of a row,how to get the block_number of index?

SQL> select rowid from hxl.tb_test where id=1;

ROWID
------------------
AAAMo/AAFAAAAAOAAA

View 7 Replies View Related

Server Administration :: Rebuilding Of Index

Oct 30, 2010

we have 25 GB free space in Index tablespace, to avoid space issue i have added 10GB of space. Next shift DBA, rebuild INDEX of the from INDEX tablespace. Now i can check, only 27GB free space in INDEX tablespace...

Rebuilding of INDEX takes extra space.. ?

View 7 Replies View Related

Server Administration :: How To Compress Index

Oct 23, 2011

How to compress a index?

SQL> Create Table tb_compress_test
2 As
3 Select * From dba_objects;

Table created.

SQL> Create Index idx_object_id On tb_compress_test(object_id);

Index created.

SQL> Alter Index idx_object_id Move Compress;
Alter Index idx_object_id Move Compress
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

View 9 Replies View Related

Server Administration :: Density Of Index?

Nov 29, 2010

What exactly mean density of the index in db meanings ? I mean when you configure rebuilt of the index and how do you determine the value of density? for e.g when I have density 40 what does it mean ? next question : how shall I determine which tables are included in index ?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved