This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Dec 3, 2012 9:55 PM by jgarry Go to original post RSS
  • 15. Re: doubt about a question
    Fran Guru
    Currently Being Moderated
    Yes, by default is USER tablespace, if you don't set it. You can set another tablespace, but you can't unset it.
  • 16. Re: doubt about a question
    Roger25 Explorer
    Currently Being Moderated
    Ok, i don't want to change default tablespace.
    Now we're off topic regarding to that question:)
  • 17. Re: doubt about a question
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Roger22 wrote:
    Ok, i don't want to change default tablespace.
    Now we're off topic regarding to that question:)
    Then you can create your user like the following ;)
    Create user <USER> identified by <PW> 
  • 18. Re: doubt about a question
    John Spencer Oracle ACE
    Currently Being Moderated
    As Justin said, number 1 is obviously true, 2 is true assuming deferred segment creation is enabled, but user01 cannot add any rows to the table. The setting of default_permanent_tablespace is irrelevant.

    In the sample question, the users default tablespace was specified explicitly, so it is irrelevant on that basis. However, even if the default tablespace is not specified at user creation, the defualt_permanent_tablespace attribute still does not imply quota on the tablespace.
    SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production
    
    SQL> select PROPERTY_VALUE from database_properties
      2  where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';
    
    PROPERTY_VALUE
    ----------------
    USERS
    So I have default_permanent_tablespace set. Using an explicit non-default value in the create user statement:
    SQL> CREATE USER user01 IDENTIFIED BY user01
      2  DEFAULT TABLESPACE sitedefined
      3  TEMPORARY TABLESPACE temp;
    
    User created.
    
    SQL> GRANT CREATE SESSION, CREATE TABLE TO user01;
    
    Grant succeeded.
    
    SQL> connect user01/user01
    Connected.
    SQL> create table t (id number, descr varchar2(10));
    
    Table created.
    
    SQL> insert into t values (1, 'One');
    insert into t values (1, 'One')
                *
    ERROR at line 1:
    ORA-01950: no privileges on tablespace 'SITEDEFINED'
    Not specifying a default tablesapce:
    SQL> connect /
    Connected.
    SQL> drop user user01 cascade;
    
    User dropped.
    
    SQL> CREATE USER user01 IDENTIFIED BY user01;
    
    User created.
    
    SQL> GRANT CREATE SESSION, CREATE TABLE TO user01;
    
    Grant succeeded.
    
    SQL> connect user01/user01
    Connected.
    SQL> create table t (id number, descr varchar2(10));
    
    Table created.
    
    SQL> insert into t values (1, 'One');
    insert into t values (1, 'One')
                *
    ERROR at line 1:
    ORA-01950: no privileges on tablespace 'USERS'
    Where deferred segment creation is not available, the no quota error comes at table creation time instead of first insert:
    SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
    PL/SQL Release 10.2.0.3.0 - Production
    CORE    10.2.0.3.0      Production
    TNS for Solaris: Version 10.2.0.3.0 - Production
    NLSRTL Version 10.2.0.3.0 - Production
    
    SQL> select PROPERTY_VALUE from database_properties
      2  where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';
    
    PROPERTY_VALUE
    ----------------
    USERS
    
    SQL> CREATE USER user01 IDENTIFIED BY user01;
    
    User created.
    
    SQL> GRANT CREATE SESSION, CREATE TABLE TO user01;
    
    Grant succeeded.
    
    SQL> connect user01/user01
    Connected.
    SQL> create table t (id number, descr varchar2(10));
    create table t (id number, descr varchar2(10))
    *
    ERROR at line 1:
    ORA-01950: no privileges on tablespace 'USERS'
    Someone needs to have words with Oracle University :-)

    John
  • 19. Re: doubt about a question
    jgarry Guru
    Currently Being Moderated
    Looks like Uwe did! 1 is most correct. :p
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points