Oracle Direct NFS – Database 12c

On this post i will show you how to enable the database to use Direct NFS 
over Infiniband.
References:
https://docs.oracle.com/cd/E51475_01/html/E52872/integration__ssc__configure_appliance__configuring_oracle_direct_nfs_dnfs.html
Oracle ZFS Storage Appliance: How to Enable Oracle Intelligent Storage Protocol (OISP) (Doc ID 1943618.1)
Direct NFS monitoring and v$views (Doc ID 1495739.1)

**** BEFORE START:
----> SHUTDOWN THE INSTANCE(S)/LISTENERS UNDER THE ORACLE_HOME <-----


root@sun-prod02:~# ping -a pathA-ib ---> (This is the HOSTNAME or IP Address for the InfiniBand PATH)
pathA-ib (192.168.255.10) is alive

 
1.) Re-Link the binary to use DNFS
make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk dnfs_on
 
2.) Configure the file: oranfstab
Note: The export parameters should reflect the mount points similar to the entries 
created in /etc/vfstab. The entries should look similar to the following

cd $ORACLE_HOME/dbs
 
-bash-4.1$ cat oranfstab

server:pathA-ib
local:192.168.255.27
path:192.168.255.10
nfs_version:nfsv4
export:/export/sun-prod02/oracle mount:/oracle
export:/export/sun-prod02/export mount:/export
export:/export/sun-prod02/fast_recovery_area mount:/fast_recovery_area
export:/export/sun-prod02/oradata mount:/oradata
export:/export/sun-prod02/oralog mount:/oralog
management:192.168.255.10
community:comm0

3.) Restart the database
srvctl start database -d <DB Name>

4.) Check the Alert Log:

grep -i '"Sun ZFS"|Direct' alert_*.log
grep -i "Direct NFS:" alert_sun2.log

Thu Jan 12 14:15:44 2017
Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0

Direct NFS: channel id [0] path [192.168.255.10] to filer [pathA-ib] via local 
[192.168.255.27] is UP
Thu Jan 12 14:16:45 2017
Direct NFS: channel id [0] path [192.168.255.10] to filer [pathA-ib] via local 
[192.168.255.27] is UP
Thu Jan 12 14:16:45 2017
Direct NFS: channel id [0] path [192.168.255.10] to filer [pathA-ib] via local 
[192.168.255.27] is UP

5.) You can check inside of DB also using the views below:

Monitoring:

v$dnfs_servers - Displays information about the NFS servers accessed by Direct NFS.
v$dnfs_files - Displays information about the Oracle files opened using Direct NFS.
v$dnfs_channels - Displays information about the network connections (channels) 
open to the NFS servers.
v$dnfs_stats - Displays statistics of the different NFS operations issued by 
Direct NFS (dNFS does not expose statistics per mount point, but rather per 
process).

Flashback Database – Enable/Disable

Hi, this is the quick one, sometimes we forget the basic stuffs, so …
https://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#BRADV582

--- Enable Flashback Database:

select FLASHBACK_ON from v$database;

1.) Choose the DIRECTORY:
/u01/oradata/KDBPP/FRA

2.) SHUT Immediate;

3.) Startup mount;

4.) Enable Archivelog Mode, if needed

alter database archivelog;

5.) Define the FRA - Size:
alter system set db_recovery_file_dest_size=50g scope=both;

6.) Define the FRA - Directory:
alter system set db_recovery_file_dest='/u01/oradata/KDBPP/FRA' scope=both;

7.) Enable FLASHBACK on DATABASE:
alter database flashback on;

8.) Open database:
alter database open;

-- Disable:
1.) Shutdown Database;
shut immediate;

2.) Startup mount;

3.) alter database flashback off;

4.) If needed, disable the Archivelog
alter database noarchivelog;

5.) alter database open;

select FLASHBACK_ON from v$database;

--------------------------------------------------------------------
Note:

If you face this issue (ORA-38781: cannot disable media recovery - 
have guaranteed restore points) trying put back in NOARCHIVELOG mode.

You need drop the active restore point before, so :

a.) SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
        GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
        FROM V$RESTORE_POINT; 

b.) drop restore point <RESTORE POINT NAME>;

c.) if you are in MOUNT state you can execute:
alter database noarchivelog;


Oracle Database File System (DBFS) 11gR2

References:
https://oracle-base.com/articles/11g/dbfs-11gr2
DBFS resource not starting as crs resource (Doc ID 1908868.1)
https://oracle-base.com/articles/11g/dbfs-11gr2#creating_a_file_system
https://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_client.htm#ADLOB45998

Tested on:
Oracle Database 11g Enterprise Edition Release 2

x86_64 GNU/Linux

1.) Install Package on Linux as Root

yum install kernel-devel
yum install fuse fuse-libs

[root@lnx01 ~]# rpm -qa | grep kernel-devel
kernel-devel-2.6.32-573.22.1.el6.x86_64

root@lnx01 ~]# rpm -qa | grep fuse
fuse-libs-2.8.3-4.0.2.el6.x86_64
fuse-2.8.3-4.0.2.el6.x86_6

2.) Setup Database

sqlplus / as sysdba

create tablespace ts_dbfs datafile '/data03/db11//ts_dbfs_01.dbf' size 10m autoextend on next 10m;

create user dbfs_owner identified by dbfs default tablespace ts_dbfs;
alter user dbfs_owner quota unlimited on ts_dbfs;

GRANT CREATE SESSION, RESOURCE, CREATE VIEW, DBFS_ROLE TO dbfs_owner;

cd $ORACLE_HOME
cd rdbms
cd admin

sqlplus dbfs_owner/dbfs

@dbfs_create_filesystem.sql ts_dbfs FS_DBFS

3.) Mount

- As root:

mkdir -p /mnt/dbfs
chown oracle:oinstall /mnt/dbfs

usermod -a -G fuse oracle
id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),499(fuse)

export ORACLE_HOME=/oracle/product/11.2/db_1
echo "/usr/local/lib" >> /etc/ld.so.conf.d/usr_local_lib.conf
ln -s $ORACLE_HOME/lib/libclntsh.so.11.1 /usr/local/lib/libclntsh.so.11.1
ln -s $ORACLE_HOME/lib/libnnz11.so /usr/local/lib/libnnz11.so
ln -s /lib64/libfuse.so.2 /usr/local/lib/libfuse.so.2
ldconfig
ln -s $ORACLE_HOME/bin/dbfs_client /sbin/mount.dbfs
chmod +x /bin/fusermount

- As Oracle:
dbfs_client dbfs_owner@DB11 /mnt/dbfs

- In another session as Oracle User:

[oracle@lnx01 dbfs]$ pwd
/mnt/dbfs
[oracle@lnx01 dbfs]$ ls -ltra
total 4
drwxrwxrwx 3 root root 0 Apr 20 04:33 FS_DBFS
drwxr-xr-x. 3 root root 4096 Apr 20 05:08 ..
drwxr-xr-x 3 root root 0 Apr 20 05:28 .

- umount

fusermount -u /mnt/dbfs

4.) Using the DBFS (Password):

dbfs_client dbfs_owner@DB11 --command ls -a -l dbfs:/FS_DBFS/
Password:
drwxr-xr-x root root 0 Apr 20 04:33 .sfs

dbfs_client dbfs_owner@DB11 --command mkdir dbfs:/FS_DBFS/test1

dbfs_client dbfs_owner@DB11 --command ls -a -l dbfs:/FS_DBFS/
Password:
drwx------ oracle oinstall 0 Apr 20 05:34 dbfs:/FS_DBFS/test1
drwxr-xr-x root root 0 Apr 20 04:33 .sf

5.) Wallet

As you can see, every time the dbfs_client ask for password and to start the DBFS the session is in HOLD.
So, to avoid this problem let's use WALLET;

- Stop the session in HOLD, with CTRL+C, the filesystem will be stoped;

As Oracle user:

mkdir -p $HOME/oracle/wallet

$ORACLE_HOME/bin/mkstore -wrl $HOME/oracle/wallet -create
--> Passwords must have a minimum length of eight characters 
 and contain alphabetic characters combined with numbers 
 or special characters.

--> DB11 here is my TNS entry
mkstore -wrl $HOME/oracle/wallet -createCredential DB11 dbfs_owner dbfs

- Mounting the File System:

nohup dbfs_client -o wallet /@db11 /mnt/dbfs &

[oracle@lnx01 dbfs]$ ls -ltr
total 0
drwxrwxrwx 4 root root 0 Apr 20 05:34 FS_DBFS
[oracle@lnx01 dbfs]$ pwd
/mnt/dbfs

dbfs_client /@DB11 --command ls -a -l dbfs:/FS_DBFS/

drwx------ oracle oinstall 0 Apr 20 05:34 dbfs:/FS_DBFS/test1
drwxr-xr-x root root 0 Apr 20 04:33 .sfs

6.) MOUNT with FSTAB

- to umount: 
fusermount -u /mnt/dbfs

- Add on FSTAB:

/sbin/mount.dbfs#dbfs_owner@db11 /mnt/dbfs fuse rw,user,noauto,direct_io 0 0

- Create File:

cat /sbin/mount.dbfs

#!/bin/bash
export ORACLE_HOME=/oracle/product/11.2/db_1
nohup $ORACLE_HOME/bin/dbfs_client $@ << FEEDPWD &
dbfs
FEEDPWD

chmod 775 /sbin/mount.dbfs
chgrp fuse /sbin/mount.dbfs

- As ORACLE user
mount /mnt/dbfs
 
[oracle@lnx01 db_1]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 11G 7.2G 2.5G 75% /
tmpfs 2.9G 1.2G 1.8G 40% /dev/shm
/dev/sda1 479M 54M 396M 12% /boot
/dev/sdb1 30G 25G 3.4G 88% /oracle
/dev/sdd1 4.8G 3.2G 1.4G 71% /data01
/dev/sde1 9.8G 5.4G 3.9G 59% /data02
/dev/sdf1 20G 6.6G 13G 36% /data03
dbfs 5.3M 136K 5.2M 3% /mnt/dbfs <<-- DBFS

[oracle@lnx01 FS_DBFS]$ pwd
/mnt/dbfs/FS_DBFS
[oracle@lnx01 FS_DBFS]$ ls -ltr
total 0
drwx------ 2 oracle oinstall 0 Apr 20 05:34 test1
[oracle@lnx01 FS_DBFS]$

[oracle@lnx01 test1]$ touch myDBFS.txt
[oracle@lnx01 test1]$ ls -ltr
total 0
-rw-r--r-- 1 oracle oinstall 0 Apr 20 06:04 myDBFS.txt
[oracle@lnx01 test1]$ pwd
/mnt/dbfs/FS_DBFS/test1
[oracle@lnx01 test1]$

-- Now is possible use dbfs_client without password:

dbfs_client /@DB11 --command ls -a -l dbfs:/FS_DBFS/
drwx------ oracle oinstall 0 Apr 20 06:04 dbfs:/FS_DBFS/test1
drwxr-xr-x root root 0 Apr 20 04:33 .sfs

Online Move Datafile

Oracle Database 12c

--> Here i want move the datafiles from all PDBs to 
    the same location where the datafiles for the CDB are placed:

==> CDB:

  1* select file_name from dba_data_files
10:21:48 SQL> /

FILE_NAME
-----------------------------------------------
/data03/CDB/users01.dbf
/data03/CDB/undotbs01.dbf
/data03/CDB/system01.dbf
/data03/CDB/sysaux01.dbf

4 rows selected.

==> PDB:

10:22:17 SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB3                           READ WRITE

==> Creating directories:

cd /data03/CDB

mkdir PDB1
mkdir PDB3

1.) Connecting on the PDB1;

alter session set container=PDB1;

10:27:25 SQL>  SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') pdb from dual;

PDB
----------
PDB1

10:28:01 SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------
/oracle/oradata/CDB/pdb1/system01.dbf
/oracle/oradata/CDB/pdb1/sysaux01.dbf
/oracle/oradata/CDB/pdb1/ts_pdb1.dbf


2.) Moving datafiles:

alter database move datafile '/oracle/oradata/CDB/pdb1/system01.dbf' to '/data03/CDB/PDB1/system01.dbf';
alter database move datafile '/oracle/oradata/CDB/pdb1/sysaux01.dbf' to '/data03/CDB/PDB1/sysaux01.dbf';
alter database move datafile '/oracle/oradata/CDB/pdb1/ts_pdb1.dbf' to '/data03/CDB/PDB1/ts_pdb1.dbf';

3.) Checking:

10:30:34 SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------
/data03/CDB/PDB1/sysaux01.dbf
/data03/CDB/PDB1/ts_pdb1.dbf
/data03/CDB/PDB1/system01.dbf

3 rows selected.

10:30:42 SQL> !ls -ltr /oracle/oradata/CDB/pdb1/
total 520
-rw-r----- 1 oracle oinstall 20979712 Mar  7 12:49 pdbseed_temp012016-01-07_08-28-45-AM.dbf

4.) Connecting on the PDB3;

alter session set container=PDB3;
 
10:32:13 SQL>  SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') pdb from dual;

PDB
----------
PDB3

10:32:26 SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------
+DATA/CDB/PDB3/system01.dbf
+DATA/CDB/PDB3/sysaux01.dbf

5.) Moving datafiles, on this case from ASM:

alter database move datafile '+DATA/CDB/PDB3/system01.dbf' to '/data03/CDB/PDB3/system01.dbf';
alter database move datafile '+DATA/CDB/PDB3/sysaux01.dbf' to '/data03/CDB/PDB3/sysaux01.dbf';

6.) Checking:

10:35:22 SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------
/data03/CDB/PDB3/system01.dbf
/data03/CDB/PDB3/sysaux01.dbf

Virtual Columns

Tested on:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option


CREATE TABLE CMAGNO.TB_VIRTUAL 
(
  NUM NUMBER,
  TEXT VARCHAR2(200),
  VL_1 NUMBER,
  COL_V1     AS (VL_1 * 2),
  COL_V2     NUMBER GENERATED ALWAYS AS (VL_1 + (VL_1/2)) VIRTUAL,
  CONSTRAINT PK_TB_VIRTUAL PRIMARY KEY (NUM)
);

Table created.

CREATE INDEX CMAGNO.IX_TB_VIRTUAL_01 
ON CMAGNO.TB_VIRTUAL(COL_V1);

Index created.

CREATE INDEX CMAGNO.IX_TB_VIRTUAL_02 
ON CMAGNO.TB_VIRTUAL(COL_V2);

Index created.

desc CMAGNO.TB_VIRTUAL
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUM                                       NOT NULL NUMBER
 TEXT                                               VARCHAR2(200)
 VL_1                                               NUMBER
 COL_V1                                             NUMBER
 COL_V2                                             NUMBER

COLUMN data_default FORMAT A50
SELECT column_name, data_default, DATA_TYPE,HISTOGRAM
FROM   dba_tab_columns
WHERE  table_name = 'TB_VIRTUAL';

COLUMN_NAME                    DATA_DEFAULT                                       DATA_TYPE  HISTOGRAM
------------------------------ -------------------------------------------------- ---------- ---------------
COL_V2                         "VL_1"+"VL_1"/2                                    NUMBER     NONE
COL_V1                         "VL_1"*2                                           NUMBER     NONE
VL_1                                                                              NUMBER     NONE
TEXT                                                                              VARCHAR2   NONE
NUM                                                                               NUMBER     NONE


declare 
i number;
begin

for i in 1..1000 loop
    insert into cmagno.tb_virtual
    (NUM, TEXT, VL_1)
    values
    (i,'TEXT:'||TO_CHAR(I),I*10);
end loop;
commit;

end;
/

COL TEXT FORMAT A30

SELECT * FROM 
cmagno.tb_virtual
WHERE 
ROWNUM<10;

       NUM TEXT                                 VL_1     COL_V1     COL_V2
---------- ------------------------------ ---------- ---------- ----------
       342 TEXT:342                             3420       6840       5130
       343 TEXT:343                             3430       6860       5145
       344 TEXT:344                             3440       6880       5160
       345 TEXT:345                             3450       6900       5175
       346 TEXT:346                             3460       6920       5190
       347 TEXT:347                             3470       6940       5205
       348 TEXT:348                             3480       6960       5220
       349 TEXT:349                             3490       6980       5235
       350 TEXT:350                             3500       7000       5250


SET AUTOTRACE on explain

SELECT * 
FROM CMAGNO.TB_VIRTUAL
WHERE COL_V1=6840;

       NUM TEXT                                 VL_1     COL_V1     COL_V2
---------- ------------------------------ ---------- ---------- ----------
       342 TEXT:342                             3420       6840       5130


Execution Plan
----------------------------------------------------------
Plan hash value: 3962365088

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |   154 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_VIRTUAL       |     1 |   154 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TB_VIRTUAL_01 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL_V1"=6840)

Note
-----
   - dynamic sampling used for this statement (level=2)


 SET AUTOTRACE OFF;

CREATE VIEW CMAGNO.VW_TB_VIRTUAL
AS
SELECT NUM, TEXT,  COL_V1, COL_V1 * 2 COL_V1_DOUBLE
FROM
CMAGNO.TB_VIRTUAL;

View created.

DESC CMAGNO.VW_TB_VIRTUAL

 Name                Null?    Type
 ------------------- -------- -----------------
 NUM                 NOT NULL NUMBER
 TEXT                         VARCHAR2(200)
 COL_V1                       NUMBER
 COL_V1_DOUBLE                NUMBER

SELECT * FROM 
CMAGNO.VW_TB_VIRTUAL
WHERE ROWNUM < 10;

       NUM TEXT                               COL_V1 COL_V1_DOUBLE
---------- ------------------------------ ---------- -------------
       342 TEXT:342                             6840         13680
       343 TEXT:343                             6860         13720
       344 TEXT:344                             6880         13760
       345 TEXT:345                             6900         13800
       346 TEXT:346                             6920         13840
       347 TEXT:347                             6940         13880
       348 TEXT:348                             6960         13920
       349 TEXT:349                             6980         13960
       350 TEXT:350                             7000         14000

==> LIMITATIONS
       
UPDATE
CMAGNO.TB_VIRTUAL
SET COL_V1=1;     2    3
CMAGNO.TB_VIRTUAL
       *
ERROR at line 2:
ORA-54017: UPDATE operation disallowed on virtual columns

==> Virtual column can’t be used for:

    temporary table
    index-organized tables
    external objects
    clusters
   
==> Virtual column can be used for:

    heap-organized tables,
    partitioning,
    indexing,

In-Database Archiving

http://docs.oracle.com/database/121/VLDBG/GUID-5A76B6CE-C96D-49EE-9A89-0A2CB993A933.htm#VLDBG14154
Tested on:
Connected to:
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
1.) Create Table:
create sequence cmagno.seq_IDBARCH 
minvalue 1 maxvalue 9999999999 nocache order increment by 1;
CREATE TABLE cmagno.test_IDBARCH 
(  id_test          NUMBER,
 text VARCHAR2(100),
 dt date,
 CONSTRAINT test_IDBARCH PRIMARY KEY (id_test)
 )
 ROW ARCHIVAL;
declare
begin
for i in 1..100 loop
 insert into cmagno.test_IDBARCH 
 values 
(cmagno.seq_IDBARCH.nextval,'Test: ' || to_char(i), sysdate);
 end loop;
 commit;
 end;
 /
SQL> desc cmagno.test_IDBARCH
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 ID_TEST                                   NOT NULL NUMBER
 TEXT                                               VARCHAR2(100)
 DT                                                 DATE
SQL> select count(*) from cmagno.TEST_IDBARCH;
COUNT(*)
 ----------
 100
col column_name FORMAT A20
 col data_type FORMAT A20
 col table_name format a20
 col DATA_TYPE_OWNER format a20
SELECT
 OWNER,
 TABLE_NAME,
 COLUMN_NAME,
 HIDDEN_COLUMN,
 DATA_TYPE,
 DATA_TYPE_MOD,
 DATA_TYPE_OWNER,
 DATA_LENGTH,
 DATA_PRECISION,
 DATA_SCALE,
 NULLABLE
 FROM   dba_tab_cols
 WHERE  table_name = 'TEST_IDBARCH'
 ORDER BY column_id;
OWNER           TABLE_NAME           COLUMN_NAME          HID DATA_TYPE                 DATA_LENGTH DATA_PRECISION DATA_SCALE N
 --------------- -------------------- -------------------- --- -------------------- --- -------------------- ----------- -------------- ---------- -
 CMAGNO          TEST_IDBARCH         ID_TEST              NO  NUMBER                                         22                           N
 CMAGNO          TEST_IDBARCH         TEXT                 NO  VARCHAR2                                      100                           Y
 CMAGNO          TEST_IDBARCH         DT                   NO  DATE                                            7                           Y
 CMAGNO          TEST_IDBARCH         ORA_ARCHIVE_STATE    YES VARCHAR2                                     4000                           Y
select distinct ORA_ARCHIVE_STATE from cmagno.TEST_IDBARCH;
ORA_ARCHIVE_STATE
 --------------------
 0
1 row selected.
2.) Putting rows on invisible state:
update cmagno.TEST_IDBARCH
 set ORA_ARCHIVE_STATE=1
 where ID_TEST <51;
 commit;
select count(*), ORA_ARCHIVE_STATE
 from cmagno.TEST_IDBARCH
 group by ORA_ARCHIVE_STATE;
COUNT(*) ORA_ARCHIVE_STATE
 ---------- ------------------
 50 0
select count(*) from cmagno.TEST_IDBARCH;
COUNT(*)
 ----------
 50
3.) Showing Rows
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
select count(*) from cmagno.TEST_IDBARCH;
COUNT(*)
 ----------
 100
1 row selected.
select count(*), ORA_ARCHIVE_STATE
 from cmagno.TEST_IDBARCH
 group by ORA_ARCHIVE_STATE;
COUNT(*) ORA_ARCHIVE_STATE
 ---------- --------------------
 50 1
 50 0
2 rows selected.
To put rows back in the INVISIBLE state:
 ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

					

DBMS_REDEFINITION – Partitioned Table

CREATE TABLE DBATST.TEMP_TABLE_INTERIM
 (
 "TLO_ID"             NUMBER ,
 "MARKUP_TIMESTAMP"     TIMESTAMP (6),
 "TRACE_LEVEL"         NUMBER(1,0),
 "TRACE_TEXT"         VARCHAR2(4000 CHAR),
 "CREATED_BY"         VARCHAR2(64 CHAR),
 "CREATED_DATE"         DATE,
 "MODIFIED_BY"         VARCHAR2(64 CHAR),
 "MODIFIED_DATE"     DATE,
 "PRC_ID"             NUMBER
 )
 PARTITION BY RANGE (PRC_ID) INTERVAL (5000)
 ( PARTITION BEFORE_PRC_ID_5000 VALUES LESS THAN (5000))
 tablespace USERS ;
set pages 10000
 set head on
 set lin 150
 col owner format a10
 col table_name format a20
 col partitioned format a12
spool REDEF_TEMP_TABLE.log
 alter session set db_file_multiblock_read_count=128;
select owner, table_name, partitioned
 from dba_tables where table_name like 'TEMP_TABLE%';
 set head off
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') START_PROCESS from dual;
prompt "COUNTING ROWS table TEMP_TABLE before REDEF...";
create table tt_check_rows_trc
 (rows_before number, rows_after number,dt_ini date,dt_fin date);
insert into tt_check_rows_trc (rows_before,dt_ini)
 select count(*) ROWS_ORIG_TABLE,sysdate from DBATST.TEMP_TABLE;
 commit;
-- 1.) NOLOGGING
 prompt "Nologging INTERIM table ...";
 alter table DBATST.TEMP_TABLE_INTERIM nologging;
-- 2.) Checking if can redef:
 prompt "Executing CAN_REDEF table ...";
 EXEC DBMS_REDEFINITION.can_redef_table('DBATST', 'TEMP_TABLE');
-- 3.) Start REDEF
 prompt "Executing START REDEF table ...";
 BEGIN
 DBMS_REDEFINITION.start_redef_table(
 uname      => 'DBATST',
 orig_table => 'TEMP_TABLE',
 int_table  => 'TEMP_TABLE_INTERIM');
 END;
 /
-- 4.) Sync TABLE
 prompt "Executing SYNC table ...";
 BEGIN
 dbms_redefinition.sync_interim_table(
 uname      => 'DBATST',
 orig_table => 'TEMP_TABLE',
 int_table  => 'TEMP_TABLE_INTERIM');
 END;
 /
-- 5.) Copy Dependencies
 prompt "Executing COPY DEPENDENTS table ...";
 SET SERVEROUTPUT ON
 DECLARE
 l_errors  NUMBER;
 BEGIN
 DBMS_REDEFINITION.copy_table_dependents(
 uname            => 'DBATST',
 orig_table       => 'TEMP_TABLE',
 int_table        => 'TEMP_TABLE_INTERIM',
 copy_indexes     => DBMS_REDEFINITION.cons_orig_params,
 copy_triggers    => TRUE,
 copy_constraints => TRUE,
 copy_privileges  => TRUE,
 ignore_errors    => FALSE,
 num_errors       => l_errors,
 copy_statistics  => FALSE,
 copy_mvlog       => FALSE);
if l_errors <> 0 then
 raise_application_error(-20001,'Error on DBMS_REDEFINITION.copy_table_dependents' ||SQLCODE||' - ERROR - '||SQLERRM);
 end if;
END;
 /
-- 7.) Sync TABLE
prompt "Executing SYNC table ...";
 BEGIN
 dbms_redefinition.sync_interim_table(
 uname      => 'DBATST',
 orig_table => 'TEMP_TABLE',
 int_table  => 'TEMP_TABLE_INTERIM');
 END;
 /
-- 8.) Finishing REDEF
prompt "Executing FINISH REDEF table ...";
 BEGIN
 dbms_redefinition.finish_redef_table(
 uname      => 'DBATST',
 orig_table => 'TEMP_TABLE',
 int_table  => 'TEMP_TABLE_INTERIM');
 END;
 /
-- 9.) Logging table
 prompt "Executing LOGGING table ...";
 alter table DBATST.TEMP_TABLE logging;
-- 10.) Finishing the process
 prompt "COUNTING ROWS table TEMP_TABLE after REDEF...";
 set head on
update tt_check_rows_trc
 set rows_after=(select count(*) ROWS_REDEF_TABLE 
                  from DBATST.TEMP_TABLE), dt_fin=sysdate;
 commit;
 select 
to_char(dt_ini,'dd-mm-yyyy hh24:mi:ss') Start_Date, 
rows_before,to_char(dt_fin,'dd-mm-yyyy hh24:mi:ss') End_Date, 
rows_after 
from tt_check_rows_trc;
 select 
to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') END_PROCESS 
from dual;
 select owner, table_name, partitioned 
from dba_tables 
where table_name like 'TEMP_TABLE%';
drop table tt_check_rows_trc;
 spool off
 prompt " LOG of this execution on  REDEF_TEMP_TABLE.log"
 prompt " End of process"
 exit