Online Segment Shrink for Tables : Free Unused Space

Подписаться 15 тыс.

Просмотров 10 тыс.

50% 1

In this video we'll give a demonstration of how to shrink tables that contain a lot of empty space.
ALTER TABLE ... SHRINK SPACE Command : Online Segment Shrink for Tables, LOBs and IOTs
oracle-base.co...
Online Statistics Gathering for Bulk Loads in Oracle Database 12c Release 1 (12.1)
oracle-base.co...
Reclaiming Unused Space in Datafiles
oracle-base.co...
Website: oracle-base.com
Blog: oracle-base.co...
Twitter: / oraclebase
Cameo by Kellyn Pot'Vin-Gorman :
Twitter : / dbakevlar
Blog: dbakevlar.com/
Cameo appearances are for fun, not an endorsement of the content of this video.
All trademarks, product names and logos are the property of their respective owners.

Опубликовано:

29 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 13

@AlomLt 7 месяцев назад

If I have a table with LONG columns, how does it work? Since it shows me this error: ORA-00997: illegal use of LONG datatype

@oracle-base 7 месяцев назад

As you've discovered, it doesn't. Oracle deprecated LONG columns a very, very long time ago. As a result, most features added since that deprecation do not support LONG columns. You should migrate from LONG to LOB (CLOB or BLOB).

@mikhaill244 4 года назад

Hi Tim. When does it make sense to use the COMPACT option? Only in the case of preventing parsed SQL statements from being invalidated?

@oracle-base 4 года назад

The docs say it is useful when long running statements may try to access blocks that have been cleaned up after the query was initiated. So you do the work, but only deallocate the space after the fact. If you don't know how long a shrink will take, which you don't, you may not want it completing at some random time. It's a judgement call. I'm a bit of a cowboy, so I would probably run the SHRINK SPACE. I'm sure my boss would prefer me to use the COMPACT, and run the SHRINK SPACE to finalise the stuff at a specific known time. :)

@mikhaill244 4 года назад

Hi Tim. Thanks for your reply. >>The docs say it is useful when long running statements may try to access blocks that have been cleaned up after the query was initiated. But why long-running statement would access the real blocks if it read blocks from the UNDO segment?

@oracle-base 4 года назад

@@mikhaill244 No idea. I assume there must be some underlying reason. The docs are quite vague on it, and I don't have any other info than the docs. As I mentioned before, don;t use the COMPACT option. I also don't use the CASCADE option either. :)

@mikhaill244 4 года назад

Thanks, Tim. Got it. I just wanted to know the correct way to shrink the table, with or without the COMPACT option. In the Advanced PLSQL student guide it was written: ALTER TABLE t1 ENABLE ROW MOVEMENT; ALTER TABLE t1 SHRINK SPACE COMPACT; ALTER TABLE t1 SHRINK SPACE; But in the Performance Tuning student guide it was written: ALTER TABLE t1 ENABLE ROW MOVEMENT; ALTER TABLE t1 SHRINK SPACE CASCADE; So I decided to handle this with your help.)

@TonyBijoux 3 года назад

Could I interrupt the SHRINK command ? I have a table by 1.3 Tb, I do not have any other solution than SHRINK, after 40 hours the SHRINK COMPACT crushed because of UNDO space. May I run the SHRINK table and to interrupt it ? Thank you!

@oracle-base 3 года назад

It should be fine to rerun it. It's just moving rows.

@TonyBijoux 3 года назад

Can be parallelized the command ALTER TABLE SHRINK SPACE ? Thank you!

@oracle-base 3 года назад

I don't believe so. I can't find any syntax to support that.