create table while logged in as sysdba
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: create table while logged in as sysdba

  1. #1
    Join Date
    Jun 2007
    Posts
    59

    create table while logged in as sysdba

    Hi, I am creating a table while being logged as sysdba and it gets created in sys tablespace. What wrong with my user ?

    i.e sqlplus aztest/aztest as sysdba

    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    PL/SQL Release 11.1.0.7.0 - Production
    CORE 11.1.0.7.0 Production
    TNS for Solaris: Version 11.1.0.7.0 - Production
    NLSRTL Version 11.1.0.7.0 - Production

    Thanks

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by zam View Post
    Hi, I am creating a table while being logged as sysdba and it gets created in sys tablespace. What wrong with my user ?

    i.e sqlplus aztest/aztest as sysdba

    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    PL/SQL Release 11.1.0.7.0 - Production
    CORE 11.1.0.7.0 Production
    TNS for Solaris: Version 11.1.0.7.0 - Production
    NLSRTL Version 11.1.0.7.0 - Production

    Thanks
    I guess you didn't include the TABLESPACE option on your CREATE TABLE statement; don't you?

    By the way - there is no reason to log as SYSDBA to create a table; SYSDBA is a powerful set of privileges needed in very specific cases.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jun 2007
    Posts
    59
    Thanks PAVB. Yes I have not specified tablespace. But my Q is not about whether to use sysdba for that or not. I am asking is it a default behavior in11g ? I know that in 10g table will be created in user default tablespace of the user.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    When you connect as SYSDBA you default schema becomes SYS - do a "select user from dual"; therefore you are SYS not aztest.

    On a totally unrelated note, just spotted an ugly typo on my first reply, referring to SYSDBA I said "account", be kind enough to read "system privilege" - Thank you.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jun 2007
    Posts
    59
    Sorry PAVB, I didn't mean to be rude. What I was saying is that this is not the case in 10g as far as I can tell. If you create table logged in as sysdba it will create table in the schema of the aztest user and not in sys schema as opposed to 11g.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    No worries Zam.
    On the other hand, I'm pretty sure Ora10g behaves exactly the same way; if you connect as SYSDBA no matter which credentials you are using, default schema gets switched to SYS.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    You can specify a default tablespace for each user in 10g, you should make sure that something other than system is the default tablespace overall and for the user that you are creating the table for. If I create a table for another schema using sys as sysdba it should go to the default tablespace for that user.
    this space intentionally left blank

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Agreed Gandolf but I think poster did something different, like:
    sqlplus aztest/aztest as sysdba
    create table mytable (col1 number);
    Zam, please correct me if wrong.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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



Click Here to Expand Forum to Full Width