vendredi 31 juillet 2015

Datawarehouse



1] ETL ( OWB , ODI )

Product
Oracle Warehouse Builder
Version
11GR2


** Annoying Problems with Oracle warehouse Builder **

1.1 Logging To The OWB 11.2 Design Center Fails With API5055 : « This user has not been registered to any Warehouse Builder workspace »

Grants were missing : get from Pre-Prod DB or a good backup :

select 'grant '||granted_role||' to '||grantee||' ; ' from dba_role_privs where granted_role like '%OWB%' and ADMIN_OPTION = 'NO' and grantee not in ('SYS','OWBPRO')
union
select distinct 'grant '||granted_role||' to '||grantee||' with admin option ;'  from dba_role_privs where granted_role like '%OWB%' and ADMIN_OPTION = 'YES' and grantee not in ('SYS','OWBPRO')

1.2 OWB unix service fails to start

Ø  with ORA-01017: « invalid username/password; logon denied »


/oracle/11GDB/owb/bin/unix/run_service.sh -automatic 1 /oracle/11GDB OWBSYS <HOST>:1522:<SERVICE>


The doctor gives :

SQL> @$ORACLE_HOME/owb/rtp/sql/service_doctor.sql

Role set.

All PL/SQL packages and functions are valid
Platform properties have been loaded correctly
Platform location has been seeded correctly
NLS messages have been loaded correctly
>>>>>> The platform service is not available
Service script is accessible to the database server
>>>>>> There is a problem with the repository connection information used to
start the service
>>>>>>   Here is the detailed error message which contains the connection
information passed to the service:-
>>>>>>     Cannot access the repository owner password from within the database
server
>>>>>>   Please verify that this information is accurate
>>>>>>   If this information is correct, then please use the
'set_repository_password.sql' script to reset the stored password

PL/SQL procedure successfully completed.

Let’s set this password :

SQL> @$ORACLE_HOME/owb/rtp/sql/set_repository_password.sql

Role set.

Enter value for 1: OWB_PSW
declare
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.sql.SQLException: The file /oracle/11GDB/owb/bin/admin/rtrepos.properties
cannot be accessed or has not been properly created on the server srv. If
the file does not exist or if the database owner (normally user 'oracle') does
not have the required file permissions or if the file has not been properly
created then the file can be recreated by running the SQL*Plus script
/oracle/11GDB/owb/rtp/sql/reset_repository.sql (in a RAC environment the file
must be manually copied to each server which is used for OWB). Otherwise if
using a 10.2 database instance, then please run the SQL*Plus script
/oracle/11GDB/owb/UnifiedRepos/reset_owbcc_home.sql.The exception which caused
this failure is 'java.security.AccessControlException(the Permission
(java.io.FilePermission /oracle/11GDB/owb/bin/admin/<HOST>/rtrepos.properties
write) has not been granted to OWBSYS. The PL/SQL to grant this is
dbms_java.grant_permission( 'OWBSYS', 'SYS:java.io.FilePermission',
'/oracle/11GDB/owb/bin/admin/<HOST>
ORA-06512: at "OWBSYS.WB_RTI_UTIL", line 49
ORA-06512: at "OWBSYS.WB_RT_SCRIPT_UTIL", line 1018
ORA-06512: at line 3

Set the permissions under SYS user :

SQL> exec dbms_java.grant_permission( 'OWBSYS','SYS:java.util.PropertyPermission', '*', 'read,write' ) ;

PL/SQL procedure successfully completed.

SQL> exec dbms_java.grant_permission( 'OWBSYS', 'SYS:java.io.FilePermission','/oracle/11GDB/owb/bin/admin/<HOST>/rtrepos.properties', 'read,write' ) ;

PL/SQL procedure successfully completed.

Ø  set_repository_password.sql produces « java.lang.NullPointerException »

SQL> @$ORACLE_HOME/owb/rtp/sql/set_repository_password.sql

Role set.

Enter value for 1: OWB_PSW
declare
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.NullPointerException
ORA-06512: at "OWBSYS.WB_RTI_UTIL", line 49
ORA-06512: at "OWBSYS.WB_RT_SCRIPT_UTIL", line 1018
ORA-06512: at line 3

Wrong Path is the cause :

$ export OWB_HOME=$ORACLE_HOME

$ cd $OWB_HOME/rtp/sql

/oracle/11GDB/owb/rtp/sql > sqlplus OWBSYS/OWB_PSW @set_repository_password.sql OWB_PSW

Role set.

PL/SQL procedure successfully completed.

Commit complete.

SQL>

$ sqlplus OWBSYS/OWB_PSW  @$ORACLE_HOME/owb/rtp/sql/service_doctor.sql

Role set.

All PL/SQL packages and functions are valid
Platform properties have been loaded correctly
Platform location has been seeded correctly
NLS messages have been loaded correctly
The platform service is available
Service script is accessible to the database server
Connection information stored within the repository is correct

PL/SQL procedure successfully completed.

SQL>

$ sqlplus OWBSYS/OWB_PSW @$ORACLE_HOME/owb/rtp/sql/start_service.sql

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 11 16:19:04 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Role set.

Available
Database managed service using <HOST>:1522:<SERVICE> and home /oracle/11GDB

PL/SQL procedure successfully completed.

SQL> exit


API5055: This user has not been registered to any warehouse builder workspace. Do you want to proceed to create a new workspace for it, or register it to an existing one

1.3 Problem when connecting to OWB Studio

ORA-20213: Unable to create standalone job record for  map named "MAP_NAME" having UOID 04E636AB8BA9016AE053C0A80B376D

Lack of WRITE grant for OWBSYS user : GRANT UNLIMITED TABLESPACE TO OWBSYS WITH ADMIN OPTION;

2] Materialized Views

A note on MV
Materialized View may be interesting when
-       we need real time data access ( ON COMMIT )
-       the optimizer may switch the execution plan towards the MV ( QUERY REWRITE )
-       refresh data without impact for users ( consistent read are allowed from UNDOs while refreshing )

When we are not meeting these criterias, it may be better to create a table ( CTAS CREATE TABLE AS SELECT )
 -          drop or rename old table
 -          CTAS
 -          index creation

3] Partitions

3.1 Oracle - How to partition an existing table

Hereafter, we’ll see how to Partition

3.1.0 an existing table Online
3.1.1 a newly created table
3.1.2 an existing table using Direct INSERT /*+ APPEND */

3.1.0 How to Partition an existing table Online [ DBMS_REDEFINITION Oracle 12C ]

Create the target table.

CREATE TABLE OWNER.PART_TABLE_P
(
  MATRIC    NUMBER(10)                          NOT NULL,
  DAT       DATE                                NOT NULL,
  TDU       NUMBER(10),
  TPOINT    NUMBER(10),
…
  C300      NUMBER(10)
)
NOCOMPRESS
TABLESPACE OWNER_DAT
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING
PARTITION BY RANGE (DAT)
INTERVAL( NUMTOYMINTERVAL(1, 'YEAR'))
( 
  PARTITION P2_2003 VALUES LESS THAN (TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE OWNER_DAT
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ), 
  PARTITION P2_2004 VALUES LESS THAN (TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
…
  PARTITION P1_2018 VALUES LESS THAN (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE OWNER_DAT
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          30M
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
)
NOCACHE
NOPARALLEL
MONITORING;

Table created.
 

Prepare and start synchronization between Tables.
EXEC DBMS_REDEFINITION.can_redef_table('OWNER', 'PART_TABLE'); 
PL/SQL procedure successfully completed. 

BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'OWNER', 
orig_table => 'PART_TABLE',
int_table => 'PART_TABLE_P');
END;
/ 
PL/SQL procedure successfully completed.

BEGIN
DBMS_REDEFINITION.sync_interim_table(
uname => 'OWNER', 
orig_table => 'PART_TABLE',
int_table => 'PART_TABLE_P');
END;
/ 
 PL/SQL procedure successfully completed. 

Create Index and Constraints
CREATE UNIQUE INDEX OWNER.UNQ_I3_PART_TABLE_P ON OWNER.PART_TABLE_P
(MATRIC, DAT)
LOGGING
TABLESPACE OWNER_DAT
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          175864K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL; 
Index created. 

Gather Statistics.
EXEC DBMS_STATS.gather_table_stats ('OWNER','PART_TABLE_P',null,DBMS_STATS.AUTO_SAMPLE_SIZE,null,'FOR ALL INDEXED COLUMNS SIZE AUTO',2,'ALL',TRUE); 
 PL/SQL procedure successfully completed. 

Lastly : split from target and existing Table.
BEGIN
  dbms_redefinition.finish_redef_table(
    uname      => 'OWNER',       
    orig_table => 'PART_TABLE',
    int_table  => 'PART_TABLE_P');
END;
/ 
PL/SQL procedure successfully completed. 

Remove temp table and rename objects.
DROP TABLE PART_TABLE_P ;
ALTER INDEX OWNER.UNQ_I3_PART_TABLE_P RENAME TO OWNER.UNQ_I3_PART_TABLE ;
Table dropped.
Index altered. 

Very important, take care of Grants : report them to newly Partitioned Table.

3.1.1 How to Partition a newly created Table [ Partitions – RANGE ]

This is an example of RANGE Partitioning a Table on Dates and Load Data into the partitions.

  • Create the Table with NOLOGGING clause to prepare the Loading

[10G] We pre-create the 2014 / 2015 partitions.

CREATE TABLE TICK_OWN.TICK_TAB
(
  NO_POSTE                VARCHAR2(20 BYTE),
…
  HOUR_BEGIN_DATE        DATE,
  HOUR_END_DATE          DATE,
…
  DIRECTION               VARCHAR2(50 BYTE)
)
TABLESPACE TICK_OWN_DAT NOLOGGING
PARTITION BY RANGE (HOUR_BEGIN_DATE)
(
PARTITION P201401 VALUES LESS THAN (TO_DATE('2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P201402 VALUES LESS THAN (TO_DATE('2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
…
PARTITION P201509 VALUES LESS THAN (TO_DATE('2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P201510 VALUES LESS THAN (TO_DATE('2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P201511 VALUES LESS THAN (TO_DATE('2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P201512 VALUES LESS THAN (TO_DATE('2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

  • Load data ( sqlldr input files are .txt )

$ cat OIT_Loader_Tickets.ctl
LOAD DATA
infile 'Tickets_2013_2015_02.txt'
APPEND INTO TABLE TICK_TAB
FIELDS TERMINATED BY '  '
("NO_POSTE",
…
 HOUR_BEGIN_DATE "to_date(:HOUR_BEGIN_DATE, 'DD/MM/RR HH24:MI:SS')",
 HOUR_END_DATE "to_date(:HOUR_END_DATE, 'DD/MM/RR HH24:MI:SS')",
…
 "DIRECTION"
)

$ cat charge_part_app.sh
for i in *.txt ; do
sed "s/#FIC#/$i/" OIT_Loader_Tickets.ctl_ori > OIT_Loader_Tickets.ctl
sqlldr TICK_OWN/TICK_OWN control=OIT_Loader_Tickets.ctl SILENT=FEEDBACK
#l=$( echo $i | sed 's/\.txt/\.log/' )
echo $i
egrep "read|rejected" OIT_Loader_Tickets.log
done

To prevent the error, modify the territory to accept ‘,’ as number separator

Record 1: Rejected - Error on table TICK_TAB, column "COUT_FAC".
ORA-01722: invalid number

$ export NLS_LANG=FRENCH

$ time ./charge_part_app.sh

SQL*Loader: Release 10.2.0.5.0 - Production on Ven. Avr. 3 11:32:56 2015

Tickets_2013_2014_01.txt
Total logical records read:       1052998
Total logical records rejected:         4
…
Tickets_2013_2015_02.txt
Total logical records read:       1052881
Total logical records rejected:         4

real    10m30.147s

Loader Performance : 1 million Rows per Minute

EXEC DBMS_STATS.GATHER_TABLE_STATS('TICK_OWN','TICK_TAB',estimate_percent => 100, degree => 4, cascade => true);

select table_name , partition_name , num_rows from dba_tab_partitions where table_owner = 'TICK_OWN' and table_name = 'TICK_TAB' order by 2

TABLE_NAME   PARTITION_NAME      NUM_ROWS

TICK_TAB     P201401             1052994
TICK_TAB     P201402             1084812
TICK_TAB     P201403             1129865
TICK_TAB     P201404             1118937
TICK_TAB     P201405             974122
TICK_TAB     P201406             1173035
TICK_TAB     P201407             1044504
TICK_TAB     P201408             713284
TICK_TAB     P201409             1252036
TICK_TAB     P201410             1174544
TICK_TAB     P201411             1051977
TICK_TAB     P201412             1005226
TICK_TAB     P201501             1115338
TICK_TAB     P201502             1052877
TICK_TAB     P201503             0
TICK_TAB     P201504             0
TICK_TAB     P201505             0
TICK_TAB     P201506             0
TICK_TAB     P201507             0
TICK_TAB     P201508             0
TICK_TAB     P201509             0
TICK_TAB     P201510             0
TICK_TAB     P201511             0
TICK_TAB     P201512             0

  • Create LOCAL Index

select count(1) from TICK_OWN.TICK_TAB where HOUR_BEGIN_DATE < TO_DATE('2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') -- 25 sec

CREATE INDEX TICK_OWN.TICK_TAB_I01 ON TICK_OWN.TICK_TAB (HOUR_BEGIN_DATE) LOCAL;

select count(1) from TICK_OWN.TICK_TAB where HOUR_BEGIN_DATE < TO_DATE('2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') -- 6 sec

alter table TICK_OWN.TICK_TAB LOGGING ;


  • Add New Partitions on the existing table for the following Year

ALTER TABLE TICK_OWN.TICK_TAB ADD PARTITION P201601 VALUES LESS THAN (TO_DATE('2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ;
…
ALTER TABLE TICK_OWN.TICK_TAB ADD PARTITION P201612 VALUES LESS THAN (TO_DATE('2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ;

See next 11G « new Automatic Partitions Creation »

  • Drop Partition of the previous Year/Month

ALTER TABLE TICK_OWN.TICK_TAB DROP PARTITION P201401 ;

3.1.2 How to Partition an existing Table [ Partitions RANGE KEY – 11g Automatic Creation with the INTERVAL option ] using Direct INSERT /*+ APPEND */

We had a BI Queries on 4 Millions Rows table that took 20 SEC.
Even if we always queried only a single Month, the table was Full Scanned (FTS).

Let’s Partition the table on MONTH basis on the ID_TIME NUMBER column ‘YYYYMM’
Using the New ( 11G+ ) INTERVAL option :

-- FACT_CALL_OUT -> PARTITIONNING

CREATE TABLE FACT_CALL_OUT_P
(
  ID_TIME              NUMBER(6),
…
  DATE_MODIFICATION     DATE,
  DATE_CREATION         DATE
)
PARTITION BY RANGE (ID_TIME) -- number
INTERVAL (1)
(
   PARTITION PART_01 values LESS THAN (201402)
) ;

insert /*+ APPEND */ into FACT_CALL_OUT_P select * from FACT_CALL_OUT order by ID_TIME ;
-- 3 244 328 rows

commit ;

CREATE UNIQUE INDEX PK_FACT_CALL_OUT_P ON FACT_CALL_OUT_P
(ID_TIME, …,ID_POSTE) LOCAL ;

ALTER TABLE FACT_CALL_OUT_P ADD ( CONSTRAINT PK_FACT_CALL_OUT_P PRIMARY KEY (ID_TIME, … ID_POSTE) USING INDEX LOCAL ) ;

alter table FACT_CALL_OUT rename to FACT_CALL_OUT_ORI ;

alter table FACT_CALL_OUT_P rename to FACT_CALL_OUT ;

exec DBMS_STATS.GATHER_TABLE_STATS ( 'OWNER','FACT_CALL_OUT',null,DBMS_STATS.AUTO_SAMPLE_SIZE,null,'FOR ALL INDEXED COLUMNS SIZE AUTO',2,'ALL',TRUE) ;

select table_name , partition_name , high_value , num_rows from user_tab_partitions where table_name = 'FACT_CALL_OUT' order by 2


TABLE_NAME
PARTITION_NAME
HIGH_VALUE
NUM_ROWS




FACT_CALL_OUT
PART_01
201402
211067
FACT_CALL_OUT
SYS_P281
201403
214543
FACT_CALL_OUT
SYS_P282
201404
221098
FACT_CALL_OUT
SYS_P283
201406
184379
FACT_CALL_OUT
SYS_P284
201407
233427
FACT_CALL_OUT
SYS_P285
201408
209784
FACT_CALL_OUT
SYS_P286
201409
136560
FACT_CALL_OUT
SYS_P287
201410
226518
FACT_CALL_OUT
SYS_P288
201411
222147
FACT_CALL_OUT
SYS_P289
201412
198880
FACT_CALL_OUT
SYS_P290
201413
194802
FACT_CALL_OUT
SYS_P291
201502
206142
FACT_CALL_OUT
SYS_P292
201503
199144
FACT_CALL_OUT
SYS_P293
201504
213911
FACT_CALL_OUT
SYS_P294
201505
208092
FACT_CALL_OUT
SYS_P295
201506
163834

  • 3 SEC
Moreover, this elapsed time will stay Constant as long as new Month will be loaded in the partitions.
New Plan with the PARTITION RANGE ITERATOR :


Another one with the INTERVAL option on a DATE column

CREATE TABLE FACT_CALL_IN_P
(
  ID_TIME                 VARCHAR2(8 BYTE),
  BEGIN_DATE               DATE,
  END_DATE                 DATE,
…
  DATE_CREATION            DATE
)
PARTITION BY RANGE (END_DATE) -- DATE
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION PART_01 values LESS THAN (TO_DATE('2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
) ;

3.1.3 Partitions DATE KEY – 11g Automatic Creation with the INTERVAL option


XXXXXXXXXXXXX

3.2 Alter Table to Add Parallelism

3.2.1 Enable Parallelism

  • [11G] Activate Oracle Parallelism at the Instance Level

$ sqlplus / as sysdba

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     285
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_threads_per_cpu             integer     2

SQL> alter system set parallel_min_servers = 8 ;
System altered.

SQL> alter system set PARALLEL_DEGREE_POLICY = 'AUTO' ;
System altered.

  • [11G] Activate Oracle Parallelism at the Table Level

HINT in the Query

SELECT /* PARALLEL(FACT_DM_CALL_IN,4) */
  DIM_DM_POSTE.NUM_POSTE,

ALTER TABLE

alter table FACT_DM_CALL_IN PARALLEL 4 ;


3.2.2 Results on Partitioning and Parallelism

Reports
Partition
Parallel
Elapsed
Report 1 MONTH
N
N
22 sec
Report  1 MONTH
Y
N
6 sec
Report  12 MONTH
Y
N
42 sec
Report  12 MONTH
Y
Y
20 sec
Report  1 MONTH
Y
Y
3 sec
Statistics
N
N
20 sec
Statistics
Y
N
3 sec

3.3 Split Partition

Using Range on Date Partitioning
Be careful to the « UPDATE GLOBAL INDEXES » option as with Index, they are very long to maintain while splitting - 4 Hours + goes down to 5 minutes with the following :
Set INDEX UNUSABLE
Split
Rebuild INDEX


$ time sqlplus system/$psw @create partition_2016_2016.sql TAB01 OWNER
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

Elapsed: 00:00:00.08

==> Gather Statistics

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

PL/SQL procedure successfully completed.

Elapsed: 00:00:52.31

==> Invalid Objects Before

OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
OWNER                            V01              VIEW

21 rows selected.

Elapsed: 00:00:00.03

==> Size and Num Rows Before

SEGMENT_TYPE       TABLE_NAME                                                                           Size_MB
------------------ --------------------------------------------------------------------------------- ----------
INDEX              TAB01                                                                                      1165
TABLE PARTITION    TAB01                                                                                      1051

Elapsed: 00:00:05.31

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
OWNER                            TAB01                             P1_2015                           6050824

Elapsed: 00:00:00.02
START : 14:58:30
ALTER INDEX OWNER.I6_TAB01 UNUSABLE
ALTER INDEX OWNER.I5_TAB01 UNUSABLE
ALTER INDEX OWNER.COMP_MATRIC_DATS UNUSABLE
ALTER INDEX OWNER.I2_TAB01 UNUSABLE
ALTER INDEX OWNER.I3_TAB01 UNUSABLE
ALTER INDEX OWNER.UNQ_I1_TAB01 UNUSABLE
ALTER INDEX OWNER.I4_TAB01 UNUSABLE

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09

Session altered.

Elapsed: 00:00:00.00
==> Partition table TAB01 : P1_2015 => P1_2015 + P1_2016 : alter table TAB01  split partition P1_2015 => P1_2015 + P1_2016

Table altered.

Elapsed: 00:00:32.78
START : 14:59:03
ALTER INDEX OWNER.I6_TAB01 REBUILD ONLINE NOLOGGING
ALTER INDEX OWNER.I5_TAB01 REBUILD ONLINE NOLOGGING
ALTER INDEX OWNER.COMP_MATRIC_DATS REBUILD ONLINE NOLOGGING
ALTER INDEX OWNER.I2_TAB01 REBUILD ONLINE NOLOGGING
ALTER INDEX OWNER.I3_TAB01 REBUILD ONLINE NOLOGGING
ALTER INDEX OWNER.UNQ_I1_TAB01 REBUILD ONLINE NOLOGGING
ALTER INDEX OWNER.I4_TAB01 REBUILD ONLINE NOLOGGING
ALTER INDEX OWNER.I6_TAB01 LOGGING
ALTER INDEX OWNER.I5_TAB01 LOGGING
ALTER INDEX OWNER.COMP_MATRIC_DATS LOGGING
ALTER INDEX OWNER.I2_TAB01 LOGGING
ALTER INDEX OWNER.I3_TAB01 LOGGING
ALTER INDEX OWNER.UNQ_I1_TAB01 LOGGING
ALTER INDEX OWNER.I4_TAB01 LOGGING
ALTER TABLE OWNER.TAB01 LOGGING

PL/SQL procedure successfully completed.

Elapsed: 00:04:53.72

Session altered.

Elapsed: 00:00:00.00

==> Invalid Objects After

OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
OWNER                            V01              VIEW

21 rows selected.

Elapsed: 00:00:00.01

==> Gather Statistics

PL/SQL procedure successfully completed.

Elapsed: 00:00:51.15

==> Size and Num Rows After

SEGMENT_TYPE       TABLE_NAME                                                                           Size_MB
------------------ --------------------------------------------------------------------------------- ----------
INDEX              TAB01                                                                                      1178
TABLE PARTITION    TAB01                                                                                      1049

Elapsed: 00:00:01.63

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
OWNER                            TAB01                             P1_2015                           3403628
OWNER                            TAB01                             P1_2016                           2614093

Elapsed: 00:00:00.02
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

real    7m17.378s


SQL Scripts


set pages 5000 lines 150
set serveroutput on
SET VERIFY OFF
set timing on
col object_name for a20
col segment_name for a20

alter session set current_schema=&2;

prompt
prompt ==> Gather Statistics
exec DBMS_STATS.UNLOCK_TABLE_STATS ( '&2','&1') ;
exec DBMS_STATS.GATHER_TABLE_STATS ( '&2','&1',null,DBMS_STATS.AUTO_SAMPLE_SIZE,null,'FOR ALL INDEXED COLUMNS SIZE AUTO',2,'ALL',TRUE) ;

prompt
prompt ==> Invalid Objects Before
select owner , object_name , object_type from dba_objects where owner = '&2' and status != 'VALID' ;

--select count(1) from hrt.comptj where DAT > to_date('20150101','YYYYMMDD')

prompt
prompt ==> Size and Num Rows Before
select segment_type , b.table_name , ceil(sum(bytes)/1024/1024) "Size_MB" from dba_segments a , dba_indexes b
where a.owner = '&2' and segment_type = 'INDEX' and b.table_name = '&1' and a.owner = b.owner and a.segment_name = b.index_name
group by segment_type , b.table_name
union select segment_type , segment_name , ceil(sum(bytes)/1024/1024) "Size_MB" from dba_segments where owner = '&2' and segment_name in  ('&1') group by segment_type , segment_name ;

select table_owner,table_name,partition_name,num_rows from dba_tab_partitions where table_owner = '&2' and table_name = '&1'
and ( -- partition_name like '%2013%' or partition_name like '%2014%' or
partition_name like '%2015%' or partition_name like '%2016%' ) order by 1,2,3 ;

...skipping...
set serveroutput on
set timing on
set lines 150 pages 5000

DECLARE
  TYPE idxcurtyp IS REF CURSOR;
  usr_idxs idxcurtyp ;
  TYPE proplist IS TABLE OF dba_indexes.owner%TYPE;
  TYPE idxnlist IS TABLE OF dba_indexes.index_name%TYPE;
  prop  proplist ;
  idxn  idxnlist ;

  v_sql  VARCHAR2(1024);
  v_res  VARCHAR2(1024);
  v_res_2  VARCHAR2(1024);

BEGIN
DBMS_OUTPUT.ENABLE(1000000);       -- Clear DBMS_OUTPUT buffer.

DBMS_OUTPUT.PUT_LINE('START : '||TO_CHAR(SYSDATE,'HH24:MI:SS'));

-- index management before
   OPEN usr_idxs for select owner , index_name from dba_indexes where owner = '&2' and table_name = '&1' order by 1 ;
   FETCH usr_idxs BULK COLLECT INTO prop , idxn ;
   CLOSE usr_idxs ;

    FOR j IN prop.FIRST .. prop.LAST
    LOOP
      v_sql:= 'ALTER INDEX '||prop(j)||'.'||idxn(j)||' UNUSABLE';
      DBMS_OUTPUT.PUT_LINE( v_sql ) ;
      EXECUTE IMMEDIATE v_sql;
    END LOOP;
-- index mngt before end

END;
/

...skipping...
-- AUC 19/09/2016
-- Partitionnement annuel pour &2
-- define logd=&1

set pages 5000 lines 150
set serveroutput on
SET VERIFY OFF
set timing on
col object_name for a20
col segment_name for a20

alter session set current_schema=&2;

prompt ==> Partition table &1 : P1_2015 => P1_2015 + P1_2016 : alter table &1  split partition P1_2015 => P1_2015 + P1_2016
--alter table &2..&1 split partition P1_2015 at (to_date('20160101','YYYYMMDD')) into (partition P1_2015, partition P1_2016) UPDATE GLOBAL INDEXES ;
alter table &2..&1 split partition P1_2015 at (to_date('20160101','YYYYMMDD')) into (partition P1_2015, partition P1_2016) ;

...skipping...
set serveroutput on
set timing on
set lines 150 pages 5000

DECLARE
  TYPE idxcurtyp IS REF CURSOR;
  usr_idxs idxcurtyp ;
  TYPE proplist IS TABLE OF dba_indexes.owner%TYPE;
  TYPE idxnlist IS TABLE OF dba_indexes.index_name%TYPE;
  prop  proplist ;
  idxn  idxnlist ;

  v_sql  VARCHAR2(1024);
  v_res  VARCHAR2(1024);
  v_res_2  VARCHAR2(1024);

BEGIN
DBMS_OUTPUT.ENABLE(1000000);       -- Clear DBMS_OUTPUT buffer.

DBMS_OUTPUT.PUT_LINE('START : '||TO_CHAR(SYSDATE,'HH24:MI:SS'));

-- index management after
   OPEN usr_idxs for select owner , index_name from dba_indexes where owner = '&2' and table_name = '&1' order by 1 ;
   FETCH usr_idxs BULK COLLECT INTO prop , idxn ;
   CLOSE usr_idxs ;

    FOR j IN prop.FIRST .. prop.LAST
    LOOP
        v_sql:= 'ALTER INDEX '||prop(j)||'.'||idxn(j)||' REBUILD ONLINE NOLOGGING';
        DBMS_OUTPUT.PUT_LINE( v_sql ) ;
        EXECUTE IMMEDIATE v_sql;
    END LOOP;
    FOR j IN prop.FIRST .. prop.LAST
    LOOP
        v_sql:= 'ALTER INDEX '||prop(j)||'.'||idxn(j)||' LOGGING';
        DBMS_OUTPUT.PUT_LINE( v_sql ) ;
        EXECUTE IMMEDIATE v_sql;
    END LOOP;
-- index mngt after end

  v_sql:= 'ALTER TABLE &2..&1 LOGGING';
  DBMS_OUTPUT.PUT_LINE( v_sql ) ;
  EXECUTE IMMEDIATE v_sql;

END;
/

...skipping...
-- AUC 19/09/2016
-- Partitionnement annuel pour &2
-- define logd=&1

set pages 5000 lines 150
set serveroutput on
SET VERIFY OFF
set timing on
col object_name for a20
col segment_name for a20

alter session set current_schema=&2;

prompt
prompt ==> Invalid Objects After
select owner , object_name , object_type from dba_objects where owner = '&2' and status != 'VALID' ;

prompt
prompt ==> Gather Statistics
exec DBMS_STATS.GATHER_TABLE_STATS ( '&2','&1',null,DBMS_STATS.AUTO_SAMPLE_SIZE,null,'FOR ALL INDEXED COLUMNS SIZE AUTO',2,'ALL',TRUE) ;

prompt
prompt ==> Size and Num Rows After
select segment_type , b.table_name , ceil(sum(bytes)/1024/1024) "Size_MB" from dba_segments a , dba_indexes b
where a.owner = '&2' and segment_type = 'INDEX' and b.table_name = '&1' and a.owner = b.owner and a.segment_name = b.index_name
group by segment_type , b.table_name
union select segment_type , segment_name , ceil(sum(bytes)/1024/1024) "Size_MB" from dba_segments where owner = '&2' and segment_name in  ('&1') group by segment_type , segment_name
/

select table_owner,table_name,partition_name,num_rows from dba_tab_partitions where table_owner = '&2' and table_name = '&1'
and ( -- partition_name like '%2013%' or partition_name like '%2014%' or
partition_name like '%2015%' or partition_name like '%2016%' ) order by 1,2,3 ;

exit


Publié par orACsd à 00:18
Libellés :