Nova 10g opcija - SEGMENT SHRINK

Note:242090.1

Nova 10g opcija - SEGMENT SHRINK.. 1

Objašnjenje. 1

Uslov - Mandatory. 1

Komande. 1

Primer 2

Kreiraj Tabelu - Create TABLE. 2

Napuni podatke - Load some data. 2

Kreiraj Index – Create Index. 3

Veličina zauzetog prostora u bazi - Check in user_segments. 3

Obriši podatke - Delete Rows. 3

Veličina zauzetog prostora u bazi - Check in user_segments. 3

Defragmentiraj MY_NEW_TABLE - Shrink MY_NEW_TABLE. 3

Veličina zauzetog prostora u bazi - Check in user_segments. 4

Objašnjenje

Opcija da se segment komprimuje, tj. DBA je u mogućnosti da na bolji način iskoristi postojeći prostor.

Navedeni proces takođe pomaže poboljšanju performansi.

U DB operacijama hwm se pomera samo naviše. Izuzetak je npr. komanda truncate. (bez REUSE STORAGE opcije).

Ako postoji dosta praznog prostora ispod HVM DBA će se možda odlučiti da navedeni prostor defragmentira (oslobodi praznina).

Uslov - Mandatory

- Init.ora parameter 'Compatible' >=10.0

- Segment must be a AUTO Segment Space Managed Tablespace.

Komande

Enable row movement for the table first.

SQL> ALTER TABLE scott.MY_NEW_TABLE ENABLE ROW MOVEMENT;

1. Shrink table but don't want to shrink HWM (High Water Mark).

SQL> ALTER TABLE scott.MY_NEW_TABLE SHRINK SPACE COMPACT;

2. Shrink table and HWM too.

SQL> ALTER TABLE scott.MY_NEW_TABLE SHRINK SPACE;

3. Shrink table and all dependent index too.

SQL> ALTER TABLE scott.MY_NEW_TABLE SHRINK SPACE CASCADE;

4. Shrink table under Mview - Mview Materialized View (Snapshot)

SQL> ALTER TABLE <table name> SHRINK SPACE;

5. Shrink Index only.

SQL> ALTER INDEX <index nam> SHRINK SPACE;

Primer

SQL> connect scott/tiger@glob

Connected.

Kreiraj Tabelu - Create TABLE

SQL> create table my_new_table (col1 varchar2(21), col2 number);

Table created.

SQL> alter table "SCOTT"."MY_NEW_TABLE" enable row movement;

Table altered.

Napuni podatke - Load some data

SQL> begin

for i in 1 .. 10000 loop

insert into my_new_table values

(dbms_random.string('X', 20),i);

end loop;

end;

/

PL/SQL procedure successfully completed.

Kreiraj Index – Create Index

SQL> create index ix_on_my_new_table on my_new_table(col2)

/

Index created.

Veličina zauzetog prostora u bazi - Check in user_segments

SQL> select substr(segment_name,1,20) segment, bytes / 1024 "Size [KB]" from user_segments

where segment_name in ('MY_NEW_TABLE', 'IX_ON_MY_NEW_TABLE')

SQL> /

SEGMENT Size [KB]

------------------------------------------ -----------------

MY_NEW_TABLE 384

IX_ON_MY_NEW_TABLE 256

Obriši podatke - Delete Rows

SQL> DELETE FROM MY_NEW_TABLE;

10000 rows deleted.

Veličina zauzetog prostora u bazi - Check in user_segments

SQL> select substr(segment_name,1,20) segment, bytes / 1024 "Size [KB]" from user_segments

where segment_name in ('MY_NEW_TABLE', 'IX_ON_MY_NEW_TABLE')

/

SEGMENT Size [KB]

------------------------------------------ -----------------

MY_NEW_TABLE 384

IX_ON_MY_NEW_TABLE 256

Defragmentiraj MY_NEW_TABLE - Shrink MY_NEW_TABLE

SQL> ALTER TABLE scott.MY_NEW_TABLE SHRINK SPACE CASCADE;

Table altered.

Veličina zauzetog prostora u bazi - Check in user_segments

SQL> select substr(segment_name,1,20) segment, bytes / 1024 "Size [KB]" from user_segments

where segment_name in ('MY_NEW_TABLE', 'IX_ON_MY_NEW_TABLE');

SEGMENT Size [KB]

------------------------------------------ -----------------

MY_NEW_TABLE 64

IX_ON_MY_NEW_TABLE 64