Tags

, , , , , , , ,

validate index OWNER.TABLE_INDEX;
SELECT opt_cmpr_count, opt_cmpr_pctsave
FROM index_stats
WHERE name = ‘TABLE_INDEX’;
OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
————– —————-
1 10

select min(LOG_DATE), max(LOG_DATE) from OWNER.TABLE_NAME; –2011 – 2012
OWNER.TABLE_NAME

select * from SYS.DBA_TAB_MODIFICATIONS
where table_name like ‘%TABLE_NAME%’

select * from OWNER.TABLE_NAME
where LOG_DATE is null;

CREATE TABLE OWNER.TABLE_NAME_INT
(
LOG_DATE DATE,
SERVICE_NAME VARCHAR2(150 BYTE),
EXECUTION_TIME NUMBER,
SESSION_ID NUMBER,
PARAMS VARCHAR2(2000 BYTE),
EXECUTION_STATUS NUMBER,
DEPT_NO VARCHAR2(20 BYTE),
USER_NO VARCHAR2(10 BYTE),
EXCEPTION_MSG VARCHAR2(2000 BYTE)
)
TABLESPACE TABLESPACE_NAME
PCTUSED 0
PCTFREE 0
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 100M
NEXT 100M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
) –2011
PARTITION BY RANGE (LOG_DATE)
(
PARTITION TABLE_NAME_P2011 VALUES LESS THAN (TO_DATE(‘2012-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
LOGGING
COMPRESS
TABLESPACE TABLESPACE_NAME
PCTFREE 0
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 100M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TABLE_NAME_P2012 VALUES LESS THAN (TO_DATE(‘2013-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
LOGGING
COMPRESS
TABLESPACE TABLESPACE_NAME
PCTFREE 0
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 100M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TABLE_NAME_P2013 VALUES LESS THAN (TO_DATE(‘2014-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
LOGGING
COMPRESS
TABLESPACE TABLESPACE_NAME
PCTFREE 0
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 100M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TABLE_NAME_P2014 VALUES LESS THAN (TO_DATE(‘2015-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
LOGGING
COMPRESS
TABLESPACE TABLESPACE_NAME
PCTFREE 0
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 100M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
)
LOGGING
COMPRESS
NOCACHE
NOPARALLEL
MONITORING;

BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => ‘OWNER’, –USER
orig_table => ‘TABLE_NAME’,
int_table => ‘TABLE_NAME_INT’,
options_flag => 2);
END;
/

–BEGIN
–DBMS_REDEFINITION.ABORT_REDEF_TABLE(
–uname => ‘OWNER’, –USER
–orig_table => ‘TABLE_NAME’,
–int_table => ‘TABLE_NAME_INT’
–);
–END;
/

select from dba_segments
where segment_name like ‘%TABLE_INDEX%’

CREATE INDEX OWNER.TABLE_INDEX_ ON OWNER.TABLE_NAME_INT
(LOG_DATE, SERVICE_NAME)
LOGGING
TABLESPACE TABLESPACE_NAME
COMPRESS 1
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
NEXT 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOCAL(
PARTITION TABLE_NAME_P2011
LOGGING
COMPRESS
TABLESPACE TABLESPACE_NAME
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TABLE_NAME_P2012
LOGGING
COMPRESS
TABLESPACE TABLESPACE_NAME
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TABLE_NAME_P2013
LOGGING
COMPRESS
TABLESPACE TABLESPACE_NAME
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TABLE_NAME_P2014
LOGGING
COMPRESS
TABLESPACE TABLESPACE_NAME
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
);

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(‘OWNER’, ‘TABLE_NAME’, ‘TABLE_NAME_INT’);
END;

BEGIN
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
uname => ‘OWNER’,
orig_table => ‘TABLE_NAME’,
int_table => ‘TABLE_NAME_INT’,
dep_type => DBMS_REDEFINITION.CONS_INDEX,
dep_owner => ‘OWNER’,
dep_orig_name => ‘TABLE_INDEX’,
dep_int_name => ‘TABLE_INDEX_’);
END;
/

declare
cursor cr_x is
select TABLE_owner,table_name,partition_name
from dba_tab_partitions where
table_name in (‘TABLE_NAME_INT’)
–and last_analyzed trunc(sysdate)
;
begin
DBMS_SESSION.SET_NLS(‘NLS_SORT’,’BINARY’);
for l in cr_x loop
dbms_output.put_line(l.table_name||l.partition_name);
DBMS_STATS.GATHER_TABLE_STATS ( l.TABLE_owner,l.table_name,l.partition_name,null,null,’FOR ALL INDEXED COLUMNS SIZE 254′, NULL, ‘PARTITION’,TRUE, null, null, null);
end loop;
end;

BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => ‘OWNER’
,TabName => ‘TABLE_NAME’
,Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
,Method_Opt => ‘FOR ALL INDEXED COLUMNS SIZE 254’
,Degree => 4
,Cascade => TRUE
,No_Invalidate => TRUE);
END;
/

select * from SYS.DBA_REDEFINITION_ERRORS
select * from dba_redefinition_objects

SELECT COUNT(*) FROM OWNER.TABLE_NAME
SELECT COUNT(*) FROM OWNER.TABLE_NAME_INT

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(‘OWNER’, ‘TABLE_NAME’, ‘TABLE_NAME_INT’);
END;

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(‘OWNER’, ‘TABLE_NAME’,’TABLE_NAME_INT’,
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(‘OWNER’, ‘TABLE_NAME’, ‘TABLE_NAME_INT’);
END;

SELECT COUNT(*) FROM OWNER.TABLE_NAME
SELECT COUNT(*) FROM OWNER.TABLE_NAME_INT

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(‘OWNER’, ‘TABLE_NAME’, ‘TABLE_NAME_INT’);
END;
/
EXEC SYS.UTL_RECOMP.RECOMP_PARALLEL(8);

Compression

ALTER TABLE OWNER.TABLE_NAME MOVE PARTITION TABLE_NAME_P2014 COMPRESS;

ALTER INDEX OWNER.TABLE_INDEX REBUILD PARTITION TABLE_NAME_P2011;

ALTER TABLE OWNER.TABLE_NAME MOVE PARTITION TABLE_NAME_P2014 COMPRESS;

ALTER INDEX OWNER.TABLE_INDEX REBUILD PARTITION TABLE_NAME_P2012;

ALTER TABLE OWNER.TABLE_NAME MOVE PARTITION TABLE_NAME_P2014 COMPRESS;

ALTER INDEX OWNER.TABLE_INDEX REBUILD PARTITION TABLE_NAME_P2013;

ALTER TABLE OWNER.TABLE_NAME MOVE PARTITION TABLE_NAME_P2014 COMPRESS;

ALTER INDEX OWNER.TABLE_INDEX REBUILD PARTITION TABLE_NAME_P2014;

select * from SYS.DBA_REDEFINITION_ERRORS
select * from SYS.DBA_REDEFINITION_OBJECTS

select * from dba_objects
where status ‘VALID’

Advertisements