OraFAQ Forum: Server Administration » Table Reorg / DBMS_REDEFINITION

Home » RDBMS Server » Server Administration » Table Reorg / DBMS_REDEFINITION (Oracle 12.1.0.2 (12c), Red Hat Linux 7.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Table Reorg / DBMS_REDEFINITION [message #659691]

Fri, 27 January 2017 09:15

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

All - I can see a table which can go for a re-org. I found this by comparing NUM_ROWS*AVG_ROW_LEN (DBA_TABLES) with segment_size (DBA_SEGMENTS).

I was able to see there are a list of options to do the re-org (like):

I would like to know the benefit of using DBMS_REDFINITION for reorg against the other options which would not require downtime. To me it looked like a complex task. We have the option to do the TABLE SHRINK and this does not require a downtime for the table.

However, I am unable to understand the purpose of using DBMS_REDEFINITION for reorg. Can someone help me to understand it? Is it option that a DBA needs to use at some point of time?

Regards,
Antony

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #659692 is a reply to message #659691]

Fri, 27 January 2017 09:22

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

What is the business case for doing this?

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #659693 is a reply to message #659692]

Fri, 27 January 2017 09:28

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

The DB is growing at a faster rate. We found that few of the tables are occupying more size in the tablespace than the actual data. There is a request to get this space issue addressed.

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #659695 is a reply to message #659691]

Fri, 27 January 2017 09:33

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

I would use SHRINK SPACE. Unlkess you want to move the table and indexes as you do it, in which case DBMS_REDEFINITION. Either way there should not be any downtime.

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #659696 is a reply to message #659693]

Fri, 27 January 2017 09:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

jesuisantony wrote on Fri, 27 January 2017 07:28

The DB is growing at a faster rate. We found that few of the tables are occupying more size in the tablespace than the actual data. There is a request to get this space issue addressed.

Two separate activities must be happening for table size to be increasing.
1) INSERT adds new rows into table which consumes space
2) DELETE removes rows from table which frees space

What is the rate that data is being added by INSERT?
What is the rate that data is being removed by DELETE?

Why are you unwilling to allow Oracle to reuse the free space?

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #659698 is a reply to message #659691]

Fri, 27 January 2017 10:19

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

We are still waiting for you feedback in your previous topics:
http://www.orafaq.com/forum/m/658715/#msg_658715
http://www.orafaq.com/forum/m/657947/#msg_657947
http://www.orafaq.com/forum/m/652507/#msg_652507
http://www.orafaq.com/forum/m/651319/#msg_651319

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #659700 is a reply to message #659696]

Fri, 27 January 2017 11:56

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

We can see that atleast the data grows by 10g in a week. Not sure about how to find the speed of data insert. The table which I have been talking about has 100G of wasted space and has actual dual for around 20G only. We do not want it to grow further. As well, instead of allowing the files to grow further we just want to reclaim the space both from the OS/DB layer.

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #659705 is a reply to message #659698]

Fri, 27 January 2017 12:24

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Michel - I have responded to those topics. Sorry that I totally missed them.

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #659706 is a reply to message #659700]

Fri, 27 January 2017 12:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

jesuisantony wrote on Fri, 27 January 2017 09:56

We can see that atleast the data grows by 10g in a week. Not sure about how to find the speed of data insert. The table which I have been talking about has 100G of wasted space and has actual dual for around 20G only. We do not want it to grow further. As well, instead of allowing the files to grow further we just want to reclaim the space both from the OS/DB layer.

Is the new data being loaded via sqlldr DIRECT=TRUE or INSERT /*+ APPEND */ ?

Each of the above will load new data above HWM.
If you do as above the table will ALWAYS continue to grow.

Do periodically DELETE rows?

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #659711 is a reply to message #659706]

Fri, 27 January 2017 15:20

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

We are not using sqlldr here. This is a table which holds logging information of the business transactions. One month old data is purged through the application.

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #659712 is a reply to message #659711]

Fri, 27 January 2017 15:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Is the new data being loaded via INSERT /*+ APPEND */ ?

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #659824 is a reply to message #659712]

Tue, 31 January 2017 07:04

ibDBA
Messages: 2
Registered: January 2017
Location: Kolkata

Junior Member

If your application allows downtime go for datapump or exp/imp. If you can afford to have CTAS, you can go for redef which is CTAS internally while you can update table but make sure of datatype limitation. If you analyze the table to have issue around the HWM, go for shrink option.
However, each approach should be driven by Business need.

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #660027 is a reply to message #659824]

Tue, 07 February 2017 13:11

Bill B
Messages: 1971
Registered: December 2004

Senior Member

If you are purging an entire month at a time then you should be using a partitioned table. Then to purge, all you would do it to drop the specified months partition and your done. No wasted space and VERY quick to do the drop.

[Updated on: Tue, 07 February 2017 13:11]

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #660401 is a reply to message #660027]

Wed, 15 February 2017 14:20

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Thanks everyone. I guess, I would go with the SHRINK option. Was not sure about the time that will be required, if at all an application outage is needed. Just trying to avoid the dependency with the application team.

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #660403 is a reply to message #660401]

Wed, 15 February 2017 14:24

Bill B
Messages: 1971
Registered: December 2004

Senior Member

I would again suggest the partitioned table. NO application changes are needed. Just a one time build and populate the table and then it is done. oracle handles everything else including automatically creating the new partition when the month changes. Shrink will not really work, it only pulls down to the high water mark and your old deleted records will probably not be at the top of your table.

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665358 is a reply to message #660403]

Fri, 01 September 2017 05:52

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

All - Following up on this after several discussions with the internal teams. The DB which has this bigger table is critical one and the business would not want to go for any outage more than couple of hours.

Online Reorg using

Code: [Select all] [Show/ hide]

DBMS_REDEFINITION

was the only option I was left with. And I tried the reorg in one of the non-prod environments with a smaller table of size 4GB as per DBA_SEGMENTS. The size is 2GB as per DBA_TABLES

Code: [Select all] [Show/ hide]

(num_rows*avg_row_len)

. But the blocks column in both the tables shows the same number (572408) which is close to 4GB. The result is same after the online REORG.

The tablespace uses ASSM and the table's PCTFREE value is 10. The DB block size is 8192. Considering these factors, the segment size can just be above 2GB. As well, Quote:

this table contains only numbers and varchar columns and no LOB related ones

. I am not sure why the segment size is almost double than that of

Code: [Select all] [Show/ hide]

(num_rows*avg_row_len)

?

How can I find where this 2GB of space is used by this object?

Regards,
Antony

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665360 is a reply to message #665358]

Fri, 01 September 2017 06:27

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

jesuisantony wrote on Fri, 01 September 2017 05:52

All - Following up on this after several discussions with the internal teams. The DB which has this bigger table is critical one and the business would not want to go for any outage more than couple of hours.

Online Reorg using
Code: [Select all] [Show/ hide]
DBMS_REDEFINITION
was the only option I was left with.

The only option? What about partitioning, which was mentioned multiple times?

Quote:

And I tried the reorg in one of the non-prod environments with a smaller table of size 4GB as per DBA_SEGMENTS. The size is 2GB as per DBA_TABLES
Code: [Select all] [Show/ hide]
(num_rows*avg_row_len)
. But the blocks column in both the tables shows the same number (572408) which is close to 4GB. The result is same after the online REORG.

The tablespace uses ASSM and the table's PCTFREE value is 10. The DB block size is 8192. Considering these factors, the segment size can just be above 2GB. As well, Quote:
this table contains only numbers and varchar columns and no LOB related ones
. I am not sure why the segment size is almost double than that of
Code: [Select all] [Show/ hide]
(num_rows*avg_row_len)
?

How can I find where this 2GB of space is used by this object?

Regards,
Antony

Now you are sweating over a lousy 2gb? That's not even a rounding error in space calculation. You've probably got more space than that on your smartphone. You can get more space than that on a cheap thumb drive given away as marketing swag.

Show us the exact queries that give you those 2gb and 4gb numbers. I dare say you are comparing apples and oranges.

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665361 is a reply to message #665360]

Fri, 01 September 2017 06:39

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Find the query that I am using on the environment for testing. Partition is different strategy but I need to discuss with the application teams and their vendors. It is not a easy thing to convince the business, I don't see even a single partitioned table on the database.

The testing is done on a smaller table as I mentioned before, the actual production table has close to 40G of data but the segment size is nearing 200G. I would like to understand the difference so that I can ignore any negotiable difference in the size when the activity is performed in Production.

Code: [Select all] [Show/ hide]

select bytes/1024/1024/1024 from dba_segments where owner='TEST' and segment_name='MESSAGES';
select (num_rows*avg_row_len)/1024/1024/1024 from dba_tables where owner='TEST' and table_name='MESSAGES';

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665362 is a reply to message #665361]

Fri, 01 September 2017 07:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

When was the last time that statics collected for MESSAGES table?

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665363 is a reply to message #665358]

Fri, 01 September 2017 07:54

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Quote:

All - Following up on this after several discussions with the internal teams. The DB which has this bigger table is critical one and the business would not want to go for any outage more than couple of hours.

Online Reorg using
DBMS_REDEFINITION
was the only option I was left with.

Wrong! You would be better off using ALTER TABLE SHRINK SPACE. Much faster, and does not require any extra disc space (unlike dbms_redefinition) No down time. You could follow up with an online rebuild of the indexes.

Why didn't you do this back in January, when I first suggested it?

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665366 is a reply to message #665363]

Fri, 01 September 2017 08:22

Bill B
Messages: 1971
Registered: December 2004

Senior Member

I agree with John Watson. You can do a shrink while your users are using the messages table. This allows you to do the shrink with NO downtime. Do the following

First find out if Row Movement is turned on in the messages table by using the following query

select row_movement from user_tables where TABLE_NAME = 'MESSAGES';

If it is DISABLED then do all three commands. If it is ENABLED then ONLY do the shrink command

Code: [Select all] [Show/ hide]

ALTER TABLE MESSAGES ENABLE ROW MOVEMENT;

ALTER TABLE MESSAGES SHRINK SPACE;

ALTER TABLE MESSAGES DISABLE ROW MOVEMENT;

The one downside of turning ROW MOVEMENT on is that the ROWID's of the table will change. However you should NEVER store the ROWID's anyway.

Make sure to test this on your test server first.

[Updated on: Fri, 01 September 2017 08:26]

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665372 is a reply to message #665366]

Fri, 01 September 2017 09:25

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Blackswan - The "MESSAGES" table has the stats gathered on a daily basis.
Bill/Watson - I did try the SHRINK option. I would be happy if it works. But the SHRINK failed with the below error.

Code: [Select all] [Show/ hide]

ORA-10631:SHRINK clause should not be specified for this object

The table has an associated function-based index. Hence, SHRINK option is not supported.

And right now, I am good to do the REORG. My main concern is that the segment size is 2 times than the actual data even after the REORG. I am keen to understand where is this data used. Also, I would like to keep you informed that the unused column was also dropped following the completion of REORG.

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665374 is a reply to message #665372]

Fri, 01 September 2017 09:35

Bill B
Messages: 1971
Registered: December 2004

Senior Member

that is not hard to get around. Do the following first

drop index my_function_index ONLINE;

run the shrink
create index my_function_index on my_table(...) online;

The online clause says make it while people are using the table.

Thats it.

[Updated on: Fri, 01 September 2017 09:50]

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665376 is a reply to message #665372]

Fri, 01 September 2017 10:33

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Tried it. I was able to drop index online, shrink but create index online failed with the below error:

Code: [Select all] [Show/ hide]

ORA-01450: maximum key length (string) exceeded

I was able to create index without ONLINE option though.

The size displayed in DBA_SEGMENTS and the size from DBA_TABLES (num_rows*avg_row_len) still has the difference.

Code: [Select all] [Show/ hide]

select bytes/1024/1024/1024 from dba_segments where owner='TEST' and segment_name='MESSAGES'; (size:4.3GB)
select (num_rows*avg_row_len)/1024/1024/1024 from dba_tables where owner='TEST' and table_name='MESSAGES'; (Size: 2.3GB)
Note: Segment size is 2 times of the actual data.

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665377 is a reply to message #665376]

Fri, 01 September 2017 10:42

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Interesting. How large is your functional index. This is a known bug (note 236329.1) the text from the note is below. This will not happen unless your functional index is very large

fact: Oracle Server - Enterprise Edition 8
fact: Oracle Server - Enterprise Edition 9
fact: Oracle Server - Enterprise Edition 10
symptom: Command ALTER INDEX REBUILD ONLINE fails
symptom: ORA-01450: maximum key length (%s) exceeded
symptom: The total length of all indexed columns is much less than the
number specified in ORA-01450 error
symptom: Command ALTER INDEX REBUILD works fine
cause: This is caused by issue Bug:2525767. The online rebuild of the index
creates a journal table and index. This internal journal IOT table contains
more columns in its index. Their total length is greater than number reported
in ORA-01450 error message. This is a feature of online rebuild.
Maximum key length is calculated with respect to the database block size. It
means that current value of the initialization parameter db_block_size is not
large enough so that the internal journal IOT can be created without errors.

fix:

Rebuild the index without ONLINE clause. There is no way to rebuild this index
ONLINE without the change of the initialization parameter db_block_size.
OR
Rebuild the database with greater value of the initialization parameter
db_block_size according to Note:136158.1:
ORA-01450 and Maximum Key Length - How it is Calculated.

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665378 is a reply to message #665377]

Fri, 01 September 2017 10:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post CREATE TABLE MESSAGES statement

what is INSERT rate for new rows going into MESSAGES table?
what is DELETE rate for old rows being removed from MESSAGES table?

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665380 is a reply to message #659691]

Fri, 01 September 2017 12:21

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Bill - The current index which we are trying to recreate without "ONLINE" option is 15MB.

BlackSwan - The DDL is given below. Since this is test environment no deletes happen at the moment. 60 rows/minute gets inserted.

Code: [Select all] [Show/ hide]

CREATE TABLE "MESSAGE" 
   (	"REGN" NUMBER(10,0) NOT NULL ENABLE, 
	"ZONE_NUMBER" NUMBER(8,0) NOT NULL ENABLE, 
	"MSG_TEXT" VARCHAR2(4000 CHAR) NOT NULL ENABLE, 
	"SER_ID" NUMBER(10,0)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "APPS_TBS"

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665381 is a reply to message #665380]

Fri, 01 September 2017 12:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

We have met the enemy & they is US!

>PCTUSED 40
so how full will any block be, before Oracle places next INSERT into new block?

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665382 is a reply to message #665381]

Fri, 01 September 2017 13:25

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Blackswan - I will agree with you. I got that value from the DDL script. However, the tablespace uses ASSM and I assume that ASSM would use PCTUSED value of 40 by default (As per the documentation this value seems to be ignored though). In this case, I have a fair reason why DBA_SEGMENTS was showing higher utilization compared to the data size.

Let me know if there is anything wrong with my assumption.

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665434 is a reply to message #665382]

Tue, 05 September 2017 10:12

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Can you please calrify my question in my earlier post please?

When I used the DBMS_METADATA.GET_DDL for the table, it showed PCTUSED as 40. Does this mean, ASSM automatically forces 40 PCTUSED value? Still I see that PCTFREE is 10. Why would not Oracle make an attempt to insert rows until 90%?

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665437 is a reply to message #665434]

Tue, 05 September 2017 11:25

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

PCTUSED is ignored for an ASSM tablespace. If you want to see where the space is, you can use dbms_space.space_usage to inspect the bitmaps and see how many blocks are in each of the 25% full ranges.

It has been known for the bitmaps to get corrupted which can result in wasted space, perhaps that is what has happened. You can try to repair them with dbms_space_admin.tblespace_fix_bitmaps.

Report message to a moderator

Re: Table Reorg / DBMS_REDEFINITION [message #665466 is a reply to message #665437]

Wed, 06 September 2017 10:21

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

DBMS_SPACE.SPACE_USAGE shows that 95% of the data size is in FULL BLOCKS section. Other 25% range size is ignorable As well, the TAB$ shows PCTUSED is 40% while we are using ASSM though.

I am doing a comparison between

Code: [Select all] [Show/ hide]

distinct DBMS_ROWID.ROWID_BLOCK_NUMBER

and

Code: [Select all] [Show/ hide]

DBA_SEGMENTS

. These sizes are closer to each other.

This makes me conclude that Quote:

NUM_ROWS*AVG_ROW_LEN from DBA_TABLES

is not an ideal information to compare the segment size.

I don't think there is any corruption with the BITMAPS as the table was newly created as part of the REORG.

Report message to a moderator

Previous Topic:	Only one temp file is used
Next Topic:	Is there a way to run Oracle Universal Installer in english without resetting the system locale

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 10:19:09 CDT 2025