Results 1 to 12 of 12

Thread: "SYS" table

  1. #1
    Join Date
    May 2011
    Posts
    36

    Unanswered: "SYS" table

    How to delete a column from a table that is owned by "SYS".

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Why would you want to do that? You should not mess around with Oracle's internal tables

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Owner is able to do that.

    Don't mess with SYS. Pretend you don't even know it exists. Never, ever abuse its existence. Try to contact Oracle Support saying "I deleted a column from a table owned by SYS and now I have a problem". Looking forward to hear how they responded.

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I think you simply cannot. ORA-12988
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Huh, true. So the only "workaround" is to drop the whole table and create it without that column.

    On the other hand, as one shouldn't create anything in SYS schema, dropping the table and creating it connected as another user (different from SYS) would most probably be the best idea.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    No one asked why you want to remove a column and break your oracle database? set up a view on the table without the column.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Quote Originally Posted by Littlefoot View Post
    dropping the table and creating it connected as another user (different from SYS) would most probably be the best idea.
    I honestly do not have the ... (put any word you want here) to drop a SYS table.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by aflorin27 View Post
    I honestly do not have the ... (put any word you want here) to drop a SYS table.
    Depends on what exactly is meant with "table that is owned by "SYS"".

    If it is a table that was created by end user/application (as everybody here expects) in SYS schema, I would not hesitate to drop it and recreate under different user.

    If it is a system view (e.g. USER_TABLES) or even "real" SYS table (e.g. TAB$), it would really be a very bad idea; anyhow, as original poster did not post any reason for it (as there are no rational ones) and does not give any feedback, it is hard to tell more.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Florin, what I meant (by saying that he should drop a table) was that he created it (connected as SYS) but should have created it connected as a different user. Never thought of dropping the "original" SYS objects. Should have said that, though.

    It seems that Flyboy understood that.

  10. #10
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    @Littlefoot
    I got your idea from the first time. But I still feel some throat lumps when dropping anything that begins with SYS.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  11. #11
    Join Date
    May 2011
    Posts
    36

    @Littlefoot

    Yes, your assumption is correct. That table has been created by user in SYS schema. Now it needs to be dropped.

    I need both Ans.

    1] How to drop a column from table.

    2] How to drop the entire table.

  12. #12
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by GaneshAnanth View Post
    I need both Ans.
    Sorry I'm not a native speaker, what does "Ans" mean?

    1] How to drop a column from table.
    http://download.oracle.com/docs/cd/B...1.htm#i2057060

    2] How to drop the entire table.
    http://download.oracle.com/docs/cd/B...3.htm#i2061306

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •