12.1 Datapump Expdp Impdp Enhancements

Data Pump uses a multiprocess architecture in which a master control process dispatches job items to one or more worker processes. These worker processes may use PX processes to move data. 
Parallelism in this context refers to the total number of active worker processes and PX processes that are able to operate in parallel. 
The default PARALLEL value is one. If Data Pump decides to use PX processes to load or unload a table, then the PX processes are counted against the degree of parallelism used by Data Pump, but the worker process that starts the PX processes is not since it is idle while the PX processes are executing the query. Also, if a worker process has been started but it is idle, it is not counted against the limit specified by the PARALLEL parameter.

Datapump Job, Master Control Process creates a pool of data pump worker processes.
The degree of parallelism can be increased or decreased during execution of export/ import job.

The decrease in parallelism reduces the number of parallel workers. It may take a while to reduce the workers jobs.

The increase in parallelism takes affects immediately; 

Data Pump Import processes the database objects in the following order:

1. The first worker begins to load all the metadata: the tablespaces, schemas, etc., until all the tables are created.
2. Once the tables are created, the first worker starts loading data instead of metadata and the rest of the workers start loading data too.
3. Once the table data is loaded, the first worker returns to loading metadata again. The rest of the workers are idle until the first worker loads all the metadata up to package bodies.
4. Multiple workers load package bodies in parallel.
5. One worker loads metadata up to and including secondary tables.
6. Multiple workers load secondary table data.
7. One worker loads the remaining metadata.

Bug 24423416 : IMPDP FOR SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY TAKES HOURS

DataPump Import Is Extremely Slow When Importing Package Bodies (Doc ID 1452917.1)

If there are multiple partitions and subpartitions for a table, it takes high time to import the partition and subpartition segment information.
Here are things to perform speeding up the progress.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> ALTER SYSTEM SET STREAMS_POOL_SIZE=0;
SQL> ALTER SYSTEM SET STREAMS_POOL_SIZE=300M;


Use the parameter METRICS=Y to include additional logging information about the number of objects and the time it took to process them in the log file. With METRICS, additional information can be obtained about the number of objects that were processed and the time it took for processing them.

Tracing can be enabled by specifying an 7 digit hexadecimal mask in the TRACE parameter of expdp or impdp.TRACE does not add anything to the output of DataPump, it creates additional trace files in ADR_HOME location.

10300 SHDW: To trace the Shadow process   20300 KUPV: To trace Fixed table   40300 'div' To trace Process services   80300 KUPM: To trace Master Control Process  100300 KUPF: To trace File Manager  200300 KUPC: To trace Queue services  400300 KUPW: To trace Worker process(es)          800300 KUPD: To trace Data Package 1000300 META: To trace Metadata Package 1FF0300 'all' To trace all components, full tracing

 eg:

expdp ..... EXCLUDE=statistics PARALLEL=16 TRACE=1FF0300

Analyzing B-Tree Indexes

Analyzing B-Tree Indexes

CREATE TABLE index_log (
 owner          VARCHAR2(30),
 index_name     VARCHAR2(30),
 last_inspected DATE,
 leaf_blocks    NUMBER,    
 target_size    NUMBER,
 idx_layout     CLOB);

ALTER TABLE index_log ADD CONSTRAINT pk_index_log PRIMARY KEY (owner,index_name);

CREATE TABLE index_hist (
 owner          VARCHAR2(30),
 index_name     VARCHAR2(30),
 inspected_date DATE,
 leaf_blocks    NUMBER,    
 target_size    NUMBER,
 idx_layout     VARCHAR2(4000));

ALTER TABLE index_hist ADD CONSTRAINT pk_index_hist PRIMARY KEY  (owner,index_name,inspected_date);

--
-- Variables:
--  vMinBlks: Specifies the minimum number of leaf blocks for scanning the index
--            Indexes below this number will not be scanned/reported on
--  vScaleFactor: The scaling factor, defines the threshold of the estimated leaf block count 
--                to be smaller than the supplied fraction of the current size. 
--  vTargetUse : Supplied percentage utilisation. For example 90% equates to the default pctfree 10 
--  vHistRet : Defines the number of records to keep in the INDEX_HIST table for each index entry
--

CREATE OR REPLACE PACKAGE index_util AUTHID CURRENT_USER IS
vMinBlks     CONSTANT POSITIVE := 1000;
vScaleFactor CONSTANT NUMBER := 0.6;
vTargetUse   CONSTANT POSITIVE := 90;  -- equates to pctfree 10  
vHistRet     CONSTANT POSITIVE := 10;  -- (#) records to keep in index_hist
 procedure inspect_schema (aSchemaName IN VARCHAR2);
 procedure inspect_index (aIndexOwner IN VARCHAR2, aIndexName IN VARCHAR2, aTableOwner IN VARCHAR2, aTableName IN VARCHAR2, aLeafBlocks IN NUMBER);
END index_util; 
/

CREATE OR REPLACE PACKAGE BODY index_util IS
procedure inspect_schema (aSchemaName IN VARCHAR2) IS
 begin
 FOR r IN (select table_owner, table_name, owner index_owner, index_name, leaf_blocks 
           from dba_indexes  
           where owner = upper(aSchemaname)
             and index_type in ('NORMAL','NORMAL/REV','FUNCTION-BASED NORMAL')
             and partitioned = 'NO'  
             and temporary = 'N'  
             and dropped = 'NO'  
             and status = 'VALID'  
             and last_analyzed is not null  
           order by owner, table_name, index_name) LOOP

   IF r.leaf_blocks > vMinBlks THEN
   inspect_index (r.index_owner, r.index_name, r.table_owner, r.table_name, r.leaf_blocks);
   END IF;
  END LOOP;
 commit;
end inspect_schema;
procedure inspect_index (aIndexOwner IN VARCHAR2, aIndexName IN VARCHAR2, aTableOwner IN VARCHAR2, aTableName IN VARCHAR2, aLeafBlocks IN NUMBER) IS
 vLeafEstimate number;  
 vBlockSize    number;
 vOverhead     number := 192; -- leaf block "lost" space in index_stats 
 vIdxObjID     number;
 vSqlStr       VARCHAR2(4000);
 vIndxLyt      CLOB;
 vCnt          number := 0;
  TYPE IdxRec IS RECORD (rows_per_block number, cnt_blocks number);
  TYPE IdxTab IS TABLE OF IdxRec;
  l_data IdxTab;
begin  
  select a.block_size into vBlockSize from dba_tablespaces a,dba_indexes b where b.index_name=aIndexName and b.owner=aIndexOwner and a.tablespacE_name=b.tablespace_name;
 select round (100 / vTargetUse *       -- assumed packing efficiency
              (ind.num_rows * (tab.rowid_length + ind.uniq_ind + 4) + sum((tc.avg_col_len) * (tab.num_rows) )  -- column data bytes  
              ) / (vBlockSize - vOverhead)  
              ) index_leaf_estimate  
   into vLeafEstimate  
 from (select  /*+ no_merge */ table_name, num_rows, decode(partitioned,'YES',10,6) rowid_length  
       from dba_tables
       where table_name  = aTableName  
         and owner       = aTableOwner) tab,  
      (select  /*+ no_merge */ index_name, index_type, num_rows, decode(uniqueness,'UNIQUE',0,1) uniq_ind  
       from dba_indexes  
       where table_owner = aTableOwner  
         and table_name  = aTableName  
         and owner       = aIndexOwner  
         and index_name  = aIndexName) ind,  
      (select  /*+ no_merge */ column_name  
       from dba_ind_columns  
       where table_owner = aTableOwner  
         and table_name  = aTableName 
         and index_owner = aIndexOwner   
         and index_name  = aIndexName) ic,  
      (select  /*+ no_merge */ column_name, avg_col_len  
       from dba_tab_cols  
       where owner = aTableOwner  
         and table_name  = aTableName) tc  
 where tc.column_name = ic.column_name  
 group by ind.num_rows, ind.uniq_ind, tab.rowid_length; 

 IF vLeafEstimate < vScaleFactor * aLeafBlocks THEN
  select object_id into vIdxObjID
  from dba_objects  
  where owner = aIndexOwner
    and object_name = aIndexName;
   vSqlStr := 'SELECT rows_per_block, count(*) blocks FROM (SELECT /*+ cursor_sharing_exact ' ||
             'dynamic_sampling(0) no_monitoring no_expand index_ffs(' || aTableName || 
             ',' || aIndexName || ') noparallel_index(' || aTableName || 
             ',' || aIndexName || ') */ sys_op_lbid(' || vIdxObjID || 
             ', ''L'', ' || aTableName || '.rowid) block_id, ' || 
             'COUNT(*) rows_per_block FROM ' || aTableOwner || '.' || aTableName || ' GROUP BY sys_op_lbid(' || 
             vIdxObjID || ', ''L'', ' || aTableName || '.rowid)) group by rows_per_block order by rows_per_block';
   execute immediate vSqlStr BULK COLLECT INTO l_data;
  vIndxLyt := '';

   FOR i IN l_data.FIRST..l_data.LAST LOOP
    vIndxLyt := vIndxLyt || l_data(i).rows_per_block || ' - ' || l_data(i).cnt_blocks || chr(10);
   END LOOP;

   select count(*) into vCnt from index_log where owner = aIndexOwner and index_name = aIndexName;

   IF vCnt = 0   
    THEN insert into index_log values (aIndexOwner, aIndexName, sysdate, aLeafBlocks, round(vLeafEstimate,2), vIndxLyt);
    ELSE vCnt := 0;

         select count(*) into vCnt from index_hist where owner = aIndexOwner and index_name = aIndexName;

         IF vCnt >= vHistRet THEN
           delete from index_hist
           where owner = aIndexOwner 
             and index_name = aIndexName 
             and inspected_date = (select MIN(inspected_date) 
                                   from index_hist
                                   where owner = aIndexOwner 
                                     and index_name = aIndexName);
         END IF;

          insert into index_hist select * from index_log where owner = aIndexOwner and index_name = aIndexName;

         update index_log  
         set last_inspected = sysdate,
             leaf_blocks = aLeafBlocks, 
             target_size = round(vLeafEstimate,2),
             idx_layout = vIndxLyt
        where owner = aIndexOwner and index_name = aIndexName;

   END IF;
  END IF;
 END inspect_index;
END index_util;
/

EXADATA 1Z0-070 Oracle Exadata X5 Administration

->
->1. Exadata Database Machine Overview
->Identify the benefits of using Database Machine for different application classes
->Describe the key capacity and performance specifications for Database Machine
->
->2. Exadata Database Machine Architecture
->Describe the Database Machine network architecture and requirements
->Describe the Database Machine software architecture
->Describe the Exadata Storage Server storage entities and their relationships
->Describe how multiple Database Machines can be interconnected and scaled up
->
->3. Key Capabilities of Exadata Database Machine
->Describe the key capabilities of Exadata Database Machine
->Describe the Exadata Smart Scan capabilities
->Describe the capabilities of hybrid columnar compression
->Describe the capabilities and uses of the Smart Flash Cache
->Describe t he capabilities of Columnar Flash Caching
->Describe the capabilities of the Smart Flash Log
->Describe the purpose and benefits of Storage Indexes
->Describe the capabilities and uses of Exadata Snapshot databases
->Describe Cell to Cell Data Transfer and Exadata Network Resource Management
->
->4. Exadata Database Machine Initial Configuration
->Describe site planning requirements for Database Machine
->Describe the installation and configuration process with the Exadata Deployment Assistant
->Describe the default configuration for Database Machine
->Describe supported and unsupported customizations for Database Machine
->
->5. Configure Exadata Storage Server
->Configure Exadata software and resources using Cellcli or other tools
->Create and configure ASM disk groups using Exadata
->Use the CellCLI, ExaCLI, DCLI and ExaDCLI Exadata administration tools
->Configure Exadata Storage Server security
->
->6.I/O Resource Management
->Use Exadata Storage Server I/O Resource Management to manage workloads within a database and across multiple databases
->Configure database resource management plans and profiles
->Configure category plans
->Configure inter-database plans
->Describe and configure the I/O resource manager objectives
->Monitor I/O using I/O Metrics
->
->7.Recommendations for Optimizing Database Performance
->Optimize database performance in conjunction with Exadata Database Machine
->Optimize Cell performance for Flash Cache and latency capping
->
->8.Using Smart Scan
->Describe Smart Scan and the query processing that can be offloaded to Exadata Storage Server
->Describe the requirements for Smart Scan
->Describe the circumstances that prevent using Smart Scan
->Identify Smart Scan in SQL execution plans including smart joins
->Use database statistics and wait events to confirm how queries are processed
->
->9.Consolidation Options and Recommendations
->Describe the options for consolidating multiple databases on Database Machine
->Describe the benefits and costs associated with different options
->Identify the most appropriate approach for consolidation in different circumstances
->
->10. Migrating Databases to Exadata Database Machine
->Describe the steps to migrate your database to Database Machine
->Explain the main approaches for migrating your database to  Database Machine
->Identify the most appropriate approach for migration in different circumstances
->
->11. Bulk Data Loading
->Configure the Database File System (DBFS) feature for staging input data files
->Use external tables based on input data files stored in DBFS to perform high-performance data loads
->
->12. Exadata Database Machine Platform Monitoring
->Describe the purpose and uses of SNMP for the Database Machine
->Describe the purpose and uses of IPMI for the Database Machine
->Describe the purpose and uses of ILOM for the Database Machine
->
->13 Configuring Enterprise Manager Cloud Control 12c  to Monitor Exadata Database Machine
->Describe the Enterprise Manager cloud Control architecture as it specifically applies to Exadata Database Machine
->Describe the discovery process and post discovery configurations
->
->14 Monitoring Exadata Storage Servers
->Describe Exadata Storage Server metrics, alerts, Thresholds and active requests
->Describe and use Exedata Storage Server Quarantines, Disk Scrubbing and repair
->Monitor Exadata Storage Server using Command line or Cloud control
->
->15 Monitoring Exadata Database Machine Database Servers
->Describe the monitoring recommendations for Exadata Database Machine database servers
->Use Cloud Control or DBMCLI to monitor Exadata Database Machine Database Servers
->
->16  Monitoring the InfiniBand Network
->Monitor InfiniBand switches With Cloud Control or CLI
->Monitor InfiniBand switch ports
->Monitor InfiniBand ports on the database servers ajd Cells with LIST IBPORT command
->
->17 Monitoring other Exadata Database Machine Components
->Monitor Exadata Database Machine components: Cisco Switch, Power Distribution Units
->
->18 Monitoring Tools
->Use monitoring tools: Exachk, ExaWatcher, TFA Collector DiagTools, ADRCI, Imageinfo and Imagehistory, OSWatcher
->
->19 Backup and Recovery for Exadata Database Machines
->Describe how RMAN backups are optimized using Exadata Storage Server
->Describe the recommended approaches for disk-based and tape-based backups of databases on Database Machine
->Perform backup and recovery
->Connect a media server to the Database Machine InfiniBand network
->
->20 Database Machine Maintenance tasks
->Power Database Machine on and off
->Safely shut down a single Exadata Storage Server
->Replace a damaged physical disk on a cell
->Replace a damaged flash card on a cell
->Move all disks from one cell to another
->Use the Exadata Cell Software Rescure Procedure
->
->21 Patching Exadata Database Machine
->Describe how software is maintained on different Database Machine components
->
->22 Database Machine Automated Support Ecosystem
->Describe the Auto Service Request (ASR) function and how it relates to Exadata Database Machine
->Describe the implementation requirements for ASR
->Describe the ASR configuration process
->Describe Oracle Configuration Manager (OCM) and how it relates to Exadata Database Machine

Creating Range Partition on date column using numtodsinterval

==========CH_NOBOOK=====================================
Step 1: Rename CH_NOBOOK to CH_NOBOOK_TEMP.
SQL> RENAME CH_NOBOOK TO CH_NOBOOK_TEMP;


Step 2: Create CH_NOBOOK partitioned table.
CREATE TABLE CH_NOBOOK
   (    "DAT_TXN" DATE,
        "COD_CC_BRN_TXN" NUMBER(5,0),
        "COD_USERNO" NUMBER(10,0),
        "CTR_BATCH_NO" NUMBER(10,0),
        "REF_SYS_TR_AUD_NO" NUMBER(10,0),
        "REF_SUB_SEQ_NO" NUMBER(10,0),
        "COD_ACCT_NO" CHAR(48 CHAR),
        "TXT_TXN_DESC" VARCHAR2(120 CHAR),
        "DAT_VALUE" DATE,
        "REF_CHQ_NO" VARCHAR2(36 CHAR),
        "COD_DRCR" CHAR(3 CHAR),
        "COD_TXN_MNEMONIC" NUMBER(5,0),
        "COD_TXN_LITERAL" VARCHAR2(9 CHAR),
        "AMT_TXN" NUMBER DEFAULT 0,
        "FLG_PASBKUPD" CHAR(3 CHAR),
        "RAT_CCY" NUMBER,
        "COD_MSG_TYP" NUMBER(5,0),
        "COD_PROC" NUMBER(10,0),
        "CTR_UPDAT_SRLNO" NUMBER(10,0),
        "DAT_POST" DATE,
        "AMT_TXN_TCY" NUMBER DEFAULT 0,
        "COD_TXN_CCY" NUMBER(5,0),
        "RAT_CONV_TCLCY" NUMBER,
        "COD_SC" NUMBER(5,0),
        "COD_AUTH_ID" VARCHAR2(36 CHAR),
         CHECK (cod_drcr IN ('C','D')) ENABLE
   )  partition by range (dat_post)
   interval (numtodsinterval(1,'DAY'))
   (partition p0 values less  than 
    (to_date('31-OCT-2001','DD-MON-YYYY')));

Step 2: Insert into table and commit. 
    SQL> insert /*+append parallel(10) */ into RPRUSRBASE.CH_NOBOOK select /*+ parallel(10)*/  * from RPRUSRBASE.CH_NOBOOK_TEMP;
SQL> commit;

Steps 2.1: Drop Indexes on CH_NOBOOK

SQL> DROP INDEX RPRUSRBASE.IN_CH_NOBOOK_1;
SQL> DROP INDEX RPRUSRBASE.IN_CH_NOBOOK_2;
SQL> DROP INDEX RPRUSRBASE.IN_CH_NOBOOK_3;

Step 3: Create partitioned indexes.


 SQL> CREATE INDEX "RPRUSRBASE"."IN_CH_NOBOOK_1" ON "RPRUSRBASE"."CH_NOBOOK" ("COD_ACCT_NO", "DAT_TXN")
  PCTFREE 15 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 786432000 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FCUBSTBS" ;

 SQL>  CREATE INDEX "RPRUSRBASE"."IN_CH_NOBOOK_2" ON "RPRUSRBASE"."CH_NOBOOK" ("COD_ACCT_NO", "DAT_POST")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 534773760 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FCUBSTBS" ;
  
  SQL> CREATE INDEX "RPRUSRBASE"."IN_CH_NOBOOK_3" ON "RPRUSRBASE"."CH_NOBOOK" ("DAT_POST")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 282066944 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FCUBSTBS" LOCAL;

Step 4: Verification 
A. Count number of partitions in CH_NOBOOK,
SQ> select * from dba_tab_partitions where table_name='CH_NOBOOK_TEMP' and table_owner='RPRUSRBASE';

B. verify indexes
SQL> select * from dba_indexes where table_name='CH_NOBOOK_TEMP' and table_owner='RPRUSRBASE';

C. verify table row count between CH_NOBOOK and CH_NOBOOK_TEMP;
SQL> select /*+parallel(20) */  count(*) from RPRUSRBASE.CH_NOBOOK ;
SQL> select /*+parallel(20) */  count(*) from RPRUSRBASE.CH_NOBOOK_TEMP ;


Step5: Drop table CH_NOBOOK_TEMP.
SQL> DROP TABLE RPRUSRBASE.CH_NOBOOK_TEMP;

==========CH_NOBOOK_ORG=====================================
Step 1: Rename CH_NOBOOK_ORG to CH_NOBOOK_ORG_TEMP
SQL> RENAME CH_NOBOOK_ORG TO CH_NOBOOK_ORG_TEMP;

Step 2: Create CH_NOBOOK_ORG as partitioned table;

 SQL>  CREATE TABLE "CH_NOBOOK_ORG"
   (    "DAT_TXN" DATE,
        "COD_CC_BRN_TXN" NUMBER(5,0),
        "COD_USERNO" NUMBER(10,0),
        "CTR_BATCH_NO" NUMBER(10,0),
        "REF_SYS_TR_AUD_NO" NUMBER(10,0),
        "REF_SUB_SEQ_NO" NUMBER(10,0),
        "COD_ACCT_NO" CHAR(48 CHAR),
        "TXT_TXN_DESC" VARCHAR2(120 CHAR),
        "DAT_VALUE" DATE,
        "REF_CHQ_NO" VARCHAR2(36 CHAR),
        "COD_DRCR" CHAR(3 CHAR),
        "COD_TXN_MNEMONIC" NUMBER(5,0),
        "COD_TXN_LITERAL" VARCHAR2(9 CHAR),
        "AMT_TXN" NUMBER DEFAULT 0,
        "FLG_PASBKUPD" CHAR(3 CHAR),
        "RAT_CCY" NUMBER,
        "COD_MSG_TYP" NUMBER(5,0),
        "COD_PROC" NUMBER(10,0),
        "CTR_UPDAT_SRLNO" NUMBER(10,0),
        "DAT_POST" DATE,
        "AMT_TXN_TCY" NUMBER DEFAULT 0,
        "COD_TXN_CCY" NUMBER(5,0),
        "RAT_CONV_TCLCY" NUMBER,
        "COD_SC" NUMBER(5,0),
        "COD_AUTH_ID" VARCHAR2(36 CHAR)
   ) ENABLE ROW MOVEMENT    partition by range (dat_post)
   interval (numtodsinterval(1,'DAY'))
   (partition p0 values less  than 
    (to_date('07-DEC-2014','DD-MON-YYYY')));


Step 2: Insert into CH_NOBOOK_ORG from CH_NOBOOK_ORG_TEMP table.    
    insert /*+append parallel(10) */ into REPUSRBASE.CH_NOBOOK_ORG select /*+ parallel(10)*/  * from REPUSRBASE.CH_NOBOOK_ORG_TEMP;


Steps 2.1: Drop Indexes on CH_NOBOOK_ORG_TEMP

SQL> DROP INDEX REPUSRBASE.MIG_CH_NOBOOK_ORG_1;
SQL> DROP INDEX REPUSRBASE.IN_CH_NOBOOK_ORG_1;
SQL> DROP INDEX REPUSRBASE.IN_CH_NOBOOK_ORG_2;

Step 3: Create partitioned indexes.

  
  SQL> CREATE INDEX "REPUSRBASE"."MIG_CH_NOBOOK_ORG_1" ON "REPUSRBASE"."CH_NOBOOK_ORG" ("COD_ACCT_NO", "COD_TXN_MNEMONIC", "COD_PROC")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FCUBSTBS" LOCAL PARALLEL 10;

  
  
  SQL> CREATE INDEX "REPUSRBASE"."IN_CH_NOBOOK_ORG_1" ON "REPUSRBASE"."CH_NOBOOK_ORG" ("COD_ACCT_NO", "DAT_TXN")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 104857600 NEXT 31457280 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FCUBSTBS" LOCAL PARALLEL 10;

  SQL> CREATE INDEX "REPUSRBASE"."IN_CH_NOBOOK_ORG_2" ON "REPUSRBASE"."CH_NOBOOK_ORG" ("DAT_POST")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 104857600 NEXT 31457280 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FCUBSTBS" LOCAL PARALLEL 10;


Step 4: Verification 
A. Count number of partitions in CH_NOBOOK_ORG,
SQ> select * from dba_tab_partitions where table_name='CH_NOBOOK_ORG' and table_owner='REPUSRBASE';

B. verify indexes
SQL> select * from dba_indexes where table_name='CH_NOBOOK_ORG' and table_owner='REPUSRBASE';

C. verify table row count between CH_NOBOOK_ORG and CH_NOBOOK_ORG_TEMP;
SQL> select /*+parallel(20) */  count(*) from REPUSRBASE.CH_NOBOOK_ORG ;
SQL> select /*+parallel(20) */  count(*) from REPUSRBASE.CH_NOBOOK_ORG_TEMP ;


Step5: Drop table CH_NOBOOK_ORG_TEMP.
SQL> DROP TABLE REPUSRBASE.CH_NOBOOK_ORG_TEMP;