Pluggable database save state -12C

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

no rows selected

SQL> SELECT name, open_mode FROM v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE

SQL> ALTER PLUGGABLE DATABASE pdborcl1 SAVE STATE;

Pluggable database altered.


SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

CON_NAME     INSTANCE_NAME  STATE
-------------------- -------------------- --------------
PDBORCL1     orcl  OPEN

SQL> SHUTDOWN IMMEDIATE;
STARTUP;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.
Database opened.
SQL> SELECT name, open_mode FROM v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       MOUNTED
PDBORCL2       MOUNTED
PDBORCL1       READ WRITE

SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>

CON_NAME     INSTANCE_NAME  STATE
-------------------- -------------------- --------------
PDBORCL1     orcl  OPEN

***********************************************
DISCARD PDB SAVED STATE
***********************************************

SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>

CON_NAME     INSTANCE_NAME  STATE
-------------------- -------------------- --------------
PDBORCL1     orcl  OPEN


SQL> ALTER PLUGGABLE DATABASE pdborcl1 discard state;

Pluggable database altered.

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

no rows selected

how to create nested cursor in plsql

select * from empinfo; 

"ID"    "FNAME"    "LNAME"    "DEPTNO"
1    "arv"    "red"    100
2    "sam"    "kum"    200
3    "tom"    "com"    100
4    "Hef"    "Kom"    200
5    "fam"    "ily"    300



select * from deptinfo;

"ID"    "DEPTNO"    "DEPTNAME"    "MGR"
1    100    "HR"    "Bill"
2    200    "IT"    "Greg"
3    300    "SALES"    "Phil"


-- sqlcode starts here

declare

CURSOR CUR1 is select * from empinfo ;
REC1 empinfo%rowtype;

CURSOR CUR2(DID number) is select * from deptinfo where deptno=DID;
REC2 deptinfo%rowtype;
begin
open CUR1 ;
loop
FETCH CUR1 into REC1;
EXIT WHEN CUR1%NOTFOUND;

OPEN CUR2(REC1.deptno);
loop
FETCH CUR2 into REC2;
EXIT WHEN CUR2%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('REC1.id:  '||REC1.ID||'  belongs to dept ID : '||REC2.DEPTNO||'  DEPT :'||REC2.DEPTNAME);

END LOOP;
CLOSE CUR2;

end loop;
close CUR1;
end;

Setup Oracle database using Docker container

Step-1:

Install docker container. Based on your windows OS Version. if you are using windows 7 will need docker engine and Kitematic 

if you are on windows 10 or higher use : Docker Community Edition (Download)

Docker Instructions : https://docs.docker.com/install/

Once installed you will see Docker Quickstart Terminal and Kitematic.

open docker machine and then Kitematic.

Search for oracle XE 11g by Seth89. Download and install the below shown container

Once installed You will have oracle database installed and ready to use. you can ignore the below error message  /docker-entrypoint-init.d/cache: no such file or dir.

Another way to confirm successful installation is you will see Unauthorized image in web preview section

Make sure you have bridge network configured. As below

Check if below configured ports are configured.

How to connect to oracle database in the container :

Based on the IP Address and port configured in above image. Use those credentials to connect to DB.

Username : System

Password : oracle

Or to connect via sqlplus use as below:

Optional settings :

To configure or change volumes use. Volumes will help you to copy files over or attach a local working directory to container:

Expdp exclude table/exclude schema

expdp export exclude syntax:

Here is a simple example on how to use exclude in your export cmds.

----------------------------------------------
Exclude Tables
----------------------------------------------

Using parfile:

First create a parfile with details.
$ vi impdp_full.par

--add the details to parfile
directory=DPUMP logfile=SCOTT_EXP.log dumpfile=SCOTT_%U.dmp parallel=4  EXCLUDE=TABLE:"IN ('TABLE1','TABLE2','TABLE3')

Then execute using parfile:
expdp SCOTT/XXXXXX parfile=impdp_full.par

on cmdline:
expdp SCOTT/XXXXX directory=DPUMP dumpfile=SCOTT_%U.dmp logfile=SCOTT.log schemas=SCOTT  parallel=6 EXCLUDE=TABLE:\"IN (\'TABLE1\', \'TABLE2\')\"

 

----------------------------------------------
Exclude Schemas
----------------------------------------------

First create a parfile with details.

$ vi expdp_full.par
--add the details to parfile
directory=DPUMP FULL=Y dumpfile=FULLDB_%U.dmp logfile=FULL.log   parallel=6 EXCLUDE=SCHEMA:"IN ('SCHEMA1','SCHEMA2','SCHEMA3')

Then execute using parfile:
expdp SYSTEM/XXXXXX parfile=expdp_full.par

on cmdline:
expdp SYSTEM/XXXXX directory=DPUMP dumpfile=FULLDB_%U.dmp logfile=FULL.log FULL=Y  parallel=6 EXCLUDE=TABLE:\"IN (\'SCHEMA1\', \'SCHEMA2\')\"

ORA-31633: unable to create master table ".SYS_IMPORT_FULL_05"

 Today I encountered a problem while importing a schema into my local database. I have exported a schema from ORCL (lets say) using expdp command. I tried to import it to another database and I was getting this error.

 ORA-31633: unable to create master table

[oracle@orcl dpump]$ impdp sam/oracle directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2

Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01031: insufficient privileges

I tried again and again same error. Then I checked may be it is missing some grants, I havegranted  sysdba privillage to SAM now and tried.

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 14 14:02:27 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant sysdba to SAM;

Lets try again:

[oracle@orcl dpump]$ impdp SAM/SAM  directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2

Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01031: insufficient privileges.



Then I realized that it might be missing create table permission and granted that permission to user SAM.

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 14 14:02:27 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant create table to SAM;

Grant succeeded.

Now tried again :

[oracle@orcl dpump]$ impdp SAM/SAM  directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2


 Import: Release 11.2.0.4.0 - Production on Fri Nov 14 14:02:54 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SAM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SAM"."SYS_IMPORT_SCHEMA_01":  sam/******** dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Possible Solutions :

•  It might be a simple missing of create table grant  if you are trying to import it through another user.

• Try to check if there is already a table created (SYS_IMP*) with the name oracle is trying to create table.

• Check if the job name being used in session is already created in database.

IMPDP TABLE_EXISTS_ACTION = APPEND, REPLACE, [SKIP] and TRUNCATE

 

IMPDP  TABLE_EXISTS_ACTION PARAMETER EXPLAINED

 Data Pump IMPDP TABLE_EXISTS_ACTION = APPEND, REPLACE, [SKIP] and TRUNCATE
In conventional import utility (IMP) we have ignore =y option which will ignore the error when the object is already exist with the same name.

When it comes to the data pump there is one enhanced option of ignore=y which is called TABLE_EXISTS_ACTION. The values for this parameter give 4 different options to handle the existing table and data.

$ impdp help=y

    TABLE_EXISTS_ACTION
    Action to take if imported object already exists.
    Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

    SKIP: Default value for this parameter is SKIP. This parameter is exactly same as the IGNORE=Y option in conventional import utility.

    APPEND: This option appends the data from the data dump. The extra rows in the dump will be appended to the table and the existing data remains unchanged.

    TRUNCATE: This option truncate the exiting rows in the table and insert the rows from the dump

    REPLACE: This option drop the current table and create the table as it is in the dump file. Both SKIP and REPLACE options are not valid if you set the  CONTENT=DATA_ONLY for the impdp.

   
Method to Import only rows does not exist in the target table
See some examples here.

In this example lets use abc table in my schema (ATOORPU)

SQL> select * from abc;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
eds                                    44 19-DEC-12
rew                                    33 19-DEC-12
ARV                                    70 20-DEC-12
ARVIND                                 69 20-DEC-12
ATOORPU                                64 19-DEC-12
BI                                     63 19-DEC-12

I took the data pump dump export EXPDP of employee table.

oracle@orcl: $ expdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc.log tables=abc

Export: Release 11.2.0.1.0 - Production on Fri Oct 24 09:25:02 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ATOORPU"."SYS_EXPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc.log tables=abc
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ATOORPU"."ABC"                             5.921 KB       6 rows
Master table "ATOORPU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ATOORPU.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/dpump/abc.dmp
Job "ATOORPU"."SYS_EXPORT_TABLE_01" successfully completed at 09:25:36


oracle@qpdbdev201:[/u01/app/oracle/dpump] $ sqlplus atoorpu

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 24 09:25:57 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


   ------ TABLE_EXISTS_ACTION=SKIP  ------

In this example I want to use table_exists_action=skip, where I want to skip the table data in my import, if a similar table exists.

oracle@orcl: $ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_skip.log tables=abc table_exists_action=skip

Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:32:32 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_skip.log tables=abc table_exists_action=skip
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "ATOORPU"."ABC" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:32:42


    ------     TABLE_EXISTS_ACTION=APPEND    ------

I have deleted and inserted 4 new rows into employee table. So as of now the rows the dump and table are different and I am going to import the dump with APPEND option.

SQL> delete from employee;

4 rows deleted.

SQL> insert into abc (select * from abc_bak);

4 rows created.

SQL> commit;
SQL> select * from abc;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
ARVd                                   70 20-DEC-12
ARVIND2                                69 20-DEC-12
ATOORPUe                               64 19-DEC-12
BIf                                    63 19-DEC-12

$  impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_append.log tables=abc table_exists_action=append

Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:37:34 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_append.log tables=abc table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "ATOORPU"."ABC" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC"                             5.921 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:37:42

Now 4 more rows appended to the table. Lets verify that

SQL> select * from abc;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
ARVd                                   70 20-DEC-12
ARVIND2                                69 20-DEC-12
ATOORPUe                               64 19-DEC-12
BIf                                    63 19-DEC-12
eds                                    44 19-DEC-12
rew                                    33 19-DEC-12
ARV                                    70 20-DEC-12
ARVIND                                 69 20-DEC-12
ATOORPU                                64 19-DEC-12
BI                                     63 19-DEC-12

8 rows selected.

        ------ TABLE_EXISTS_ACTION=TRUNCATE     ------ 

    Now let’s try with table_exists_action=truncate option. In truncate option it will truncate the content of the existing table and insert the rows from the dump. Currently my abc table has 8 rows which we inserted last insert.

$   impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_trunc.log tables=abc table_exists_action=truncate

Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:39:39 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_trunc.log tables=abc table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "ATOORPU"."ABC" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC"                             5.921 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:39:46


SQL >  select * from abc


USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
eds                                    44 19-DEC-12
rew                                    33 19-DEC-12
ARV                                    70 20-DEC-12
ARVIND                                 69 20-DEC-12
ATOORPU                                64 19-DEC-12
BI                                     63 19-DEC-12


        ------ TABLE_EXISTS_ACTION=REPLACE    ------


This option drop the current table in the database and the import recreate the new table as in the dumpfile.

$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_replace.log tables=abc table_exists_action=replace

Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:41:59 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_replace.log tables=abc table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC"                             5.921 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" successfully completed at 09:42:07


Now if you check the last_ddl_time for the table it would be the same as the import time.


SQL> select OBJECT_NAME, to_char(LAST_DDL_TIME,'dd-mm-yyyy hh:mi:ss') created from dba_objects where OBJECT_NAME='ABC' and owner='ATOORPU';


OBJECT_NAME                    CREATED          
--------------------------             -------------------
ABC                                        24-10-2014 09:42:06

In-Database Archiving in Oracle Database - Data Archiving in 12C

In-Database Archiving in Oracle Database 12c - Data Archiving in 12C

Rather than deleting data physically, some applications have a concept of "mark for delete" logical delete, so the data remains present in the table, but is not visible to the application. This can be achieved by doing the following.

Add an extra column to the relevant tables that holds a flag to indicate the data is deleted.
Add an extra predicate to every statement that checks the deleted status, like "WHERE deleted = 'N'", to exclude the deleted rows from the SQL. The predicate can be hard coded into the SQL, or applied dynamically using a security policy, like in Virtual Private Database (VPD).

In-Database Archiving is a feature added to Oracle Database 12c to allow this type of "mark for delete" functionality out-of-the-box, with fewer changes to the existing application code.

Enable In-Database Archiving
Archiving (Deleting) Rows
Displaying Archived Rows


Enable In-Database Archiving

The ROW ARCHIVAL clause is used to enable in-database archiving. It can be used during table creation as part of the CREATE TABLE command, or after table creation using the ALTER TABLE command.

DROP TABLE ARCH_TEST PURGE;

-- Create the table with in-database with archiving of data enabled.

CREATE TABLE ARCH_TEST (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT ARCH_TEST_pk PRIMARY KEY (id)
)
ROW ARCHIVAL;

-- Insert data into TEST TABLE ARCH_TEST

declare
n number;
VSQL Varchar(2000);
--i number:=1;
begin
for n in  1..750
loop
-- n := n+1;
VSQL:='insert into ARCH_TEST (id ,DESCRIPTION) values ('||n||',''VALUE OF N IS '||n||''')';
-- DBMS_OUTPUT.PUT_LINE(VSQL);
Execute Immediate VSQL;

end loop;
commit;
end;
/

-- Check the contents of the table.

SELECT COUNT(*) FROM ARCH_TEST;

  COUNT(*)
----------
       750

 
COLUMN column_name FORMAT A20
COLUMN data_type FORMAT A20

SELECT column_id,
       column_name,
       data_type,
       data_length,
       hidden_column
FROM   user_tab_cols
WHERE  table_name = 'ARCH_TEST'
ORDER BY column_id;


 COLUMN_ID COLUMN_NAME DATA_TYPE           DATA_LENGTH HID
----------          --------------------                      --------------------             -----------           ---
1 ID         NUMBER (22)        NO
2 DESCRIPTION VARCHAR2 (50)        NO
ORA_ARCHIVE_STATE VARCHAR2 (4000)        YES

  

By default, this column is populated with the value '0' for each row.

COLUMN ora_archive_state FORMAT A20

SELECT ora_archive_state, COUNT(*)
FROM   ARCH_TEST
GROUP BY ora_archive_state
ORDER BY ora_archive_state;   
  
ORA_ARCHIVE_STATE      COUNT(*)
-------------------- ----------
0                           750  
  

-- Disable, the re-enable in-database archiving.

ALTER TABLE ARCH_TEST NO ROW ARCHIVAL;
ALTER TABLE ARCH_TEST ROW ARCHIVAL;

************************************************************Archiving (Deleting) Rows************************************************************

Rather than deleting unneeded rows, update the ORA_ARCHIVE_STATE system generated hidden column with the value '1'. This will make the rows invisible to your applications.

UPDATE ARCH_TEST
SET    ora_archive_state = '1'
WHERE  id BETWEEN 550 and 750;
COMMIT;

SELECT COUNT(*) FROM ARCH_TEST;

  COUNT(*)
----------
       549

  
We can actually set ORA_ARCHIVE_STATE column to any string value other than '0' to archive the data, but the DBMS_ILM package uses the following constants.

ARCHIVE_STATE_ACTIVE='0'
ARCHIVE_STATE_ARCHIVED='1'

SQL>

************************************************************
Displaying Archived Rows
************************************************************

The hidden rows can be made visible to a session by setting ROW ARCHIVAL VISIBILITY to the value ALL. Setting it back to ACTIVE makes the rows invisible again.

-- Make archived rows visible.
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

SELECT COUNT(*) FROM ARCH_TEST;

  COUNT(*)
----------
       750

SQL>


COLUMN ora_archive_state FORMAT A20

SELECT ora_archive_state, COUNT(*)
FROM   ARCH_TEST
GROUP BY ora_archive_state
ORDER BY ora_archive_state;

ORA_ARCHIVE_STATE      COUNT(*)
-------------------- ----------
0                           549
1                           201

2 rows selected.

SQL>

-- Make archived rows invisible again.

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

SELECT COUNT(*) FROM ARCH_TEST;

 COUNT(*)
----------
       549

SQL>