External Table – Compressed Files

https://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN12898
Below the example with External tables using PREPROCESSOR option,
this is usefull where you have compressed files. The example has been
done with Pluggable Database.

Files:
C1.txt
C2.txt
ENG,England,English
SCO,Scotland,English
IRE,Ireland,English
WAL,Wales,Welsh
FRA,France,French
GER,Germany,German
USA,Unites States of America,English
 gzip c1.txt c2.txt

-rw-r--r--. 1 oracle oinstall       77 Dec 12 16:00 c1.txt.gz
-rw-r--r--. 1 oracle oinstall       92 Dec 12 16:00 c2.txt.gz

sqlplus / as sysdba
SQL> alter session set container=PDBPROD2;
Session altered.
  • Create a directory where the DATA is placed:
    SQL> create or replace directory dir_dba as '/home/oracle/dba';
    
    Directory created.
    
    SQL> grant read , write on directory dir_dba to public;
    
    Grant succeeded.
  • Create a directory pointing to the Utility that will be used to
    UNCOPRESS the file:
    
    [oracle@vm1 dba]$ which gunzip
    /usr/bin/gunzip
    
    [oracle@vm1 dba]$ which zcat
    
    /usr/bin/zcat
    
    SQL> create or replace directory dir_bin as '/usr/bin';
    
    Directory created.
    
    SQL> grant read, write on directory dir_bin to public;
    
    Grant succeeded.
    
    Drop table cmagno.countries_ext;
    
    CREATE TABLE cmagno.countries_ext (
      country_code      VARCHAR2(5),
      country_name      VARCHAR2(50),
      country_language  VARCHAR2(50)
    )
    ORGANIZATION EXTERNAL (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY dir_dba
      ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        PREPROCESSOR dir_bin:'zcat'
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
        (
          country_code      CHAR(5),
          country_name      CHAR(50),
          country_language  CHAR(50)
        )
      )
      LOCATION ('c1.txt.gz','c1.txt.gz')
    )
    PARALLEL 1
    REJECT LIMIT UNLIMITED;
    
    SQL> select * from cmagno.countries_ext;
    
    COUNT COUNTRY_NAME                                   COUNTRY_LANGUAGE
    ----- -------------------------------------------------- -----------------
    ENG   England                                        English
    SCO   Scotland                                       English
    IRE   Ireland                                        English
    WAL   Wales                                          Welsh
    ENG   England                                        English
    SCO   Scotland                                       English
    IRE   Ireland                                        English
    WAL   Wales                                          Welsh
    
    After you can check inside of the data directory to see the logs:
    
    -rw-r--r--. 1 oracle oinstall       77 Dec 12 16:00 c1.txt.gz
    -rw-r--r--. 1 oracle oinstall       92 Dec 12 16:00 c2.txt.gz
    -rw-r--r--. 1 oracle oinstall      676 Dec 12 16:59 COUNTRIES_EXT_15232.log
    -rw-r--r--. 1 oracle oinstall      676 Dec 12 16:59 COUNTRIES_EXT_15230.log
Advertisements

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;