1 2 Previous Next 19 Replies Latest reply: Dec 3, 2012 11:55 PM by jgarry Go to original post RSS
      • 15. Re: doubt about a question
        Fran
        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
          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
            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
              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
                Looks like Uwe did! 1 is most correct. :p
                1 2 Previous Next