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


ADVERTISEMENT

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 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 :: Size Of The Oracle Database 11g?

Apr 21, 2010

how can we find the size of the oracle database 11g.

View 2 Replies View Related

Server Administration :: Increase Size Of Database To 50 GB

Jan 31, 2013

I have Oracle 11gR2 running on windows xp machine. Windows xp has total size of 150 GB and free space of 95 GB.

I checked the size of the database that I created. It showed the total size of the database as 2 GB and used space as 2 GB. If I want to increase the total size of the database to 50 GB, what should i do? Now which is the disk space size? Windows or Oracle?

View 4 Replies View Related

Server Administration :: How To Get Size Of All 80 Users From Database

Mar 19, 2012

select username,account_status,default_tablespace from dba_users;

The ran the above query and it return 80 users but when i ran below query it shows just 14 rows.

select owner,sum(bytes)/(1024*1024) "GB" from dba_segments group by owner;how to get the size of all 80 users from database ?

View 2 Replies View Related

Server Administration :: Estimating Size Of Database / Datafiles

Jul 3, 2010

I have checked the space of my tablespaces/datafiles in my database. I have 8 GB space left in my database server. I cant add more hard-disk as there is no slot left. We r planning to buy a new server with latest config.

My question is, how can we know upto what size our database can increase and when a datafile need to be added in advance. Sometimes even though datafiles have space left,it shows errors abt extents cannot be extended. We have coalesce the tablespaces and added a new datafile.

View 5 Replies View Related

Server Administration :: Size Storing JPG File In Database

Feb 13, 2012

I am storing customer's snaps in a table ( column's data type as LONG RAW) using oracle forms Webutil. Now there are 250 snaps in the table. The file type of these snaps is JPG with the average size 30KB.

I made a backup using export utility before storing these snaps and the exported DMP file's size was 36MB. Now after storing these just 250 snaps of 30KB the DMP file's size is gone over 300MB.

i need to change column's datatype? or some where in oracle forms's image item. Because on window's file system the size of these files is just 8MB.

View 8 Replies View Related

Server Administration :: Control File And Database Block Size?

Oct 24, 2011

SQL> select block_size from v$controlfile;

BLOCK_SIZE
----------
16384

SQL> show parameter db_block_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

the 2 can have difference block size?

View 3 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 :: ORA-04030 - Split From Tablespace With Different Block Size Inside Same Database

Nov 26, 2011

i written this code i m facing ORA-04030: out of process memory when trying to allocate 16408 bytes error

/* Formatted on 2011/11/26 11:52 (Formatter Plus v4.8. */
DECLARE
row_id varchar2(50);
v_batch_id temp.batch_id%TYPE;
v_slab_id temp.slab_id%TYPE;
flag NUMBER (2);
num varchar2(50) := &row_id;

[code].....

View 1 Replies View Related

Server Administration :: How To Reduce Size Of TEMP DBF File Size

Apr 13, 2011

I am using oracle 8.1.5 database and my temp01.dbf file size is increased upto 19.8 GB now i want reduce its size .

View 13 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

How To Rebuild Huge Indexes Upto 30gb In Size

Sep 7, 2011

i am having some indexes upto size of 40gb, i need to rebuild them having only 15 temp tablespace and without downtime

View 5 Replies View Related

CPU Usage On Server Steadily Grows After Instance Startup

Mar 4, 2013

We have Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production on AIX platform. We've noticed that the CPU usage on the server is slowly but steadily grows since instance restart: after the instance restart the maximum CPU usage is about 25-30%, and a month later with the comparable transactional load it's about 60-65%. In the top I see a lot of Oracle sessions each of which use about 1% of CPU, so I cannot find the one "CPU eater".

View 4 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

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 :: 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 :: How To Know DB Size Increase Per Day

Jan 31, 2011

How to know DB size increase per hour or day on the Oracle?

View 3 Replies View Related

Server Administration :: Max Size Of Tablespace

Jan 4, 2010

I have one tablespace PSINDEX with Maxsize of 6 GB. But when I query the tablespace its showing the BYTES is greater than MAXBYTES.

View 5 Replies View Related







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