5 Replies Latest reply on Sep 5, 2009 3:21 PM by 465815

    Set unused column

    661723
    661723 Sep 5, 2009 5:25 AM
      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
          JustinCave Sep 5, 2009 6:24 AM (in response to 661723)
          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
            718739 Sep 5, 2009 10:54 AM (in response to 661723)
            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
              Hoek Sep 5, 2009 12:28 PM (in response to 661723)
              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
              1 person found this helpful
              • 4. Re: Set unused column
                661723
                661723 Sep 5, 2009 3:07 PM (in response to Hoek)
                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
                  465815 Sep 5, 2009 3:21 PM (in response to 661723)
                  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
                  1 person found this helpful