OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Unused columns (DB12.1.0.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Unused columns [message #590010]

Sat, 13 July 2013 10:23

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

If you mark a column unused, is there any way to project it? I know the docs say you can't, but as the data is still there I would have thought it should be possible. I can see the column in dba_tab_cols, but the obvious ways of making it usable don't work:

Code: [Select all] [Show/ hide]

orcl> select column_name,hidden_column from user_tab_cols where table_name='DEPT';

COLUMN_NAME                    HID
------------------------------ ---
LOC                            NO
DNAME                          NO
DEPTNO                         NO

orcl> alter table dept set unused column loc;

Table altered.

orcl> select column_name,hidden_column from user_tab_cols where table_name='DEPT';

COLUMN_NAME                    HID
------------------------------ ---
SYS_C00003_13071316:19:02$     YES
DNAME                          NO
DEPTNO                         NO

orcl> select "SYS_C00003_13071316:19:02$" from dept;
select "SYS_C00003_13071316:19:02$" from dept
       *
ERROR at line 1:
ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier


orcl> alter table dept rename column "SYS_C00003_13071316:19:02$"
  2  to loc;
alter table dept rename column "SYS_C00003_13071316:19:02$"
                               *
ERROR at line 1:
ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier


orcl> alter table dept modify "SYS_C00003_13071316:19:02$"
  2  visible;
alter table dept modify "SYS_C00003_13071316:19:02$"
                        *
ERROR at line 1:
ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier


orcl>

This is not of any practical significance, just for my interest. Thank you for any insight (If anyone else is as pathetically interested in Oracle as me).

Report message to a moderator

Re: Unused columns [message #590047 is a reply to message #590010]

Sat, 13 July 2013 13:14

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Apart from hacking the dictionary, I don't see any way.
Note that the data in an unused column can be overwritten by subsequent updates, I think this is why Oracle does not allow you to access to it (OK, Oracle can set a flag to tell if the data in the block we want to access is overwritten or not... maybe in a future release).
Maybe dumping the block, you can see what happens then at block level (just an idea to investigate).

Regards
Michel

Report message to a moderator

Re: Unused columns [message #590092 is a reply to message #590047]

Sun, 14 July 2013 15:57

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Well, I'm glad there is nothing obvious that I had missed. If the data in an unused column can be overwritten, that is a good reason for denying access.
In the current release, I can see little purpose in the concept of "unused columns". Making them invisible would seem to give more functionality. Invisible columns have been annoying me for several releases. They have been there since (at least) 8.1 for function based indexes and label security, but it is only with 12.1 that we have been the ability to manage them.

Report message to a moderator

Previous Topic:	Schema stats
Next Topic:	language in oracle 10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 01:07:23 CDT 2025