| revoking unused columns ...!!! [message #144691] |
Thu, 27 October 2005 12:23  |
balavignesh
Messages: 180
Registered: March 2005
|
Senior Member |
|
|
hai all,
we have set a column unused for some updation process.
now we need get back the column..
how to get back...?
we have made the column as follows
alter table test set unused column eno;
now we need to get back the column.
plz help out ..
regards
bala
|
|
|
|
| Re: revoking unused columns ...!!! [message #144697 is a reply to message #144691] |
Thu, 27 October 2005 12:54  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>now we need to get back the column.
From where?
By asking oracle to unuse the column, you asked oracle to just 'forget' it.
All vital information is destroyed.
The Column is set to be unused forever.
Oracle dictionary will just erase all its references to the name_of_column and henceforth it is just some_unused_column which may or maynot contain some data. You may be able to get some information, but not the name of column or the data.
scott@9i > desc dept
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
scott@9i > alter table dept set unused (dname);
Table altered.
scott@9i > desc dept
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
DEPTNO NUMBER(2)
LOC VARCHAR2(13)
scott@9i > select table_name,column_name,data_type,data_length,hidden_column from user_tab_cols where table_name='DEPT';
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH HID
---------- ------------------------------ ---------------------------------------- ----------- ---
DEPT DEPTNO NUMBER 22 NO
DEPT SYS_C00002_05102714:02:02$ VARCHAR2 14 YES
DEPT LOC VARCHAR2 13 NO
[Updated on: Thu, 27 October 2005 13:04] Report message to a moderator |
|
|
|
| Re: revoking unused columns ...!!! [message #144754 is a reply to message #144691] |
Fri, 28 October 2005 01:47 |
Rahul Desai
Messages: 26
Registered: March 2002
|
Junior Member |
|
|
Wow!!! i never knew there is a keyword called "Unused"
Thanks for the information. But i was wondering is usage of "Unused" and "drop" of a column the same.
here are the few R&D i did on unused:
Desc TEST1;
Name Null? Type
------------------- -------- ----------------------------
SLNO NUMBER
VAL NUMBER
query 1:
---------
select table_name,column_name,data_type from user_tab_cols where
table_name = 'TEST1';
TABLE_NAME COLUMN_NAME DATA_TYPE
------------------------------ ------------------ --------------
TEST1 SLNO NUMBER
TEST1 VAL NUMBER
alter table test1 set unused(val);
--Table altered.
query 1: GIVES
TABLE_NAME COLUMN_NAME DATA_TYPE
------------ ------------------------------ ----------
TEST1 SLNO NUMBER
TEST1 SYS_C00001_05102811:51:51$ NUMBER
--------------------------------------------
SQL> alter table test1 set unused(val);
alter table test1 set unused(val)
*
ERROR at line 1:
ORA-00904: "VAL": invalid identifier
--------------------------------------------
--------------------------------------------
SQL> alter table test1 set unused(slno);
alter table test1 set unused(slno)
*
ERROR at line 1:
ORA-12983: cannot drop all columns in a table
--------------------------------------------
--------------------------------------------
SQL> alter table test1 add val number;
Table altered.
--------------------------------------------
--------------------------------------------
query 1: GIVES
TABLE_NAME COLUMN_NAME DATA_TYPE
------------ ------------------------------ ----------
TEST1 VAL NUMBER
TEST1 SLNO NUMBER
TEST1 SYS_C00001_05102811:51:51$ NUMBER
And now:-------------
SQL> alter table test1 drop column val;
Table altered.
-------------------------------------------------------
query 1: GIVES
TABLE_NAME COLUMN_NAME DATA_TYPE
------------ ------------------------------ ----------
TEST1 SLNO NUMBER
---------------------------------------------------------
----------------------------------------------------------
what happened to the information?
TEST1 SYS_C00001_05102811:51:51$ NUMBER
i tried all the combination, if i drop any of the column, the information is lost.
can anyone explain me wot exactly "unused" does and y the information exists till "drop" statement is executed. And
with :
TEST1 SYS_C00001_05102811:51:51$ NUMBER
is it possible to do something?
thanks a million
Rahul Desai.
|
|
|
|
|
|
| Re: revoking unused columns ...!!! [message #144763 is a reply to message #144691] |
Fri, 28 October 2005 02:47 |
Rahul Desai
Messages: 26
Registered: March 2002
|
Junior Member |
|
|
That was great information indeed!!
After reading the link, i arrived at a point
"Drop" is a physical delete where as
"Unused" a logical delete.
A logical delete....U dont see the information, u cant retrieve it back, and it just consumes space??? i mean wots the use of such a command. When exactly use "Unused" command?
Thanks again
Rahul Desai
|
|
|
|
|
|
| Re: revoking unused columns ...!!! [message #144769 is a reply to message #144691] |
Fri, 28 October 2005 03:24 |
Rahul Desai
Messages: 26
Registered: March 2002
|
Junior Member |
|
|
off topic: Oops!!! IM speak has invaded everyone. Its human mentality to learn n use the new things and make things simpler.
I guess even emoticons shouldn't be used.
No issues, i make myself follow the rule now on....
on topic: i found this information from the link you provided. Guess i posted the question a bit early.
------------------------------------------
Drop column generates proportionately massive amounts of redo and in my
experience is a horrible way to remove columns. The table is locked until the
column is removed and its data deleted (all rows are re-written to exclude the
drop column data). On big tables this can be horrible. You can force regular
checkpoints to avoid blowing your rollback, but the whole process can be
painful.
Your alternatives are to set all the columns you want to drop to unused and drop
them all in one go. Personally, I prefer to set all the unused columns and then
either perform a CTAS to create a new copy of the table without the unused
columns or even an ALTER TABLE MOVE which is a very quick way of re-writing the
data without the unused columns. Either way you will need to consider your
indexes.
If it's a small table with just one or two columns to remove, then fine, go
ahead and drop, but if the table is of any size at all, then use with caution...
----------------------------------
Rahul Desai.
|
|
|
|
| Re: revoking unused columns ...!!! [message #144795 is a reply to message #144769] |
Fri, 28 October 2005 06:48 |
|
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>off topic: Oops!!! IM speak has invaded everyone. Its human mentality to learn n use the new things and make things simpler.
OFF TOPIC again:
Rahul, I am not picking on you here.
Simpler to whom? Definately not for someone reading the posting and trying to answer it.
I (we) never bother speaking the IM language in messenger/casual talks.
But when presenting something 'technically' in a public forum, anyone need to follow some netiquette. This is very important to convey the message across the folks in board (forum / web posting ) in a manner understood by all.
Myself,Maaher and most are NOT NATIVE ENGLISH speakers and most speak only English (Not the 'local' slang).
English( and sql/plsql ) is the common language here.
No one is expecting the posters to write in a Shakespearean English. Simple words can always make a better sentence.
IM language, self abbreviated words and local slang will confuse folks and in many cases, will mis-represent the information.
Some IM language examples are
u=you , hlp=help,prb=problem etc.
Local Slang usage like
Laks,crore etc have driven many members crazy!.
We all have little and precious time over here. Spending it over decoding and understanding what was posted is not worthy.
[Updated on: Fri, 28 October 2005 09:18] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
).