Home » RDBMS Server » Server Administration » Unused columns (DB12.1.0.1)
Unused columns [message #590010] Sat, 13 July 2013 10:23 Go to next message
John Watson
Messages: 4570
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:
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).
Re: Unused columns [message #590047 is a reply to message #590010] Sat, 13 July 2013 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Unused columns [message #590092 is a reply to message #590047] Sun, 14 July 2013 15:57 Go to previous message
John Watson
Messages: 4570
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.
Previous Topic: Schema stats
Next Topic: language in oracle 10g
Goto Forum:
  


Current Time: Sun Sep 21 23:52:30 CDT 2014

Total time taken to generate the page: 0.12668 seconds