5 Replies Latest reply: Sep 5, 2009 10:07 AM by 661723 RSS

    Set unused column

    661723
      Sir

      i am new to database.I am doing an oracle course on 051.My oracle faculty is saying
      Set unused column 
      option will hide the column for-ever.user can't retrieve it again.
      what i believe if a statement is hiding a column there must be some option to regain it.

      Is there any option of to make an unused column to reusable again?
        • 1. Re: Set unused column
          JustinCave
          The instructor is correct. Once you set a column to unused, there is no way to make it used again. This feature is just intended to allow you to hide one or more columns from a large table in preparation for dropping them later when you have a larger downtime window. It is intended to be just as irreversible as dropping the column.

          Justin
          • 2. Re: Set unused column
            718739
            Hi,

            Once you user SET UNUSED COLUMN then you cannot get back the column again, it is treated as a dropped column. Moreover you can add a new column with that name.

            Any time you can drop the SET UNUSED COLUMNS with the following command.

            ALTER TABLE table_name DROP UNUSED COLUMNS;

            Thanks&Regards,
            Rajam Raju.
            • 3. Re: Set unused column
              Hoek
              Hi,
              i am new to database.
              Is there any option of to make an unused column to reusable again?
              Since you're new:
              Try to find your answers in the Oracle documentation as well, as it explains clearly how 'Oracle things' work, with examples and you can do quick searches on keywords of your interest.
              Example: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2329
              You should bookmark: http://tahiti.oracle.com and start reading the concepts and fundamentals when you've got some spare time.
              assuming you're on 10g
              • 4. Re: Set unused column
                661723
                1) [http://www.orafaq.com/node/968] although this link is talking about the table BUT
                can i get back the total column from BIN (after/before removing the column)?

                2) [http://oracle.ittoolbox.com/groups/technical-functional/oracle-db-l/set-unused-column-undo-it-194155]

                this link is saying exactly the same until and unless this part:

                "...Also, if you mark a column of datatype LONG as UNUSED, you cannot add
                another LONG column to the table until you actually drop the unused LONG
                column."

                ---cant i add a new col with same name and same data types?
                • 5. Re: Set unused column
                  465815
                  1. no. a table will go to the recycle bin afte a drop table command.
                  2. this is correct, there can be only one long column in a table, but after dropping it
                  you can use the same column name:
                  SQL> create table p(l long);
                  
                  Table created.
                  
                  SQL> alter table p add l2 long;
                  alter table p add l2 long
                                    *
                  ERROR at line 1:
                  ORA-01754: a table may contain only one column of type LONG
                  
                  SQL>  alter table p add l2 number;
                  
                  Table altered.
                  
                  SQL> alter table p set unused column l;
                  
                  Table altered.
                  
                  SQL>  alter table p add l clob;
                  
                  Table altered.
                  Amiel