1 2 Previous Next 28 Replies Latest reply: Aug 5, 2013 11:11 PM by Venkateshj Go to original post RSS
      • 15. Re: Users Tablespaces are Changing
        Aman....
        Well, rather than claiming that it is happening, how about showing us a sample ? Why don't you build a test case and show us here? As said by others and me repeatedly, such thing can't happen on its own.

        Aman....
        • 16. Re: Users Tablespaces are Changing
          Venkateshj
          Please give ur mail id i'l send screen shots which was taken by me..

          Venkatesh J.
          • 17. Re: Users Tablespaces are Changing
            Venkateshj
            It happens in oracle 11g.. i have screen shots images of it...check the below link
            http://www.sendspace.com/file/6qm4gd
            • 18. Re: Users Tablespaces are Changing
              EdStevens
              Venkateshj wrote:
              It happens in oracle 11g.. i have screen shots images of it...check the below link
              http://www.sendspace.com/file/6qm4gd
              Forget screen shots. We don't want to have go to some third site, which is often blocked by our respective company's firewalls. Learn to use a command prompt so you can copy and paste pure text.
              • 19. Re: Users Tablespaces are Changing
                Aman....
                And why should I? Why can't you do a test case that shows us that it is really happening?

                Aman....
                • 20. Re: Users Tablespaces are Changing
                  Venkateshj
                  ok Let me Check by query..

                  Edited by: Venkateshj on Dec 10, 2012 4:40 PM
                  • 21. Re: Users Tablespaces are Changing
                    Venkateshj
                    SQL> select default_tablespace,username,temporary_tablespace from dba_users;

                    DEFAULT_TABLESPACE USERNAME
                    ------------------------------ ------------------------------
                    TEMPORARY_TABLESPACE
                    --------------------
                    KPTS KP
                    KPTEMP

                    KIRMLBKPTBS SPATIAL_WFS_ADMIN_USR
                    KIRMLBKPTMPTBS

                    KIRMLBKPTBS SPATIAL_CSW_ADMIN_USR
                    KIRMLBKPTMPTBS


                    DEFAULT_TABLESPACE USERNAME
                    ------------------------------ ------------------------------
                    TEMPORARY_TABLESPACE
                    --------------------
                    KIRMLBKPTBS HR
                    KIRMLBKPTMPTBS

                    KIRMLBKPTBS APEX_PUBLIC_USER
                    KIRMLBKPTMPTBS

                    KIRMLBKPTBS OE
                    KIRMLBKPTMPTBS


                    DEFAULT_TABLESPACE USERNAME
                    ------------------------------ ------------------------------
                    TEMPORARY_TABLESPACE
                    --------------------
                    KIRMLBKPTBS DIP
                    KIRMLBKPTMPTBS

                    KIRMLBKPTBS SH
                    KIRMLBKPTMPTBS

                    KIRMLBKPTBS IX
                    KIRMLBKPTMPTBS


                    DEFAULT_TABLESPACE USERNAME
                    ------------------------------ ------------------------------
                    TEMPORARY_TABLESPACE
                    --------------------
                    KIRMLBKPTBS MDDATA
                    KIRMLBKPTMPTBS

                    KIRMLBKPTBS PM
                    KIRMLBKPTMPTBS

                    KIRMLBKPTBS BI
                    KIRMLBKPTMPTBS


                    DEFAULT_TABLESPACE USERNAME
                    ------------------------------ ------------------------------
                    TEMPORARY_TABLESPACE
                    --------------------
                    KIRMLBKPTBS XS$NULL
                    KIRMLBKPTMPTBS

                    KIRMLBKPTBS ORACLE_OCM
                    KIRMLBKPTMPTBS

                    KIRMLBKPTBS SCOTT
                    KIRMLBKPTMPTBS


                    DEFAULT_TABLESPACE USERNAME
                    ------------------------------ ------------------------------
                    TEMPORARY_TABLESPACE
                    --------------------
                    KIRMLBKPTBS BKPKIRML
                    KIRMLBKPTMPTBS

                    KIRMLBKPTBS KIRML
                    KIRMLBKPTMPTBS

                    SYSAUX OLAPSYS
                    KIRMLBKPTMPTBS


                    DEFAULT_TABLESPACE USERNAME
                    ------------------------------ ------------------------------
                    TEMPORARY_TABLESPACE
                    --------------------
                    SYSAUX SI_INFORMTN_SCHEMA
                    KIRMLBKPTMPTBS

                    SYSAUX OWBSYS
                    KIRMLBKPTMPTBS

                    SYSAUX ORDPLUGINS
                    KIRMLBKPTMPTBS


                    DEFAULT_TABLESPACE USERNAME
                    ------------------------------ ------------------------------
                    TEMPORARY_TABLESPACE
                    --------------------
                    SYSAUX XDB
                    KIRMLBKPTMPTBS

                    SYSAUX ANONYMOUS
                    KIRMLBKPTMPTBS

                    SYSAUX CTXSYS
                    KIRMLBKPTMPTBS


                    DEFAULT_TABLESPACE USERNAME
                    ------------------------------ ------------------------------
                    TEMPORARY_TABLESPACE
                    --------------------
                    SYSAUX ORDDATA
                    KIRMLBKPTMPTBS

                    SYSAUX OWBSYS_AUDIT
                    KIRMLBKPTMPTBS

                    SYSAUX APEX_030200
                    KIRMLBKPTMPTBS


                    DEFAULT_TABLESPACE USERNAME
                    ------------------------------ ------------------------------
                    TEMPORARY_TABLESPACE
                    --------------------
                    SYSAUX APPQOSSYS
                    KIRMLBKPTMPTBS

                    SYSAUX WMSYS
                    KIRMLBKPTMPTBS

                    SYSAUX EXFSYS
                    KIRMLBKPTMPTBS


                    DEFAULT_TABLESPACE USERNAME
                    ------------------------------ ------------------------------
                    TEMPORARY_TABLESPACE
                    --------------------
                    SYSAUX ORDSYS
                    KIRMLBKPTMPTBS

                    SYSAUX MDSYS
                    KIRMLBKPTMPTBS

                    SYSAUX FLOWS_FILES
                    KIRMLBKPTMPTBS


                    DEFAULT_TABLESPACE USERNAME
                    ------------------------------ ------------------------------
                    TEMPORARY_TABLESPACE
                    --------------------
                    SYSAUX SYSMAN
                    KIRMLBKPTMPTBS

                    SYSAUX DBSNMP
                    KIRMLBKPTMPTBS

                    SYSTEM OUTLN
                    KIRMLBKPTMPTBS


                    DEFAULT_TABLESPACE USERNAME
                    ------------------------------ ------------------------------
                    TEMPORARY_TABLESPACE
                    --------------------
                    SYSTEM SYSTEM
                    KIRMLBKPTMPTBS

                    SYSTEM SYS
                    KIRMLBKPTMPTBS

                    SYSTEM MGMT_VIEW
                    KIRMLBKPTMPTBS


                    39 rows selected.

                    SQL> spool off



                    Please see the above.. in this it is changing users allocated tablespaces by automatically....

                    Edited by: Venkateshj on Dec 17, 2012 4:30 PM
                    • 22. Re: Users Tablespaces are Changing
                      jgarry
                      It's still hard to say, as you are not showing us the output just after you created, and after it changes. Also, please put the tag
                       before and after your output when you post, then use the preview tab to see that it looks better.
                      
                      Wildly guessing without the above, there might be a couple of things:  You could be quoting the default tablespace with lowercase letters, while they were created all uppercase (or vice versa).  Or you are doing some kind of export import that is specifying something wrong.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                      • 23. Re: Users Tablespaces are Changing
                        Venkateshj
                        Hi sir..

                        am not getting you...please tell clearly...
                        • 24. Re: Users Tablespaces are Changing
                          JohnWatson
                          This topic is driving me insane. Everyone has told you that default and temporary tablespaces will NEVER change unless you, or someone with similar privileges, change them. Six weeks ago I gave you the commands to run to show the defaults for the database, and the actual for each user. Run those commands, right now. Then wait a few days, and run them again. See if anything has changed. FOr the second time, here are the queries:
                          select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;
                          select property_name,property_value from database_properties where property_name like '%SPACE';
                          (and I notice thbat you have said "thank you" to anyone yet :) )
                          • 25. Re: Users Tablespaces are Changing
                            Venkateshj
                            SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 18 17:03:36 2012

                            Copyright (c) 1982, 2010, Oracle. All rights reserved.

                            Enter user-name: / as sysdba

                            Connected to:
                            Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

                            SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;

                            USERNAME DEFAULT_TABLESPACE
                            ------------------------------ ------------------------------
                            TEMPORARY_TABLESPACE
                            ------------------------------
                            KP KPTS
                            KPTEMP

                            SPATIAL_WFS_ADMIN_USR KIRMLBKPTBS
                            KIRMLBKPTMPTBS

                            SPATIAL_CSW_ADMIN_USR KIRMLBKPTBS
                            KIRMLBKPTMPTBS


                            USERNAME DEFAULT_TABLESPACE
                            ------------------------------ ------------------------------
                            TEMPORARY_TABLESPACE
                            ------------------------------
                            HR KIRMLBKPTBS
                            KIRMLBKPTMPTBS

                            APEX_PUBLIC_USER KIRMLBKPTBS
                            KIRMLBKPTMPTBS

                            OE KIRMLBKPTBS
                            KIRMLBKPTMPTBS


                            USERNAME DEFAULT_TABLESPACE
                            ------------------------------ ------------------------------
                            TEMPORARY_TABLESPACE
                            ------------------------------
                            DIP KIRMLBKPTBS
                            KIRMLBKPTMPTBS

                            SH KIRMLBKPTBS
                            KIRMLBKPTMPTBS

                            IX KIRMLBKPTBS
                            KIRMLBKPTMPTBS


                            USERNAME DEFAULT_TABLESPACE
                            ------------------------------ ------------------------------
                            TEMPORARY_TABLESPACE
                            ------------------------------
                            MDDATA KIRMLBKPTBS
                            KIRMLBKPTMPTBS

                            PM KIRMLBKPTBS
                            KIRMLBKPTMPTBS

                            BI KIRMLBKPTBS
                            KIRMLBKPTMPTBS


                            USERNAME DEFAULT_TABLESPACE
                            ------------------------------ ------------------------------
                            TEMPORARY_TABLESPACE
                            ------------------------------
                            XS$NULL KIRMLBKPTBS
                            KIRMLBKPTMPTBS

                            ORACLE_OCM KIRMLBKPTBS
                            KIRMLBKPTMPTBS

                            SCOTT KIRMLBKPTBS
                            KIRMLBKPTMPTBS


                            USERNAME DEFAULT_TABLESPACE
                            ------------------------------ ------------------------------
                            TEMPORARY_TABLESPACE
                            ------------------------------
                            BKPKIRML KIRMLBKPTBS
                            KIRMLBKPTMPTBS

                            KIRML KIRMLBKPTBS
                            KIRMLBKPTMPTBS

                            OLAPSYS SYSAUX
                            KIRMLBKPTMPTBS


                            USERNAME DEFAULT_TABLESPACE
                            ------------------------------ ------------------------------
                            TEMPORARY_TABLESPACE
                            ------------------------------
                            SI_INFORMTN_SCHEMA SYSAUX
                            KIRMLBKPTMPTBS

                            OWBSYS SYSAUX
                            KIRMLBKPTMPTBS

                            ORDPLUGINS SYSAUX
                            KIRMLBKPTMPTBS


                            USERNAME DEFAULT_TABLESPACE
                            ------------------------------ ------------------------------
                            TEMPORARY_TABLESPACE
                            ------------------------------
                            XDB SYSAUX
                            KIRMLBKPTMPTBS

                            ANONYMOUS SYSAUX
                            KIRMLBKPTMPTBS

                            CTXSYS SYSAUX
                            KIRMLBKPTMPTBS


                            USERNAME DEFAULT_TABLESPACE
                            ------------------------------ ------------------------------
                            TEMPORARY_TABLESPACE
                            ------------------------------
                            ORDDATA SYSAUX
                            KIRMLBKPTMPTBS

                            OWBSYS_AUDIT SYSAUX
                            KIRMLBKPTMPTBS

                            APEX_030200 SYSAUX
                            KIRMLBKPTMPTBS


                            USERNAME DEFAULT_TABLESPACE
                            ------------------------------ ------------------------------
                            TEMPORARY_TABLESPACE
                            ------------------------------
                            APPQOSSYS SYSAUX
                            KIRMLBKPTMPTBS

                            WMSYS SYSAUX
                            KIRMLBKPTMPTBS

                            EXFSYS SYSAUX
                            KIRMLBKPTMPTBS


                            USERNAME DEFAULT_TABLESPACE
                            ------------------------------ ------------------------------
                            TEMPORARY_TABLESPACE
                            ------------------------------
                            ORDSYS SYSAUX
                            KIRMLBKPTMPTBS

                            MDSYS SYSAUX
                            KIRMLBKPTMPTBS

                            FLOWS_FILES SYSAUX
                            KIRMLBKPTMPTBS


                            USERNAME DEFAULT_TABLESPACE
                            ------------------------------ ------------------------------
                            TEMPORARY_TABLESPACE
                            ------------------------------
                            SYSMAN SYSAUX
                            KIRMLBKPTMPTBS

                            DBSNMP SYSAUX
                            KIRMLBKPTMPTBS

                            OUTLN SYSTEM
                            KIRMLBKPTMPTBS


                            USERNAME DEFAULT_TABLESPACE
                            ------------------------------ ------------------------------
                            TEMPORARY_TABLESPACE
                            ------------------------------
                            SYSTEM SYSTEM
                            KIRMLBKPTMPTBS

                            SYS SYSTEM
                            KIRMLBKPTMPTBS

                            MGMT_VIEW SYSTEM
                            KIRMLBKPTMPTBS


                            39 rows selected.

                            SQL> select property_name,property_value from database_properties where property
                            _name like '%SPACE';

                            PROPERTY_NAME
                            ------------------------------
                            PROPERTY_VALUE
                            --------------------------------------------------------------------------------

                            DEFAULT_TEMP_TABLESPACE
                            KIRMLBKPTMPTBS

                            DEFAULT_PERMANENT_TABLESPACE
                            KIRMLBKPTBS


                            SQL>





                            Sir actually for SYS user the default tablespace is SYSTEM.DBF & temporary tablespace is TEMP.DBF file should be there but it is changed automatically to KIRMLBKPTMPTBS ...Not only SYS all the users are changed to KIRMLBKPTMPTBS.... check it sir..


                            Thank u sir:-)
                            • 26. Re: Users Tablespaces are Changing
                              JohnWatson
                              Your query shows this,
                               USERNAME DEFAULT_TABLESPACE
                              ------------------------------
                              TEMPORARY_TABLESPACE
                              SYSTEM SYSTEM
                              KIRMLBKPTMPTBS
                              
                              SYS SYSTEM
                              KIRMLBKPTMPTBS
                              but you say this
                              Sir actually for SYS user the default tablespace is SYSTEM.DBF & temporary tablespace is TEMP.DBF
                              and you are still confusing datafiles with tablespaces (which I told you about 6 weeks ago).

                              I give up. Perhaps someone else with mor patience will assist.
                              • 27. Re: Users Tablespaces are Changing
                                Mark Malakanov (user11181920)
                                1. Learn how to use
                                 tag to make your posts conveniently readable. Posting them hardly readable you show your disrespect to people you asking help from.
                                
                                2. The fact that  tablespaces changed for user(s) tells that *someone* or *some script* did it. As it said by many people here, Oracle itself does not change default tablespaces *automatically*.
                                You can investigate this by running LogMiner for the period this change happened and searching for DDLs in V$LOGMNR_CONTENTS. It will show when the DDL(s) done. And may be, if LogMiner period covers that session start it will show OS username and machine that issued DDL(s). If not, try to extend period to past. Make sure you have all required archived logs in place.
                                
                                Edited by: user11181920 on Dec 18, 2012 10:35 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                • 28. Re: Users Tablespaces are Changing
                                  Venkateshj
                                  user11181920 wrote:
                                  1. Learn how to use
                                   tag to make your posts conveniently readable. Posting them hardly readable you show your disrespect to people you asking help from. 2. The fact that  tablespaces changed for user(s) tells that *someone* or *some script* did it. As it said by many people here, Oracle itself does not change default tablespaces *automatically*. You can investigate this by running LogMiner for the period this change happened and searching for DDLs in V$LOGMNR_CONTENTS. It will show when the DDL(s) done. And may be, if LogMiner period covers that session start it will show OS username and machine that issued DDL(s). If not, try to extend period to past. Make sure you have all required archived logs in place. Edited by: user11181920 on Dec 18, 2012 10:35 AM
                                  Ok sir..
                                  i got to know why it is changing defaultly...In Enterprise manager Console while creating tablespace one of my colleague checking the tablespace as default option ..so it is changing...

                                  Thank u sir..
                                  1 2 Previous Next