[ORACLE DB] Create a job, that autos gathering tablespace space

/* Formatted on 8/15/2017 1:40:28 PM (QP5 v5.227.12220.39724) */
--DBA_TABLESPACE_HIST--


CREATE TABLE DBA_TABLESPACE_HIST
(
   DATE_HIST         DATE,
   TABLESPACE_NAME   VARCHAR2 (20),
   STATUS            VARCHAR2 (20),
   CONTENTS          VARCHAR2 (20),
   MEGS_ALLOC        NUMBER,
   MEGS_FREE         NUMBER,
   MEGS_USED         NUMBER,
   PCT_FREE          NUMBER,
   PCT_USED          NUMBER,
   MAX               NUMBER
);

--CREATE PROCEDURE



CREATE OR REPLACE PROCEDURE PROC_GATHER_TABLESPACE
IS
   V_DATE_HIST         DBA_TABLESPACE_HIST.DATE_HIST%TYPE;
   V_TABLESPACE_NAME   DBA_TABLESPACE_HIST.TABLESPACE_NAME%TYPE;
   V_STATUS            DBA_TABLESPACE_HIST.STATUS%TYPE;
   V_CONTENTS          DBA_TABLESPACE_HIST.CONTENTS%TYPE;
   V_MEGS_ALLOC        DBA_TABLESPACE_HIST.MEGS_ALLOC%TYPE;
   V_MEGS_FREE         DBA_TABLESPACE_HIST.MEGS_FREE%TYPE;
   V_MEGS_USED         DBA_TABLESPACE_HIST.MEGS_USED%TYPE;
   V_PCT_FREE          DBA_TABLESPACE_HIST.PCT_FREE%TYPE;
   V_PCT_USED          DBA_TABLESPACE_HIST.PCT_USED%TYPE;
   V_MAX               DBA_TABLESPACE_HIST.MAX%TYPE;

   CURSOR C_GATHER
   IS
        SELECT TS.TABLESPACE_NAME,
               TS.STATUS,
               TS.CONTENTS,
               SIZE_INFO.MEGS_ALLOC,
               SIZE_INFO.MEGS_FREE,
               SIZE_INFO.MEGS_USED,
               SIZE_INFO.PCT_FREE,
               SIZE_INFO.PCT_USED,
               SIZE_INFO.MAX
          FROM (SELECT A.TABLESPACE_NAME,
                       ROUND (A.BYTES_ALLOC / 1024 / 1024) MEGS_ALLOC,
                       ROUND (NVL (B.BYTES_FREE, 0) / 1024 / 1024) MEGS_FREE,
                       ROUND (
                          (A.BYTES_ALLOC - NVL (B.BYTES_FREE, 0)) / 1024 / 1024)
                          MEGS_USED,
                       ROUND ( (NVL (B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100)
                          PCT_FREE,
                         100
                       - ROUND ( (NVL (B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100)
                          PCT_USED,
                       ROUND (MAXBYTES / 1048576) MAX
                  FROM (  SELECT F.TABLESPACE_NAME,
                                 SUM (F.BYTES) BYTES_ALLOC,
                                 SUM (
                                    DECODE (F.AUTOEXTENSIBLE,
                                            'YES', F.MAXBYTES,
                                            'NO', F.BYTES))
                                    MAXBYTES
                            FROM DBA_DATA_FILES F
                        GROUP BY TABLESPACE_NAME) A,
                       (  SELECT F.TABLESPACE_NAME, SUM (F.BYTES) BYTES_FREE
                            FROM DBA_FREE_SPACE F
                        GROUP BY TABLESPACE_NAME) B
                 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
                UNION ALL
                  SELECT H.TABLESPACE_NAME,
                         ROUND (SUM (H.BYTES_FREE + H.BYTES_USED) / 1048576)
                            MEGS_ALLOC,
                         ROUND (
                              SUM (
                                   (H.BYTES_FREE + H.BYTES_USED)
                                 - NVL (P.BYTES_USED, 0))
                            / 1048576)
                            MEGS_FREE,
                         ROUND (SUM (NVL (P.BYTES_USED, 0)) / 1048576) MEGS_USED,
                         ROUND (
                              (  SUM (
                                      (H.BYTES_FREE + H.BYTES_USED)
                                    - NVL (P.BYTES_USED, 0))
                               / SUM (H.BYTES_USED + H.BYTES_FREE))
                            * 100)
                            PCT_FREE,
                           100
                         - ROUND (
                                (  SUM (
                                        (H.BYTES_FREE + H.BYTES_USED)
                                      - NVL (P.BYTES_USED, 0))
                                 / SUM (H.BYTES_USED + H.BYTES_FREE))
                              * 100)
                            PCT_USED,
                         ROUND (SUM (F.MAXBYTES) / 1048576) MAX
                    FROM SYS.V_$TEMP_SPACE_HEADER H,
                         SYS.V_$TEMP_EXTENT_POOL P,
                         DBA_TEMP_FILES F
                   WHERE     P.FILE_ID(+) = H.FILE_ID
                         AND P.TABLESPACE_NAME(+) = H.TABLESPACE_NAME
                         AND F.FILE_ID = H.FILE_ID
                         AND F.TABLESPACE_NAME = H.TABLESPACE_NAME
                GROUP BY H.TABLESPACE_NAME) SIZE_INFO,
               SYS.DBA_TABLESPACES TS
         WHERE TS.TABLESPACE_NAME = SIZE_INFO.TABLESPACE_NAME
      ORDER BY TABLESPACE_NAME;

BEGIN
   OPEN C_GATHER;

   LOOP
      FETCH C_GATHER
         INTO V_TABLESPACE_NAME,
              V_STATUS,
              V_CONTENTS,
              V_MEGS_ALLOC,
              V_MEGS_FREE,
              V_MEGS_USED,
              V_PCT_FREE,
              V_PCT_USED,
              V_MAX;

      EXIT WHEN C_GATHER%NOTFOUND;

      INSERT INTO DBA_TABLESPACE_HIST
           VALUES (SYSDATE,
                   V_TABLESPACE_NAME,
                   V_STATUS,
                   V_CONTENTS,
                   V_MEGS_ALLOC,
                   V_MEGS_FREE,
                   V_MEGS_USED,
                   V_PCT_FREE,
                   V_PCT_USED,
                   V_MAX);

      COMMIT;
   END LOOP;

   CLOSE C_GATHER;
END;
/

--CREATE JOB RUN DAILY AT 6 AM --

BEGIN
   SYS.DBMS_SCHEDULER.CREATE_JOB (
      job_name          => 'SYS.JOB_GATHER_TABLESPACE',
      start_date        => NULL,
      repeat_interval   => 'FREQ=DAILY;INTERVAL=1;BYHOUR=6;BYMINUTE=0;BYSECOND=0',
      end_date          => NULL,
      job_class         => 'DEFAULT_JOB_CLASS',
      job_type          => 'STORED_PROCEDURE',
      job_action        => 'SYS.PROC_GATHER_TABLESPACE',
      comments          => NULL);
   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (name        => 'SYS.JOB_GATHER_TABLESPACE',
                                     attribute   => 'RESTARTABLE',
                                     VALUE       => FALSE);
   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL (
      name        => 'SYS.JOB_GATHER_TABLESPACE',
      attribute   => 'MAX_FAILURES');
   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL (
      name        => 'SYS.JOB_GATHER_TABLESPACE',
      attribute   => 'MAX_RUNS');

   BEGIN
      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (name        => 'SYS.JOB_GATHER_TABLESPACE',
                                        attribute   => 'STOP_ON_WINDOW_CLOSE',
                                        VALUE       => FALSE);
   EXCEPTION
      -- could fail if program is of type EXECUTABLE...
      WHEN OTHERS
      THEN
         NULL;
   END;

   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (name        => 'SYS.JOB_GATHER_TABLESPACE',
                                     attribute   => 'JOB_PRIORITY',
                                     VALUE       => 3);
   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL (
      name        => 'SYS.JOB_GATHER_TABLESPACE',
      attribute   => 'SCHEDULE_LIMIT');
   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (name        => 'SYS.JOB_GATHER_TABLESPACE',
                                     attribute   => 'AUTO_DROP',
                                     VALUE       => FALSE);
   SYS.DBMS_SCHEDULER.ENABLE (name => 'SYS.JOB_GATHER_TABLESPACE');
END;
/




BEGIN
  SYS.DBMS_SCHEDULER.RUN_JOB
    (job_name             => 'SYS.JOB_GATHER_TABLESPACE'
    ,use_current_session  => FALSE);
END;

/
--select full table--

select * from sys.DBA_TABLESPACE_HIST;

--select by date--

SELECT *
  FROM SYS.DBA_TABLESPACE_HIST
 WHERE TO_CHAR (TO_DATE (DATE_HIST, 'dd-mm-yyyy')) = '15-AUG-17';

--Sum by Date not including UNDO,TEMP, SYS%--

  SELECT TO_CHAR (TRUNC (DATE_HIST), 'DD-MM-YYYY') "Date",SUM (MEGS_USED) "Total_Used(MB)"
    FROM SYS.DBA_TABLESPACE_HIST
   WHERE    TABLESPACE_NAME NOT LIKE 'UNDO%'
         OR TABLESPACE_NAME NOT LIKE 'TEMP%'
         OR TABLESPACE_NAME NOT LIKE 'SYS%'
GROUP BY TRUNC (DATE_HIST)
ORDER BY TRUNC (DATE_HIST) ASC;
 

[IBM AIX] How to configure the NTP Client (The same for NTP Server) in AIX servers.

1. Stop the ntp services

          #stopsrc –s xntpd

2. Sync the time with ntp, login as root, type

          #ntpdate <ip_address of the NTP server>

          #ntpdate –d <ip_address of the NTP server>

3. Make a copy of the file /etc/ntp.conf

          #cp /etc/ntp.conf /etc/ntp.conf_bak

4. Edit file /etc/ntp.conf and append

          #broadcastclient --Comment out

          server <ip_address of the NTP server> prefer

server <ip_address of the NTP server>

          driftfile /etc/ntp.drift

          tracefile /etc/ntp.trace

6. Create the file /etc/ntp.drift with the following entry:

          0.0

7. After creating the drift file, ensure that it has the right ownership and permissions:

          -rw-r--r-- 1 ntp ntp 6 Mar 2 11:09 drift

8. Start the NTP sercice

          #startsrc –s xntpd

NOTE: If the server runs databases, use the -x flag to prevent the clock from changing in a negative direction. Enter the following

           # startsrc -s xntpd -a "-x"

9. Take backup and uncomment xntpd from /etc/rc.tcpip. so it will start on a reboot.

          #cp –p /etc/rc.tcpip /etc/rc.tcpip_bak

          #vi /etc/rc.tcpip

          Uncomment the following line

          Start /usr/sbin/xntpd “$src-running”

10. Verify that the server is synched.

          #lssrc -ls xntpd

          ntpdate –q <ntpserver> should not show the time difference more than 1 sec.

Reference:

http://www-01.ibm.com/support/docview.wss?uid=isg3T1000653

https://www.ibm.com/support/knowledgecenter/en/ssw_aix_61/com.ibm.aix.files/aixfiles191.htm

broadcastclient

Specifies that the local server listen for broadcast messages on the local network in order to discover other servers on the same subnet. When the local server hears a broadcast message for the first time, it measures the nominal network delay using a brief client/server exchange with the remote server, then enters the broadcastclient mode, where it listens for and synchronizes to succeeding broadcast messages.

[ORACLE - GRID] How to create an app HA on GRID

1/ Create VIP IP

/u01/app/grid/bin/appvipcfg create -network=1 -ip=192.168.0.220 -vipname=ggate_vip -user=root

2/ Set Grid can manage VIP IP


/u01/app/grid/bin/crsctl setperm resource ggate_vip -u user:oracle:r-x


3/ Start VIP IP


/u01/app/grid/bin/crsctl start resource ggate_vip

4/ Create Script Manage Resource


/u01/app/grid/bin/crsctl add resource ggate_mgr -type cluster_resource -attr "ACTION_SCRIPT=/u01/app/grid/crs/script/GoldenGate_action.scr, CHECK_INTERVAL=30, START_DEPENDENCIES='hard(ggate_vip,ora.racdb.db) pullup(ggate_vip)'STOP_DEPENDENCIES='hard(ggate_vip)' SCRIPT_TIMEOUT=300, RESTART_ATTEMPTS=6"


5/ set Permisssion


/u01/app/grid/bin/crsctl setperm resource ggate_mgr -o ggate


6/ Stop ggs and start ggs by grid command


GGSCI> STOP MGR

/u01/app/grid/bin/crsctl start resource ggate_mgr

[ORACLE 11G] Automatic Oracle startup on AIX

1/ Make a script to stop/start Oracle DB and grant execution:

#vi /etc/oracleservice.sh:

####################################

#!/bin/ksh
#
# Description: Starts and stops the Oracle database and listeners
case "$1" in
  start)
        echo "Starting Oracle Databases: "
        su - oracle -c dbstart
        echo "Done."
        echo "Starting Oracle Listeners: "
        su - oracle -c "lsnrctl start"
        echo "Done."
        ;;
  stop)
        echo "Shutting Down Oracle Listeners: "
        su - oracle -c "lsnrctl stop"
        echo "Done."
        echo "Shutting Down Oracle Databases: "
        su - oracle -c dbshut
        echo "Done."
        ;;
  restart)
        echo "Restarting Oracle Databases: "
        su - oracle -c dbstop
        su - oracle -c dbstart
        echo "Done."
        echo "Restarting Oracle Listeners: "
        su - oracle -c "lsnrctl stop"
        su - oracle -c "lsnrctl start"
        echo "Done."
        ;;
  *)
        echo "Usage: oracle {start|stop|restart}"
        exit 1
esac

####################################


#chmod a+x /etc/oracleservice.sh

2/ Define new service start with IBM AIX OS startup:


vi /etc/rc.oracle

####################################

#!/bin/ksh
/etc/oracleservice.sh start

####################################

chmod a+x /etc/rc.oracle


3/ Create inittab entry:

#mkitab oracleservice:2:wait:/etc/rc.oracle 2>&1 | alog -tboot > /dev/console

4/ config oratab point to database need start:


#vi /etc/oratab

HELPDESK:/u01/app/oracle/product/11.2.0/dbhome_1:Y

[ORACLE 11G] dbca Silent Mode

dbca -silent -createDatabase -templateName /u01/app/oracle/products/11gr2/assistants/dbca/templates/General_Purpose.dbc -gdbName PAYGATE -sid PAYGATE  -sysPassword oracle -systemPassword oracle -emConfiguration NONE -datafileDestination /datafs -characterSet AL32UTF8 -nationalCharacterSet UTF8

#######

$ ssword oracle -emConfiguration NONE -datafileDestination /datafs -characterSet AL32UTF8 -nationalCharacterSet UTF8      <
Copying database files
1% complete
3% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/products/cfgtoollogs/dbca/PAYGATE/PAYGATE0.log" for further details.

########


dbca -silent -deleteDatabase -sourceDB PAYGATE

#####

$ dbca -silent -deleteDatabase -sourceDB PAYGATE

Connecting to database

4% complete

9% complete

14% complete

19% complete

23% complete

28% complete

47% complete

Updating network configuration files

48% complete

52% complete

Deleting instance and datafiles

76% complete

100% complete

Look at the log file "/u01/app/oracle/products/cfgtoollogs/dbca/PAYGATE.log" for further details.

#####

[ORACLE 11G] Clone Oracle Software

Node Source[May tar when instance is runnning]:

1/ cd /u01/app/oracle/product/11.2.0/
2/ tar -cvf dbhome_1.tar dbhome_1
3/ scp dbhome_1.tar root@10.1.14.173:/u01/app/oracle/product/11.2.0

Node Target:

1/ cd /u01/app/oracle/product/11.2.0
2/ tar -xvf dbhome_1.tar
3/ dc_ptud_test02:oracle11:/u01/app/oracle/product/11.2.0/dbhome_1>cat oraInst.loc

inventory_loc=/u01/app/oraInventory
inst_group=oinstall


4/ mkdir -p /u01/app/oraInventory/ContentsXML
5/ cd /u01/app/oraInventory/ContentsXML
6/ vi inventory.xml


<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2013, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>11.2.0.4.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraDb11g_home1" LOC="/u01/app/oracle/product/11.2.0/dbhome_1" TYPE="O" IDX="1">
</HOME>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>

7/ ln -s /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc /etc/oraInst.loc
8/ cd $ORACLE_HOME/oui/bin
9/ ./runInstaller -detachHome ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
10/ cd $ORACLE_HOME/clone/bin
11/ dc_ptud_test02:oracle11:/u01/app/oracle/product/11.2.0/dbhome_1/clone/bin>perl clone.pl ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 ORACLE_HOME_NAME=OraDb11g_home1
********************************************************************************

Your platform requires the root user to perform certain pre-clone
OS preparation.  The root user should run the shell script 'rootpre.sh' before
you proceed with cloning.  rootpre.sh can be found at
/u02/app/oracle/product/11.2.0/dbhome_1/clone directory.
Answer 'y' if the root user has run 'rootpre.sh' script.

********************************************************************************

Has 'rootpre.sh' been run by the root user? [y/n] (n)
y
./runInstaller -clone -waitForCompletion  "ORACLE_BASE=/u01/app/oracle" "ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1" "ORACLE_HOME_NAME=OraDb11g_home1" -silent -noConfig -nowait
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 32768 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-05-26_11-03-56AM. Please wait ...Oracle Universal Installer, Version 11.2.0.4.0 Production
Copyright (C) 1999, 2013, Oracle. All rights reserved.

You can find the log of this install session at:
 /u01/app/oracle/products/oraInventory/logs/cloneActions2017-05-26_11-03-56AM.log
.................................................................................................... 100% Done.



Installation in progress (Friday, May 26, 2017 11:04:21 AM GMT+07:00)
..............................................................................                                                  78% Done.
Install successful

Linking in progress (Friday, May 26, 2017 11:04:27 AM GMT+07:00)
Link successful

Setup in progress (Friday, May 26, 2017 11:05:27 AM GMT+07:00)
Setup successful

End of install phases.(Friday, May 26, 2017 11:05:51 AM GMT+07:00)
WARNING:
The following configuration scripts need to be executed as the "root" user.
/u01/app/oracle/product/11.2.0/dbhome_1/root.sh
To execute the configuration scripts:
    1. Open a terminal window
    2. Log in as "root"
    3. Run the scripts
   
The cloning of OraDb11g_home1 was successful.
Please check '/u01/app/oracle/products/oraInventory/logs/cloneActions2017-05-26_11-03-56AM.log' for more details.

12/ Run script in root user: /u01/app/oracle/product/11.2.0/dbhome_1/root.sh

[ORACLE 11G] trcroute utility - Check route, listener to Oracle Listener Server

dbsvr@oracle:/home/oracle> trcroute 10.1.13.97

Trace Route Utility for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 20-MAY-2017 11:10:55

Copyright (c) 1995, 2013, Oracle.  All rights reserved.

Route of TrcRoute:
------------------

Node: Client            Time and address of entry into node:
-------------------------------------------------------------
20-MAY-2017 11:10:55 ADDRESS= PROTOCOL=TCP  HOST=10.1.13.97  PORT=1521

Node: Server            Time and address of entry into node:
-------------------------------------------------------------
20-MAY-2017 11:10:55 ADDRESS= PROTOCOL=TCP  HOST=10.1.13.97  PORT=1521

[ORACLE 11G] adrci show alert -tail -f

[oracle@fccnode1:/home/oracle]# adrci

ADRCI: Release 11.2.0.4.0 - Production on Thu Apr 13 11:10:57 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci> show homes
ADR Homes:
diag/rdbms/fccprod/FCCPROD1
diag/rdbms/fccprod/FLEXPROD1
diag/rdbms/fccprod/FLEXPROD_DR
diag/rdbms/flexprod_dr/FLEXPROD_DR
diag/tnslsnr/fccnode1/listener
adrci> set homes diag/rdbms/fccprod/FCCPROD1
adrci> show alert -tail -f

-----

ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/fccprod/FCCPROD1/trace/FCCPROD1_lgwr_9306700.trc:
ORA-00313: open failed for members of log group 14 of thread 1
ORA-00312: online log 14 thread 1: '/datafs/BORPT02/stb_redo05.f'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/fccprod/FCCPROD1/trace/FCCPROD1_lgwr_9306700.trc:
ORA-00313: open failed for members of log group 14 of thread 1
ORA-00312: online log 14 thread 1: '/datafs/BORPT02/stb_redo05.f'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/fccprod/FCCPROD1/trace/FCCPROD1_lgwr_9306700.trc:


-----

CTRL + C to exit

[ORACLE 11G] DBMS_REDEFINITION Table Online.

Step 1: Verify that the table is a candidate for online redefinition

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE ('SCHEMA', 'TABLE_ORGIN', DBMS_REDEFINITION.CONS_USE_PK);
END;
/


Step 2: Create an interim table


create table SCHEMA.TABLE_INTERIM
partition by hash(zip) partitions 8
as
select * from movies.customer where 1=0;


---1. Select * from table where 1=0  --false condition return just header of the fields (attribute) but with 0 rows
---2. Select * from table where 1=1 --true condition return whole table entries this is same as select * from table

Step 3: Enable parallel DML operations

alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;

Step 4: Start the redefinition process

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE('SCHEMA','TABLE_ORIGIN','TABLE_INTERIM');
END;
/

dbms_redefinition.start_redef_table('SCHEMA','TABLE_ORIGIN','TABLE_INTERIM','COLUM1_SRC COLUM1_DEST,COLUM2_SRC COLUM2_DEST');



Step 5: Copy dependent objects

DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('SCHEMA','TABLE_ORIGIN','TABLE_INTERIM',DBMS_REDEFINITION.CONS_ORIG_PARAMS,TRUE,TRUE,TRUE,TRUE,num_errors);
END;


Step 6: Check for any errors

select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;


Step 7: Synchronize the interim table (optional)

BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('SCHEMA', 'TABLE_ORIGIN', 'TABLE_INTERIM');
END;
/

Step 8: Complete the redefinition

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE ('SCHEMA', 'TABLE_ORIGIN', 'TABLE_INTERIM');
END;
/

Step 9: Drop the interim table

drop table SCHEMA.TABLE_INTERIM cascade constraints purge;

Step 10: Analyze Table

BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
      OwnName        => 'SCHEMA'
     ,TabName        => 'TABLE_ORIGIN'
    ,Estimate_Percent  => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
    ,Method_Opt        => 'FOR ALL COLUMNS SIZE 1'
    ,Degree            => 4
    ,Cascade           => TRUE
    ,No_Invalidate     => FALSE);
END;
/

[ORA 11GR2 12C] Initial Statspack

If you don’t have a license to use the AWR, ADDM, and ASH reports, the free Statspack utility can help you identify poorly performing SQL statements. Run the following script as SYS to install Statspack: 

SQL> @?/rdbms/admin/spcreate.sql
SQL> @ ?/rdbms/admin/spauto.sql  

SQL> @?/rdbms/admin/spreport.sql