DBA Hub

📋Steps in this guide1/3

Invisible column in oracle 12c - DBACLASS DBACLASS

Invisible column has been introduced in oracle 12c

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

We have invisible indexes in oracle 11g. But in oracle 12c we invisible column was introuduced. Lets create a normal table: Make a column invisible:

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
SQL> create table dbatest ( empno number, name varchar2(10));

Table created.


SQL> insert into dbatest values ( 1,'SERY');

1 row created.

SQL> insert into dbatest values ( 2,'MORY');

1 row created.

SQL> commit;

Commit complete.


SQL> ;
  1* select owner,table_name,column_name,hidden_column from dba_tab_cols where table_name='DBATEST'
SQL> /

OWNER	  TABLE_NAME COLUMN_NAM HID
--------- ---------- ---------- ---
TEST	  DBATEST    EMPNO	NO
TEST	  DBATEST    NAME	NO
2

Section 2

Now the column “NAME” is invisible to application. And we if create another table from this table, then invisible column won’t be created. But still you can access the data of invisible column by specifically using the column_name.

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
SQL> alter table dbatest modify ( NAME invisible);

Table altered.

SQL> select owner,table_name,column_name,hidden_column from dba_tab_cols where table_name='DBATEST';

OWNER	  TABLE_NAME COLUMN_NAM HID
--------- ---------- ---------- ---
TEST	  DBATEST    EMPNO	NO
TEST	  DBATEST    NAME	YES

SQL> select * from dbatest;

     EMPNO
----------
	 1
	 2

SQL> create table dbaprod as select * from dbatest;

Table created.


SQL> select * from dbaprod;

     EMPNO
----------
	 1
	 2

SQL> select empno, name from dbatest;

     EMPNO NAME
---------- ----------
	 1 SERY
	 2 MORY
3

Section 3

You can make it visible again:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
SQL> alter table dbatest modify ( name visible);

Table altered.

SQL>  select owner,table_name,column_name,hidden_column from dba_tab_cols where table_name='DBATEST';

OWNER	  TABLE_NAME COLUMN_NAM HID
--------- ---------- ---------- ---
TEST	  DBATEST    EMPNO	NO
TEST	  DBATEST    NAME	NO

Comments (0)

Please to add comments

No comments yet. Be the first to comment!