Thứ Tư, 14 tháng 12, 2022

[VIP5] Quản lý Index Partition từ A-Z trong Oracle Database

1.CHECK

-- Check index partition unusable cả DB

select * from dba_ind_partitions where status!='USABLE' and index_owner not in ('SYS','SYSTEM') order by index_owner, index_name;

 

-- Check index partition unusable like 20131231 cả DB

select a.* from DBA_PART_INDEXES a, DBA_TAB_PARTITIONS b where a.owner=B.TABLE_OWNER and a.table_name=B.TABLE_NAME and a.owner not like 'SYS%' and b.partition_name like '%20131231';

select * from dba_ind_partitions where status!='USABLE'

 

-- Nếu có index partition unusable partititon của 1 số bảng

select * from dba_ind_partitions where

status!='USABLE'

and (index_owner,index_name) in

(select owner,index_name from dba_indexes where owner='APP_OWNER'

and table_name in (‘TAB1’)

);

 

select * from dba_ind_partitions where

status!='USABLE'

and (index_owner,index_name) in

(select owner,index_name from dba_indexes where owner='APP_OWNER'

and table_name in (TAB1’)

);

 

Nếu có fix:

alter index APP_OWNER. TAB1’_UK rebuild partition data20210501 parallel 8 nologging online;

alter index APP_OWNER. TAB1’_UK noparallel;

 

Hoặc dùng script:

select 'alter index ' || index_owner ||'.' || index_name || rebuild partition ' || partition_name || ' parallel 8 nologging online;'

from dba_ind_partitions where

status!='USABLE'

and (index_owner,index_name) in

(select owner,index_name from dba_indexes where owner='APP_OWNER'

and table_name in (TAB1’)

);

2. CREATE

-- Tạo index

CREATE INDEX APP_OWNER. TAB1_I1 ON MC_OWNER. TAB1_LOG

(SUB_ID )

tablesapce INDX

NOLOGGING

NOPARALLEL  local online;

 

--+ Rebuild index

select 'alter index mc_owner.kmtd4_log_i1 rebuild partition ' || partition_Name || ' tablespace INDX2016 parallel 8 nologging online;' from dba_ind_partitions where index_name like ' TAB1_I1';

 

--+ Set nologging noparallel

alter index APP_OWNER. TAB1_I1 nologging noparellel;

-- Tạo index lớn

 

CREATE INDEX APP_OWNER. TAB1_I4 ON  APP_OWNER. TAB1

(col1, col2)

LOCAL PARALLEL 4 UNUSABLE ONLINE;

 

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name

|| ' tablespace INDX' || substr(partition_name,5,4)||' nologging parallel 8 online;', partition_name, tablespace_name from dba_ind_partitions

where index_name in

(select index_name from dba_indexes where

table_owner='APP_OWNER' and table_name=' TAB1 and index_name=' TAB1_I4')

Order by partition_name desc;

-- Tạo index lớn, core

 

Bước 1:

 

--9s

create index app_owner.TAB1_I1 on app_owner.TAB1 (col1, col2) tablespace INDX local unusable ;

 

Bước 2: Rebuild index  partition unusable

 

select  'alter index ' || index_owner || '.' || index_name || ' rebuild partition ' || partition_name || ' tablespace ' || substr ('INDX' || substr(partition_name,5,9),1,10) || ' nologging parallel 8 online;'

from dba_ind_partitions where status!='USABLE'

and index_owner=APP_OWNER and index_name=TAB1_I1'

and partition_name like '%2021%'

order by 1 desc;

 

create tablespace indx202101 datafile '+DATA' size 1G autoextend on next 100m;

create tablespace indx202102 datafile '+DATA' size 1G autoextend on next 100m;

create tablespace indx202103 datafile '+DATA' size 1G autoextend on next 100m;

create tablespace indx202104 datafile '+DATA' size 1G autoextend on next 100m;

create tablespace indx202105 datafile '+DATA' size 1G autoextend on next 100m;

create tablespace indx202106 datafile '+DATA' size 1G autoextend on next 100m;

create tablespace indx202107 datafile '+DATA' size 1G autoextend on next 100m;

create tablespace indx202108 datafile '+DATA' size 1G autoextend on next 100m;

create tablespace indx202109 datafile '+DATA' size 1G autoextend on next 100m;

create tablespace indx202110 datafile '+DATA' size 1G autoextend on next 100m;

create tablespace indx202111 datafile '+DATA' size 1G autoextend on next 100m;

create tablespace indx202112 datafile '+DATA' size 1G autoextend on next 100m;

 

create tablespace indx2020 datafile '+DATA' size 1G autoextend on next 100m;

alter tablespace indx2020 add datafile '+DATA' size 1G autoextend on next 100m;

 

create tablespace indx2019 datafile '+DATA' size 1G autoextend on next 100m;

 

create tablespace indx2018 datafile '+DATA' size 1G autoextend on next 100m;

 

create tablespace indx2017 datafile '+DATA' size 1G autoextend on next 100m;

 

create tablespace indx2016 datafile '+DATA' size 1G autoextend on next 100m;

 

create tablespace indx2015 datafile '+DATA' size 1G autoextend on next 100m;

 

create tablespace indx2014 datafile '+DATA' size 1G autoextend on next 100m;

 

 

alter tablespace indx2020 add datafile '+DATA' size 1G autoextend on next 100m;

alter tablespace indx2020 add datafile '+DATA' size 1G autoextend on next 100m;

alter tablespace indx2020 add datafile '+DATA' size 1G autoextend on next 100m;

 

alter tablespace indx2019 add datafile '+DATA' size 1G autoextend on next 100m;

alter tablespace indx2019 add datafile '+DATA' size 1G autoextend on next 100m;

alter tablespace indx2019 add datafile '+DATA' size 1G autoextend on next 100m;

 

alter tablespace indx2018 add datafile '+DATA2' size 1G autoextend on next 100m;

alter tablespace indx2018 add datafile '+DATA2' size 1G autoextend on next 100m;

alter tablespace indx2018 add datafile '+DATA2' size 1G autoextend on next 100m;

 

alter tablespace indx2017 add datafile '+DATA2' size 1G autoextend on next 100m;

alter tablespace indx2017 add datafile '+DATA2' size 1G autoextend on next 100m;

alter tablespace indx2017 add datafile '+DATA2' size 1G autoextend on next 100m;

 

alter tablespace indx2016 add datafile '+DATA2' size 1G autoextend on next 100m;

alter tablespace indx2016 add datafile '+DATA2' size 1G autoextend on next 100m;

alter tablespace indx2016 add datafile '+DATA2' size 1G autoextend on next 100m;

 

alter tablespace indx2015 add datafile '+DATA2' size 1G autoextend on next 100m;

alter tablespace indx2015 add datafile '+DATA2' size 1G autoextend on next 100m;

alter tablespace indx2015 add datafile '+DATA2' size 1G autoextend on next 100m;

 

alter tablespace indx2014 add datafile '+DATA2' size 1G autoextend on next 100m;

alter tablespace indx2014 add datafile '+DATA2' size 1G autoextend on next 100m;

alter tablespace indx2014 add datafile '+DATA2' size 1G autoextend on next 100m;

 

 

select  'alter index ' || index_owner || '.' || index_name || ' rebuild partition ' || partition_name || ' tablespace ' || substr ('INDX' || substr(partition_name,5,9),1,8) || ' nologging parallel 8 online;'

from dba_ind_partitions where status!='USABLE'

and index_owner= ‘APP_OWNER’ and index_name='TAB1_I1'

and partition_name  like '%2020%'

order by 1 desc;

 

select  'alter index ' || index_owner || '.' || index_name || ' rebuild partition ' || partition_name || ' tablespace ' || substr ('INDX' || substr(partition_name,5,9),1,8) || ' nologging parallel 8 online;'

from dba_ind_partitions where status!='USABLE'

and index_owner= ‘APP_OWNER’ and index_name= ‘TAB1_I1'

and partition_name  like '%2019%'

order by 1 desc;

 

select  'alter index ' || index_owner || '.' || index_name || ' rebuild partition ' || partition_name || ' tablespace ' || substr ('INDX' || substr(partition_name,5,9),1,8) || ' nologging parallel 8 online;'

from dba_ind_partitions where status!='USABLE'

and index_owner= ‘APP_OWNER’ and index_name= ‘TAB1_I1'

and partition_name  not like '%2021%'

and partition_name  not like '%2020%'

and partition_name  not like '%2019%'

order by 1 desc;

Tạo index bảng core

Khi tạo lại index partition bảng core cần tạo unusable sau đó rebuild từng partition từ mới đến cũ vì thường quét 1 vài ngày hoặc 1 vài tháng dữ liệu mới nhất

 

Create index customer_i1 on customer(sub_id) local unusable;

 

-- Rebuild index  partition unusable

select  'alter index ' || index_owner || '.' || index_name || ' rebuild partition ' || partition_name || ' tablespace ' || substr ('INDX' || substr(partition_name,5,9),1,10) || ' nologging parallel 8 online;'

from dba_ind_partitions where status!='USABLE'

--and index_owner='APP_OWNER' and index_name='TAB1_I1’'

order by 1 desc;

 

3.Alter

-- Rebuild index partition, non-partition sau khi move du lieu bang ONLINE

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name || ' tablespace INDX' || substr(partition_name,5,6)||' nologging parallel 8 online;', partition_name, tablespace_name

from dba_ind_partitions

where index_name in

(select index_name from dba_indexes

where table_owner='APP_OWNER' and table_name in (‘TAB1’))

and partition_name like 'DATA202010%'

union

select 'alter index '||owner||'.'||index_name||' noparallel;', null, null from dba_indexes

where table_owner='APP_OWNER' and table_name in (‘TAB1’))

order by 2, 1 desc, 3; 

 

--+ Sum Dung luong can chuyen

select owner,segment_name, round(sum(bytes)/1024/1024,2) "MB" from dba_segments

where owner='APP_OWNER' and segment_name='TAB1’

group by owner, segment_name

order by 1,2;

 

--+ Dung luong partition can chuyen

select owner,segment_name, partition_name, round(sum(bytes)/1024/1024,2) "MB" from dba_segments

where owner= ‘APP_OWNER' and segment_name='TAB1’

group by owner, segment_name, partition_name

order by 1,2,3;

 

--+ Sum Index can rebuild

select owner, segment_name, round(sum(bytes)/1024/1024,2) "MB" from dba_segments

where (owner, segment_name) in (select owner, index_name from dba_indexes where table_owner='APP_OWNER' and table_name='TAB1’)

group by owner, segment_name;

 

--+ Index partition can rebuild

select owner, segment_name,partition_name, round(sum(bytes)/1024/1024,2) "MB" from dba_segments

where (owner, segment_name) in (select owner, index_name from dba_indexes where table_owner='APP_OWNER' and table_name='TAB1’)

group by owner, segment_name, partition_name

order by 1,2,3;

 

-- Bang non-partion

select 'alter table '||owner||'.'||table_name||' move  tablespace DATA nologging parallel 8;', tablespace_name

from dba_tables

where owner='APP_OWNER' and table_name='TAB1' and partitioned='NO'--and partition_name like 'DATA201401%'

union

select 'alter index '||owner||'.'||index_name||' rebuild tablespace INDX' ||' nologging parallel 8 online;', tablespace_name from dba_indexes

where owner='APP_OWNER' and table_name='TAB1'

union

select 'alter index '||owner||'.'||index_name||' noparallel;', null from dba_indexes where

owner='APP_OWNER' and table_name='TAB1' order by 2, 1 desc; 

 

--+ Dung luong can chuyen

select owner, segment_name, round(sum(bytes)/1024/1024,2) "MB" from dba_segments

where (owner, segment_name) in (select owner, index_name from dba_indexes

where owner='APP_OWNER' and table_name='TAB1')

group by owner, segment_name;

 

--+ Size Index can chuyen

select owner, segment_name, round(sum(bytes)/1024/1024,2) "MB" from dba_segments

where (owner,segment_name) in (select owner,index_name from dba_indexes

where owner='APP_OWNER' and table_name='TAB1')

group by owner,segment_name

order by 1,2;

 

select * from dba_ind_partitions where partition_name='DATA20201016' and status !='USABLE';

-- Index Partition

select 'alter index ' || index_owner || '.' || index_name || ' rebuild partition ' || partition_name || ' nologging parallel 8 online;'

from dba_ind_partitions

where status !='USABLE' and partition_name='DATA20201016'

union

select 'alter index ' || index_owner || '.' || index_name || '  nologging noparallel;'

from dba_ind_partitions

where status !='USABLE' and partition_name='DATA20201016'

order by 1 desc;

 

 

-- Index Sub-Partition

select 'alter index ' || index_owner || '.' || index_name || ' rebuild partition ' || partition_name || ' nologging parallel 8 online;'

from dba_ind_subpartitions

where status !='USABLE'

union

select 'alter index ' || index_owner || '.' || index_name || '  nologging noparallel;'

from dba_ind_subpartitions

where status !='USABLE'

order by 1 desc;

 

 

-- Rebuild partition

ALTER INDEX idx_sales REBUILD PARTITION DATA20140401 TABLESPACE INDX2014 nologging parallel 8 online;

 

-- Rebuild index  partition unusable

select  'alter index ' || index_owner || '.' || index_name || ' rebuild partition ' || partition_name || ' tablespace ' || substr ('INDX' || substr(partition_name,5,9),1,10) || ' nologging parallel 8 online;'

from dba_ind_partitions where status!='USABLE';

 

 

-- Rebuild subpartition

alter index APP_OWNER4.TAB1_UK rebuild subpartition DATA201512_SEGMENT4 tablespace PPS_INDX  nologging parallel 8 online;

 

-- No Paralell

ALTER INDEX IND_XXX NOPARALLEL;

 

-- Unusable index

ALTER INDEX cost_ix MODIFY PARTITION p2 UNUSABLE;

 

-- Rename partition index

create index T1_index_partitioned on T1 (a)
  2  global partition by range (a)
  3  (partition p1 values less than (10),
  4  partition p2 values less than (100),
  5  partition p3 values less than (maxvalue));

 

alter index T1_index_partitioned rename partition p3 to pmax;

 

Note:

--Khi thêm partition cho bảng thì index trong bảng đó sẽ tự động có partition, nhưng cần rebuild lại index

 

 select * from dba_ind_partitions

 where index_Name like ‘TAB1%'

 --and partition_name like 'DATA201%'

 --and tablespace_name not in ('DATA','INDX');

 and tablespace_name like '%OTHER%'

 order by 1,2,4;

 

 

 select 'alter index ' || index_owner ||'.' || index_name ||' rebuild partition ' || partition_name  || ' tablespace INDX  parallel 16 nologging online;'

 from dba_ind_partitions

 where index_Name like ‘TAB1%'

 and partition_name like 'DATA201%'

 and tablespace_name not in ('DATA','INDX');

-- Script rebuild các partition index 

--+ Bước 1: Rebuild

select 'alter index ' || index_owner || '.' || index_name || ' rebuild partition ' || partition_name || ' tablespace INDX' ||substr(partition_name,5,4)||' nologging parallel 8 online;' from dba_ind_partitions where 

index_owner='APP_OWNER'

and index_name in (

'index_name_1',

'index_name_2'

)

order by partition_name desc

;

--+ Bước 2: Nologging noparallel

alter index app_owner.index_name_1 noparallel nologging;

alter index app_owner.index_name_2 noparallel nologging; 

 

=============================
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
Quản lý Index Partition từ A-Z trong Oracle Database, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
By lúc
Labels: , , , ,

ĐỌC NHIỀU
Trần Văn Bình - Oracle Database Master