how can i do reorg on table without increaseing database space

Dear sybase experts,

I have a big table where i am trying to run the reorg rebuild but database space is not much and hence its failing. Table size is approx 30GB and space left in database is also 30GB. Is there a way without increasing database space i can complete reorg rebuild.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

learning_sybaseAuthor Commented:
one day passed , no reply ?????
slightwv (䄆 Netminder) Commented:
I've been asked to see if I can help you find a solution since no other Expert has jumped in.

I've never used Sybase so I'm not sure how other database products will related to this question.  I'm an Oracle person.

What are you hoping the reorg to accomplish?

From a quick Google I see there are several types of reorgs in Sybase.  I'm guessing you are wanting to do rebuild?

If so, I don't see how you can to a full rebuild in place without enough space to hold the rebuilt table while the reorg is taking place.

I would look at exporting/dumping the table, dropping it and import the data back in.  In Oracle an export/import can consolidate the data blocks which is what I believe you are trying to do.
Joe WoodhousePrincipal ConsultantCommented:
Sometimes reorg rebuild is able to "rebuild in place" which does not need extra space. This depends on a lot of factors - what locking scheme is on the table; how many indexes; how much fragmentation exists on the table and the nature of that fragmentation.

And of course your table may not be fragmented at all in which case reorg rebuild will do nothing for you. So first things first: can you post the results of running

exec sp_spaceused [table], 1

Open in new window

and also the header (table-level information, we don't need any column-level information) of running "optdiag" on the table? That's an external tool you run from the OS, like isql and bcp.

We should also have your exact ASE version please as that will also limit what options are available to you.

But it's probably bad news - almost any defragmenter tool (database tables, files, clutter on the living room table) needs some free space in order to move things around in. If you simply have very little free space then you will be very limited in what you can do.

You might try first running the "lesser" versions of reorg, to try to free up some space before running a full reorg rebuild:

reorg compact_space [table]
reorg defrag [table]                        -- if you are using ASE 16.0+
reorg rebuild [table], [index]        -- one index at a time, start with the smallest

Lastly remember it's been possible for a while now in ASE to shrink a database down again, so you could in principle add some database space, reorg, then remove the space you added.

If your ASE license permits, it might also then be time for you to look into ASE compression on this table and possibly also its indexes. Page compression is better than row compression... but it only applies to new rows. To compress existing data you need... reorg rebuild. :)

Serious question: why not add more database space?

I have ever been in a situation when there were good reasons I couldn't permanently add more database space, and this was before it was possible to shrink databases. I used bcp to copy the data out to file, truncated the table, then used bcp to copy it back in again. Of course this still needs disk space at the file level, and it's a complete outage for the duration end to end, but it did what I needed.

PS: Re. "One day passed, no reply" - we're volunteers doing this for free, and may not have set notifications for when new questions are asked.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
learning_sybaseAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.