create index etl_load_date_func_idx on cso.rma_detail_fact (trunc(etl_load_date)) local tablespace CSO_I_A01 UNUSABLE;
create index etl_load_date_serial_func_idx on cso.rma_serial_fact (trunc(etl_load_date)) local tablespace CSO_I_A01 UNUSABLE;
create index etl_load_date_holds_func_idx on cso.rma_holds_fact (trunc(etl_load_date)) local tablespace CSO_I_A01 UNUSABLE;
create index etl_load_date_tran_func_idx on cso.rma_transaction_fact (trunc(etl_load_date)) local tablespace CSO_I_A01 UNUSABLE;
create index insert_date_func_idx on dim.customer_site_dim (trunc(insert_date)) tablespace DIM_I_A01 unusable;
select INDEX_OWNER, INDEX_NAME, PARTITION_NAME, TABLESPACE_NAME, STATUS,
'alter index '||INDEX_OWNER||'.'||INDEX_NAME||' rebuild partition '||PARTITION_NAME||' online PARALLEL 16;'
from dba_ind_partitions
where INDEX_OWNER = 'CSO' and INDEX_NAME in ('ETL_LOAD_DATE_FUNC_IDX','ETL_LOAD_DATE_SERIAL_FUNC_IDX','ETL_LOAD_DATE_HOLDS_FUNC_IDX','ETL_LOAD_DATE_TRAN_FUNC_IDX')
and status = 'UNUSABLE'
select STATUS,'ALTER INDEX ' || index_owner || '.' || index_name || ' REBUILD SUBPARTITION ' ||
subpartition_name || ' online;'
from dba_ind_subpartitions
where index_owner = 'CSO'
and index_name = 'ETL_LOAD_DATE_SERIAL_FUNC_IDX';
select index_name,'alter index ' || owner || '.' || index_name || ' rebuild online; ' ,a.*
from dba_indexes a
where OWNER in ('CSO','DIM')
and INDEX_NAME in ('ETL_LOAD_DATE_HOLDS_FUNC_IDX','INSERT_DATE_FUNC_IDX')
and status = 'UNUSABLE'
select TABLE_OWNER, PARTITION_NAME from DBA_TAB_PARTITIONS where TABLE_NAME='MAR_HA_FACT' order by PARTITION_NAME desc
select * from DELETE_PARAM where TABLE_NAME in ('<tbname>');
INSERT INTO ITDBA.DELETE_PARAM VALUES
('HSA', 'MAR_HA_FACT','YES','TEST_DATE','WEEKLY',39,4,1,'FACT_DP_A02','YES',NULL,NULL,NULL,NULL, NULL, NULL);
INSERT INTO itdba.event_log ( owner, table_name, operation, status, last_updated )
SELECT distinct owner, a1.table_name, 'FLIP', 'CLEAR', sysdate
FROM dba_tables a1, dba_tab_partitions b1
WHERE OWNER = 'HSA'
AND ( a1.table_name = 'MAR_HA_FACT')
AND a1.table_name = b1.table_name
AND a1.owner = b1.table_owner
AND NOT EXISTS (
SELECT null
FROM itdba.event_log b1
WHERE a1.owner = b1.owner
AND A1.TABLE_NAME = B1.TABLE_NAME);
select spid, sid, a.serial#, b.username
from v$session a, v$process b
where a.serial# = b.serial# and a.sid = '1561'
select spid, sid, a.serial#, b.username from v$session a, v$process b where a.paddr=b.addr and a.serial#=32229;
/*-- check event log status --*/
select OWNER, TABLE_NAME, OPERATION, STATUS, TO_CHAR(LAST_UPDATED, 'DD-MON-YY HH24:MM:SS') LAST_UPDATED from EVENT_LOG where STATUS != 'CLEAR'
and table_name in ('TEST_TIME_BY_STATE_FACT');
select to_char(sysdate, 'DD-MON-YY HH24:MM:SS') from dual;
/*-- check delete param status --*/
select * from DELETE_PARAM where TABLE_NAME in ('DRIVE_PARM_COLLECTIONS_LKP');
/*-- check latest partition --*/
select max(PARTITION_NAME) from DBA_TAB_PARTITIONS where TABLE_NAME='DRIVE_PARM_COLLECTIONS_LKP';
select TABLE_OWNER, PARTITION_NAME from DBA_TAB_PARTITIONS where TABLE_NAME='P177_GAIN_DATA_FACT' order by PARTITION_NAME desc
-- Date E.g P20110327
/*--Extract DDL--*/
ALTER TABLE DRIVE.DRIVE_PARM_COLLECTIONS_LKP add
PARTITION "P20110711" VALUES LESS THAN (20110711000000000000000000000000) TABLESPACE "DRIVE_FACT_DP_A01";
/*--Update event log--*/
update event_log set status = 'CLEAR' where status != 'CLEAR' and table_name in ('DRIVE_PARM_COLLECTIONS_LKP');
commit;