CODEWITHSUNDEEP
oracle-databaseoracle12carchiving
Melad Basilius
Melad Basilius

Reputation: 4306

Oracle in-database archival check

In oracle DB we can mark a table to allow archiving using this SQL

  create table table1 (col1 number, col2 char(200)) row archival;

and we can archive/de-archive columns using the SQL

update table1 set ora_archive_state=0 where col2='test2';
update table1 set ora_archive_state=1 where col2='test2';

But before i do all of that , is there any way that i can check if the table is already marked for archiving or not ?

Upvotes: 0

Views: 319

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

You can check for the existence of the hidden column, eg

SQL> create table t ( x int );

Table created.

SQL> alter table t row archival;

Table altered.


SQL> select column_name, hidden_column
  2  from   user_tab_cols
  3  where  table_name = 'T';

COLUMN_NAME                    HID
------------------------------ ---
X                              NO
SYS_NC00002$                   YES
ORA_ARCHIVE_STATE              YES

or if you want to go geeky its the 48th bit on the PROPERTY column in SYS.TAB$

Upvotes: 1

Related Questions