1 2 Previous Next 28 Replies Latest reply: Aug 5, 2013 11:11 PM by Venkateshj RSS

    Users Tablespaces are Changing

    Venkateshj
      Hi all,

      In my Oracle Server System 11g, the normal users which are created by me for table storing purpose are changing to other Tablespaces which are created at the time Oracle Database Configuration..

      please give reasons & why it causes?

      Thanks in Advance..:-)
        • 1. Re: Users Tablespaces are Changing
          yoonas
          Hi,

          You want all the objects created by the users you created to go to a particular table space?

          Can you explain a bit more to better understand your scenario

          Regards
          Yoonas
          • 2. Re: Users Tablespaces are Changing
            vlethakula
            Is there any tablespace clause specified during creation of object?

            User's default tablespace is TOOLS

            but my object creation script:

            create table t (i number) tablespace USERS;

            Then table t would be created in USERS tablespace instead of TOOLS
            • 3. Re: Users Tablespaces are Changing
              972330
              HI,

              if you create user as "create user <username> identified by <password>" and then creating tables with this users then it will go to default tablespace "users" which is created during database configuration.
              If the tablespace "users" is not there in your database then tables will go into "system" tablspace.

              if you want to create the tables in specific tablespace then need to create objects as belwo.
              create table T(i number) tablespace ERPLN;

              then the tables will go into ERPLN tablespace. if you have Quota to ERPLN tablespace.




              Regards,
              Naseer.

              Edited by: 969327 on Nov 3, 2012 9:56 AM
              • 4. Re: Users Tablespaces are Changing
                Aman....
                885756 wrote:
                Hi all,

                In my Oracle Server System 11g, the normal users which are created by me for table storing purpose are changing to other Tablespaces which are created at the time Oracle Database Configuration..

                please give reasons & why it causes?
                It can't happen on its own. If you have mentioned a default tablespace for the user, it won't change on its own. So did you try to check that someone has explicitly issued any alter user command or not?

                Aman....
                • 5. Re: Users Tablespaces are Changing
                  Paulie
                  >


                  Hi, and welcome to the forums.


                  >
                  In my Oracle Server System 11g, the normal users which are created by me for
                  table storing purpose are changing to other Tablespaces which are created at
                  the time Oracle Database Configuration..
                  please give reasons & why it causes?
                  How did you create your Oracle instance?

                  Do you manually create users or do you use a tool? If you use tool, check the defaults.

                  Also, as 969327 wrote, if a tablespace does not exist, it is created in System, which is
                  not a good idea.

                  HTH,


                  Paul...
                  • 6. Re: Users Tablespaces are Changing
                    Osama_Mustafa
                    No way this is happened by Oracle , You need to check if someone when create tables used default tablespace attr while creation phase .
                    • 7. Re: Users Tablespaces are Changing
                      Venkateshj
                      Hi all,

                      I Created my Oracle Database with DBCA which was available in Oracle Software.

                      Now i Created a Permanent Tablespace as VENKIPTS & Temporary Tablespace as VENKITMPTS. Then i created a user as venki and assigned a Permanent & Temporary Tablespace as VENKIPTS,VENKITMPTS & granted required privileges & roles. i will post data i.e table into this user daily. After few days i got a problem as that for my user venki the Permanent Tablespace & Temporary Tablespace are changed to USERS01.DBF Permannet Tablespace & Temporary Tablespace to TEMP.

                      If any one need screen shot please give ur mail id i'l send a mail of that screen shot. so that u people wil clearly understand the problem.

                      http://us.mc1111.mail.yahoo.com/mc/welcome?.gx=1&.tm=1352106560&.rand=8vmp2uiupr5kg#_pg=showMessage&sMid=0&fid=Sent&filterBy=&.rand=1639769662&midIndex=0&mid=2_0_0_2_100190_ANUPw0MAAIbJUJeDAw5Q8S%2FZv9I&f=1&fromId=jvenkiit@yahoo.com&m=2_0_0_2_100190_ANUPw0MAAIbJUJeDAw5Q8S%2FZv9I,2_0_0_2_278_ANsPw0MAAA4tUEsYlgnd5iKJ4Lc,2_0_0_2_943_AMMPw0MAAW0vUEAJQwsda2tEwrI,2_0_0_2_1631_AKsPw0MAAPbxUD93Sg5pbjlVMgk,2_0_0_2_2310_AKsPw0MAAFQZUD4p9AlNUihBYoQ,2_0_0_2_2989_AIEPw0MAAT74UD4p3wNUORbn07o,&sort=date&order=down&startMid=0&hash=cea09c215d3689769922eca56ddd6adb&.jsrand=6215541

                      Thanks in advance,
                      VENKATESH J.

                      Edited by: 885756 on Nov 5, 2012 2:38 PM
                      • 8. Re: Users Tablespaces are Changing
                        JohnWatson
                        What you are describing does not make sense: USERS01.DBF is not a legal name for tablespace. If you post the result of these queries, it might help with diagnosis:
                        select segment_name,tablespace_name from dba_Segments where owner='VENKI';
                        select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='VENKI';

                        Edited by: JohnWatson on Nov 5, 2012 9:20 AM
                        And one more,
                        select property_name,property_value from database_properties where property_name like '%SPACE';
                        • 9. Re: Users Tablespaces are Changing
                          Venkateshj
                          please send ur mail id
                          • 10. Re: Users Tablespaces are Changing
                            JohnWatson
                            885756 wrote:
                            please send ur mail id
                            Please confirm that you wish a quotation for consultancy.
                            • 11. Re: Users Tablespaces are Changing
                              Chanchal Wankhade
                              Hi,

                              1) When you create a database that you specify the temporary,undo etc... tablespace for database.

                              2) When you create a user, Generally, before creating user we create tablespace for storing objects created by new user.
                              As create tablespace table_name as 'path_for_data_file' size and extend...
                              3) new while creating user we create we create it as
                              create user user_name identified by password default tablespace table_created_now.
                              Now whatever object's created by this new user will be store in new tablespace.

                              now if you provide tablespace name while creating object like
                              create table table_name tablespace tablespace_name;
                              then only it will go to the tablespace in which you have provided while creating table.
                              • 12. Re: Users Tablespaces are Changing
                                jgarry
                                You really shouldn't use yahoo mail to try to post a screen shot. Cut and paste what you enter and see from a command line, then surround it with
                                 tags so we can read it.                                                                                                                                                                                                                                                                                                                                                                    
                                • 13. Re: Users Tablespaces are Changing
                                  Venkateshj
                                  Hi all,

                                  Iam Creating Both Permanent & temporary tablespaces with Memory Extents , after that am creating a user & assigning permanent & Temporary tablespace for that user.

                                  For Example:-

                                  I created Permannet Tablespace as VenkiPTS & Temporary Tablespace as Venkitmpts. Then Creating a user as Venki & assigning Storage tablespaces as Venkipts to permanent & Temporary Tablespaces as Venkitmpts to that user.

                                  After getting data into the tablespace i.e permanent tablespace Venkipts to 75-95% of full the assigned tablespaces are changing to some other tablespaces by automatically as System tablespace for permanent & Temp tablespace for Temporary tablespace.

                                  I seen changes Occurred by the help of Enterprise Manager Console.

                                  Am not understanding why it is automatically changing.. please give a solution for this scenario & in which case it is changing automatically.. i got this problem in Oracle 10g & 11g.


                                  Thanks in advance..
                                  Venkatesh J.
                                  • 14. Re: Users Tablespaces are Changing
                                    EdStevens
                                    Venkateshj wrote:
                                    Hi all,

                                    Iam Creating Both Permanent & temporary tablespaces with Memory Extents , after that am creating a user & assigning permanent & Temporary tablespace for that user.

                                    For Example:-

                                    I created Permannet Tablespace as VenkiPTS & Temporary Tablespace as Venkitmpts. Then Creating a user as Venki & assigning Storage tablespaces as Venkipts to permanent & Temporary Tablespaces as Venkitmpts to that user.

                                    After getting data into the tablespace i.e permanent tablespace Venkipts to 75-95% of full the assigned tablespaces are changing to some other tablespaces by automatically as System tablespace for permanent & Temp tablespace for Temporary tablespace.

                                    I seen changes Occurred by the help of Enterprise Manager Console.

                                    Am not understanding why it is automatically changing.. please give a solution for this scenario & in which case it is changing automatically.. i got this problem in Oracle 10g & 11g.


                                    Thanks in advance..
                                    Venkatesh J.
                                    Such a change cannot happen automatically. Please present proof. Use sqlplus to run the queries that show exactly what is happening and copy and paste the results back to this forum.
                                    select username
                                    , default_tablespace
                                    , tempororay_tablespace 
                                    from dba_users
                                    where username = 'VENKI'
                                    ;
                                    --
                                    select tablespace_name
                                    , segment_name
                                    from dba_segments
                                    where owner='VENKI'
                                    order by tablespace_name
                                    , segment_name
                                    ;
                                    1 2 Previous Next