DBA Hub

📋Steps in this guide1/4

In-Database Archiving in Oracle Database 12c Release 1 (12.1)

Use the in-database archiving functionality to implement

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

Enable In-Database Archiving

The clause is used to enable in-database archiving. It can be used during table creation as part of the command, or after table creation using the command. Enabling in-database archiving on the table results in the addition of a system generated hidden column called . By default, this column is populated with the value '0' for each row.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
DROP TABLE tab1 PURGE;

-- Create the table with in-database archiving enabled.
CREATE TABLE tab1 (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT tab1_pk PRIMARY KEY (id)
)
ROW ARCHIVAL;

-- Disable, the re-enable in-database archiving.
ALTER TABLE tab1 NO ROW ARCHIVAL;
ALTER TABLE tab1 ROW ARCHIVAL;

-- Populate the table with 1000 rows.
INSERT /*+ APPEND */ INTO tab1
SELECT level, 'Description of ' || level
FROM   dual
CONNECT BY level <= 1000;

COMMIT;

-- Check the contents of the table.
SELECT COUNT(*) FROM tab1;

  COUNT(*)
----------
      1000

SQL>

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 = 'TAB1'
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

SQL>

COLUMN ora_archive_state FORMAT A20

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

ORA_ARCHIVE_STATE      COUNT(*)
-------------------- ----------
0                          1000

1 row selected.

SQL>
2

Archiving (Deleting) Rows

Rather than deleting unneeded rows, update the system generated hidden column with the value '1'. This will make the rows invisible to your applications. You can actually set 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'

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
UPDATE tab1
SET    ora_archive_state = '1'
WHERE  id BETWEEN 751 and 1000;
COMMIT;

SELECT COUNT(*) FROM tab1;

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

SQL>
3

Displaying Archived Rows

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- Make archived rows visible.
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

SELECT COUNT(*) FROM tab1;

  COUNT(*)
----------
      1000

SQL>


COLUMN ora_archive_state FORMAT A20

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

ORA_ARCHIVE_STATE      COUNT(*)
-------------------- ----------
0                           750
1                           250

2 rows selected.

SQL>


-- Make archived rows invisible again.
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

SELECT COUNT(*) FROM tab1;

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

SQL>
4

Unarchiving Rows

If you need to do this, you should probably question why you made such a poor decision in archiving the rows in the first place. You can unarchive rows by resetting the column value to '0' for the archived rows. The archived rows must be visible in your session before you do this. Thanks for Koen Lostrie in the comments for suggesting this addition. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Make archived rows visible.
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

UPDATE tab1
SET    ora_archive_state = '0'
WHERE  id BETWEEN 751 and 1000;
COMMIT;

-- Make archived rows invisible again.
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

SELECT COUNT(*) FROM tab1;

  COUNT(*)
----------
      1000

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!