CREATE USER z_test
   IDENTIFIED BY welcome1;

GRANT CONNECT, RESOURCE TO z_test;

ALTER USER z_test QUOTA UNLIMITED ON users;
 
CONN z_test/welcome1
CREATE TABLE cars_for_sale (
   units    NUMBER,
   make     VARCHAR2(20),
   model    VARCHAR2(30),
   year     NUMBER)
ROW ARCHIVAL;

DESC cars_for_sale

SELECT units, make, ora_archive_state
  FROM cars_for_sale;

INSERT INTO cars_for_sale
   VALUES (100, 'Ford', 'Focus', 1997);

INSERT INTO cars_for_sale
   VALUES (50, 'Ford', 'Mustang', 1970);

INSERT INTO cars_for_sale
   VALUES (20, 'Ford', 'Mustang', 1968);

COMMIT;

SELECT units, make, model, year, ora_archive_state
  FROM cars_for_sale 
 ORDER BY 3;

CONN / AS SYSDBA

UPDATE z_test.cars_for_sale
   SET ora_archive_state = dbms_ilm.archivestatename(1)
 WHERE make = 'Ford'
   AND model = 'Mustang'
   AND year = 1968;

COMMIT;

CONN z_test/welcome1

SELECT units, make, model, year, ora_archive_state
  FROM cars_for_sale 
 ORDER BY 3;

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

SELECT units, make, model, year, ora_archive_state
  FROM cars_for_sale 
 ORDER BY 3;

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL ;

SELECT COUNT(*) FROM cars_for_sale;


ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE ;

SELECT COUNT(*) FROM cars_for_sale;

-- Clean-Up

CONN / AS SYSDBA

DROP USER z_test CASCADE;